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>
Post Views: 2,863
Thumbnail [60x60]
by
Oracle Team