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.

 

Advertisements

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

 

 

 

 

 

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

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

 

January 2018 Oracle Critical Patch Update

Oracle announced January 2018 Critical Patch Update. This patch includes  238 new security fixes. At the same this patch contains a special   Addendum Spectre (CVE-2017-5753, CVE-2017-5715) and Meltdown (CVE-2017-5754) for  Intel processor vulnerabilities.( https://yusufanilakduygu.wordpress.com/2018/01/17/oracle-announcement-about-spectre-and-meltdown/)

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

http://www.oracle.com/technetwork/security-advisory/cpujan2018-3236628.html

At this note; we will focus on Oracle Database , Oracle WebLogic Server and MySQL products.

Let’s start with Oracle Database;

This PSU contains 5 new security fixes for the Oracle Database Server.  With there of these vulnerabilities , Oracle database may be exploited over a network without requiring user credentials.  And one vulnerability  which is called  CVE-2017-10282 is very critical in 12.1.0.2 and 12.2.0.1 versions. In this vulnerability; If you have CREATE SESSION and EXECUTE CATALOG ROLE privilege you can make privilege escalation easily.

Capture

You can find fixes about these vulnerabilities  at  this Metalink note;

Critical Patch Update (CPU) Program January 2018 Patch Availability Document (PAD) (Doc ID 2325393.1)

Simple ; the Oracle Database Patch List is given below:

  • Combo OJVM PSU 11.2.0.4.180116 (CPUJan2018) and Database SPU 11.2.0.4.171017 (CPUOct2017) Patch 27010991 for UNIX
  • Combo OJVM PSU 12.1.0.2.180116 and Database PSU 12.1.0.2.180116 Patch 27010839 for UNIX, or
  • Combo OJVM RU 12.2.0.1.180116 and Database RU 12.2.0.1.180116 Patch 27010695 for UNIX, or

Continue with Oracle Fusion Middleware. The Base score for this product starts from 9.9.

This CPU contains 27 new security fixes for Oracle Fusion Middleware and  21 of these vulnerabilities may be remotely exploitable without authentication,

The most critical fixes is given below. If  you have Oracle Web Logic Servers which serve in the Internet; you have to apply these patches immediately.

 

Capture

To find patches you should look at ; Metalink document (Doc ID 2325393.1)

Oracle MySQL CPU contains  25 new security fixes for Oracle MySQL and  6 of these vulnerabilities may be remotely exploitable without authentication. The most important vulnerabilities are given below.

Capture

We do not need to say.  But I have to say;

you should apply this patch as soon as earlier.

Thanks for reading this note

Good Luck.

Anıl Akduygu