Veritabanı Güvenliği Notları

Veritabanı Güvenliği Notları

veritabanı Güvenliği

 

Advertisements

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

Transparent Data Encryption for MS SQL Server Databases

With Transparent Data Encryption; you can encrypt data at rest to protect your data files especially from  theft.If you move all data files of a database from one SQL Server to another SQL Server; you can easily browse the data in the database. By using TDE;  if your backup or physical media are stolen; The data in the database can not be read by any means.

TDE performs I/O encryption and decryption of data and log files online. Developers do not need to change their programs. You can query your tables like before you did. Encryption of   the data files is done  at he page level automatically by background processes.

The Process to make encryption by TDE.

  1. At MASTER database; Create a Master Key
  2. At MASTER database; Create a Certificate .
  3. At the database that you want to encrypt ; Create database encryption Key.
  4. The last step; Encrypt the database.

Now; Let’s make sample to show  you TDE process. At this sample we will encrypt test01 database (Picture -1)

Capture

( Picture -1 )

After creating Master key and Certificate you should backup them with the below commands.(Picture-2)

Capture.JPG

(Picture-2)

Now In order to check that your database is encrypted; Look at the properties of the database ( Picture-3)

Capture

(Picture-3)

In the Options section of the database; Encryption Enabled state must be True in encrypted databases.

Thanks for reading this note.

Anıl Akduygu.

 

April 2018 Oracle Critical Patch Update

Oracle announced April  2018 Critical Patch Update. This patch includes  254 new security fixes. At the same, this patch contains a special addendum  which is called; Addendum to the January 2018 CPU Advisory for Spectre and Meltdown (Doc ID 2347948.1) about Spectre (CVE-2017-5753, CVE-2017-5715) and Meltdown (CVE-2017-5754) vulnerabilities.

Patch Availability Table for Spectre & Meltdown vulnerabilities
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.1
Oracle Key Vault [Product ID 10221] MOS note 2366657.1
Oracle Linux [Product ID 1309] MOS note 2348448.1
Oracle Private Cloud Appliance [Product ID 10635] MOS note 2370398.1
Oracle Solaris Operating System [Product ID 10006] SPARC: MOS note 2349278.1, X86: MOS note 2383531.1
Oracle VM [Product ID 4455] MOS note 2348460.1
Oracle VM VirtualBox [Product ID 8370] MOS note 2339562.1
Oracle X86 Servers [Product ID Multiple] MOS note 2336753.1
Oracle ZFS Storage Appliance (ZFSSA) [Product ID 10026] MOS note 2371830.1
Zero Data Loss Recovery Appliance Software [Product ID 11342] MOS note 2356406.1

All details about  April 2018 CPU  can be found at this site :

http://www.oracle.com/technetwork/security-advisory/cpuapr2018-3678067.html

If you want to get a brief information about this CPU  you can read  the below  MOS note

April 2018 Critical Patch Update: Executive Summary and Analysis (Doc ID 2383583.1)

In this note; We will focus on Oracle Database, Oracle Fusion Middleware and MySQL database products.

Let’s start with; Oracle Database. This patch includes 2 fixes for Oracle Database and one of these fixes is for Oracle Goldengate.As you see the number of the fixes is very low in this CPU. All vulnerabilities in Oracle Database can be remotely exploitable without authentication. You can find all details about these vulnerabilities in picture-1.

Capture

Capture

picture-1

If you look at fixes in Oracle Fusion Middleware products; You can see 39 new security fixes and 30 of these vulnerabilities may be remotely exploitable without authentication. Top critical fixes are given in picture – 2.

Capture

picture-2

For MySQL database, 33 new security fixes are released and 2 of these vulnerabilities may be remotely exploitable without authentication. Top fixes for MySQL is given in picture-3.

Capture

picture-3

As a result Fixes for Oracle Fusion Middleware products are very critical. But Oracle strongly recommends that; you should apply all these fixes as soon as earlier from this MOS note :  Database, Fusion Middleware, and Enterprise Manager Critical Patch Update April 2018 Patch Availability  Document 2353306.1. 

Thanks for reading this note.

Yusuf Anıl Akduygu

 

 

 

 

 

Important New Features in Oracle Database Vault Oracle 12c Release 2 version

At this note; you will find the most  important new features in Oracle Database Vault 12c Release 2.First start with Oracle Database Vault Policies.

A new Object type is introduced in Oracle Database Vault 12c Release 2 version. This type is called   Oracle Database Vault Policy. With Oracle Database Vault Policies you can group and manage some realms and command rules  together. By this way you can change the status of some realms and commands with a one command. As you expect ; when you collect some realms and command rules in a vault policy ; there should be something commons in realms and command rules. It will  be nonsense If you put all Realms and Commend Rules into one Vault Policy.

Another enhancement  in Database Vault in the new version is the simulation mode. When you put   Realms and Command Rules in a simulation mode; SQL commands are not blocked , but violations are logged.

Another change has been  made in Privilege Analysis at this version. Now Privilege Analysis captures more privileges. At the same time a new object is created in Privilege Analysis which is called Capture runs. You can create multiple capture runs for one analysis and produce comparisons reports against different capture runs.

As you know;  All Oracle 12c Release 2 databases are  multi-tenant databases. Therefore new Common Realms and Common Command Rules are introduced in this version.

Common Realms  and Common  Command Rules can only be created in application root not in CDB root. A Common Command Rule in the application root is applied to all associated PDBs

The last word;  Changes have been made  added ALTER SESSION, ALTER SYSTEM and CONNECT Command Rules.By this way; you can more preciously define prevention rules on ALTER SESSION, ALTER SYSTEM commands.

Thanks for reading this note.

Y. Anıl Akduygu

 

 

Oracle Life Time Support stages

In this note; you will get information about support end date about all Oracle databases.

Oracle databases have three different  lifetime support stages. These are called ;

  • Premier Support
  • Extended Support
  • Sustaining Support

In these stages; The important stage is Extended Support stage. Because when a database software ends this stage; Oracle never produces Security Alerts and Critical Patch updates for this software.  Therefore; Before Extended support date, you should upgrade your databases to any database version which is still in Premier Support stage.

https://www.oracle.com/support/lifetime-support/index.html

Now look the below diagram for the explanation of these stages ( this diagram  is taken from Oracle official documents)

Capture.JPG

Now; Oracle is released the end date of these stages. You can find the the end dates on the below diagram. According to these end dates. You should upgrade your Oracle 11.2 databases before Dec 2020 to Oracle 12c Release 2 . If you ask me do not consider to upgrade to Oracle 12c R1. Because its end time is July 2021 .

For the security of your Oracle databases; you should track the end date of these stages.

You must be sure that; All your Oracle 11g databases must be in Release 2 . Because Oracle 11g R1 was not be supported from Aug 2015.

Capture.JPG

 

 

 

 

Monitoring DCL operations with Oracle DB Vault

DCL ( Data Control Languages ) operations control privileges in Database. Privileges in Oracle are granted and revoked by GRANT and REVOKE commands. Auditing these kind of operations are very critical for the security of any databases. There are three different ways to audit DCL operations in Oracle Databases.

One of them is to use audit commands like below;

audit grant any object privilege by access
audit grant any privilege by access
audit grant any role by access

The disadvantage of this method is when DBAs run GRANT  and REVOKE commands with SYSDBA role; the audit is written to a file in the database server ( in Oracle 11g version ). In that case It can be difficult to report these operations,  and DBAs can disable the audit in the database. If you use Oracle 12c version; you can collect all data in a table but still DBAs can disable audit rules.

The another method is to use AFTER GRANT OR REVOKE ON DATABASE trigger. But in this method; you have to keep all monitoring data in a special table and DBAs can easily disable this trigger and delete the audited table.

The third method is to use DB Vault. When you use the DB vault; audited data can not be deleted by DBAs,  at the same DBAs can not disable the special rule to audit DCL commands. Using the Vault is the best and secure method to monitor DCL operations in Oracle. At the same time;  you can make prevention on GRANT and REVOKE commands with DB Vault. But the disadvantage of this method is  to pay for DB vault license;

Now in this note I will show you ; how you can monitor DCL operations with DB Vault. I assume that you have some knowledge about in Oracle DB Vault.

First; Create a special rule set   named DCL_Operations. The important point in this rule set is the Audit Option. The audit option must be “Audit On Success or Failure”  ( Figure -1).

dcl01.JPG

Figure-1

Now Create a rule which is always TRUE. And This rule is associated with DLC_Operations rule set  (Figure-2).

dcl02.JPG

Figure-2

Now We can create a Command Rule for GRANT command with DLC_Operations Rule set ( Figure-3).

dcl03.JPG

Create a Command Rule for REVOKE command as well ( Figure-4).

dcl04.JPG

Figure-4

Now , your new commands should be seen like Figure – 5.

dcl05.JPG

Figure-5

Let’s check the DB Vault definitions by running a GRANT and REVOKE commands ( Figure-6)

dcl07.JPG

Now ; run the Command Rule Audit Report to see how the DB vault definitions are auditied GRANT and REVOKE commands ( Figure-7)

dcl06.JPG

As you see; we successfully audited GRANT and REVOKE commands by using DB Vault.

Thanks for reading this note.

Yusuf Anıl Akduygu