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

Post Views: 1,153
Thumbnail [60x60]
by
Oracle Team