查询某个用户的权限的时候别忘记了PUBLIC权限

昨天收到封邮件，叫我帮他执行两条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.