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.
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
CREATE USER ADV_OWNER IDENTIFIED BY ######
DEFAULT TABLESPACE "DATA"
TEMPORARY TABLESPACE "TEMP";
GRANT "DV_OWNER" TO "ADV_OWNER" WITH ADMIN OPTION;
ALTER USER "ADV_OWNER" DEFAULT ROLE ALL;
GRANT CREATE SESSION TO "ADV_OWNER";
For DV_ACCTMGR :
CREATE USER "ADV_ACCT_ADMIN" IDENTIFIED BY #######
DEFAULT TABLESPACE "DATA"
TEMPORARY TABLESPACE "TEMP";
GRANT "DV_ACCTMGR" TO "ADV_ACCT_ADMIN" WITH ADMIN OPTION;
ALTER USER "ADV_ACCT_ADMIN" DEFAULT ROLE ALL;
GRANT CREATE SESSION TO "ADV_ACCT_ADMIN";
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:
EXEC DBMS_CLOUD_MACADM.CONFIGURE_DATABASE_VAULT('ADV_OWNER', 'ADV_ACCT_ADMIN');
Use the following command to check if Oracle Database Vault is enabled or disabled:
SELECT * FROM DBA_DV_STATUS;
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.
CREATE TABLE "ADMIN"."USERS_CONS_GRP"
( "USERNAME" VARCHAR2(200 BYTE) NOT NULL,
"CONS_GRP" VARCHAR2(20 BYTE) NOT NULL,
CONSTRAINT "USERS_CONS_GRP_CHK1" CHECK (cons_grp in ('HIGH','LOW','MEDIUM','TPURGENT','TP'))
TABLESPACE "DATA" ;
Lets add two users “SCOTT” and “TOTO” and map it to respective consumer group “TP” and “LOW” services.
Insert into ADMIN.USERS_CONS_GRP (USERNAME,CONS_GRP) values ('TOTO','LOW');
Insert into ADMIN.USERS_CONS_GRP (USERNAME,CONS_GRP) values ('SCOTT','TP');
Select * from USERS_CONS_GRP
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.
CREATE OR REPLACE FUNCTION USER_GRP_FN
RETURN number IS
usr number ;
WHERE username = SYS_CONTEXT('USERENV', 'SESSION_USER') and cons_grp = (SELECT resource_consumer_group
WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID'));
Grant execute on the function to DVSYS user.
grant execute on ADMIN.USER_GRP_FN to dvsys;
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:
A simple example of these concepts working together might be:
Create Rule Set
rule_set_name => 'RULE_SET_USER_CONS',
description => 'Rule Set enabled for Consumer Group User Mapping',
enabled => DVSYS.DBMS_MACUTL.G_YES,
eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL, -- all rules must be true,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL, -- no audit
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
fail_message => '',
fail_code => NULL,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => NULL
rule_name => 'Check USER and Consumer group',
rule_expr =>'ADMIN.USER_GRP_FN = 1');
Add Rule to Rule Set
rule_set_name => ' RULE_SET_USER_CONS',
rule_name => 'Check USER and Consumer group'
Create command rule for session connect
command => 'CONNECT',
rule_set_name => ‘RULE_SET_USER_CONS’,
object_owner => '%',
object_name => '%',
enabled => DBMS_MACUTL.G_YES);
Check the rule has been implemented
SELECT * FROM DVSYS.DBA_DV_RULE where name like '%Cons%';
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.
Goutam is a Senior Cloud Engineer, part of Cloud Engineering under Custom Apps and Database pillar with career spanning more than 10 yrs of experience in Oracle database Administration, Consulting, Database Management, High Availability, Disaster Recovery Strategies, Real Application clusters
Focus is on architecting cloud solution & migrating the workload from on premise to Cloud@Customer & OCI Public cloud platform.
Dataguard & High Availabilty
Golden Gate & ZDM.
Oracle DB 19c,18c,12c,11g.