使用sys用户登录
select * from dba_users; 查询数据库中的所有用户
select * from dba_roles; 查询数据库中的所有角色
select * from dba_sys_privs ; 查询数据库中的所有用户和角色的权限
select * from dba_role_privs; 查询数据库中所有用户的角色
select * from dba_tab_privs; 查询数据库中所有用户的表权限
普通用户登录
select * from user_sys_privs; 用户的拥有的系统权限
查询用户拥有的角色权限
select * from (select distinct connect_by_root grantee username,granted_role
from dba_role_privs
connect by prior granted_role =grantee ) a
where exists (select 1 from dba_users b where b.username=a.username)
order by 1,2
;
查询数据库所有用户的系统权限
select d.username,d.privilege from
(select a.username,b.privilege from
(select distinct connect_by_root grantee username,granted_role
from dba_role_privs
connect by prior granted_role =grantee) a,
( select grantee,privilege from dba_sys_privs) b
where a.granted_role=b.grantee
union
select grantee,privilege from dba_sys_privs) d
where exists((select 1 from dba_users c where d.username=c.username))
order by 1,2;
查看数据库所有用户的表权限
SQL> select d.username,d.privilege,d.owner,d.table_name from
(select a.username,b.privilege,b.owner,b.table_name from
(select distinct connect_by_root grantee username,granted_role
from dba_role_privs
connect by prior granted_role =grantee) a,
(select grantee,owner,table_name,privilege from dba_tab_privs) b
where a.granted_role=b.grantee
union
select grantee,privilege,owner,table_name from dba_tab_privs) d
where exists((select 1 from dba_users c where d.username=c.username))
order by 1,2;
查看用户拥有的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';
grant dba to panie; 将角色dba赋给用户panie
revoke dba from panie; 将角色dba从用户panie移除
ALTER USER panie DEFAULT ROLE ALL; 修改panie的默认角色
create role nie identified by nie; 创建角色
set role nie identified by 1111; 使角色生效
select * from dba_session;