SQL> desc dba_sys_privs; 查询对象拥有的系统权限
Name Null? Type
---------------------------------------- -------- ---------------------------
GRANTEE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> select * from dba_sys_privs where grantee='HR';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
HR UNLIMITED TABLESPACE NO
HR CREATE SESSION NO
HR CREATE TABLE NO
SQL> desc dba_role_privs;
Name Null? Type
---------------------------------------- -------- ---------------------------
USERNAME VARCHAR2(30)
GRANTED_ROLE VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
OS_GRANTED VARCHAR2(3)
SQL> select * from dba_role_privs where grantee='HR';对象拥有的角色
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
HR RESOURCE NO YES
SQL> select * from role_sys_privs where ROLE='RESOURCE'; 通过角色查找权限
ROLE 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
SQL> select a.granted_role,b.privilege,c.privilege
From dba_role_privs a,role_sys_privs b ,dba_sys_privs c
Where a.granted_role=b.role and a.grantee=c.grantee and a.grantee='HR
RESOURCE CREATE SEQUENCE UNLIMITED TABLESPACE
RESOURCE CREATE INDEXTYPE CREATE SESSION
--看起来很美好,但是这是等值才返回结果,1=1=1 3*8=24t条记录
select a.granted_role,b.privilege,c.privilege from dba_role_privs
a join role_sys_privs b on a.granted_role=b.role join dba_sys_privs
c on a.grantee=c.grantee and a.grantee='HR'; --一样无法达到效果;
--a 通过用户查找拥有的角色
select * from dba_role_privs grantee granted_role
-b 通过角色查找拥有的权限
select * from role_sys_privs 1-2 role granted_role
-c 通过用户查找拥有的系统权限
select * from dba_sys_privs 1-3 grantee
--思路1+2=2 => 2+3=总
select * from (select a.grantee,b.privilege from
dba_role_privs a join role_sys_privs b on a.granted_role=b.role
union
select c.grantee,c.privilege from dba_sys_privs c)
where grantee='HR';
--于上一样--where条件执行速度更快
select a.grantee,b.privilege from
dba_role_privs a join role_sys_privs b
on a.granted_role=b.role where grantee='HR'
union
select c.grantee,c.privilege from dba_sys_privs c where grantee='HR';
意义:有些权限不是通过角色单独授予、或者单独授予权限:通过集合更好查找用户有啥权限
Name Null? Type
---------------------------------------- -------- ---------------------------
GRANTEE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> select * from dba_sys_privs where grantee='HR';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
HR UNLIMITED TABLESPACE NO
HR CREATE SESSION NO
HR CREATE TABLE NO
SQL> desc dba_role_privs;
Name Null? Type
---------------------------------------- -------- ---------------------------
USERNAME VARCHAR2(30)
GRANTED_ROLE VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
OS_GRANTED VARCHAR2(3)
SQL> select * from dba_role_privs where grantee='HR';对象拥有的角色
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
HR RESOURCE NO YES
SQL> select * from role_sys_privs where ROLE='RESOURCE'; 通过角色查找权限
ROLE 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
SQL> select a.granted_role,b.privilege,c.privilege
From dba_role_privs a,role_sys_privs b ,dba_sys_privs c
Where a.granted_role=b.role and a.grantee=c.grantee and a.grantee='HR
RESOURCE CREATE SEQUENCE UNLIMITED TABLESPACE
RESOURCE CREATE INDEXTYPE CREATE SESSION
--看起来很美好,但是这是等值才返回结果,1=1=1 3*8=24t条记录
select a.granted_role,b.privilege,c.privilege from dba_role_privs
a join role_sys_privs b on a.granted_role=b.role join dba_sys_privs
c on a.grantee=c.grantee and a.grantee='HR'; --一样无法达到效果;
--a 通过用户查找拥有的角色
select * from dba_role_privs grantee granted_role
-b 通过角色查找拥有的权限
select * from role_sys_privs 1-2 role granted_role
-c 通过用户查找拥有的系统权限
select * from dba_sys_privs 1-3 grantee
--思路1+2=2 => 2+3=总
select * from (select a.grantee,b.privilege from
dba_role_privs a join role_sys_privs b on a.granted_role=b.role
union
select c.grantee,c.privilege from dba_sys_privs c)
where grantee='HR';
--于上一样--where条件执行速度更快
select a.grantee,b.privilege from
dba_role_privs a join role_sys_privs b
on a.granted_role=b.role where grantee='HR'
union
select c.grantee,c.privilege from dba_sys_privs c where grantee='HR';
意义:有些权限不是通过角色单独授予、或者单独授予权限:通过集合更好查找用户有啥权限