查看用戶(hù)在某個(gè)對(duì)象上面的使用權(quán)限
2024-07-21 02:12:15
供稿：網(wǎng)友
查看用戶(hù)在某個(gè)對(duì)象上面的使用權(quán)限可以用數(shù)據(jù)字典表dba_tab_privs.表結(jié)構(gòu)如下:
name null? type
---------------------------- -------- ----------------------------
grantee not null varchar2(30) <== 權(quán)限獲得者
owner not null varchar2(30)
table_name not null varchar2(30)
grantor not null varchar2(30) <-- 權(quán)限授予者
privilege not null varchar2(40)
grantable varchar2(3) <-- 權(quán)限獲得者是否有權(quán)限授予別人權(quán)限
權(quán)限由命令grant授予由命令revoke收回:
grant select, insert, update, delete, references on my_table to user_joe ;
revoke insert, delete on my_table from user_joe ;
grant create public synonym to user_joe ;
其他相關(guān)權(quán)限安全的數(shù)據(jù)字典表有:
all_tab_privs
all_tab_privs_made
all_tab_privs_recd
dba_sys_privs
dba_roles
dba_role_privs
role_role_privs
role_sys_privs
role_tab_privs
session_privs
session_roles
user_sys_privs
user_tab_priv
在做完exp/imp后,權(quán)限需要重新授予時(shí)可用下面的腳本:
set echo off
rem
rem 19980729 m d powell new script.
rem
set verify off
set pagesize 0
set feedback off
spool grt_&&owner._&&table_name..sql
select 'rem grants on &&owner..&&table_name'
from sys.dual ;
select 'grant '||privilege||' on '||lower(owner)||'.'||
lower(table_name)||' to '||grantee||
decode(grantable,'yes',' with grant option',null)||
' ;'
from sys.dba_tab_privs
where owner = upper('&&owner')
and table_name = upper('&&table_name')
order by grantee, privilege ;
spool off
undefine owner
undefine table_name
