Installing Oracle DB Vault to Oracle 11g Database

Installing Oracle DB Vault to Oracle 11g Database

At this document; I will show you how you can install Oracle DB vault to Oracle 11g Database.

Step 1: Check If DB Vault installed before

We use GV$OPTION view to check this. I checked DB vault in two nodes RAC database.

SQL> column parameter format a25

SQL> column value format a25

SQL> SELECT * FROM gV$OPTION WHERE PARAMETER in ( ‘Oracle Database Vault’,’Oracle Label Security’);

INST_ID|PARAMETER |VALUE

———-|————————-|————————-

1|Oracle Label Security |FALSE

1|Oracle Database Vault |FALSE

2|Oracle Label Security |FALSE

2|Oracle Database Vault |FALSE

If Oracle database Vault and Oracle Label Security are already installed ( It means all TRUE returned from this query ) goto step 3;

Step 2: Enable Oracle Label Security and Oracle DB Vault

2.1 Close the Database

Shutdown Oracle database , stop listener ( If you opened a listener with this binary ) and stop enterprise manager ( If Enterprise manager uses this binary )

SQL> SHUTDOWN IMMEDIATE

$ lsnrctl stop listener

$ emctl stop dbconsole

2.2 Enable DB Vault Binaries

Now enable Oracle Label Security and Oracle database vault consequently with the following commands

$ chopt enable lbac

$ chopt enable dv

2.3 Open the Database

After enabling Oracle Label security and DB vault you have to open database and other closed applications.

SQL> startup

$ lsnrctl start listener

$ emctl start dbconsole

2.4 Check if Binaries linked properly

Now Check DB vault and Oracle Label Security.

SQL> column parameter format a25

SQL> column value format a25

SQL> SELECT * FROM gV$OPTION WHERE PARAMETER in ( ‘Oracle Database Vault’,’Oracle Label Security’);

INST_ID|PARAMETER |VALUE

———-|————————-|————————-

1|Oracle Label Security |TRUE

1|Oracle Database Vault |TRUE

2|Oracle Label Security |TRUE

2|Oracle Database Vault |TRUE

All returned values have to be TRUE

Step 3 : Install DB Vault

Now It is time  to install DB vault. Start installation with dbca

$ dbca

Choose Configure database option.

null

Chose the correct DB name to install DB vault.( If there are multiple instances at the same ORACLE_HOME)

null

Skip Enterprise Manager Configuration

null

Now Chose Oracle LAbel Security and Oracle Database Vault to install them

null

Now enter the usernames and passwords for Database Vault Owner and Account Manager separately.

null

Chose Finish to start the Installation

null

Now the installation will start

null

After this windows and the installation window will appear and shows the status of installation. After the installation finishes you can close dbca.

Now you installed Oracle DB Vault to your database. Just connect with  DB Owner and Account Manager usernames to the database to check the installation

At the following notes I will show you how you can manage Oracle DB Vault.

Security Control on Default Oracle Database Users

When you install Oracle databases , some predefined default  users are created.The name of the default users are known by hackers and these users are a attack surface for a database.  These default user passwords are the first passwords which are tried by hackers.Therefore the passwords of these users should be changed after the database installation and at the same time;  these users should be in EXPIRED & LOCKED status.

How we control the password of these passwords. Oracle database includes a view which control the password of these users;

SELECT * FROM DBA_USERS_WITH_DEFPWD;

This view shows the default users which have default passwords. Normally zero records should return from this query.

Let’s check at my database;

Capture

 

As you see nearly all the default users have default passwords at my test database.  Before changing their passwords we should  check the status of these users. If these users are in EXPIRED & LOCKED status. It is acceptable , although they have default passwords.  Zero record should return from this query, otherwise it is a big finding.

SELECT
A.USERNAME ,
B.ACCOUNT_STATUS
FROM
SYS.DBA_USERS_WITH_DEFPWD A,
DBA_USERS B
WHERE
A.USERNAME = B.USERNAME
AND B.ACCOUNT_STATUS <> ‘EXPIRED & LOCKED’;

Let’s run it at my test database;

Capture

Gotcha ; At my database there are two default users which are on OPEN mode . This  is a  finding what I have to  do is;  I have to  change these  passwords and then I have to change their status to EXPIRED & LOCKED too.

SQL> Alter user Adams identified by complexpasswd01
2         account lock password expire;

User altered.

SQL> alter user Orddata identified by complexpasswd02
2 account lock password expire;

User altered.

Now check all default user status;

Capture

 

Good  ; All default users are on EXPIRED & LOCKED status.  But still some of them have default passwords ( except Orddata and adams ). We have to change all default passwords and make them Expired & Locked with the below query

SELECT
‘Alter User ‘||USERNAME||’ identified by ‘
||dbms_random.string(‘U’, 6)
||trunc(dbms_random.value(1000,9999))
||’ account lock password expire;’
FROM
SYS.DBA_USERS_WITH_DEFPWD ;

 

Now run the output of the query;

Alter User DIP identified by JACYDY9781 account lock password expire;
Alter User MDSYS identified by KPIJES7846 account lock password expire;
Alter User SPATIAL_WFS_ADMIN_USR identified by VQOAHQ7579 account lock password expire;
Alter User CTXSYS identified by AQOXGV7508 account lock password expire;
Alter User OLAPSYS identified by RWQIOP7224 account lock password expire;
Alter User OUTLN identified by WZMAQB2175 account lock password expire;
Alter User SPATIAL_CSW_ADMIN_USR identified by YYLLQH7066 account lock password expire;
Alter User EXFSYS identified by CXDMCS3349 account lock password expire;
Alter User ORACLE_OCM identified by GXRUUP7532 account lock password expire;
Alter User DBSNMP identified by DXBASG8552 account lock password expire;
Alter User MDDATA identified by HPEFPE5098 account lock password expire;
Alter User ORDPLUGINS identified by GWOITV2439 account lock password expire;
Alter User ORDSYS identified by ZIPVNJ6941 account lock password expire;
Alter User APPQOSSYS identified by TCTUYF9776 account lock password expire;
Alter User XDB identified by QRGXXV3781 account lock password expire;
Alter User SI_INFORMTN_SCHEMA identified by MHYJOV6216 account lock password expire;
Alter User WMSYS identified by RMYTXH9752 account lock password expire;

With this query we changed default passwords and we made all users EXPIRED&LOCKED again.

Now If you query SYS.DBA_USERS_WITH_DEFPWD , zero record will return. It means that all default passwords have been changed.

SQL> SELECT * FROM DBA_USERS_WITH_DEFPWD;

no rows selected

Have a good day.

Now your default username passwords are secure.

Have a good day.

Anıl Akduygu

 

 

Oracle Database Security Assesment Tool DBSAT

Oracle Database Security Assessment Tool ( DBSAT) is a new security assessment product for Oracle databases. I heard it from Pedro Lopes ( (EMEA Field Product Manager at Oracle). He told to me give a try for this new product. Firsts,  I was reluctant about the subject . But after I tried it; I saw that it is very practical tool to see your potential vulnerabilities  at Oracle databases. It is very easy to install and you will get your report directly in a second.And It gives you time to think about your security bugs, you do not need to think about how you can install and start the product.

Overview of the Product

DBSAT runs on a Oracle database server to analyze database security. It is a command line program.It runs queries to collect information about the Oracle database and database server  For each database you have install and run it.

It has two parts;

DBSAT collector; runs queries to collect data

DBSAT reporter: Produces report from collected data and gives recommendation on different formats.

Installation

I made my installation on a Oracle Vm machine Linux 7 and I used Oracle 12c database.

Create a directory to work on DBSAT files

[oracle@ol7 ~]$ mkdir /home/oracle/dbsat

[oracle@ol7 ~]$ cd  /home/oracle/dbsat

Download  dbsat.zip file from Oracle Metalink and put this file to the dbsat directory.

Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1)

And Install the dbsat.zip

[oracle@ol7 dbsat]$ unzip dbsat.zip -d /home/oracle/dbsat

Directory Listing should be like that;

[oracle@ol7 dbsat]$ ls -lrt
total 520
-r-xr-xr-x. 1 oracle oinstall 24757 Sep 27 20:55 sat_analysis.py
-r-xr-xr-x. 1 oracle oinstall 9198 Oct 7 19:09 dbsat.bat
-r-xr-xr-x. 1 oracle oinstall 229245 Oct 21 19:09 sat_reporter.py
-r-xr-xr-x. 1 oracle oinstall 9039 Oct 21 19:09 dbsat
-r-xr-xr-x. 1 oracle oinstall 42135 Oct 27 21:11 sat_collector.sql
-rwxr-x—. 1 oracle dba 198362 Mar 5 10:09 dbsat.zip
drwxr-xr-x. 2 oracle oinstall 4096 Mar 5 10:12 xlsxwriter

Running DBSAT Collector

you can run DBSAT collector with a user which needs below privileges.

CREATE SESSION
SELECT on SYS.REGISTRY$HISTORY
Role SELECT_CATALOG_ROLE
Role DV_SECANALYST (if Database Vault is enabled)
Role AUDIT_VIEWER (12c only)
Role CAPTURE_ADMIN (12c only)
SELECT on SYS.DBA_USERS_WITH_DEFPWD (11g and 12c)
SELECT on AUDSYS.AUD$UNIFIED (12c only)

You can create a special user with these privileges or you can use a highly privileged user like I use .

First set the Oracle environments

Run the DBSAT collector with sys user like below; Note my DB name is DB3

At the end of the data collection; you need to enter a password to protect the collected data. Do not forget this password you will enter the same password to produce report.
[oracle@ol7 ~]$ cd /home/oracle/dbsat
[oracle@ol7 dbsat]$ ./dbsat collect “sys/manager as sysdba” DB3

DBSAT Collector completed successfully.

Calling /u01/app/oracle/product/12.1.0.2/db_1/bin/zip to encrypt DB3.json…

Enter password:
Verify password:
adding: DB3.json (deflated 86%)
zip completed successfully.

Running DBSAT Reporter

DBSAT reportor onyl needs Python 2.6 or later to run.

at the end of the report , the reporter will zip the files with a password

[oracle@ol7 ~]$ cd /home/oracle/dbsat

[oracle@ol7 ~]$  ./dbsat report DB3

Calling /usr/bin/zip to encrypt the generated reports…

Enter password:
Verify password:
adding: DB3.txt (deflated 79%)
adding: DB3.html (deflated 84%)
adding: DB3.xlsx (deflated 3%)
zip completed successfully.

At the end of the process you will get two files like below.

DB3.zip consists of collected data and DB3_report.zip contains reports.

[oracle@ol7 dbsat]$ ls -lrt  DB3*
-rw——-. 1 oracle oinstall 34030 Mar 5 16:12 DB3.zip
-rw——-. 1 oracle oinstall 61746 Mar 5 16:17 DB3_report.zip

Report Sample

Now copy the report file to your PC and open it

capture

The report contains below parts

capture

You can go to any parts from the summary part.

I added my report as an Excel file and pdf file

db3-oracle-database-security-risk-assessment

db3

Documentation

Some Documents about the DBSAT.

Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1)

Database Security Assessment Tool User Guide

https://docs.oracle.com/cd/E76178_01/SATUG/toc.htm#SATUG-GUID-C7E917BB-EDAC-4123-900A-D4F2E561BFE9

https://stefanpanek.wordpress.com/2017/02/04/oracle-dbsat-first-experience/

,

Creating Oracle Users

At this note; I will teach you how you can create a simple Oracle user. In fact there are many types of Oracle users , but at this note we only discuss about simple Oracle users.

To create an Oracle user first you need CREATE USER privilege. When you create a new database ; SYS user has DBA role and consequently has CREATE USER privilege.

Now check which users have  CREATE USER privilege with the below  query;

select grantee from dba_sys_privs where privilege=’CREATE USER’;
GRANTEE
——————————
DBA
SYS
APEX_040000
IMP_FULL_DATABASE

At the simplest way you can create an Oracle user with the below command. This command work on Oracle 11g and Oracle 12c databases.

CREATE USER test01
IDENTIFIED BY oracle
DEFAULT TABLESPACE users
QUOTA 500K ON users
TEMPORARY TABLESPACE temp
PROFILE DEFAULT;
User created.

This command was run by SYS user. You can find all details about CREATE USER command at Oracle documents.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8003.htm#SQLRF01503

https://docs.oracle.com/database/121/SQLRF/statements_8003.htm#SQLRF01503

 

After creating an Oracle user ;  you should give CREATE SESSION privilege  to the user. By this way your created user can connect to Oracle database. Otherwise It can not connect to the database.

SQL> Grant CREATE SESSION to test01;
Grant succeeded.

As you see; this user has very simple password.  Simple passwords are  not a good way to protect our users. The complex passwords is the first defense line to protect our databases. User password should be complex to predict by others. This is the first very important rule to protect our databases. At the same time you should not share the passwords with other persons. The passwords should only be known with the person to know.  And you should change the passwords periodically.

Put in a nut shell.

The simple rules to protect Database users is to manage user passwords correctly with below methods.

  • Use complex passwords
  • Do not share your passwords
  • The passwords should only be known with the person to know
  • Change your passwords periodically.

 

At the next notes I will write about how we can achive these goals.

 

 

 

 

How to find hidden granted roles at Oracle Database

This is very critical issue to find hidden granted roles to any user. For example If you want to find users whose have granted DBA roles.Normally , you can use below query at your database

SELECT GRANTEE, GRANTED_ROLE   FROM DBA_ROLE_PRIVS

WHERE GRANTED_ROLE=’DBA’

AND GRANTEE NOT IN (‘SYS’,’SYSTEM’);

But this kind of search does not show DBA users all time.

Lets give an example ;

We have an user named  appuser03 and we have two roles;  admin_role and admin_role01

Now ; give DBA grant to admin_role01 and assign   admin_role01 to admin_role and at the end assign admin_role to appuser03;

Let’s show it;

SQL> create role admin_role01;

Role created.

SQL> create role admin_role;

Role created.

SQL> grant dba to admin_role01;

Grant succeeded.

SQL> grant admin_role01 to admin_role;

Grant succeeded.

SQL> grant admin_role to appuser03;

Grant succeeded.

Now check the DBA users at your database;

SQL>

SELECT GRANTEE, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE=’DBA’
AND GRANTEE NOT IN (‘SYS’,’SYSTEM’);

GRANTEE                GRANTED_ROLE
————       —————–
ADMIN_ROLE01     DBA

As you see;  you can not see that APPUSER03 have DBA grant you still you have to make investigation about Admin_role01 to find APPUSER03 have DBA grant.

Instead use hierarchical  queries ; like this

SELECT DISTINCT a.grantee , granted_role
FROM
(
SELECT DISTINCT LEVEL level_deep, grantee, granted_role
FROM dba_role_privs
START WITH granted_role = ‘DBA’
CONNECT BY PRIOR grantee = granted_role ) a, dba_users b
WHERE a.GRANTEE = b.USERNAME AND
b.USERNAME NOT IN (‘SYSTEM’,’SYS’) AND
b.ACCOUNT_STATUS = ‘OPEN’

GRANTEE            GRANTED_ROLE
——————– ——————–
APPUSER03          ADMIN_ROLE

Bingo, at this query you can find that APPUSER03 have DBA role via ADMIN_ROLE role. Simply  you should revoke ADMIN_ROLE from APPUSER03 .

By hierarchical queries ; you can see which roles are granted to other roles.

The hierarchy of granted roles are shown below.

SQL>

SELECT DISTINCT LEVEL level_deep, grantee,granted_role
FROM  dba_role_privs WHERE grantee NOT in (‘SYS’,’SYSTEM’)
START WITH granted_role = ‘DBA’
CONNECT BY PRIOR grantee = granted_role
ORDER BY level_deep desc

LEVEL_DEEP   GRANTEE        GRANTED_ROLE
----------  -----------     --------------------
 3          APPUSER03        ADMIN_ROLE
 2          ADMIN_ROLE       ADMIN_ROLE01
 1          ADMIN_ROLE01     DBA

 

You can use this query to find other granted system roles like EXP_FULL_DATABASE or IMP_FULL_DATABASE ..

See you on the next note.

Anil Akduygu.

 

Oracle 12c New Security Features – 01, Use READ Privilige for Inquiry Users.

I would like to start a new series to introduce Oracle 12c new security features.

I want to start with READ privilege first. This privilege solves many problems at Oracle security. Before Oracle 12c you can give SELECT privilege to a user to SELECT any table. But with this privilege the user not only SELECT any table but also she or he can LOCK the table with SELECT for UPDATE or direct LOCK TABLE statement . This is an unwanted situation for inquiry users; because any inquiry user with  SELECT privilege can lock your table and stop your application. For this reason administrators would create views and then would grant these views to  inquiry users. It was an long operation if you had more than hundred tables and It was difficult to manage all views and tables.

To solve this problem; Oracle introduced a new privilege – READ privilege. With this privilege you can give SELECT grant to a user. But the user can only SELECT the table and he cannot lock it anymore.This enhancement is very important for inquiry users.

I will show you with examples.

Let’s start with Oracle 11g

SQL> connect scott/oracle

Connected.
SQL> grant SELECT on EMP to USER01;

Grant succeeded.

SQL> connect USER01/oracle

Connected.

SQL> select empno,ename from scott.emp;

EMPNO ENAME
———- ———-
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

14 rows selected.

And now the tricky point

SQL> select * from scott.emp for update of ename;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

……

And now nobody at the database can update this table till USER01 commit the transaction.

Try it. Connect with Scott user at the same time

sqlplus scott/oracle

SQL> update emp set ename=’XXX’;

This statement hangs forever till USER01 commits its transaction. Simply a SELECT statement hangs you your database and your application. This is unwanted sitiuation

You can lock the EMP table with Lock table command  with USERB01 as well.

SQL> Lock table scott.emp in exclusive mode;

Table(s) Locked.

Therefore At Oracle 11g It is very dangerous to give SELECT grant table on any tables to a inquiry user. Instead , you should create views and then you should grant these views to inquiry users.

Let’s look at Oracle 12c version now.

SQL> connect appuser/oracle;
Connected.
SQL> grant READ on test to appuser03;

Grant succeeded.

SQL> connect appuser03/oracle
Connected.
SQL> select * from appuser.test;

X
———-
1
2
3

SQL> lock table appuser.test in exclusive mode;
lock table appuser.test in exclusive mode
*
ERROR at line 1:
ORA-01031: insufficient privileges

As you see; you cannot lock with READ privilege

And then check for Select for Update command;
SQL> select * from appuser.test for update of x;
select * from appuser.test for update of x
*
ERROR at line 1:
ORA-01031: insufficient privileges

Simply; After upgrade your database to Oracle 12c, recheck the privileges of  inquiry users

and use READ privileges for them.

 

Anıl Akduygu

 

 

 

 

My Book About Oracle Database Security

My book about the Oracle Database security “ORACLE VERİTABANI GÜVENLİĞİ” is on the sale. You can find it D&R stores at Turkey. At the web;  you can get it from ABAKUS bookstore Web-site.

http://www.abakuskitap.com/oracle-veritabani-guvenligi

This book is about general Oracle database security and includes Oracle 11g and Oracle 12c  versions. It is a Turkish book.

 

oracle_guvenlik

Main Topics on the book

  • Oracle User Management
  • Enterprise User Security
  • Secure External Password Store
  • Authorization
  • Privilege Analysis
  • Application Context
  • Virtual Private Database
  • Security at Oracle Container Databases
  • Data Reduction
  • Auditing
  • New Security Features at Oracle 12c
  • Transperant Data Reduction
  • Encryption on the network
  • Security for Network Services

 

oracle_guvenlik-1

If you are interested to translate it to Engish , Please Contact to me.

 

Anıl Akduygu

 

yusufanilakduygu@gmail.com