用sql查询oracle用户,查询Oracle中用户的权限--好用的查询sql--转载

Oracle中用户的权限有几种分配方法：

1)通过grant system privilege直接授权给用户。

2)通过grant role分配role给用户，然后通过给role分配权限来间接的给用户分配权限。

3)通过grant object privilege给用户分配特定对象的比如：select,update..等权限。

要查一个用户的全部权限就要分别查上面的三种。

下面给出一个sql,一次性查处某个用户(SCOTT)的所有权限：

(为了说明效果，特意给scott分配了一些权限

SQL> grant select on sys.dba_users to scott;

授权成功。

SQL> grant select on v_$sysstat to scott;

授权成功。)

SQL> select

a.username,

2 'Granted role-> '||b.granted_role||

3 decode(admin_option,'YES','(With Admin Option)',null)

what_granted

4 from dba_users a,dba_role_privs b

5 where a.username = b.grantee

6 and a.username='SCOTT'

7 UNION

8 select a.username,

9 'Granted sys privilege-> '||b.privilege ||

decode(admin_option,'YES',

10 ' (With Admin Option)', null) what_granted

11 from dba_users a,dba_sys_privs b

12 where a.username = b.grantee

13 and a.username='SCOTT'

14 UNION

15 select a.username,

16 'Object privilege->

'||c.object_type||'-'||c.owner||'.'||b.table_name||'-'||

17 b.privilege||decode(grantable,'YES','(With Grant Option)',null)

what_granted

18 from sys.dba_users a,dba_tab_privs b,dba_objects c

19 where a.username = b.grantee

20 and b.table_name=c.object_name

21 and a.username='SCOTT';

USERNAME WHAT_GRANTED

------------------------------

--------------------------------------------------------

SCOTT Granted role-> CONNECT

SCOTT Granted role-> RESOURCE

SCOTT Granted sys privilege-> ALTER SESSION

SCOTT Granted sys privilege-> UNLIMITED TABLESPACE

SCOTT Object privilege->

SYNONYM-PUBLIC.DBA_USERS-SELECT

SCOTT Object privilege-> VIEW-SYS.DBA_USERS-SELECT

SCOTT Object privilege->

VIEW-SYS.V_$SYSSTAT-SELECT

已选择7行。

上面的查询在role那里只能查到用户被授予的role.稍微修改一下，就把用户的权限显示齐全了：SQL>

set pagesize 500

SQL> set linesize 200

SQL> select a.username,

2 'Inherit from

role:'||b.granted_role||'->'||c.privilege||

3 decode(b.admin_option,'YES','(With Admin Option)',null)

what_granted

4 from dba_users a,dba_role_privs b,dba_sys_privs c

5 where a.username = b.grantee

6 and c.grantee=b.granted_role

7 and a.username='SCOTT'

8 UNION

9 select a.username,

10 'Granted sys privilege-> '||b.privilege ||

decode(admin_option,'YES',

11 ' (With Admin Option)', null) what_granted

12 from dba_users a,dba_sys_privs b

13 where a.username = b.grantee

14 and a.username='SCOTT'

15 UNION

16 select a.username,

17 'Object privilege->

'||c.object_type||'-'||c.owner||'.'||b.table_name||'-'||

18 b.privilege||decode(grantable,'YES','(With Grant Option)',null)

what_granted

19 from sys.dba_users a,dba_tab_privs b,dba_objects c

20 where a.username = b.grantee

21 and b.table_name=c.object_name

22 and a.username='SCOTT';

USERNAME WHAT_GRANTED

------------------------------

-----------------------------------------------------

SCOTT Granted sys privilege-> ALTER SESSION

SCOTT Granted sys privilege-> UNLIMITED TABLESPACE

SCOTT Inherit from role:CONNECT->CREATE SESSION

SCOTT Inherit from role:RESOURCE->CREATE CLUSTER

SCOTT Inherit from role:RESOURCE->CREATE INDEXTYPE

SCOTT Inherit from role:RESOURCE->CREATE OPERATOR

SCOTT Inherit from role:RESOURCE->CREATE PROCEDURE

SCOTT Inherit from role:RESOURCE->CREATE SEQUENCE

SCOTT