Precautions for revoking DBA roles

Precautions for revoking DBA roles

Oracle has very fine-grained permissions. To facilitate the management of numerous permissions, Oracle introduces the role logic concept and also sets many roles in the system. In actual applications, application users may have complicated permissions. Therefore, to facilitate authorization, many application users are granted the DBA role, and the DBA role has more system permissions, this is very unfavorable for database management and violates the security principle of minimizing permissions. For the sake of security, the system may need to revoke the DBA role. In the process of revoking permissions, pay attention to some details to ensure the normal operation of applications, several important points will be discussed below.

Lab environment description (the conclusions in this article apply to 10 Gb ~ 11.2.0.4 ):

1. SQL> select * from v $ version;
   
2. BANNER
3. --------------------------------------------------------------------------------
4. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
5. PL/SQL Release 11.2.0.3.0-Production
6. CORE 11.2.0.3.0 Production
7. TNS for Linux: Version 11.2.0.3.0-Production
8. NLSRTL Version 11.2.0.3.0-Production

1. Validity Period of the grant/revoke role operation

1. -- Create a new user and grant the connect role
   
2. Session 1:
3. SQL> create user darren identified by darren;
4. User created.
5. SQL> grant connect to darren;
6. Grant succeeded.
7. -- Use a new user to log on to the database
8. Session 2:
9. SQL> conn darren/darren
10. Connected.
11. SQL> select * from session_privs;
12. PRIVILEGE
13. ----------------------------------------
14. CREATE SESSION
15. SQL> select * from session_roles;
16. ROLE
17. ------------------------------
18. CONNECT

Keep session 2 from exiting and assign the resource role to session 1.

1. Session 1:
   
2. SQL> grant resource to darren;
3. Grant succeeded.
4. -- Query the user's system permissions and roles in session 2
5. SQL> select * from session_privs;
6. PRIVILEGE
7. ----------------------------------------
8. CREATE SESSION
9. Unlimited tablespace -- Added System Permissions
10. SQL> select * from session_roles;
11. ROLE
12. ------------------------------
13. CONNECT

Log out of the session and log on to the user again.

1. SQL> conn darren/darren
   
2. Connected.
3. SQL> select * from session_privs;
4. PRIVILEGE
5. ----------------------------------------
6. CREATE SESSION
7. UNLIMITED TABLESPACE
8. CREATE TABLE
9. CREATE CLUSTER
10. CREATE SEQUENCE
11. CREATE PROCEDURE
12. CREATE TRIGGER
13. CREATE TYPE
14. CREATE OPERATOR
15. CREATE INDEXTYPE
16. 10 rows selected.
17. SQL> select * from session_roles;
18. ROLE
19. ------------------------------
20. CONNECT
21. RESOURCE

Conclusion: 1. the unlimited tablespace system permission will be granted to the user along with the resource role, because it is a separate system permission, it will take effect immediately; 2. The grant/revoke role will not take effect immediately, it takes effect only when you use set role or log on again (directly grant system and object permissions to take effect immediately ).

2. revoke the impact of DBA roles. Continue the experiment above. Grant and revoke the user DBA role in session 1, and session 2 log on to the user again.

1. Session 1:
   
2. SQL> grant dba to darren;
3. Grant succeeded.
4. SQL> revoke dba from darren;
5. Revoke succeeded.
6. Session 2:
7. SQL> conn darren/darren
8. Connected.
9. SQL> select count (*) from session_privs;
10. COUNT (*)
11. ----------
12. 9
13. SQL> select * from session_roles;
14. ROLE
15. ------------------------------
16. CONNECT
17. RESOURCE
18. SQL> select * from session_privs; -- note that the UNLIMITED TABLESPACE permission is not available here.
19. PRIVILEGE
20. ----------------------------------------
21. CREATE SESSION
22. CREATE TABLE
23. CREATE CLUSTER
24. CREATE SEQUENCE
25. CREATE PROCEDURE
26. CREATE TRIGGER
27. CREATE TYPE
28. CREATE OPERATOR
29. CREATE INDEXTYPE

Create a table in session 2 and insert data

1. SQL> create table test1 (a varchar2 (20), B varchar2 (10); -- the creation is successful because of the latency of 11g, the segment is not actually generated in the tablespace.
   
2. Table created.
3. SQL> insert into test1 values ('A', 'B ');
4. Insert into test1 values ('A', 'B ')
5. *
6. ERROR at line 1:
7. ORA-01950: no privileges on tablespace 'users'

Conclusion: 1. When the DBA role is reclaimed, the unlimited tablespace permission is revoked. 2. After the unlimited tablespace permission is revoked, the user's tablespace quota is immediately exhausted, meaning that the TABLESPACE storage space cannot be used.

3. Process DBA permission Revocation
1. Since the unlimited tablespace system permission of the user is revoked after the DBA permission is revoked, You can authorize the user in one of the following ways so that the user can use the TABLESPACE normally:

1. SQL> grant RESOURCE to DARREN;
   
2. SQL> alter user DARREN quota unlimited on USERS;
3. SQL> grant UNLIMITED TABLESPACE to DARREN;

2. If the application user has special permissions, you must revoke the DBA role and grant the user some system and object permissions separately.

This article permanently updates the link address: