Oracle查询所有DBA账号及其他账号 - 牧人池塘 - DBA杂谈录

2018-07-03

暂无评论

Oracle查询所有DBA账号及其他账号

查询所有DBA账号

select * from dba_role_privs where granted_role='DBA';

或者

select * from (select distinct connect_by_root grantee username,granted_role from dba_role_privs connect by prior granted_role =grantee ) a where a.granted_role='DBA';

查看所有用户：

select * from dba_users;
select * from all_users;
select * from user_users;

查看用户对象权限：

select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

查看所有角色：

select * from dba_roles;

查看用户或角色所拥有的角色：

select * from dba_role_privs;
select * from user_role_privs;

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

SQL> select * from V$PWFILE_USERS;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

查看RESOURCE具有那些权限

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO
 
已选择8行。

查看MDATA用户都有哪些表的权限

SELECT * FROM USER_TAB_PRIVS WHERE GRANTEE='MDATA'

查看角色(DBA)被赋予的角色权限

select * from role_role_privs t where t.role = 'DBA';

查看角色(DBA)被赋予的对象权限

select * from role_tab_privs t1  where t1.role = 'DBA';

Tag标签:「权限 dba oracle」更新时间:「2021-11-03 20:07:01」阅读次数:「984」

相关博文：