Oracle Default Role

Oracle Default Role

Oracle System permissions are based on three dimensions: System Privilege, Object Privilege, and Role Privilege ). System permissions define certain behavior operations that can be performed by users. object permissions define the operation permissions of users on a system object (such as data tables and views). Role permissions are more like a container object, A group of system permissions, object permissions, and even other role permissions can be accommodated.

The three-dimensional permissions construct the Oracle permission system framework on three levels. A configuration method for traditional application systems is to create users at the database level and configure relevant permissions for operations. Such a system can also be seen in some old application systems or foreign business systems. With the widespread use of Web applications, the complexity of Oracle permission system requirements is actually decreasing. Generally, a Web application only needs to connect to a Schema user name. The user system is implemented at the application level.

Recently I encountered a problem about the Role, and finally found that it was a problem with the Default Role, which was often ignored. This article mainly introduces this feature.

1. Environment Introduction

The permission system of Oracle has been continuously developed and enriched in the Past versions. The author discusses the specific version of Oracle 11g, which is 11.2.0.4.

SQL> select * from v $ version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

Create a new user.

SQL> create user test identified by test;

User created

2. Introduction to Default Role

Compared with system and object permissions, role permissions are a special type of permissions. It is more like a combination container, which can organize other permissions in Group mode. Generally, the most common scenario of Role permission Role Privilege is to simplify management difficulty and achieve standardized configuration management. Another feature of role permissions is dynamic authorization. Once the system and object permissions are granted, the user can directly log on. The object permission can be selected on this issue.

First, we set the default settings to grant a series of role actions to user test.

SQL> grant connect, resource to test;

Grant succeeded

 

SQL> grant sicspccgrole to test;

Grant succeeded

 

SQL> grant sicspctbcgrole to test;

Grant succeeded

 

SQL> grant sicspctrrole to test;

Grant succeeded

View db_role_privs to view the role-granting relationship.

SQL> select * from dba_role_privs where GRANTEE = 'test ';

 

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

------------------------------------------------------------------------------------

TEST RESOURCE NO YES

TEST SICSPCCGROLE NO YES

TEST SICSPCTBTRROLE NO YES

TEST CONNECT NO YES

TEST SICSPCTBCGROLE NO YES

TEST SICSPCTRROLE NO YES

6 rows selected

Pay attention to the default_role column. Permissions corresponding to test are granted to default_role. In other words, after a user is assigned a role, the default role is the default role.

3. Permission changes

If the underlying permissions of the role object change, what is the impact of the authorized object?

SQL> create role testrole;

Role created

 

SQL> grant select on sics. cnu_environment to testrole;

Grant succeeded

 

SQL> grant testrole to test;

Grant succeeded

At this time, the new role testrole is granted as the default role.

SQL> select * from dba_role_privs where GRANTEE = 'test ';

 

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

------------------------------------------------------------------------------------

TEST RESOURCE NO YES

TEST SICSPCCGROLE NO YES

TEST SICSPCTBTRROLE NO YES

TEST TESTROLE NO YES

TEST CONNECT NO YES

TEST SICSPCTBCGROLE NO YES

TEST SICSPCTRROLE NO YES

 

7 rows selected

The underlying layer of Testrole changes.

SQL> grant select on scott. emp to testrole;

Grant succeeded

The Role authorization relationship remains unchanged.

SQL> select * from dba_role_privs where GRANTEE = 'test ';

 

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

------------------------------------------------------------------------------------

TEST RESOURCE NO YES

TEST SICSPCCGROLE NO YES

TEST SICSPCTBTRROLE NO YES

TEST TESTROLE NO YES

TEST CONNECT NO YES

TEST SICSPCTBCGROLE NO YES

TEST SICSPCTRROLE NO YES

7 rows selected

Some related experiments also prove that changes in the role-level permission groups do not affect the relationship between users and roles.

For more details, please continue to read the highlights on the next page:

1 2 Next Page