Oracle中几个常见的用于查权限的视图
在Oracle中有很多用于查权限的视图，但很多人在需要查权限时会很困惑，不知道该用哪个视图去查，这里我列出几个常见的用于查权限的视图及其用法：
1. DBA_ROLE_PRIVS
该视图主要有以下2个作用：
1） 查某个user或role拥有哪些role：
select * from DBA_ROLE_PRIVS where GRANTEE='FIRGTRS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
FIRGTRS GTRS_DMM_UPDATE_ROLE NO YES
2） 查看某个role赋给了哪些user或role：
select * from DBA_ROLE_PRIVS where GRANTED_ROLE='GTRS_DMM_UPDATE_ROLE';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
GTRSOSA GTRS_DMM_UPDATE_ROLE NO YES
FIRGTRS GTRS_DMM_UPDATE_ROLE NO YES
GTRSSUP GTRS_DMM_UPDATE_ROLE NO YES
SYSTEM GTRS_DMM_UPDATE_ROLE YES YES
2. DBA_TAB_PRIVS
该视图的名字包含‘TAB’，且其中有一个column叫TABLE_NAME容易造成误解，其实该视图是用于查询在object上的权限，不仅仅table的权限。
select GRANTOR,GRANTEE,TABLE_NAME,PRIVILEGE from DBA_TAB_PRIVS where TABLE_NAME='PAYAGENT' order by GRANTEE;
GRANTOR GRANTEE TABLE_NAME PRIVILEGE
--------------- ------------------------------ ------------------------------ ---------------
GTRS DMM_ROLE PAYAGENT INSERT
GTRS DMM_ROLE PAYAGENT UPDATE
GTRS DMM_ROLE PAYAGENT DELETE
GTRS DMM_ROLE PAYAGENT SELECT
GTRS GTRS_DMM_READONLY_ROLE PAYAGENT SELECT
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT INSERT
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT DELETE
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT UPDATE
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT SELECT
GTRS GTRS_SUPPORT_READONLY_ROLE PAYAGENT SELECT
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT UPDATE
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT INSERT
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT DELETE
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT SELECT
GTRS SUPPORT_ROLE PAYAGENT SELECT
3. DBA_SYS_PRIVS
该视图用于查询某个user拥有哪些系统权限：
select * from DBA_SYS_PRIVS where GRANTEE='FIRGTRS';
GRANTEE PRIVILEGE ADM
------------------------------ --------------- ---
FIRGTRS CREATE SESSION NO
4. ROLE_SYS_PRIVS
该视图用于查询某个role拥有哪些系统权限：
select * from ROLE_SYS_PRIVS where ROLE='DBA_SUPPORT';
ROLE PRIVILEGE ADM
------------------------------ ------------------------------ ---
DBA_SUPPORT SELECT ANY SEQUENCE NO
DBA_SUPPORT SELECT ANY DICTIONARY NO
5. SESSION_PRIVS
该视图用于查询当前user拥有哪些系统权限：
select * from SESSION_PRIVS;
PRIVILEGE
------------------------------
CREATE SESSION
SELECT ANY SEQUENCE
SELECT ANY DICTIONARY
6. SESSION_ROLES
该视图用于查询当前user拥有哪些role：
select * from SESSION_ROLES;
ROLE
------------------------------
DBA_SUPPORT
CONNECT
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
7. 附注： WITH ADMIN OPTION和WITH GRANT OPTION
WITH ADMIN OPTION是针对系统权限的，它的作用可以用下面这句话说明：
Only users who have been granted a specific system privilege with the
也就是说，对于某些权限大的user来说（比如DBA，一般拥有
1） 首先用DBA账号（a105024)登陆数据库，并创建两个测试账号(testuser1, testuser2)：
A105024@O02DMS1>create user testuser1 identified by test1;
User created.
A105024@O02DMS1>create user testuser2 identified by test2;
User created.
2) 用DBA账号把 create session权限赋给测试账号1：
A105024@O02DMS1>grant CREATE SESSION to testuser1;
Grant succeeded.
3） 用测试账号1登陆数据库，并查看测试账号1的系统权限：
TESTUSER1@O02DMS1>select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTUSER1 CREATE SESSION NO
4） 用测试账号1尝试把create session赋给其它user：
TESTUSER@O02DMS1>grant CREATE SESSION to testuser2;
grant CREATE SESSION to testuser2
*
ERROR at line 1:
ORA-01031: insufficient privileges
出现权限不足的错误，是因为ADM那列的值为NO.
5） 用DBA账号把create session权限赋给测试账号1，并加上with admin option：
A105024@O02DMS1>grant CREATE SESSION to testuser1 with admin option;
Grant succeeded.
6) 查看测试账号1的系统权限：
TESTUSER1@O02DMS1>select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTUSER1 CREATE SESSION YES
7) 用测试账号1把create session赋给其它user：
TESTUSER@O02DMS1>grant CREATE SESSION to testuser2;
Grant succeeded.
WITH GRANT OPTION类似，只是它是针对对象权限的。
1. DBA_ROLE_PRIVS
| Column | Datatype | NULL | Description |
|---|---|---|---|
GRANTEE | VARCHAR2(30) | Name of the user or role receiving the grant | |
GRANTED_ROLE | VARCHAR2(30) | NOT NULL | Granted role name |
ADMIN_OPTION | VARCHAR2(3) | Indicates whether the grant was with theADMIN OPTION(YES) or not (NO) | |
DEFAULT_ROLE | VARCHAR2(3) | Indicates whether the role is designated as aDEFAULT ROLEfor the user (YES) or not (NO) |
1） 查某个user或role拥有哪些role：
select * from DBA_ROLE_PRIVS where GRANTEE='FIRGTRS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
FIRGTRS GTRS_DMM_UPDATE_ROLE NO YES
2） 查看某个role赋给了哪些user或role：
select * from DBA_ROLE_PRIVS where GRANTED_ROLE='GTRS_DMM_UPDATE_ROLE';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
GTRSOSA GTRS_DMM_UPDATE_ROLE NO YES
FIRGTRS GTRS_DMM_UPDATE_ROLE NO YES
GTRSSUP GTRS_DMM_UPDATE_ROLE NO YES
SYSTEM GTRS_DMM_UPDATE_ROLE YES YES
2. DBA_TAB_PRIVS
| Column | Datatype | NULL | Description |
|---|---|---|---|
GRANTEE | VARCHAR2(30) | NOT NULL | Name of the user to whom access was granted |
OWNER | VARCHAR2(30) | NOT NULL | Owner of the object |
TABLE_NAME | VARCHAR2(30) | NOT NULL | Name of the object. The object can be any object, including tables, packages, indexes, sequences, and so on. |
GRANTOR | VARCHAR2(30) | NOT NULL | Name of the user who performed the grant |
PRIVILEGE | VARCHAR2(40) | NOT NULL | Privilege on the object |
GRANTABLE | VARCHAR2(3) | Indicates whether the privilege was granted with theGRANT OPTION(YES) or not (NO) | |
HIERARCHY | VARCHAR2(3) | Indicates whether the privilege was granted with theHIERARCHY OPTION(YES) or not (NO) |
select GRANTOR,GRANTEE,TABLE_NAME,PRIVILEGE from DBA_TAB_PRIVS where TABLE_NAME='PAYAGENT' order by GRANTEE;
GRANTOR GRANTEE TABLE_NAME PRIVILEGE
--------------- ------------------------------ ------------------------------ ---------------
GTRS DMM_ROLE PAYAGENT INSERT
GTRS DMM_ROLE PAYAGENT UPDATE
GTRS DMM_ROLE PAYAGENT DELETE
GTRS DMM_ROLE PAYAGENT SELECT
GTRS GTRS_DMM_READONLY_ROLE PAYAGENT SELECT
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT INSERT
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT DELETE
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT UPDATE
GTRS GTRS_DMM_UPDATE_ROLE PAYAGENT SELECT
GTRS GTRS_SUPPORT_READONLY_ROLE PAYAGENT SELECT
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT UPDATE
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT INSERT
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT DELETE
GTRS GTRS_SUPPORT_UPDATE_ROLE PAYAGENT SELECT
GTRS SUPPORT_ROLE PAYAGENT SELECT
3. DBA_SYS_PRIVS
| Column | Datatype | NULL | Description |
|---|---|---|---|
GRANTEE | VARCHAR2(30) | NOT NULL | Grantee name, user, or role receiving the grant |
PRIVILEGE | VARCHAR2(40) | NOT NULL | System privilege |
ADMIN_OPTION | VARCHAR2(3) | Grant was with theADMINoption |
select * from DBA_SYS_PRIVS where GRANTEE='FIRGTRS';
GRANTEE PRIVILEGE ADM
------------------------------ --------------- ---
FIRGTRS CREATE SESSION NO
4. ROLE_SYS_PRIVS
| Column | Datatype | NULL | Description |
|---|---|---|---|
ROLE | VARCHAR2(30) | NOT NULL | Name of the role |
PRIVILEGE | VARCHAR2(40) | NOT NULL | System privilege granted to the role |
ADMIN_OPTION | VARCHAR2(3) | Signifies the grant was with theADMINoption |
select * from ROLE_SYS_PRIVS where ROLE='DBA_SUPPORT';
ROLE PRIVILEGE ADM
------------------------------ ------------------------------ ---
DBA_SUPPORT SELECT ANY SEQUENCE NO
DBA_SUPPORT SELECT ANY DICTIONARY NO
5. SESSION_PRIVS
| Column | Datatype | NULL | Description |
|---|---|---|---|
PRIVILEGE | VARCHAR2(40) | NOT NULL | Name of the privilege |
select * from SESSION_PRIVS;
PRIVILEGE
------------------------------
CREATE SESSION
SELECT ANY SEQUENCE
SELECT ANY DICTIONARY
6. SESSION_ROLES
| Column | Datatype | NULL | Description |
|---|---|---|---|
ROLE | VARCHAR2(30) | NOT NULL | Name of the role |
select * from SESSION_ROLES;
ROLE
------------------------------
DBA_SUPPORT
CONNECT
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
7. 附注： WITH ADMIN OPTION和WITH GRANT OPTION
WITH ADMIN OPTION是针对系统权限的，它的作用可以用下面这句话说明：
Only users who have been granted a specific system privilege with the
ADMINOPTIONor users with the system privilegesGRANTANYPRIVILEGEorGRANTANYOBJECTPRIVILEGEcan grant or revoke system privileges to other users.也就是说，对于某些权限大的user来说（比如DBA，一般拥有
GRANTANYPRIVILEGE和GRANTANYOBJECTPRIVILEGE），WITH ADMIN OPTION对它们没有影响，因为它们本身就具有给其它user或role赋系统权限的权力；而对于一般的user来说，它们的权限都是DBA赋给它们的，如果在DBA赋给它们权限时加了WITH ADMIN OPTION, 则它们还可以把这些权限再赋给其它的user，否则不能，请看以下实验：1） 首先用DBA账号（a105024)登陆数据库，并创建两个测试账号(testuser1, testuser2)：
A105024@O02DMS1>create user testuser1 identified by test1;
User created.
A105024@O02DMS1>create user testuser2 identified by test2;
User created.
2) 用DBA账号把 create session权限赋给测试账号1：
A105024@O02DMS1>grant CREATE SESSION to testuser1;
Grant succeeded.
3） 用测试账号1登陆数据库，并查看测试账号1的系统权限：
TESTUSER1@O02DMS1>select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTUSER1 CREATE SESSION NO
4） 用测试账号1尝试把create session赋给其它user：
TESTUSER@O02DMS1>grant CREATE SESSION to testuser2;
grant CREATE SESSION to testuser2
*
ERROR at line 1:
ORA-01031: insufficient privileges
出现权限不足的错误，是因为ADM那列的值为NO.
5） 用DBA账号把create session权限赋给测试账号1，并加上with admin option：
A105024@O02DMS1>grant CREATE SESSION to testuser1 with admin option;
Grant succeeded.
6) 查看测试账号1的系统权限：
TESTUSER1@O02DMS1>select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTUSER1 CREATE SESSION YES
7) 用测试账号1把create session赋给其它user：
TESTUSER@O02DMS1>grant CREATE SESSION to testuser2;
Grant succeeded.
WITH GRANT OPTION类似，只是它是针对对象权限的。
沒有留言:
張貼留言