Oracle檢視使用者許可權

dbasdk發表於2014-07-16
Oracle
模擬MySQL的show grants命令

首先建立使用者,並且將示例帳號的表授權給他。

  1. sqlplus / as sysdba

  3. drop user edmond cascade;
  4. create user edmond identified by edmond;
  5. grant connect,resource to edmond;
  6. grant select,insert,update,delete on hr.employees to edmond;
  7. grant update(department_id),insert(department_name,department_id) on hr.departments to edmond;
作為DBA帳號登入,檢視他的許可權。

  1. set linesize 200;
  2. col privs_type format a10;
  3. col username format a20;
  4. col table_name format a35;
  5. col column_name format a25;
  6. col PRIVILEGE format a60;
  7. with t1 as
  8. (
  9.     select upper('edmond') username from dual
  10. )
  11. select '角色' privs_type,'NULL' username,'NULL' table_name,'NULL' column_name,wm_concat(GRANTED_ROLE) PRIVILEGE from dba_role_privs,t1 where GRANTEE=t1.username group by '角色','NULL','NULL','NULL'
  12. union all
  13. select '表許可權',owner,TABLE_NAME,'NULL',wm_concat(PRIVILEGE) PRIVILEGE from dba_tab_privs,t1 where GRANTEE=t1.username group by '表許可權',owner,TABLE_NAME,'NULL'
  14. union all
  15. select '列許可權',owner,TABLE_NAME,column_name,wm_concat(PRIVILEGE) PRIVILEGE from dba_col_privs,t1 where GRANTEE=t1.username group by '列許可權',owner,TABLE_NAME,column_name;


作為普通使用者登入,檢視許可權

  1. set linesize 200;
  2. col privs_type format a10;
  3. col username format a20;
  4. col table_name format a35;
  5. col column_name format a25;
  6. col PRIVILEGE format a60;
  7. with t1 as
  8. (
  9.     select upper('edmond') username from dual
  10. )
  11. select '表許可權' privs_type,TABLE_SCHEMA username,TABLE_NAME,'NULL' column_name,wm_concat(PRIVILEGE) PRIVILEGE from all_tab_privs,t1 where GRANTEE=t1.username group by '表許可權',TABLE_SCHEMA,TABLE_NAME,'NULL'
  12. union all
  13. select '列許可權',TABLE_SCHEMA,TABLE_NAME,column_name,wm_concat(PRIVILEGE) PRIVILEGE from all_col_privs,t1 where GRANTEE=t1.username group by '列許可權',TABLE_SCHEMA,TABLE_NAME,column_name;




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29734436/viewspace-1220138/，如需轉載，請註明出處，否則將追究法律責任。

相關文章