oracle 用户权限

使用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;