By Mouhamadou Diaw
Some debug-events and debug-actions are not safe and should be exposed to users with caution. In previous releases, privilege control for the usage of these diagnostics was not sufficient.
With Oracle 21c, regular users can be blocked from using these diagnostics to better support separation of duty.
Indeed with Oracle 21c, we have a new mechanism to control the debug-events and debug-actions through ALTER SESSION and/or ALTER SYSTEM. This mechanism is implemented by two new features
-ENABLE DIAGNOSTICS system privilege
-DIAGNOSTICS_CONTROL initialization parameter
Let’s see how these features work
As the DIAGNOSTICS_CONTROL is set to IGNORE, The default behavior is that every user can perform diagnostic tasks without error if he has ALTER SESSION PRIVILEGE
1
2
3
4
5
6
|
SQL> show parameter diagnostics_control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostics_control string IGNORE SQL> |
Let’s consider the user EDGE with the following privileges
1
2
3
4
5
6
7
8
9
|
SQL> select privilege from dba_sys_privs where GRANTEE= 'EDGE' ; PRIVILEGE ---------------------------------------- CREATE TABLE ALTER SESSION CREATE SESSION SQL> |
The user EDGE can execute following query
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> show user USER is "EDGE" SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' ; Session altered. SQL> |
Let’s now set the DIAGNOSTICS_CONTROL to WARNING
1
2
3
4
5
6
7
8
9
10
|
SQL> alter system set diagnostics_control=WARNING; System altered. SQL> show parameter diagnostics_control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostics_control string WARNING SQL> |
And let’s retry the SET EVENTS action with the user EDGE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> show user USER is "EDGE" SQL> SQL> ALTER SESSION SET EVENTS '1357 trace name context forever, level 2' ; Session altered. SQL> |
The action is executed but we have an entry in database alert log
1
2
3
4
|
2022-01-12T12:56:22.136454+01:00 PDB1(3): User 'EDGE' has set the following debug-event(s) on the event- group 'session' : PDB1(3): '1357 trace name context forever, level 2' PDB1(3): To disable such messages, refer to documentation about parameter 'diagnostics_control' . |
Now let’s put the DIAGNOSTICS_CONTROL to ERROR
1
2
3
4
5
6
7
8
9
10
|
SQL> alter system set diagnostics_control=ERROR; System altered. SQL> show parameter diagnostics_control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostics_control string ERROR SQL> |
And let’s try again the SET EVENTS action with the user EDGE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> show user USER is "EDGE" SQL> ALTER SESSION SET EVENTS '1357 trace name context forever, level 2' ; ERROR: ORA-01031: insufficient privileges ORA-49100: Failed to process event statement [1357 trace name context forever, level 2] SQL> |
We can see that the statement is not executed and that we get an error.
We can conclude than using the parameter DIAGNOSTICS_CONTROL, we can control which users can perform diag events. One can see following in Oracle documentation
ERROR: If a user who does not have the SYSDBA or ENABLE DIAGNOSTICS privilege attempts to enable a diagnostic, then the attempt will fail and an ORA-01031: insufficient privileges error appears.
WARNING: A user who does not have the SYSDBA or ENABLE DIAGNOSTICS privilege will be able to enable a diagnostic, but a warning message is written to an alert log. The warning message is similar to the following:
IGNORE: The user can perform the diagnostic task without any error messages appearing. This setting is the default.
But how if I want to set the parameter to ERROR and want to allow some non-DBA users to perform diag events? Just grant them the new system privilege ENABLE DIAGNOSTICS.
Let do a test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> select privilege from dba_sys_privs where GRANTEE= 'EDGE' ; PRIVILEGE ---------------------------------------- ENABLE DIAGNOSTICS CREATE TABLE ALTER SESSION CREATE SESSION SQL> show parameter diagnostics_control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostics_control string ERROR SQL> |
And now let’s do a diag event
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> show user USER is "EDGE" SQL> ALTER SESSION SET EVENTS '1357 trace name context forever, level 2' ; Session altered. SQL> |
As now EDGE has the privilege ENABLE DIAGNOSTICS, he can perform diag events even if the DIAGNOSTICS_CONTROL is set to ERROR.
Conclusion
This new feature can be very useful to limit the users who can perform diag actions. More information can be found in the Oracle documentation