DBA : Step by Step procedure to install Oracle Database Vault

Oracle database vault is used for separations of duties i.e., restrict access to specific data in database for super users includes administrative access.

Once DB vault is enabled, following parameters are affected.

Parameter	Default	New value
AUDIT_SYS_OPERATIONS	FALSE	TRUE
OS_AUTHENT_PREFIX	ops$	ops$
OS_ROLES	Not configured	FALSE
REMOTE_LOGIN_PASSWORDFILE	EXCLUSIVE	EXCLUSIVE
REMOT_OS_AUTHENT	FALSE	FALSE
REMOTE_OS_ROLES	FALSE	FALSE
SQL92_SECURITY	FALSE	TRUE

It creates new roles once it is installed. Database vault owner (DV_OWNER) and database vault account manager (DV_ACCTMGR).

List of roles and privileges that will be revoked are as follows.

Role	Privileges that will be revoked
DBA	BECOME USER
	SELECT ANY TRANSACTION
	CREATE ANY JOB
	CREATE EXTERNAL JOB
	EXECUTE ANY PROGRAM
	EXECUTE ANY CLASS
	MANAGE SCHEDULER
	DEQUEUE ANY QUEUE
	ENQUEUE ANY QUEUE
	MANAGE ANY QUEUE

IMP_FULL_DATABASE	BECOME USER
	MANAGE ANY QUEUE

EXECUTE_CATALOG_ROLE	EXECUTE ON DBMS_LOGMNR
	EXECUTE ON DBMS_LOGMNR_D
	EXECUTE ON DBMS_LOGMNR_LOGREP_DICT
	EXECUTE ON DBMS_LOGMNR_SESSION
	EXECUTE ON DBMS_FILE_TRANSFER

PUBLIC user	EXECUTE ON UTL_FILE

SCHEDULER_ADMIN	CREATE ANY JOB
	CREATE EXTERNAL JOB
	EXECUTE ANY PROGRAM
	EXECUTE ANY CLASS
	MANAGE SCHEDULER

Following privileges are affected for SYS & SYSTEM users.

Ø Alter user

Ø Alter profile

Ø Create profile

Ø Create user

Ø Drop profile

Ø Drop user

How Auditing table is affected?

When Oracle DB vault is enabled, the aud$ table is moved out of SYS schema to SYSTEM user. Synonym sys. Aud$ is created referring to system.aud$ table.

When auditing is enabled, DB vault will create several settings to change and it enables the auditing.

What changes when VPD or Fine grained auditing policies.

If users plan to create VPD or FGA policies, DBA cannot create as the EXECUTE privilege on DBMS_RLS pl/sql is revoked as this package is no more with DBA. It is assigned to DB_ADMIN user, so we need to EXECUTE privilege to DV_ADMIN user.

As soon as DB vault is installed, database auditing is configured but auditing is not enabled. It will be enabled once we issue “alter system set audit_trail”.

Components in DB vault

Realms - Group of schemas, objects or roles that must be secure.

Command Rules - It is a special rule that we can create to control how users can execute a SQL statements like select,insert,DDL and DML. This works with rule sets.

Factors – It is a variable or attribute such as user location, ip address or session user for DB vault to recognize and secure.

Rule Sets – It is a collection of 1 or more rules that we can associate with realm authorization on how to view the data , evaluate and secure.

Secure Application roles - It is a special DB role that can be enabled based on the evaluation of a DB vault rule set.

Schemas that are created in the DB

DVSYS - It contains the database objects needed to process data by DB vault. Contains roles, views, accounts, functions and other objects which DB vaults use. It is locked by default.

DVF - Contains the public functions to retrieve the factor values for DB vault.

1. Check whether Oracle database vault is enabled in the database

SQL> col parameter format a35

SQL> col value format a10

SQL> select * from v$option where parameter='Oracle Database Vault';

PARAMETER VALUE

----------------------------------- ----------

Oracle Database Vault FALSE

2. As per database vault, it revokes all privileges from the user once we enable it. So, before enabling, we will check the privileges for user TESTBUF and TEST1 and BCTEST (with dba privileges)

User, his roles and privileges

-----------------------------------------------------------------

TESTBUF

CONNECT

CREATE SESSION

RESOURCE

CREATE CLUSTER

CREATE INDEXTYPE

CREATE OPERATOR

CREATE PROCEDURE

CREATE SEQUENCE

CREATE TABLE

CREATE TRIGGER

CREATE TYPE

UNLIMITED TABLESPACE

User, his roles and privileges

-----------------------------------------------------------------

TEST1

ALTER ANY TABLE

CONNECT

CREATE SESSION

CREATE ANY EDITION

CREATE ANY SYNONYM

CREATE ANY TABLE

CREATE PROCEDURE

CREATE SEQUENCE

CREATE TABLE

CREATE TRIGGER

CREATE VIEW

DROP ANY EDITION

DROP ANY TABLE

INSERT ANY TABLE

RESOURCE

CREATE CLUSTER

CREATE INDEXTYPE

CREATE OPERATOR

CREATE PROCEDURE

CREATE SEQUENCE

CREATE TABLE

CREATE TRIGGER

CREATE TYPE

SELECT ANY DICTIONARY

SELECT ANY TABLE

UNLIMITED TABLESPACE

UPDATE ANY TABLE

NVL(B.GRANTEE,'NO_ROLES_GRANTE NVL(B.GRANTED_ROLE,'NULL')

------------------------------ ------------------------------

BCTEST DBA

BCTEST RESOURCE

BCTEST CONNECT

3. In our case, the database vault is DISABLED.

4. Now, we will see how to enable database vault in Oracle 11gR2 after the database is installed.

Standalone

a. Shut down the database.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

b. Stop database control and Listener.

[oracle@vm1 lib]$ emctl stop dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0

https://vm1:5500/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...

... Stopped.

[grid@vm1 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2014 15:17:31

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

The command completed successfully

For RAC, use

Srvctl stop database –d <db_name>

c. Enable DB vault as follows. Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on). You must enable Oracle Label Security before you can use Database Vault.

cd $ORACLE_HOME/rdbms/lib

make –f ins_rdbms.mk dv_on lbac_on ioracle

[oracle@vm1 lib]$ make -f ins_rdbms.mk dv_on lbac_on ioracle

/usr/bin/ar d /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libknlopt.a kzvndv.o

/usr/bin/ar cr /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/kzvidv.o

/usr/bin/ar d /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libknlopt.a kzlnlbac.o

/usr/bin/ar cr /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/kzlilbac.o

chmod 755 /u01/app/oracle/product/11.2.0/dbhome_1/bin

- Linking Oracle

rm -f /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle

gcc -o /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle -m64

……………………

………………………

test ! -f /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle ||\

mv -f /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracleO

mv /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

chmod 6751 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

d. Start the listener,database control, and the database.

[grid@vm1 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2014 15:28:07

Starting /u01/app/grid/product/11.2.0/grid/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora

Log messages written to /u01/app/grid/diag/tnslsnr/vm1/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 03-MAR-2014 15:28:08

Uptime 0 days 0 hr. 0 min. 11 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/vm1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm1)(PORT=1521)))

The listener supports no services

The command completed successfully

[oracle@vm1 lib]$ emctl start dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0

https://vm1:5500/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ................. started.

------------------------------------------------------------------

Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/vm1_noasmdb/sysman/log

SQL> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2227456 bytes

Variable Size 197133056 bytes

Database Buffers 58720256 bytes

Redo Buffers 4968448 bytes

Database mounted.

Database opened.

Note :For Oracle RAC installations, repeat these steps for each node on which the database is installed.

5. Uncomment the parameter in sqlnet.ora file,, else , while installing DB vault , we will end up with invalid username or password.

SQLNET.AUTHENTICATION_services=(NONE)

6. Now, start DBCA and add the db owner and db admin account.

Select ‘Label Security’ first to enable Database vault, else, it will be not grayed out.

Specify the users for database vault owner and database vault account manager (optional).

Dbvowner/***** , dbvam/***** (Make sure the password has 1 special character and numbers with minimum 8 characters)

During installation in alert log file,

Database Vault - DV enforcement status is set to 1

ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE;

ALTER SYSTEM SET os_roles=FALSE SCOPE=SPFILE;

ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;

ALTER SYSTEM SET remote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE;

ALTER SYSTEM SET sql92_security=TRUE SCOPE=SPFILE;

7. Let us check whether DB vault is enabled now

SQL> select * from v$option where parameter='Oracle Database Vault';

PARAMETER VALUE

---------------------------------------------------------------- ----------------------------------------------------------------

Oracle Database Vault TRUE

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

During installation in alert log file,

Database Vault - DV enforcement status is set to 1

ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE;

ALTER SYSTEM SET os_roles=FALSE SCOPE=SPFILE;

ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;

ALTER SYSTEM SET remote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE;

ALTER SYSTEM SET sql92_security=TRUE SCOPE=SPFILE;

1. Let us check whether DB vault is enabled now

SQL> select * from v$option where parameter='Oracle Database Vault';

PARAMETER VALUE

---------------------------------------------------------------- ----------------------------------------------------------------

Oracle Database Vault TRUE

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

8. Let us check whether any changes in role had happened for BCTEST user.

NVL(B.GRANTEE,'NO_ROLES_GRANTE NVL(B.GRANTED_ROLE,'NULL')

------------------------------ ------------------------------

BCTEST DBA

BCTEST RESOURCE

BCTEST CONNECT

As we see no changes in roles, but the privileges listed initially were not there for BCTEST user when we compared with roles taken before installing audit vault. .

Eg. Under DBA role, BECOME USER,CREATE ANY JOB, CREATE ANY QUEUE.

9. The roles and schema that are created by DB vault are as follows.

SQL> select username,account_status from dba_users where username like 'D%';

USERNAME ACCOUNT_STATUS

------------------------------ --------------------------------

DBSNMP OPEN

DBVOWNER OPEN

DVSYS LOCKED

DVF LOCKED

DIP EXPIRED & LOCKED

SQL> select role from dba_roles where role like 'DV%';

ROLE

------------------------------

DV_SECANALYST

DV_MONITOR

DV_ADMIN

DV_OWNER

DV_ACCTMGR

DV_PUBLIC

DV_PATCH_ADMIN

DV_STREAMS_ADMIN

DV_GOLDENGATE_ADMIN

DV_XSTREAM_ADMIN

DV_REALM_RESOURCE

DV_GOLDENGATE_REDO_ACCESS

DV_REALM_OWNER

11. If the application is dependent on the privileges that are revoked for the roles, we can grant it again using following commands.

Grant create any job to dba;

Grant create external job to dba;

Grant become user to dba;

Grant become user to imp_full_Database;

Grant dequeue any queue to dba;

Grant enqueue any queue to dba;

Grant execute any program to dba;

Grant manage any queue to imp_full_database;

Grant create any job to scheduler_admin;

Grant create external job to scheduler_admin;

Grant execute any class to scheduler_admin;

Grant execute any program to scheduler_admin;

Grant manage scheduler to scheduler_admin;

Grant execute on utl_file to public;

DBA

Pages

Tuesday, June 3, 2014

Step by Step procedure to install Oracle Database Vault - Standalone

No comments:

Post a Comment

Pages

Blog Archive