查询oracle用户角色权限

1.查看所有用户：
select*fromdba_users;
select*fromall_users;
select*fromuser_users;
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
	select*fromdba_sys_privs;
select*fromuser_sys_privs;
SQL>select*fromuser_sys_privs;

USERNAMEPRIVILEGEADM
-------------------------------------------------------------------------
ZDZQUNLIMITEDTABLESPACENO
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限

sql>select*fromrole_sys_privs;

4.查看用户对象权限：
select*fromdba_tab_privs;
select*fromall_tab_privs;
select*fromuser_tab_privs;
5.查看所有角色：
select*fromdba_roles
6.查看用户或角色所拥有的角色：
select*fromdba_role_privs;
select*fromuser_role_privs;
--查询拥有DBA权限的用户
SQL>select*fromdba_role_privswheregranted_role='DBA';

GRANTEEGRANTED_ROLEADMDEF
------------------------------------------------------------------
SYSDBAYESYES
SYSTEMDBAYESYES
ZSZQDBANOYES
KSWORK

7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select*fromV$PWFILE_USERS
比如我要查看用户wzsb的拥有的角色：


SQL>select*fromdba_sys_privswheregrantee='ZSZQ';

GRANTEEPRIVILEGEADM
-------------------------------------------------------------------------
ZSZQUNLIMITEDTABLESPACENO

查看一个用户所有的权限及角色
selectprivilege
fromdba_sys_privs
wheregrantee='ZSZQ'
union
selectprivilege
fromdba_sys_privs
wheregranteein
(selectgranted_rolefromdba_role_privswheregrantee='ZSZQ');


SQL>select*fromdba_sys_privswheregrantee='ZSZQ';

GRANTEEPRIVILEGEADM
-------------------------------------------------------------------------
ZSZQUNLIMITEDTABLESPACENO
8、查看RESOURCE具有那些权限
SQL>SELECT*FROMDBA_SYS_PRIVSWHEREGRANTEE='RESOURCE';
GRANTEEPRIVILEGEADM
-------------------------------------------------------------------------
RESOURCECREATETRIGGERNO
RESOURCECREATESEQUENCENO
RESOURCECREATETYPENO
RESOURCECREATEPROCEDURENO
RESOURCECREATECLUSTERNO
RESOURCECREATEOPERATORNO
RESOURCECREATEINDEXTYPENO
RESOURCECREATETABLENO

已选择8行。

SQL>select*fromrole_sys_privst1wheret1.role='RESOURCE';

ROLEPRIVILEGEADM
-------------------------------------------------------------------------
RESOURCECREATESEQUENCENO
RESOURCECREATETRIGGERNO
RESOURCECREATECLUSTERNO
RESOURCECREATEPROCEDURENO
RESOURCECREATETYPENO
RESOURCECREATEOPERATORNO
RESOURCECREATETABLENO
RESOURCECREATEINDEXTYPENO

已选择8行。

9.查看scott用户的默认表空间、临时表空间
selectusername,default_tablespace,temporary_tablespace
fromdba_users
whereusername='SCOTT';
10.查看scott用户的系统权限
selectusername,privilege,admin_option
fromuser_sys_privs
whereusername='SCOTT';

SQL>selectusername,admin_option
2fromuser_sys_privs
3whereusername='SCOTT';

USERNAMEPRIVILEGEADM
-------------------------------------------------------------------------
SCOTTCREATEVIEWNO
SCOTTUNLIMITEDTABLESPACENO

11.查看赋予scott用户的对象权限
selectgrantee,owner,table_name,t.grantor,t.privilege,t.grantable,t.hierarchy
fromdba_tab_privst
wheret.grantee='SCOTT';
12.查看授予了scott的角色权限
selectt.grantee,t.granted_role,t.admin_option,t.default_role
fromdba_role_privst
wheret.grantee='SCOTT';

SQL>selectt.grantee,t.default_role
2fromdba_role_privst
3wheret.grantee='SCOTT';

GRANTEEGRANTED_ROLEADMDEF
------------------------------------------------------------------
SCOTTRESOURCENOYES
SCOTTCONNECTNOYES

SQL>select*fromuser_role_privst;

USERNAMEGRANTED_ROLEADMDEFOS_
---------------------------------------------------------------------
ZSZQCONNECTNOYESNO
ZSZQDBANOYESNO
ZSZQEXP_FULL_DATABASENOYESNO
ZSZQIMP_FULL_DATABASENOYESNO
ZSZQRESOURCENOYESNO

13.查看scott用户使用了哪些表空间
selectt.table_name,t.tablespace_name
fromdba_all_tablest
wheret.owner='SCOTT';
14.查看当前用户拥有的权限
selectt.privilegefromsession_privst;

SQL>selectt.privilegefromsession_privst;

PRIVILEGE
----------------------------------------
CREATESESSION
UNLIMITEDTABLESPACE
CREATETABLE
CREATECLUSTER
CREATEVIEW
CREATESEQUENCE
CREATEPROCEDURE
CREATETRIGGER
CREATETYPE
CREATEOPERATOR
CREATEINDEXTYPE

已选择11行。

15.查看角色(DBA)被赋予的角色权限
select*fromrole_role_privstwheret.role='DBA';
查看角色(DBA)被赋予的对象权限
16.select*fromrole_tab_privst1wheret1.role='DBA';

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!