Oracle database Vault Access Control Components
- REALM is a basically group of database schemas, objects or rolls that need to be secured in the database. For example: You may have table called EMP which contains employee salaries in schema called HR and you don’t want all the users to access this table specifically by SYS user which have an administrative access. If you want to prevent SYS user from accessing this particular table in the HR schema, then you can protect this table by configuring realm. So that this table will be protected from the administrators access.
- COMMAND RULE can be created to control execution of SQL statements. For example : You don’t want to create a table in HR schema, but user has system privilege create any table that means user can create table in any of the schemas of the database. As you want to restrict this user from running create table statement you can configure command rule and restrict the execution of create table statement. The SQL statements can be DDL’s or DML’s and you can restrict the execution all of this statements.
- FACTOR- there are certain situations in which you will have to prevent access based on the user location, IP address or a particular user. In that scenario you configure variable called factor and this name attribute will recognize the components such as user locations, database IP addresses or session user and secures the area of database which you want to prevent from being accessed by the administrative users
- RULE SET is a collection of one or more rules. You can associate this rule with realm authorization, command rules, factor assignments and also secure application roles
- SECURE APPLICATION ROLE can be enabled based on the evaluation of the Oracle Database Vault rule set, the rule set evaluates to true or false depending on the evaluation of the rule which is associated with the rule set
Changes after database vault installation:
Changes in initialization and Password Parameter settings:
- AUDIT_SYS_OPERATIONS value changes from FALSE to TRUE – This means all operations performed by sys user will be audited
- OS_ROLES paraeter values changes to FALSE- by default this is not configured, after you instal database vault this will be set to FALSE . This will disable operating system granting and revoking roles and privileges to users
- RECYCLEBIN parameters values changes from ON to OFF- is this parameter Turned ON, the dropped objects in the database will be moved to recyclebean.By default recycle ben is turned on. When we enable database vault this parameter is turned off
- REMOTE_LOGIN_PASSWORDFILE-this parameter is set to EXCLUSIVE by default, after installation od Database Vault value of this parameter is set to EXCLUSIVE again.
- SQL92_SECURITY parameters value changes from FALSE to TRUE – if a user is granted update and delete privileges on a table, select privilege must enable that user from updating and deleting the table. SQL92_SECURITY enforces this functionality when update and delete privileges are granted to a user.
.New Database Roles
- DV_OWNER-Oracle Database Vault Owner
- DV_ACCTMGR-Oracle Database Vault Account Manager
.Changes to Database Auditing
- After installing Database Vault $AUD table moved to SYSTEM schema from SYS schema
- Modified audit settings- yo can also see certain change in the audit settings. dv will configure certain audit settings in the database. this again depends on the setting of audit_trail initialization parameter, if it set to none audit settings not configured, if it set db os os then audit setings can configured in the database vault in management.
.Privileges prevented for existing users:
- ALTER PROFILE
- ALTER USER
- CREATE PROFILE
- CREATE USER
- DROP PROFILE
- DROP USER
Basically this privileges are prevented from execution for sys and sysdba users . in database vault and management this privileges are granted to the special user who has DV_ACCTMGR privilege and this is will long be able perform any of the create user, drop user or any alter user operations in a database vault and management.
Privileges revoked from existing users and roles
Oracle Database Vault Schemas
When you install Database Vault two schemas (DVSYS and DVF) are created during configuration.
DVSYS schema basically contains Oracle database vault database objects in which Oracle Database Vault configuration information is stored
There is a function called DBMS_MACSEC_FUNCTION package, this contains functions which can retrieve factor identitis. This package is owned by DVF schema.
Oracle Database Vault Roles
During the database vault installation and configuration when we were confiring it using DBCA we got the option to specify DV owner and DV account manager. We had provided DV account owner name as DVOWNER. This DVOWNER has a dv_owner role , this rule basically manages oracle database vault rules and its configuration
This role controls database vault PL/SQL packages
This role granted to Oracle Database Vault account manager account. This basically creates and manages database accounts and profiles. In the database vault and management sys user will not be able to perform any database account relatively activity such as creating user, altering user to change the password, drop a user, create profile to maintain database users. So such a scenario dv_acctmgr will perform all this operations. A user who has DV_ACCTMGR rule will be performing all this operations in the database vault and management.
User with this role can run reports in the Database Vault administrative console
This role granting to a user which performing patching the database vault and management. When applying patches in the database vault and management in earliest version database vault had to be disabled, this new role was introduced to avoid this step. Whenever yo needs to apply the patch in a database vault and management temporarily this rule is granted to the user. After the application of the patch this rule will be revoked from the user.
This package basically contains the procedures and the functions to create and configure the different components of the database vault such as realms, command rules, factors, rule sets and secure application roles. This package can be executed by the users who a granted dv_owner or dv_admin roles. Configuration of realms, command rules, factors also can be done using database vault administrative console. Below I will show you how to create and configure realms, command rules, factors and rule sets using database vault administrative console, I am also providing an example how it can be done using dbms_macadm package:
Creating a Realm
realm_name IN VARCHAR2,
description IN VARCHAR2,
enabled IN VARCHAR2,
audit_options IN NUMBER);
Creating a Comman rule
command IN VARCHAR2,
rule_set_name IN VARCHAR2,
object_owner IN VARCHAR2,
object_name IN NUMBER
enabled IN VARCHAR2);
Let’s test DV configuration and management:
SQL> conn dvacctmgr/<<your password >> Connected. SQL> create user demo identified by demo quota unlimited on users; User created. SQL> conn sys as sysdba Enter password: Connected. SQL> SQL> grant create session, select any table to demo; Grant succeeded. SQL> grant create session, select any table to demo; Grant succeeded. SQL> conn demo/demo Connected. SQL> select count(*) from hr.employees; COUNT(*) ---------- 107
As you can see user DEMO can fetch the records from EMPLOYEES table in HR schema. Let me configure REALM to restrict access to this particular table.
When you access the DV administration console you give the hostname or the IP address, the port number and slash DVA:
Type your URL in your browser and press ok:
This is the login page database DV administration console.
Here we specify user name for the database vault owner dvowner and specify the password, hostname or ip address of the my database server, and default port of oracle 1521. You can specify SID or service name in my example I am specifying SID name which is DB11G and click login.
You can see different tabs on the console – Administration, Database Vault Reports, General Security Reports and Monitor. To Configure Realm click on Administration tab and select Realms:
There are certain default realms which are configured during database vault installation. These are 4 realms which are default realms offered by oracle database vault.
- Database Vault Account Management realm defines realm for users who perform account management activities in the database
- Oracle Data Dictionary this defines the realm for catalog users
- Oracle Database Vault this defines realm for oracle database vault users such as DVF and DVSYS schemas
- Oracle Enterpise Manager this is basically for the enterprise manager users such as SYSMAN and DBSNMP who wants to access oracle database information
Now let’s create new realm for securing EMPLOYEES table in the HR schema, click create. In the create realm page enter the name of the realm, the description of the realm, status and different auditing options( Audit on failure – will generate an audit record when there is a realm failure) and click OK to create realm.
Now you can see HR_realm in this list:
To protect the objects of the HR schema, select HR_realm and click edit .
In this realm under the Realm Secured Objects you can see there are no objects which are protected. Click create and specify object owner, object type and object name which need to be secured. As I am securing the object of HR schema, i am specifying object owner as HR, object type as Table and object name as %-this will protect all the tables in the HR schema and click OK.
Now all tables under HR are protected. Let’s go to database and test how the securing objects of HR_realm has affected access of user demo to this tables .Earlier when the realm was not created demo user was able to access to table EMPLOYEES of HR schema.
Connect as DEMO user and again perform select statement :
SQL> select * from HR.EMPLOYEES; ERROR at line 1: ORA-01031: insufficient privileges
And you will get error “insufficient privileges” this means HR.EMPLOYEES table is protected by the REALM. This is how the REALM authorization or REALM protection works. Lets go back database vault administration console.
We saw that some of object are protected here, but users not authorized to access the objects of this realm. To authorize users to access objects of this realm select HR_realm , click edit , go to section Realm Authorizations and click the create button. In the create Realm Authorization page you can see Grantee, Authorization type and Authorization Rule Set. I am specifying Grantee as Demo user , authorization type is participant-that means this user will be able to access the objects of this realm, if I specify the Authorization Options as OWNER I would have same functionality as Participant along with that it will have additional privilege to grantee access to the objects authorized by this realm and Authorization Rule Set – not specifying any rule set here:
Now let’s see how a Command rule functions in the DV and management.
Connect as sysdba, grantee create any table privilege to demo user.
Then create table SCOTT.TBL_TEST with user demo.
[oracle@orcl ~]$ sqlplus / as sysdba SQL> grant create any table to demo ; Grant succeeded. SQL> conn demo/demo Connected. SQL> create table SCOTT.TBL_TEST(NAME VARCHAR2(10),MARKS NUMBER); Table created.
Now let’s go to Database Vault console and click on Command rules.
As we had default realms we also have certain default command rules offered by oracle database vault . This command rules is to restrict execution of following commands in the database. To create new command rule, click create button. In the create command rule page, you can select the command which you want to restrict. Here I want to restrict creating any table in SCOTT schema by user demo:
And click OK to create command rule. Here you can see that there is new command rule created.
Now let’s go back to database and test how create table statement works now:
SQL> conn demo/demo Connected. SQL> create table SCOTT.TEST as select * from ALL_OBJECTS; create table SCOTT.TEST as select * from ALL_OBJECTS * ERROR at line 1: ORA-47400: Command Rule violation for CREATE TABLE on SCOTT.TEST
Here I try to create table under SCOTT, but I get command rule violation for CREATE TABLE on SCOTT
Let’s look to small example how we can restrict access from a certain programs or modules.
Lets create Factor click on Factors and select create:
In the create new factor page fill specified name, description and Factor type.
Here we will create factor to find name the application from which database is going to connect and where we are going to block access from SQL*PLUS.
Click create and specify name, description for rule set , select status enabled and Evaluation Options All True – which means all the conditions which you specified should be evaluated to true, auditing options disabled. Then click OK.
After creating rule set edit it and click on create button to create Rules Associated to The Rule Set. Here we specified Rule Expression as DVF.F$MODULE=’SQL*PLUS.EXE’ and DVF.F$SESSION_USER IN(‘SCOTT’). DVF.F$MODULE here means that we created factor called MODULE, ‘SQL*PLUS.EXE’ here means any connections coming from sqlplus, DVF.F$SESSION_USER IN(‘SCOTT’)- means session user as SCOTT. All this means that if the user is SCOTT and SCOTT is connecting to the database using SQLPLUS it should be allowed to connect and none of the other users should be allowed to connect. Click ok and create rule.
Now I will create command rule to restrict connection to the database:
Now let’s go back to my database and test how this command rule works:
SQL> conn scott Enter password: <your password> Connected. SQL> conn demo Enter password: <your password> ERROR: ORA-47400: Command Rule violation for CONNECT on LOGON Warning: You are no longer connected to ORACLE. SQL>
During the connect with demo user we get ERROR Command Rule violation. This means that any user other than scott are not allowed to connect to the database using SQLPLUS.
ORACLE DATABASE VAULT REPORTS
There are two categories of DV reports- Oracle database Vault reports and General Security reports
- Database Vault Reports are basically to give the information about the different components of the database vault such as Realms, their authorization violations, command rule violations, factors, rule sets and security application rules.
- General Security Reports are the general reports such as user authorization, privileges, rules, object and system privileges and also it gives special reports for the security vulnerability issues
To see this reports login to the Database Vault Administration console and click on the Database Vault Reports tab:
Click on Realm Audit Report and Run Report, now you can see report which shows you different kind of realm violations:
Now let’s look command rule audit reports, here we can see command and returned error codes:
Let’s see General Security Reports:
Similarly you can select and run different reports.