角色 RESOURCE、CONNECT、DBA具有的权限
select grantee,privilege from dba_sys_privs wheregrantee='RESOURCE' order by privilege;
select grantee,privilege from dba_sys_privs wheregrantee='CONNECT';
select grantee,privilege from dba_sys_privs wheregrantee='DBA';
查看某个用户具有的权限
select privilege from dba_sys_privs wheregrantee='ACCOUNT'
union
select privilege from dba_sys_privs where grantee in(select granted_role from dba_role_privs where grantee='ACCOUNT' );
SQL> select privilege from dba_sys_privs wheregrantee='ACCOUNT'
2 union
3 select privilege from dba_sys_privs wheregrantee in (select granted_role from dba_role_privs where grantee='ACCOUNT' );
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
UNLIMITED TABLESPACE
11 rows selected.
select privilege from dba_sys_privs wheregrantee='PLATFORM'
union
select privilege from dba_sys_privs where grantee in(select granted_role from dba_role_privs where grantee='PLATFORM' );
SQL> select privilege from dba_sys_privs wheregrantee='PLATFORM'
2 union
3 select privilege from dba_sys_privs where granteein (select granted_role from dba_role_privs where grantee='PLATFORM' );
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE
10 rows selected
1.查看所有用户:
select * fromdba_users;
select * fromall_users;
select * fromuser_users;
2.查看用户或角色系统权限:
select * fromdba_sys_privs;
select * fromuser_sys_privs;
3.查看用户对象权限:
select * from dba_tab_privs;
select * fromall_tab_privs;
select * fromuser_tab_privs;
4.查看所有角色:
select * fromdba_roles;
5.查看用户或角色所拥有的角色:
select * fromdba_role_privs;
select * fromuser_role_privs;
注意:
1、以下语句可以查看Oracle提供的系统权限
select name from sys.system_privilege_map
2、查看一个用户的所有系统权限(包含角色的系统权限)
select privilege from dba_sys_privs wheregrantee='DATAUSER'
union
select privilege from dba_sys_privs where grantee in(select granted_role from dba_role_privs where grantee='DATAUSER' );