By Mouhamadou Diaw
In a previous blog I talked about protecting data using Realms. With Database Vault we can also protect our database against some SQL statements. These statements can include SELECT, ALTER SYSTEM, database definition language (DDL), and data manipulation language (DML) statements.
We can do this with Command Rules. In this blog I am demonstrating how we can use a Command Rule to prevent SYS from creating a new pluggable database in a multitenant environment.
Before starting the demonstration, we can see that there are some predefined Command Rules which apply to all users.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL> show user USER is "C##DBV_OWNER_ROOT" SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> SELECT COMMAND, RULE_SET_NAME FROM DVSYS.DBA_DV_COMMAND_RULE; COMMAND RULE_SET_NAME -------------------- -------------------------------------------------- ALTER PROFILE Can Maintain Accounts/Profiles ALTER SYSTEM Allow Fine Grained Control of System Parameters ALTER USER Can Maintain Own Account CHANGE PASSWORD Can Maintain Own Account CREATE PROFILE Can Maintain Accounts/Profiles CREATE USER Can Maintain Accounts/Profiles DROP PROFILE Can Maintain Accounts/Profiles DROP USER Can Maintain Accounts/Profiles 8 rows selected. SQL> |
Because of these default Command Rules, for example, user sys cannot create a user once Database Vault is enabled.
1
2
3
4
5
6
7
|
SQL> conn sys/root@pdb1 as sysdba Connected. SQL> create user myuser identified by test; create user myuser identified by test * ERROR at line 1: ORA-01031: insufficient privileges |
To grant a user the ability to use these commands, you can grant the user the role that the rule set checks.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'DV_ACCTMGR' ; PRIVILEGE ---------------------------------------- DROP PROFILE ALTER PROFILE ALTER USER CREATE PROFILE CREATE USER CREATE SESSION DROP USER 7 rows selected. SQL> |
To allow sys to create a user we can grant the DV_ACCTMGR role to SYS
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> show user USER is "C##DBV_ACCTMGR_ROOT" SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> SQL> grant DV_ACCTMGR to sys; Grant succeeded. |
And now SYS can create a user
1
2
3
4
5
6
7
|
SQL> conn sys/root@pdb1 as sysdba Connected. SQL> create user myuser identified by test; User created. SQL> |
Before starting the demonstration let’s verify that user SYS, by default, can create a pluggable database
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> conn sys as sysdba Enter password : Connected. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> create pluggable database PDB2 ADMIN USER pdb2adm IDENTIFIED BY root create_file_dest= '/u01/app/oracle/oradata/DBSEC/PDB2' ; Pluggable database created. SQL> |
To prevent sys from creating a pluggable database, we are first going to create a RULE. This rule will determine when the command rule will be fired.
1
2
3
4
5
6
|
SQL> exec DVSYS.DBMS_MACADM.CREATE_RULE(rule_name => 'MY_PDB_RULE' , rule_expr => 'SYS_CONTEXT(' 'USERENV' ', ' 'SESSION_USER' ') != ' 'SYS' '' ); PL/SQL procedure successfully completed. SQL> |
After we have to create a RULE SET which is a collection of one or more rules. We can associate a rule set with a realm authorization, factor assignment, command rule, or secure application role.
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> exec DVSYS.DBMS_MACADM.CREATE_RULE_SET(rule_set_name => 'MY_PDB_RULESET' , description => ' About managing Pdbs' , enabled => DBMS_MACUTL.G_YES, eval_options => DBMS_MACUTL.G_RULESET_EVAL_ANY, audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL + DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS, fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT, fail_message => '' , fail_code => '' , handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF, handler => '' , is_static => FALSE ); PL/SQL procedure successfully completed. SQL> |
We then add the RULE to the RULE SET
1
2
3
4
5
6
7
8
|
BEGIN DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET( rule_set_name => 'MY_PDB_RULESET' , rule_name => 'MY_PDB_RULE' ); END ; / PL/SQL procedure successfully completed. |
And finally create a COMMAND RULE which will prevent SYS to execute a CREATE PLUGGABLE DATABASE statement
1
2
3
4
5
6
7
8
9
|
SQL> exec DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(command=> 'CREATE PLUGGABLE DATABASE' , rule_set_name => 'MY_PDB_RULESET' , object_owner => DBMS_ASSERT.ENQUOTE_NAME( '%' , FALSE ), object_name => '%' , enabled => 'Y' ); PL/SQL procedure successfully completed. SQL> |
And now if we try to create a Pdb with SYS
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> show user USER is "SYS" SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> CREATE PLUGGABLE DATABASE PDB3 ADMIN USER pdb3adm IDENTIFIED BY root create_file_dest= '/u01/app/oracle/oradata/DBSEC/PDB3' ; CREATE PLUGGABLE DATABASE PDB3 ADMIN USER pdb3adm IDENTIFIED BY root create_file_dest= '/u01/app/oracle/oradata/DBSEC/PDB3' * ERROR at line 1: ORA-47400: Command Rule violation for CREATE PLUGGABLE DATABASE on PDB3 SQL> |