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

ON THE SYSTEM AUTHORITY OF Oracle DATABASE UNLIMITED TABLESPACE

Author: Zhao Quanwen

The day before yesterday morning, development students feedback, application in connection with a set of Oracle 11.2.0.4.0 database production users, has been reported "no operation table space" permission. Therefore, I quickly connected to SQLPLUS to check what kind of permissions the user has (including system permissions, role permissions and object permissions), and found no exceptions. As we all know, after creating a user, you will generally give CONNECT and RESOURCE role permissions. Then, I connected to the production user of another Oracle 11.2.0.4.0 database without error, compared the two databases, and found that the database with error did not have the system permission of "UNLIMITED TABLESPACE." After granting the system permission, I contacted the development classmate again, and he said it was normal.

In view of the above fault handling, let's take a look at whether several roles and users in Oracle database have system permissions of UNLIMITED TABLESPACE.

1. View 3 roles, CONNECT, RESOURCE, and DBA.

(1)Looking at the CONNECT role, as shown in the figure below, it is found that this role only has system privileges for CREATE SESSION.

(2)Looking at the RESOURCE role, as shown in the figure below, it is found that this role has 8 system permissions, but no system permissions for UNLIMITED TABLESPACE are found.

(3)Looking at the DBA role, see the figure below, I found that this role has more system permissions, I have processed the PRIVILEGE column in descending order, and the system permissions starting with U are displayed at the top, but I did not find UNLIMITED TABLESPACE.

2. View 2 users, SYS and SYSTEM.

(1)Looking at the SYS user, see the figure below, it is found that the SYS user has the system permission of UNLIMITED TABLESPACE.

(2)Looking at the SYSTEM user, see the figure below, it is found that the SYSTEM user also has the system permission of UNLIMITED TABLESPACE.

From the above review, we can conclude that

The roles CONNECT, RESOURCE, and DBA do not have system permissions for UNLIMITED TABLESPACE;

Users SYS and SYSTEM both have system privileges for UNLIMITED TABLESPACE.

Next, let's look at what role permissions the three roles CONNECT, RESOURCE, and DBA have.

1. The roles CONNECT and RESOURCE, shown below, were found to have no role privileges.

2. The role DBA, shown in the figure below, was found to have a lot of role permissions.

Finally, we create a table space TEST and a user TEST in the test library, and set the table space TEST as the default table space of the user TEST, grant the role permissions of CONNECT and RESOURCE, and observe what kind of system permissions the user has. Operation steps are as follows,

(1)View the database version, as shown below,

(2)Create the table space TEST, as shown in the figure below,

(3)Create user TEST, see figure below,

(4)Grant CONNECT role permission to user TEST, and check the system permissions of this user, as shown in the figure below, and find that there are no system permissions.

(5)Grant RESOURCE role permission to user TEST, and check the system permissions of this user, as shown in the figure below, and unexpectedly find that he has UNLIMITED TABLESPACE system permissions.

From this operation and the previous demonstration, it can be concluded that the RESOURCE role itself does not have the system permission of UNLIMITED TABLESPACE, but granting this role to an ordinary user makes the user have the system permission of UNLIMITED TABLESPACE by default. With this question in mind, I checked Google and found that AskTom's answer was authoritative and convincing. See operation steps, as shown below,

As mentioned above, someone asked Master Tom Kyte why the RESOURCE role itself does not have the system permission of UNLIMITED TABLESPACE, but the user who is granted the RESOURCE role has the system permission of UNLIMITED TABLESPACE.

The master replied that the UNLIMITED TABLESPACE system permissions had been hardcoded into the RESOURCE role, which was a special permission. As shown in the figure below,

Master Haroon from India also commented that UNLIMITED TABLESPACE is a hidden system permission in the RESOURCE role. This bug has been fixed in Oracle version 12.1.0.1, and in 12.1.0.1, the created ordinary user does not have the system permission of UNLIMITED TABLESPACE by default after granting the RESOURCE role. Therefore, when revoking the permission of UNLIMITED TABLESPACE, an error will be reported, indicating that the permission has not been granted. See the red box below for description,

The following works experience of Master Tom Kyte is shown as the driving force and spiritual pillar for me to continue studying Oracle.

(6)Grant DBA role permission to user TEST, view the system permissions that user has, revoke DBA role permission of user, and view system permissions that user has again. See below for operation.

From the above two pictures can be drawn, DBA role authority lethality ah, granted, no impact, revoked, the original UNLIMITED TABLESPACE system authority quietly to withdraw, feel good terror yo! Therefore, caution everyone, must not grant DBA role permissions to ordinary users, if once granted, and then revoke, must give ordinary users a separate UNLIMITED TABLESPACE system permissions or re-grant a RESOURCE role permissions. As shown in the figure below,

or

If you think this article is helpful to you, welcome to pay attention to Weixin Official Accounts: DBA study notes of guestart. Your support is my greatest encouragement!

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.