Talk about the system permissions of Oracle database unlimited tablespace

Talk about the system permissions of Oracle database unlimited tablespace

Author: Zhao Full text network name: Guest

The morning before yesterday, the development of student feedback, the application is connected to a set of Oracle 11.2.0.4.0 database production users, has been reported "no Operation table space" permission. So I hurried to the sqlplus to see what permissions the user has (including system, role, and object permissions), and no exceptions were found. As we all know, generally after the establishment of users, will give connect and resource role permissions. Then, I connect to other error-free Oracle 11.2.0.4.0 database of the production users to view, two sets of databases to compare, found that the error of the database does not have "UNLIMITED tablespace" system permissions, to grant the system permissions, And the development of students contact, he said it is normal.

Given the above troubleshooting, let's take a look at how several roles and users in the Oracle database have unlimited tablespace system permissions?

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

(1) To see the Connect role, see that the role only has the system permissions of the Create session.

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/8E/E0/wKioL1jOW8LSOWsMAAAyfCSejoc000.png-wh_500x0-wm_ 3-wmp_4-s_324444022.png "title=" 1.png "alt=" Wkiol1jow8lsowsmaaayfcsejoc000.png-wh_50 "/>

(2) To see the resource role, see that the role has 8 system permissions, but no system permissions were found for unlimited tablespace.

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/8E/E0/wKioL1jOXBLgSXF5AABD2LSw2IQ448.png-wh_500x0-wm_ 3-wmp_4-s_3025269429.png "title=" 2.png "alt=" Wkiol1joxblgsxf5aabd2lsw2iq448.png-wh_50 "/>

(3) To view the DBA role, see that the role has more system privileges, I have privilege columns in descending order, system permissions beginning with u are displayed at the front, but no unlimited tablespace is found.

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/8E/E2/wKiom1jOXE-Dmoa-AABIupzQwbo052.png-wh_500x0-wm_ 3-wmp_4-s_2254086021.png "title=" 3.png "alt=" Wkiom1joxe-dmoa-aabiupzqwbo052.png-wh_50 "/>

2. View 2 users, SYS and system.

(1) To view the SYS user, see that the SYS user is a system right with unlimited tablespace.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8E/E0/wKioL1jOXJrBnqN3AABkizYlgJM897.png-wh_500x0-wm_ 3-wmp_4-s_3668717107.png "title=" 4.png "alt=" Wkiol1joxjrbnqn3aabkizylgjm897.png-wh_50 "/>

(2) To view the system user, see that the systems user also has unlimited tablespace.

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8E/E2/wKiom1jOXMih6iEPAAA1jE7XY2c066.png-wh_500x0-wm_ 3-wmp_4-s_765321926.png "title=" 5.png "alt=" Wkiom1joxmih6iepaaa1je7xy2c066.png-wh_50 "/>

From the above view, we can draw the conclusion that

• The role connect, resource, and DBA do not have system permissions unlimited tablespace;

• Both the user sys and the system have unlimited tablespace.

Next, let's take a look at what role permissions do 3 roles connect, resource, and DBAs have?

1. Role Connect and resource, see, found no role permissions.

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/8E/E2/wKiom1jOXR_TsiIeAAAttJrEycY105.png-wh_500x0-wm_ 3-wmp_4-s_957217831.png "title=" 7.png "alt=" Wkiom1joxr_tsiieaaattjreycy105.png-wh_50 "/>

2. Role DBA, see, found that the role has a lot of role permissions.

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8E/E0/wKioL1jOXUjAsmcxAABk-DJy2R4967.png-wh_500x0-wm_ 3-wmp_4-s_475721625.png "title=" 6.png "alt=" Wkiol1joxujasmcxaabk-djy2r4967.png-wh_50 "/>

Finally, we create a tablespace test and a user test in the testing library, set the table space test as the default tablespace for user test, grant the role permissions of Connect and resource, and observe what system permissions the user has? The following steps are

(1) View the database version, for example,

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/8E/E2/wKiom1jOXYXh7DutAAA4aClIVCk226.png-wh_500x0-wm_ 3-wmp_4-s_1488249469.png "title=" 8.png "alt=" Wkiom1joxyxh7dutaaa4aclivck226.png-wh_50 "/>

(2) Create table space test, see,

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8E/E0/wKioL1jOXaiD1kjdAAAY3kFcdDc171.png-wh_500x0-wm_ 3-wmp_4-s_3935267082.png "title=" 9.png "alt=" Wkiol1joxaid1kjdaaay3kfcddc171.png-wh_50 "/>

(3) Create user test, see,

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/8E/E2/wKiom1jOXcTQwgddAAAS1Yw01W4768.png-wh_500x0-wm_ 3-wmp_4-s_333676214.png "title=" 10.png "alt=" Wkiom1joxctqwgddaaas1yw01w4768.png-wh_50 "/>

(4) Give the user test the role permission to connect, and view the system permissions that the user has, see that there is no system permission found.

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/8E/E0/wKioL1jOXeCRfik8AAA1Xpjfpl8130.png-wh_500x0-wm_ 3-wmp_4-s_2166320038.png "title=" 11.png "alt=" Wkiol1joxecrfik8aaa1xpjfpl8130.png-wh_50 "/>

(5) Give the user test to grant resource role permissions, and see the user has the system permissions, see, actually found that has the UNLIMITED tablespace system permissions.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8E/E2/wKiom1jOXgLiieuPAAAoqjuTYdU468.png-wh_500x0-wm_ 3-wmp_4-s_496911780.png "title=" 12.png "alt=" Wkiom1joxgliieupaaaoqjutydu468.png-wh_50 "/>

From this and the previous demo, it can be concluded that the resource role itself does not have system permissions unlimited tablespace, but given the role to a normal user, the user defaults to unlimited tablespace system permissions. With this question, check Google, found asktom This site's answer is authoritative, and very convincing. See operation steps, e.g.,

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/8E/E2/wKiom1jOXlaQeqiuAAGiMBmWf1Y482.png-wh_500x0-wm_ 3-wmp_4-s_43338057.png "title=" 13.png "alt=" Wkiom1joxlaqeqiuaagimbmwf1y482.png-wh_50 "/>

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8E/E2/wKiom1jOXmbjjkj8AAE8a8vEmw8464.png-wh_500x0-wm_ 3-wmp_4-s_234157297.png "title=" 14.png "alt=" Wkiom1joxmbjjkj8aae8a8vemw8464.png-wh_50 "/>

As above, someone asked Tom Kyte Master, why the resource role itself does not unlimited tablespace system permissions, but to ordinary users granted resource role, the user has a unlimited What about system permissions for tablespace?

The master replied, the unlimited Tablespace's system permissions have been hardcoded into the resource role, which is a special privilege. See shown,

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/8E/E2/wKiom1jOXq7jVJooAACuejvdmNw612.png-wh_500x0-wm_ 3-wmp_4-s_4047466136.png "title=" 15.png "alt=" Wkiom1joxq7jvjooaacuejvdmnw612.png-wh_50 "/>

The following is a commentary from the Indian master Haroon , who explained that, in the resource role, UNLIMITED tablespace is a hidden system privilege, which is in Oracle 12.1.0.1 Version of the bug has been fixed, and in 12.1.0.1, the normal user created after granting resource's role, the default does not have unlimited tablespace system permissions, so the revocation unlimited Tablespace the permission will be error, prompted not granted the permission, see the Red box description,

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/8E/E0/wKioL1jOXvnRES4BAADVe2nu2S4192.png-wh_500x0-wm_ 3-wmp_4-s_130805299.png "title=" 16.png "alt=" Wkiol1joxvnres4baadve2nu2s4192.png-wh_50 "/>

Here's a look at Tom Kyte 's work experience as a source and spiritual pillar for my continued research into Oracle.

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/8E/E2/wKiom1jOX-OgtoaMAACV0pM0CcM711.png-wh_500x0-wm_ 3-wmp_4-s_503457716.png "title=" 17.png "alt=" Wkiom1jox-ogtoamaacv0pm0ccm711.png-wh_50 "/>

(6) Give the user test the role permissions of the DBA, view the system permissions that the user has, and then revoke the user's DBA role permissions, and again see what system permissions the user has? See Operation,

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8E/E0/wKioL1jOYFPRLoJXAABW7L3sjAk526.png-wh_500x0-wm_ 3-wmp_4-s_4268812055.png "title=" 18.png "alt=" Wkiol1joyfprlojxaabw7l3sjak526.png-wh_50 "/>

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8E/E0/wKioL1jOYHGyzOjLAAA_MSPodYE438.png-wh_500x0-wm_ 3-wmp_4-s_475910385.png "title=" 19.png "alt=" Wkiol1joyhgyzojlaaa_mspodye438.png-wh_50 "/>

From the above two figure can be drawn, DBA role Authority is very big Ah, grant time, no impact, when the revocation, the original unlimited tablespace system permissions quietly to take back, feel good terror yo! so warn everyone, do not give ordinary users the role of DBA authority, if once granted, and then revoked, be sure to give ordinary users unlimited tablespace system permissions or re-grant a resource role permissions. See shown,

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/8E/E2/wKiom1jOYMDxJWfcAAAqlIcGO8o343.png-wh_500x0-wm_ 3-wmp_4-s_621305862.png "title=" 20.png "alt=" Wkiom1joymdxjwfcaaaqlicgo8o343.png-wh_50 "/>

Or

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/8E/E0/wKioL1jOYNmyFlPpAAAom-nzJYE863.png-wh_500x0-wm_ 3-wmp_4-s_3185322709.png "title=" 21.png "alt=" Wkiol1joynmyflppaaaom-nzjye863.png-wh_50 "/>

If you feel that this article is helpful to you, please follow the public number: Guestart's DBA study notes, your support is my greatest encouragement!

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/8E/E0/wKioL1jOYTHDKCObAADnPdGUWRk262.jpg "title=" Subscription number QR code. jpg "alt=" wkiol1joythdkcobaadnpdguwrk262.jpg "/>

This article from the "Blue Melancholy" blog, reproduced please contact the author!

Talk about the system permissions of Oracle database unlimited tablespace