How to map Consumer Group to specific User in Autonomous Database-Shared

Overview:

Resource Managers or Consumer Groups or Service names are the integral part of Autonomous Databases as they manage the workload based on the performance characteristics of the Application. Autonomous Database comes with 5 preconfigured database services, HIGH, MEDIUM, LOW, TP and TPURGENT. The services control the priority of the sessions when the system is under resource pressure and some control the parallel degree used. By default, queries will execute serially when connected to the TP, TPURGENT and LOW services.

Users can specify a parallel degree on an object or uses optimizer hints to trigger parallel execution to be used when connected to the TP and TPURGENT services. Queries will be automatically executed in parallel when sessions are connected to the HIGH and MEDIUM services.

There are cases where we see a lot of application users misusing the consumer groups to their advantage and in doing so leading to performance overhead in the database.

So here in this blog we are going to show how to restrict users to certain consumer groups using database vault built into autonomous database.

One of the many security capabilities built into Autonomous Database is Database Vault. Database Vault implements powerful, unique security controls that restrict access to application data by privileged database users and enforce context-aware policies for data access by any user. Database Vault reduces the risk of insider and outside threats and addresses common compliance requirements.

Implementation:

Database Vault enables separation of duties by implementing two dedicated database roles – DV_OWNER (used to create and manage security policies enforced by Database Vault) and DV_ACCTMGR (used to separate the duty of database user management – including password resets – from the DBA role). By default, the ADMIN user has both the DV_OWNER and DV_ACCTMGR role. Ideally ADMIN user should only be used to provision other users with required privileges and roles and not used for day to day operations.  If you want to set up separate users for DV_OWNER and DV_ACCTMGR accounts

For DV_OWNER:

For DV_ACCTMGR :

Enable Oracle Database Vault on Autonomous Database-Shared

Shows the steps to enable Oracle Database Vault on Autonomous Database-Shared.

Oracle Database Vault is disabled by default on Autonomous Database. To configure and enable Oracle Database Vault on Autonomous Database, do the following:

1. Configure Oracle Database Vault using the following command using the user and role created in above step:

 EXEC DBMS_CLOUD_MACADM.CONFIGURE_DATABASE_VAULT('ADV_OWNER', 'ADV_ACCT_ADMIN');

2. Enable Oracle Database Vault:

EXEC DBMS_CLOUD_MACADM.ENABLE_DATABASE_VAULT;

3. Restart the Autonomous Transaction Processing instance from OCI console

Use the following command to check if Oracle Database Vault is enabled or disabled:

Output similar to the following appears:

The DV_ENABLE_STATUS value TRUE indicates Oracle Database Vault is enabled.

Now that we have Database Vault enabled, we are going to create a repository table “USERS_CONS_GRP” which maps the user with the consumer group. You can add any number of users and map it to any consumer group or groups as required. Users not included in the table will not be part of Database Vault filter and will have access to all the consumer groups as provided by default.

Lets add two users “SCOTT” and “TOTO” and map it to respective consumer group “TP” and “LOW” services.

Create function “USER_GRP_FN” to provide true value when session user fulfils mapped consumer group. We will use this function in database vault rule.

Grant execute on the function to DVSYS user.

For our scenario we want user “SCOTT” to be able to connect to database using only “TP” services and user “TOTO” to connect using “LOW” services.

In such case we will have to setup the below Database vault policy from ‘ADMIN’ account or 'ADV_OWNER' account

A Database Vault policy is made up of a few different components:

• Rule – describe conditions that will be checked before an action is allowed or blocked
• Rule set – a group of one or more rules that can be assigned to an action. You can associate the rule set with a realm authorization, factor assignment, command rule, or secure application role.
• Command rule – connects a rule set to a specific action and object or schema. A command rule ALWAYS references a rule set. Command rules are commonly used to implement context-sensitive access policies
• Realm – a logical grouping of objects or schemas. Realms automatically override DBA and * ANY privileges. Realms are commonly used to block privileged user access to data

A simple example of these concepts working together might be:

• Create a rule that checks the user session is connected using mapped consumer group service.
• Create a rule set that includes the rule, and specified when Database Vault will create an audit records (success, failure, or both).
• Create a command rule that enforces the user access restriction for non-mapped users upon CONNECT operation.

Create Rule Set

Create Rule

Add Rule to Rule Set

Create command rule for session connect

Check the rule has been implemented

Validation

Let’s connect to “SCOTT” schema using “TP” service and we see its connecting.

And when we will connect using any other service it will be denied.

Similarly for User “TOTO” access is allowed for “LOW” service.

And restricted for any other service.

References:

https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/manage-priorities.html#GUID-19175472-D200-445F-897A-F39801B0E953

https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/autonomous-database-vault.html#GUID-70F233AF-458F-44B7-9418-F9AF70CFDD15