角色 RESOURCE、CONNECT、DBA具有的权限

角色 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' );