与权限相关的数据字典
SQL> select * from dict where table_name like '%PRIVS' or table_name like '%ROLE%';
TABLE_NAME COMMENTS
-----------------------------------------------------------------------------------------------
ALL_TAB_PRIVS All object grants where the user or public is grantee
ALL_TAB_PRIVS_MADE All object grants made by user or on user owned objects
ALL_TAB_PRIVS_RECD All object grants to user or public
DBA_SYS_PRIVS System privileges granted to users and roles
DBA_ROLES List of all roles in the database
DBA_ROLE_PRIVS Roles granted to users and to other roles
ROLE_ROLE_PRIVS Roles granted to other roles
ROLE_SYS_PRIVS System privileges granted to roles
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS All privileges currently available to user
SESSION_ROLES All roles currently available to user
USER_SYS_PRIVS System privileges granted to current user
USER_TAB_PRIVS Grants on objects where current user is grantee, grantor, or owner
主要介绍***_TAB_PRIVS
DBA_TAB_PRIVS:
Name Null? Type
---------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30) <== Receiver of privilege
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30) <-- Giver of privilege
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3) <-- Grantee has ability to grant privilege to others
DBA_TAB_PRIVS 数据字典表纪录了所有数据库对象的授权情况，这些对象包括表，视图，存储过程等。利用这个视图可以生成数据库对象的权限脚本
Oracle数据库权限主要分为2类:对象访问权限(tables, indexes, views...) 和 系统权限(create session, create table, create user...). DBA_TAB_PRIVS 只记录 对象访问权限。
其他相关视图:
ALL_TAB_PRIVS All object grants where the user or public is grantee
ALL_TAB_PRIVS_MADE All object grants made by user or on user owned objects
ALL_TAB_PRIVS_RECD All object grants to user or public
DBA_SYS_PRIVS System privileges granted to users and roles
DBA_ROLES List of all roles in the database
DBA_ROLE_PRIVS Roles granted to users and to other roles
ROLE_ROLE_PRIVS Roles granted to other roles
ROLE_SYS_PRIVS System privileges granted to roles
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS All privileges currently available to user
SESSION_ROLES All roles currently available to user
USER_SYS_PRIVS System privileges granted to current user
USER_TAB_PRIVS Grants on objects where current user is grantee, grantor, or owner
获得用户的对象权限script
UT1> l
1 select grantee,
2 privilege,
3 grantable "Adm",
4 owner,
5 table_name
6 from sys.dba_tab_privs
7 where grantee = upper('&usernm')
8* order by grantee, owner, table_name, privilege
GRANTEE PRIVILEGE Adm OWNER TABLE_NAME
------------ ---------- --- ------------ -------------------------
SEFIN DELETE NO SYSTEM SRW_FIELD
INSERT NO SYSTEM SRW_FIELD
SELECT NO SYSTEM SRW_FIELD
UPDATE NO SYSTEM SRW_FIELD
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 Sample output:
grant INDEX on jit.wo_master to EDSJIT ;
grant INSERT on jit.wo_master to EDSJIT with grant option ;
grant REFERENCES on jit.wo_master to EDSJIT ;
grant SELECT on jit.wo_master to EDSJIT with grant option ;
当在开发数据库上丢失了对象权限的时候，可以在产品数据库上运行该script获得丢失的授权脚本。