模拟MySQL的show grants命令
首先创建用户,并且将示例帐号的表授权给他。
作为DBA帐号登录,查看他的权限。
作为普通用户登录,查看权限
首先创建用户,并且将示例帐号的表授权给他。
- 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;
- 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;
![](http://img.blog.itpub.net/blog/attachment/201407/14/29254281_1405306780Ll8b.jpg?x-oss-process=style/bb)
作为普通用户登录,查看权限
- 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;
![](http://img.blog.itpub.net/blog/attachment/201407/14/29254281_1405307132jshJ.jpg?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-1218238/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-1218238/