昨天收到封邮件，叫我帮他执行两条SQL语句，非常简单的两条insert 语句。其实我已经不是第一次收到这种类似的请求了。之后我通过OC询问他，问他有没有对表进行insert的权限，在他试过之后 他回答 ”有“。当时俺就郁闷了，为什么不自己动手试一试呢，什么我们DBA去做 ，真是 郁闷啊。
为此我专门写了一个 小脚本，检测某个用户对某个表 的 所有权限，包括系统权限，对象权限，角色里面的系统权限，对象权限
declare
name varchar2(100) := upper('&username');
tablename varchar2(100):= upper('&tablename');
cursor c_role is
select * from dba_role_privs where grantee = name;
cursor c_sys is
select * from dba_sys_privs where grantee = name;
cursor c_tab is
select * from dba_tab_privs where grantee = name;
cursor c_r_sys is
select *
from role_sys_privs
where role in
(select granted_role from dba_role_privs where grantee = name);
cursor c_r_tab is
select *
from role_tab_privs
where role in
(select granted_role from dba_role_privs where grantee = name) and table_name=tablename;
begin
dbms_output.enable(10000000);
dbms_output.put_line('-----------------------Look at here-------------------------------------------------');
dbms_output.put_line('Granted ROLE: ');
for v_role in c_role loop
if v_role.admin_option = 'NO' then
dbms_output.put_line(' ' || v_role.granted_role ||
' without ADMIN OPTION,');
else
dbms_output.put_line(' ' || v_role.granted_role ||
' with ADMIN OPTION,');
end if;
end loop;
dbms_output.put_line('-------------------------------------------------------------------------------------');
dbms_output.put_line('System Privilege: ');
for v_sys in c_sys loop
if v_sys.admin_option = 'NO' then
dbms_output.put_line(' ' || v_sys.privilege ||
' without ADMIN OPTION,');
else
dbms_output.put_line(' ' || v_sys.privilege ||
' with ADMIN OPTION,');
end if;
end loop;
dbms_output.put_line('-------------------------------------------------------------------------------------');
dbms_output.put_line('Object Privilege: ');
for v_tab in c_tab loop
if v_tab.grantable = 'NO' then
dbms_output.put_line(' ' || v_tab.privilege || ' on ' ||
v_tab.owner || '.' || v_tab.table_name ||
' without GRANT OPTION granted by ' ||
v_tab.grantor);
else
dbms_output.put_line(' ' || v_tab.privilege || ' on ' ||
v_tab.owner || '.' || v_tab.table_name ||
' with GRANT OPTION granted by ' ||
v_tab.grantor);
end if;
end loop;
dbms_output.put_line('-------------------------------------------------------------------------------------');
dbms_output.put_line('SYSTEM Privilege in Role:');
for v_r_sys in c_r_sys loop
if v_r_sys.admin_option = 'NO' then
dbms_output.put_line(' ' || v_r_sys.privilege ||
' without ADMIN OPTION in role ' ||
v_r_sys.role);
else
dbms_output.put_line(' ' || v_r_sys.privilege ||
' without ADMIN OPTION in role ' ||
v_r_sys.role);
end if;
end loop;
dbms_output.put_line('-------------------------------------------------------------------------------------');
dbms_output.put_line('Object Privilege in Role:');
for v_r_tab in c_r_tab loop
if v_r_tab.grantable = 'NO' then
dbms_output.put_line(' ' || v_r_tab.privilege ||
' on ' || v_r_tab.owner || '.' ||
v_r_tab.table_name || '.' ||
v_r_tab.column_name ||
' without GRANT OPTION in role ' ||
v_r_tab.role);
else
dbms_output.put_line(' ' || v_r_tab.privilege ||
' on ' || v_r_tab.owner || '.' ||
v_r_tab.table_name || '.' ||
v_r_tab.column_name ||
' without GRANT OPTION in role ' ||
v_r_tab.role);
end if;
end loop;
dbms_output.put_line('-------------------------------------------------------------------------------------');
end;
/
执行结果如下:
-----------------------Look at here-------------------------------------------------
Granted ROLE:
SH_SELECT_ROL1 without ADMIN OPTION,
IDWI_SELECT_ROL1 without ADMIN OPTION,
-------------------------------------------------------------------------------------
System Privilege:
CREATE SESSION without ADMIN OPTION,
-------------------------------------------------------------------------------------
Object Privilege:
-------------------------------------------------------------------------------------
SYSTEM Privilege in Role:
-------------------------------------------------------------------------------------
Object Privilege in Role:
SELECT on IDWBD3.SH30_CAL_DAY_DETL. without GRANT OPTION in role SH_SELECT_ROL1
SELECT on IDWBD3.SH30_CAL_DAY_DETL. without GRANT OPTION in role IDWI_SELECT_ROL1
-------------------------------------------------------------------------------------
这个时候我就郁闷了，他说他能进行INSERT，但是我看到的只是SELECT 权限。想了半天，终于发现了 忘记了PUBLIC 权限，于是赶紧查看
SQL> select * from dba_tab_privs where grantee='PUBLIC' and table_name='SH30_CAL_DAY_DETL' and privilege='INSERT';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
PUBLIC IDWBD3 SH30_CAL_DAY_DETL IDWBD3 INSERT
PUBLIC 果然有INSERT 权限，此次事件告诉我们，查询某个用户拥有的权限的时候别忘记了PUBLIC.