Shulou(Shulou.com)06/02 Report--

There are many views for checking permissions in Oracle, but many people will be confused when they need to check permissions and do not know which view to use. Here I list several common views for checking permissions and their usage:

1. DBA_ROLE_PRIVS

ColumnDatatypeNULLDescriptionGRANTEEVARCHAR2 (30) Name of the user or role receiving the grantGRANTED_ROLEVARCHAR2 (30) NOT NULLGranted role nameADMIN_OPTIONVARCHAR2 (3) Indicates whether the grant was with the ADMIN OPTION (YES) or not (NO) DEFAULT_ROLEVARCHAR2 (3) Indicates whether the role is designated as a DEFAULT ROLE for the user (YES) or not (NO) this view has the following two main functions:

1) find out which role a user or role has:

Select * from DBA_ROLE_PRIVS where GRANTEE='FIRGTRS'

GRANTEE GRANTED_ROLE ADM DEF

FIRGTRS GTRS_DMM_UPDATE_ROLE NO YES

2) check which user or role is assigned to a 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

ColumnDatatypeNULLDescriptionGRANTEEVARCHAR2 (30) NOT NULLName of the user to whom access was grantedOWNERVARCHAR2 (30) NOT NULLOwner of the objectTABLE_NAMEVARCHAR2 (30) NOT NULLName of the object. The object can be any object, including tables, packages, indexes, sequences, and so on.GRANTORVARCHAR2 (30) NOT NULLName of the user who performed the grantPRIVILEGEVARCHAR2 (40) NOT NULLPrivilege on the objectGRANTABLEVARCHAR2 (3) Indicates whether the privilege was granted with the GRANT OPTION (YES) or not (NO) HIERARCHYVARCHAR2 (3) Indicates whether the privilege was granted with the HIERARCHY OPTION (YES) or not (NO) the name of this view contains' TAB', and one column in it is called TABLE_NAME is easy to cause misunderstanding. In fact, this view is used to query permissions on object. It's not just table's authority.

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

ColumnDatatypeNULLDescriptionGRANTEEVARCHAR2 (30) NOT NULLGrantee name, user, or role receiving the grantPRIVILEGEVARCHAR2 (40) NOT NULLSystem privilegeADMIN_OPTIONVARCHAR2 (3) Grant was with the ADMIN option this view is used to query which system permissions a user has:

Select * from DBA_SYS_PRIVS where GRANTEE='FIRGTRS'

GRANTEE PRIVILEGE ADM

-

FIRGTRS CREATE SESSION NO

4. ROLE_SYS_PRIVS

ColumnDatatypeNULLDescriptionROLEVARCHAR2 (30) NOT NULLName of the rolePRIVILEGEVARCHAR2 (40) NOT NULLSystem privilege granted to the roleADMIN_OPTIONVARCHAR2 (3) Signifies the grant was with the ADMIN option this view is used to query which system permissions a role has:

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

ColumnDatatypeNULLDescriptionPRIVILEGEVARCHAR2 (40) NOT NULLName of the privilege this view is used to query which system permissions the current user has:

Select * from SESSION_PRIVS

PRIVILEGE

-

CREATE SESSION

SELECT ANY SEQUENCE

SELECT ANY DICTIONARY

6. SESSION_ROLES

ColumnDatatypeNULLDescriptionROLEVARCHAR2 (30) NOT NULLName of the role this view is used to query which role the current user has:

Select * from SESSION_ROLES

ROLE

-

DBA_SUPPORT

CONNECT

SELECT_CATALOG_ROLE

HS_ADMIN_ROLE

7. Note: WITH ADMIN OPTION and WITH GRANT OPTION

WITH ADMIN OPTION is for system permissions, and its role can be explained by the following sentence:

Only users who have been granted a specific system privilege with the ADMIN OPTION or users with the system privileges GRANT ANY PRIVILEGE or GRANT ANY OBJECT PRIVILEGE can grant or revoke system privileges to other users.

In other words, for some user with large permissions (such as DBA, which generally has GRANT ANY PRIVILEGE and GRANT ANY OBJECT PRIVILEGE), WITH ADMIN OPTION has no effect on them because they themselves have the right to grant system permissions to other user or role For general user, their permissions are assigned by DBA. If WITH ADMIN OPTION is added when DBA assigns permissions to them, they can also assign these permissions to other user. Otherwise, please see the following experiment:

1) first log in to the database with the DBA account (a105024) and create two test accounts (testuser1 and testuser2):

A105024@O02DMS1 > create user testuser1 identified by test1

User created.

A105024@O02DMS1 > create user testuser2 identified by test2

User created.

2) use the DBA account to assign create session permissions to the test account 1:

A105024@O02DMS1 > grant CREATE SESSION to testuser1

Grant succeeded.

3) Log in to the database with test account 1, and check the system permissions of test account 1:

TESTUSER1@O02DMS1 > select * from user_sys_privs

USERNAME PRIVILEGE ADM

TESTUSER1 CREATE SESSION NO

4) try to assign create session to other user with test account 1:

TESTUSER@O02DMS1 > grant CREATE SESSION to testuser2

Grant CREATE SESSION to testuser2

*

ERROR at line 1:

ORA-01031: insufficient privileges

The error of insufficient permissions occurs because the value of the ADM column is NO.

5) use the DBA account to assign create session permissions to the test account 1, and add with admin option:

A105024@O02DMS1 > grant CREATE SESSION to testuser1 with admin option

Grant succeeded.

6) View the system permissions of test account 1:

TESTUSER1@O02DMS1 > select * from user_sys_privs

USERNAME PRIVILEGE ADM

TESTUSER1 CREATE SESSION YES

7) assign create session to other user with test account 1:

TESTUSER@O02DMS1 > grant CREATE SESSION to testuser2

Grant succeeded.

WITH GRANT OPTION is similar, except that it is for object permissions.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.