Oracle DB Vault New Features in Oracle 12c R1 – Part 2 : Enabling DB Vault

In this article I will continue to describe the changes in Oracle DB Vault in  Oracle 12c version.

At the below note I explained the changes at DB Vault installation . At this note I will show you what has been changed to enable and disable DB Vault in Oracle 12c version.

https://yusufanilakduygu.wordpress.com/2017/04/16/oracle-db-vault-new-features-in-oracle-12c-changes-at-db-vault-installation/

The major changes is you have to connect to database as DB vault owner to disable and enable DB Vault in Oracle 12c. But in Oracle 11g version ,  oracle operating system user can enable and disable Oracle DB Vault. It means that ;  Oracle DBA can change DB vault status in Oracle 11g . But in version Oracle 12c only DB Vault owner can do this.

This is a big change and It makes DB Vault much more secure in Oracle 12c.

In Oracle 11g version

In Oracle 11g , you can disable and enable DB Vault bu only chopt command. Only oracle user ( operation system user ) can run this command from operating system. DBAs can disable Oracle DB Vault in Oracle 11g version and then after making changes at the Database DBAs can enable Oracle DB Vault without asking the Database Security officer. This is an insecure situation and Oracle changed it in Oracle 12c version.

Enable DB Vault in Oracle 11g

Shutdown the database
CONNECT SYS AS SYSOPER
Enter password: password

SHUTDOWN IMMEDIATE

Enable Oracle DB Vault

$ chopt enable lbac

$ chopt enable dv

And then startup the database

CONNECT SYS AS SYSOPER
Enter password: password

STARTUP

DISABLE DB Vault in Oracle 11g

Shutdown the database
CONNECT SYS AS SYSOPER
Enter password: password

SHUTDOWN IMMEDIATE

Disable  Oracle DB Vault

$ chopt disable dv

$ chopt disable lbac

And then startup the database

CONNECT SYS AS SYSOPER
Enter password: password

STARTUP

In Oracle 12c version

In Oracle 12c version you have to connect the database with an account which is a database owner. Simple; database owner can enable and disable Oracle DB vault in Oracle 12c version. And this is much more secure if you compare it with Oracle 11g version.

Enable DB Vault in Oracle 12c

connect as the Oracle Database Owner (DV_OWNER) account, and then enable Oracle Database Vault.

SQL> CONNECT dvowner
Enter password:
Connected.
SQL> EXEC DBMS_MACADM.ENABLE_DV;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

Note ; If Oracle Label security is not enabled before , You should enable it

CONNECT SYS AS SYSDBA
Enter password: password

EXEC LBACSYS.CONFIGURE_OLS;
EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;

and then restart the database;

CONNECT SYS AS SYSOPER
Enter password: password

SHUTDOWN IMMEDIATE

STARTUP 

Disable  DB Vault in Oracle 12c

connect as the Oracle Database Owner (DV_OWNER) account, and then disable Oracle Database Vault.

SQL> CONNECT dvowner
Enter password:
Connected.
SQL> EXEC DBMS_MACADM.DISABLE_DV;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

and then restart the database;

CONNECT SYS AS SYSOPER
Enter password: password

SHUTDOWN IMMEDIATE

STARTUP 

 

Advertisements

Finding Oracle Users with Import – Export Privileges – 2

Oracle Users with  IMPORT/EXPORT  role -2  DATAPUMP_IMP_FULL_DATABASE

Export utilities are used for extracting database objects and data from database to a file, and Import utilities are used for importing these extracted files into databases. In order to run IMPORT/EXPORT utilities you would have to have system roles which are given below.

  • IMP_FULL_DATABASE
  • DATAPUMP_IMP_FULL_DATABASE
  • EXP_FULL_DATABASE
  • DATAPUMP_EXP_FULL_DATABASE

These privileges should only be granted to authorized users. Normally database administrators should perform export and import operations. Therefore during our database assessment; we should find that these grants would only be given to DBA users.

If you want to list Oracle users  ( or roles ) which have DATAPUMP_IMP_FULL_DATABASE system role,  we could use the below query.  This query is developed by hierarchical query technique. Same query can be used on Oracle 11g and Oracle 12c versions.

Capture

The text version of the SQL are given below
SELECT
DISTINCT A.GRANTEE,
A.GRANTED_ROLE,
‘DATAPUMP_IMP_FULL_DATABASE’ GRANTED_CRITIC_ROLE
FROM
(
SELECT
DISTINCT LEVEL LEVEL_DEEP,
GRANTEE,
GRANTED_ROLE
FROM
DBA_ROLE_PRIVS
START WITH GRANTED_ROLE = ‘DATAPUMP_IMP_FULL_DATABASE’
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’;

 

In order to list users with DATAPUMP_IMP_FULL_DATABASE   in the multitenant architecture, we use the below query.

 

Capture

The text version of this query is given below.

SELECT
DISTINCT A.GRANTEE,
A.GRANTED_ROLE,
B.COMMON,
C.NAME,
‘DATAPUMP_IMP_FULL_DATABASE’ GRANTED_CRITIC_ROLE
FROM
(
SELECT
DISTINCT LEVEL LEVEL_DEEP,
GRANTEE,
GRANTED_ROLE,
CON_ID
FROM
CDB_ROLE_PRIVS
START WITH GRANTED_ROLE = ‘DATAPUMP_IMP_FULL_DATABASE’
CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
AND PRIOR CON_ID = CON_ID
) A,
CDB_USERS B,
V$CONTAINERS C
WHERE
A.GRANTEE = B.USERNAME
AND B.USERNAME NOT IN(
‘SYSTEM’,
‘SYS’
)
AND B.ACCOUNT_STATUS = ‘OPEN’
AND A.CON_ID = C.CON_ID
AND B.CON_ID = C.CON_ID ;

 

Finding  users with  EXP_FULL_DATABASE Role;

Capture

And the last one DATAPUMP_EXP_FULL_DATABASE ;

Capture.JPG

 

Now look the the same SQLs in Multitenant Architecture ;

EXP_FULL_DATABASE Role for Multitenant Architecture

Capture

 

And the last one DATAPUMP_EXP_FULL_DATABASE for Multitenant Architecture

Capture

 

If we want to revoke IMPORT/EXPORT privileges from a user;  we could use below commands

 

REVOKE IMP_FULL_DATABASE FROM UserName;

REVOKE DATAPUMP_ FULL_DATABASE FROM UserName;

REVOKE EXP_FULL_DATABASE FROM UserName;

REVOKE DATAPUMP_EXP_FULL_DATABASE FROM UserName;

Oracle DB Vault New Features in Oracle 12c R1 – Part1 : Changes at DB Vault Installation

There are many changes at Oracle DB Vault in Oracle 12c version. At this note I will give you information about  the change at DB  Vault Installation .

  1. DB Vault Installation

At Oracle 11g version you need to  relink Oracle binary before installing Oracle DB Vault. You do not need this operation in Oracle 12c.

At Oracle 11g version you need to relink binary with chopt command like below

$ chopt enable lbac

$ chopt enable dv

And You do not need in Oracl 12c vesion

In order to install and configure DB Vault In Oracle 11g version, you have to use dbca. At Oracle 12c you only need dbca to install Oracle Label Security and Oracle DB vault component . You can use DVSYS.CONFIGURE_DV packet to configure DB vault. Actually still you can do this configuration with dbca but it is optional.

DB Vault component installation is made with this page in  dbca in Oracle 12c version.

Capture

And DB Vault configuration can be made by dbca but It is optional

Capture

This gives us flexibility ; During database installation DBAs can install Oracle DB Vault without making any configuration on it. After the DB Vault installation is completed, you can make DB Vault configuration as a security officer without DBA intervention.

The below  query shows that DB Vault component is installed. But It does not mean that It is enabled.

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

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

With DVSYS.CONFIGURE_DV; you can mention which user is DB Vault admin and which user database account manager.

SQL> BEGIN
2 DVSYS.CONFIGURE_DV (
3 dvowner_uname => ‘dvowner’,
4 dvacctmgr_uname => ‘dvacctmngr’);
5 END;
6 /

PL/SQL procedure successfully completed.

This packet is new in Oracle 12c and It gives us flexibility to security officer to configure DB vault alone. After DB Vault component installation tou do not need to rebound the database bu you need to run utlrp.sql to compile all invalid objects.

 

 

 

 

 

A SIMPLE SOLUTION FOR ORACLE TNS REMOTE VERSION DISCLOSURE

A SIMPLE SOLUTION FOR ORACLE LISTENER REMOTE VERSION DISCLOSURE

At this note I will give you some brief information about how you can hide Oracle listener version information from hackers and network scanning tools. This vulnerability is called tns remote version disclosure.

First we know that ; there is no parameters at Oracle listeners to hide version information.

And the legal solution for this problem is to filter network traffic by any means. But this solution can be very expensive to hide version information, If you think about you have many listeners.

Therefore the simple solution is to change the default listener configuration.

The main point is that ; hackers assume that you are using factory setting at your database listener. At the factory settings the name of the listener is LISTENER and the default port is 1521 or 1522

Now ; After installing Oracle binary if you do not change default listener parameters. Hackers can get your installed binary version name  by sending version command to 1521 port. The output will be liket that.

null

After getting this information; Hackers will try the known vulnerabilities to hack your database . Therefore it is important to hide Oracle binary version.

You can do it very easily way. Change default listener name to any complicated name. Like on the following picture.

null

And then restart the listener.

After that ; when any scanning tools or hackers attempt to find Oracle binary version. They will get the below answers.

null

After changing Listener Name, if you change default port. It will be much more difficult for hackers to hack Oracle listener. But do not forget that changing listener port could be difficult job.

I will work  on listener security topics on the next notes.

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.