如何獲得某個用戶對某個對象的訪問權限
2024-07-21 02:33:48
供稿：網友
與權限相關的數據字典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_PRIVSDBA_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 獲得用戶的對象權限scriptUT1> 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獲得丟失的授權腳本。
