首页 / 数据库 / MySQL / 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;

--------------------------------------------------------------------------------Linux-6-64下安装Oracle 12C笔记 http://www.linuxidc.com/Linux/2013-07/86805.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下 安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm--------------------------------------------------------------------------------更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址