查看用户在某个对象上面的使用权限可以用数据字典表DBA_TAB_PRIVS.表结构如下:
Name Null? Type
---------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30) <== 权限获得者
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30) <-- 权限授予者
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3) <-- 权限获得者是否有权限授予别人权限
权限由命令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 ;
其他相关权限安全的数据字典表有:
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后,权限需要重新授予时可用下面的脚本:
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
