Securing enterprise business-critical data is as important for DBAs as database tuning and data protection. Oracle provides comprehensive and powerful security controls/solutions to ensure data privacy and data security which will help with meeting regulatory compliance. Oracle supports the following security controls:
- Data Masking
- Advance Security (TDE, Data Redaction)
- Label security
- Virtual Private Database (VPD)
- Fine Grained Auditing (FGA)
- Data Vault
- Database auditing
- Audit Vault
- Storage/Network authentication
The purpose of this two part article series is to briefly explore some of the most important, and useful Oracle security tools/options/utilities, and the new security features introduced in Oracle Database 12c.
Masking sensitive data
Let me brief you here the need for data masking and how it is achieved in an Oracle database. It is a very common practice to clone production database to a non-production environment for any sort of testing or a new application deployment. If you are working for a financial sector or a high-profile company as an Oracle DBA, it is essential and a common practice to protect the sensitive data using Oracle security solutions or any third party data scrambling solution. Using the Oracle data masking solution, the sensitive data can be secured by replacing it with some fictitious values. This way, an organization can achieve regulatory compliance and avoid data leak or threats.
To implement data masking, you will have to follow the 4-step Oracle data masking pack approach, Find, Assess, Security and Test (F.A.S.T).
- The first phase (find) involves identifying the sensitive data, for example, credit/debit card information columns, in a table.
- In the second phase (assess), determine a format of data masking algorithm to apply in conjunction with IT risk team.
- The third phase (secure): apply the masking scripts on the sensitive database before handing over the environment.
- The final phase (test) involves certifying the data masking where the business users validate whether the data has been masked appropriately or requires any further tweaking.
The data can be masked either through an Oracle Enterprise Manager Cloud Control or with Data Pumps. Keep in mind that the OEM Data Masking packs are licensed separately.
Oracle Database Vault
With traditional database auditing control, a DBA can audit/log application user activities. However, the challenging part is to audit and control the privileged user activities in order to restrict and distribute the duties to the individual privileged (sys) users. With Oracle database vault solution you can control administrative users’ data access and can separate/restrict their duties to specific tasks. For example, if an organization has a group of DBAs, you can restrict individual administrative/privileged users and apply the rules individually. For example, you could have one user just to control user management in the database, another user to handle patch & tuning tasks, another user to monitor, backup duties, etc. Also, you can prevent and apply rules to restrict the viewing of sensitive data by the super-users.
With Oracle 12c, the Oracle Database Vault is pre-installed, but doesn’t enabled. To enable the option, either you use Database Configuration Assistant (DBCA) or through SQL command prompt. For more information how to implement this feature, refer to Oracle security documents like Oracle Database Vault Administrator Guide.
Oracle provides advanced security options to protect enterprise data protection problems. With Oracle Advanced Security solutions in 12c, you can encrypt the data in the database using the Transparent Data Encryption (TDE) option and also on-the-fly data redaction.
Oracle TDE solution provides industry standard encryption capability for an Oracle databases, with which you can ensure only authorized users can read the sensitive data in a database. TDE can be applied at column level or at the entire tablespace level; it is transparent and requires no application modifications. Follow the below steps to configure TDE:
Specify the wallet location and other details in the sqlnet.ora file, as show below:
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /oracle/DB_WALLET/<db_name>) ) )
Generate a master key:
SQL> alter system set encryption key identified by “welcome”;
This will create a wallet in location specifiied in the DIRECTORY above with the password welcome.
To verify wallet details, run the following SQL query:
SQL> select * from v$encryption_wallet;
The following demonstrates how to encrypt the data at the column and tablespace level:
SQL> create table card_payment (card_no varchar2(30) encrypt SALT|NOSALT, pdate date, amount number(10,2)) SQL> alter table card_payment modify(card_no encrypt using ‘AES256’ SALT); SQL> create tablespace data_ts datafile size 100m encryption using ‘AES256’ DEFAULT STORAGE (ENCRYPT);
The following demonstrates how to stop/start/enable/disable the wallet:
SQL> ALTER SYSTEM SET ENCRYTPION WALLET CLOSE; SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “welcome”;
Image copyright: Oracle
With the Data Redaction feature in Oracle 12c, you can now have selective, on-the-fly redaction of important/sensitive data before the SQL result displaying to the end user. Unlike TDE, the data redaction doesn’t change the actual data, it redacts data on-the-fly, i.e, before the SQL query displays the sensitive data to the application user. You can redact data via different methods: full redaction, partial redaction, Random and regular expression.
To configure data redaction, the user must have EXECUTE privileges on the new DBMS_REDACT package.
Image copyright: Oracle
In part 2, you will explore some useful Oracle security solutions and important new security features in Oracle database 12c. For step-by-step implementation procedure, I encourage you to refer to Oracle database security documents. The objective of this article is to provide you with an overview of database security options in Oracle, rather than showing complete implementation steps.