MS SQL Server Vulnerability Assessment (VA) Tool

SQL Vulnerability Assessment(VA) is a tool that can help user to find potential security vulnerabilities in MS SQL Server databases. This product is supported from MS SQL Server 2012 and later. This tool is only available on SQL Server Management Studio           (SSMS)version 17. 4  and later.

You can find the latest version of SSMS from the below site

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

The VA runs only a database at a time. This product finds excessive permissions, database vulnerabilities, sensitive data and recommends  solutions for  weak points. You can find the SQL queries for each tests. But you can not add any new queries or you can not change any test.You can accept the risk of any test on VA to produce your security baseline.

To run VA;  Select your database  then right click ; point to  Tasks  and then Choose Vulnerability Assessment and click on Scan for Vulnerabilities ( Picture -1 )

Capture

(Picture-1)

Before the vulnerability scan runs ; It asks for a directory to save the Assessment result ( Picture-2) .  when you press OK vulnerability scan runs immediately.

Capture.JPG

(Picture-2)

After the scan complete; a new SQL window opens to show you the result ( Picture -3)

Capture.JPG

(Picture-3)

You can click on any check to get a detailed information about it (Picture-4).

Capture

(Picture-4)

and if you want to approve it as baseline just click on Approve as Baseline button on the report and then you will get the below indication ( Picture -5)

Capture

(Picture-5)

You can see the queries for each check.(Picture -6)

Capture

And you get remediation plan for the security check ( Picture-7)

Capture

As a result with the VA tool is a good starting point to harden your SQL databases. I hope you like it.

Thanks for reading this short note.

Anıl Akduygu

Advertisements

New Information about Spectre and Meltdown vulnerabilities in Oracle products

Oracle made new announcements about Spectre and Meltdown vulnerabilities. Simply Oracle offered new solutions for the below products.

Affected Products Patch Availability
Oracle Audit Vault and Database Firewall [Product ID 9749] MOS note 2359424.1
Oracle Big Data Appliance [Product ID 9734] MOS note 2357485.1
Oracle Exadata Database Machine [Product ID 2546] MOS note 2356385.1
Oracle Exalogic Elastic Cloud [Product ID 9415] MOS note 2348852.

This chart is taken from; Addendum to the January 2018 CPU Advisory for Spectre and Meltdown (Doc ID 2347948.1)

Put in a simple:

For Oracle Exadata machine:   the minimum versions of Exadata Storage Software required to resolve the vulnerabilities are 18.1.4.0.0 and  12.2.1.1.6  for  Spectre CVE-2017-5753   and CVE-2017-5754.

For  Meltdown CVE-2017-5715, Oracle is waiting for microcode update from Intel for X86 processors.

For Big Data appliance: There is a remediation plan in the document: How To Upgrade a Kernel on BDA V4.2 and Higher/V4.1 (Doc ID 2033797.1) – a Metalink note.

For Exalogic Linux: There is a remediation plan in the Metalink note: Patch Availability for Spectre (CVE-2017-5753 and CVE-2017-5715) and Meltdown (CVE-2017-5754) vulnerabilities on Oracle Exalogic Linux Physical and Virtual Racks (Doc ID 2348852.1)

For Oracle Audit Vault and Database Firewall: There is a remediation plan in the Metalink note:   Patch Availability for Spectre (CVE-2017-5753 and CVE-2017-5715) and Meltdown (CVE-2017-5754) vulnerabilities on Oracle Exalogic Linux Physical and Virtual Racks (Doc ID 2348852.1)

For the first Oracle announcement; you can read this document.

https://yusufanilakduygu.wordpress.com/2018/01/17/oracle-announcement-about-spectre-and-meltdown/

Thanks for reading this note.

Anıl Akduygu

Privilege Analysis in Oracle 12c A Quick Overview

Privilege Analysis is a new feature of Oracle 12c . This feature comes with Oracle DB Vault. Simple you have to buy Oracle DB Vault license to use Privilege Analysis. But, you do not need to enable Oracle DB Vault to use Privilege Analysis, Because  It comes with Oracle 12c Enterprise edition.

Privilege Analysis is used for identifying unused privileges and roles in the database. Discovering the set of unused roles and privileges is important to make the database more secure. By using Privilege Analysis, we can define the least number of privileges for users and roles.

The procedure for Privilege Analysis is simple;

The First  Step;

You have to create a privilege analysis with DBMS_PRIVILEGE_CAPTURE package .

In order to use privilege analysis; CAPTURE_ADMIN  must be granted to the user.

There are four types of privilege analyses which are defined by type parameter in the DBMS_PRIVILEGE_CAPTURE package.

type        =>  DBMS_PRIVILEGE_CAPTURE.g_database is used for creating a privilege analysis for the whole database

type       =>  DBMS_PRIVILEGE_CAPTURE.g_role  is used for creating a privilege analysis for a list of roles.

type  => DBMS_PRIVILEGE_CAPTURE.g_context  is defined by a logical expression with the  SYS_CONTEXT function.

Type=> DBMS_PRIVILEGE_CAPTURE.g_role_and_context; is defined by a list of rules and logical expression.

For Example; to create a privilege analysis for the whole database we use below command

BEGIN
DBMS_PRIVILEGE_CAPTURE.create_capture(
name => 'Full Database',
type => DBMS_PRIVILEGE_CAPTURE.g_database
);
/
PL/SQL procedure successfully completed.

In order to create a privilege analysis for a set of defined roles, we use the below command.

BEGIN
DBMS_PRIVILEGE_CAPTURE.create_capture(
name => 'Listed Roles',
type => DBMS_PRIVILEGE_CAPTURE.g_role,
roles => role_name_list('RoleName1', 'RoleName2') );
END;
/

PL/SQL procedure successfully completed.

In order to create a privilege analysis for USER01 user , we use the below command.

BEGIN
DBMS_PRIVILEGE_CAPTURE.create_capture(
name => 'Conditional',
type => DBMS_PRIVILEGE_CAPTURE.g_context,
condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''USER01'''
);
END;
/

In order to create a privilege analysis for USER01 when it uses DBA role we use the below command. By this way ; we can find for what reason USER01 uses DBA role. For example ; USER01 uses DBA role to only create  tables. In that case; we can only give create table privilege to USER01 instead of DBA role.

BEGIN
DBMS_PRIVILEGE_CAPTURE.create_capture(
name => 'Role and Condition',
type => DBMS_PRIVILEGE_CAPTURE.g_role_and_context,
roles => role_name_list('DBA'),
condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''USER01'''
);                                                                                                                                                                      END;
/

PL/SQL procedure successfully completed.

We use the below command  to list the list of created privilege analyses.

COLUMN name FORMAT A15
COLUMN roles FORMAT A20
COLUMN context FORMAT A30

SQL> select name,type,roles,context FROM dba_priv_captures;

The Second  Step;

We start the privilege analysis with the below command.

BEGIN
DBMS_PRIVILEGE_CAPTURE.enable_capture('Privilege Analysis Name');
END;
/

PL/SQL procedure successfully completed.

 

The Third Step;

After waiting for a while; I can be one  week or  one moth.  We have  stop the privilege analysis with the below command. During that time Oracle keeps records for the privilege analysis.

BEGIN
DBMS_PRIVILEGE_CAPTURE.disable_capture('Privilege Analysis Name');
END;
/

PL/SQL procedure successfully completed.

The Fourth Step;

We should generate result for the capture with the below command;

BEGIN
DBMS_PRIVILEGE_CAPTURE.generate_result('Privilege Analysis Name');
END;
/

PL/SQL procedure successfully completed.

 

The Fifth Step;

Now we use the below views to work on our captured data.

DBA_PRIV_CAPTURES
DBA_USED_PRIVS
DBA_UNUSED_PRIVS
DBA_USED_OBJPRIVS
DBA_UNUSED_OBJPRIVS
DBA_USED_OBJPRIVS_PATH
DBA_UNUSED_OBJPRIVS_PATH
DBA_USED_SYSPRIVS
DBA_UNUSED_SYSPRIVS
DBA_USED_SYSPRIVS_PATH
DBA_UNUSED_SYSPRIVS_PATH
DBA_USED_PUBPRIVS
DBA_USED_USERPRIVS
DBA_UNUSED_USERPRIVS
DBA_USED_USERPRIVS_PATH
DBA_UNUSED_USERPRIVS_PATH

 

thanks for reading this note;

In the near future, I will give much more information about this subject.

Y. Anıl Akduygu

 

DBHack – Black Box Database Testing Tool

 

Hello guys

I developed a free open source software appliance – DBHack –   for ethical hackers to make black box testing on databases. All of its codes is on the GitHub. But it is served as an Oracle VirtualBox machine. It is free and can be used anywhere. At this version only Oracle and MS SQL Server black box tests are ready. It is still under development. You can download and get information from below web address.

https://www.dbsecurity.info/

I hope that you will try it.

Capture

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 

 

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.

 

 

 

 

 

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.