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/

,

Advertisements

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

HOW TO UNINSTALL ORACLE DB VAULT

 

 

At this note; I will show you How you can uninstall DB vault from an Oracle 11g R2 database. This can be necessary for many reasons.

For example When  you do not want to use DB Vault option at  your  database you can decide to completely uninstall  DB Vault option.

Sometimes during installation there can be some problems and your installation halts at the middle. After solving the problem at the database, you need to uninstall uncompleted installation.

 

Configuratin is like this;

Host : Oracle Linux 6

Database : Oracle 11g R2 ( 11.2.0.4)

 

Now we can start to uninstall

Check the DB Vault is already installed first

SQL> column parameter format a40

SQL> column value format a10

 

SQL> Select parameter, value from v$option where parameter in (‘Oracle Database Vault’,’Oracle Label Security’);

 

PARAMETER                         VALUE

————————————- ———-

Oracle Label Security                   TRUE

Oracle Database Vault                   TRUE

 

Shutdown database and Stop listener

 

sqlplus / as sysdba

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

 

oracle@localhost admin]$ lsnrctl stop

 

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 22-SEP-2016 10:52:39

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.2.15)(PORT=1521)))

The command completed successfully

If you have Database Control; stop it as well.

Unlink Oracle Label Security

 

This operation is not necessary ; If you want to use Oracle Label Security you can kip this step

 

[oracle@localhost admin]$ chopt disable lbac

 

Writing to /u01/app/oracle/product/11.2.0/db_1/install/disable_lbac.log…

/usr/bin/make -f /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk lbac_off ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

/usr/bin/make -f /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

 

Unlink Oracle Database Vault

 

[oracle@localhost admin]$ chopt disable dv

Writing to /u01/app/oracle/product/11.2.0/db_1/install/disable_dv.log…

/usr/bin/make -f /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk dv_off ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

/usr/bin/make -f /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

 

[oracle@localhost admin]$

Start Database and listeners

 

[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 10:56:23 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size            2253784 bytes

Variable Size           1056967720 bytes

Database Buffers    587202560 bytes

Redo Buffers                7094272 bytes

Database mounted.

Database opened.

 

[oracle@localhost ~]$ lsnrctl start

 

Listening Endpoints Summary…

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

Services Summary…

Service “DB11G” has 1 instance(s).

Instance “DB11G”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

Find the users who have  DV_OWNER and DV_ACCTMGR roles;

 

SQL> select unique GRANTEE from dba_role_privs

where GRANTED_ROLE in (‘DV_ACCTMGR’,’DV_OWNER’)

 and grantee <> ‘DVSYS’;

 

GRANTEE

——————————

DVOWNER

DVACCTMNGR

 

Turn OFF  recyclebin   and rebound the database

 

SQL> conn / as sysdba

Connected.

 

SQL> alter system set recyclebin=off scope=spfile;

System altered.

SQL> startup force

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size            2253784 bytes

Variable Size           1056967720 bytes

Database Buffers    587202560 bytes

Redo Buffers                7094272 bytes

Database mounted.

Database opened.

 

Run dvremov.sql

SQL> conn / as sysdba

Connected.

start  ?/rdbms/admin/dvremov.sql

 

.

.

.

 PL/SQL procedure successfully completed.

 

 

Manually drop DV_OWNER and DV_ACCTMNGR users

 

conn / as sysdba

SQL> drop user  DVACCTMNGR cascade;

User dropped.

SQL> drop user  DVACCTMNGR cascade;

User dropped.

 

Turn on recyclebin  and restart the database

conn / as sysdba

alter system set recyclebin=on scope=spfile;

 

Check DB Vault Option

 

SQL> column parameter format a40

SQL> column value format a10

SQL> Select parameter, value from v$option where parameter in (‘Oracle Database Vault’,’Oracle Label Security’);

 

PARAMETER                    VALUE

——————————- ———-

Oracle Label Security              FALSE

Oracle Database Vault              FALSE

 

Now Oracle DB Vault option is uninstalled from your database. If you want you can install it cleanly.

 

At this note ; I showed uninstallation of Oracle DB Vault  for Oracle 11g R2 database . For other versions there can be small differences. You can find all detailed information at the Metalink note.

How To Uninstall Or Reinstall Database Vault in 11g (Doc ID 803948.1)

Thanks Anıl Akduygu.

 

 

Adding Oracle Label Security and DB Vault Options to Oracle 12c Database

Now at this note; I will show you how you can install Oracle Label Security and DB vault to Oracle 12c database.

Please be sure that Oracle Label Security( OLS ) and DB Vault is not installed yet.

Below query should return no rows at this case.

SQL> select comp_id,status from dba_registry where comp_id in (‘OLS’,’DV’);

no rows selected

If Oracle Label Security and DB Vault is already installed. You just need to register DB Vault.In that case follow below link.

https://yusufanilakduygu.wordpress.com/2016/07/28/installing-db-vault-to-an-oracle-12c-non-container-database/

Now  Let me introduce the environment;

Host : Oracle 7 Linux virtual  machine on Oracle VM Virtual Box

DB : Oracle 12c 12.1.0.2.0 RAC non-container database with four nodes.

Start  Database configuration assitant to install OLS and DB Vault.

$ dbca

Choose “Configure Database Options”

12c DB Vault-01 (1)

 

Now Select the database to install OLS and DB Vault and enter sys password.

12c DB Vault-02

 

Skip Directory Service tab ( you are not installing OID )12c DB Vault-03

 

Choose Oracle Label Security and Oracle Database Vault at Database Components tab.

12c DB Vault-04

 

Now ; enter DB Vault owner and Account Manager  usernames and passwords separately

12c DB Vault-06

 

Select the mode of Oracle database . Normally Dedicated Server.

12c DB Vault-07

 

Now a summary page will appear.

12c DB Vault-08

 

Select Yes at confirmation window

12c DB Vault-09

 

And the installation will start. You will see the progress

12c DB Vault-10

 

At the end the configuration has completed.

12c DB Vault-11

 

Now OLS and DB vault installed and DB Vault is registered as well.

You can check it with below queries.

SQL> select comp_id,status from dba_registry where comp_id in (‘OLS’,’DV’);

COMP_ID STATUS
—————————— ———–
DV VALID
OLS VALID

SQL> column parameter format a25
SQL> column value format a10
SQL>
SQL> SELECT * FROM gv$OPTION WHERE PARAMETER in ( ‘Oracle Database Vault’,’Oracle Label Security’);

INST_ID PARAMETER VALUE CON_ID
———- ————————- ———- ———-
1 Oracle Label Security TRUE 0
1 Oracle Database Vault TRUE 0
4 Oracle Label Security TRUE 0
4 Oracle Database Vault TRUE 0
3 Oracle Label Security TRUE 0
3 Oracle Database Vault TRUE 0
2 Oracle Label Security TRUE 0
2 Oracle Database Vault TRUE 0

 

Thanks in advance

 

Anıl Akduygu.