模拟MySQL的show grants命令
首先创建用户,并且将示例帐号的表授权给他。
- sqlplus / as sysdba
- drop user edmond cascade;
- create user edmond identified by edmond;
- grant connect,resource to edmond;
- grant select,insert,update,delete on hr.employees to edmond;
- grant update(department_id),insert(department_name,department_id) on hr.departments to edmond;
作为DBA帐号登录,查看他的权限。
- set linesize 200;
- col privs_type format a10;
- col username format a20;
- col table_name format a35;
- col column_name format a25;
- col PRIVILEGE format a60;
- with t1 as
- (
- select upper('edmond') username from dual
- )
- 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'
- union all
- select '表权限',owner,TABLE_NAME,'NULL',wm_concat(PRIVILEGE) PRIVILEGE from dba_tab_privs,t1 where GRANTEE=t1.username group by '表权限',owner,TABLE_NAME,'NULL'
- union all
- 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;
作为普通用户登录,查看权限
- set linesize 200;
- col privs_type format a10;
- col username format a20;
- col table_name format a35;
- col column_name format a25;
- col PRIVILEGE format a60;
- with t1 as
- (
- select upper('edmond') username from dual
- )
- 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'
- union all
- 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;
- 本文转自ICT时空dbasdk的博客,原文链接:Oracle查看用户权限 ,如需转载请自行联系原博主。