Archive for the ‘Security and auditing’ Category

Twelve new features for Cyber Security DBAs

In Cloud, Data, DBA, Security and auditing on June 2, 2017 at 08:32

In the early years of Oracle, Larry Ellison was asked if clients ever ask for their money back. “Nobody’s asked for their money back yet – he replied – a few have asked for their data back though!

A relatively new Wells Fargo Insurance Cyber Security study shows that companies are more concerned with private data loss than with hackers:

Thus, one of the main roles of the cyber security DBA is to protect and secure the data.

Here is what the latest Oracle release 12cR2 is offering us:

1. A Fully Encrypted Database

To encrypt an entire database, you must encrypt all the tablespaces within this database, including the Oracle-supplied SYSTEM, SYSAUX, UNDO, and TEMP tablespaces (which is now possible in 12.2). For a temporary tablespace, drop it and then recreate it as encrypted – do not specify an algorithm. Oracle recommends that you encrypt the Oracle-supplied tablespaces by using the default tablespace encryption algorithm, AES128. Here is how you do it:


2. TDE Tablespace Live Conversion

You can now encrypt, decrypt, and rekey existing tablespaces with Transparent Data Encryption (TDE) tablespace live conversion. The feature performs initial cryptographic migration for TDE tablespace encryption on the tablespace data in the background so that the tablespace can continue servicing SQL and DML statements like insert, delete, select, merge, and so on. Ensure that you have enough auxiliary space to complete the encryption and run (for example):

FILE_NAME_CONVERT = ('users.dbf', 'users_enc.dbf'); 

3. Support for ARIA, SEED, and GOST algorithms

By default, Transparent Data Encryption (TDE) Column encryption uses the Advanced Encryption Standard with a 192-bit length cipher key (AES192), and tablespace and database encryption use the 128–bit length cipher key (AES128). 12.2 provides advanced security Transparent Data Encryption (TDE) support for these encryption algorithms:

– SEED (Korea Information Security Agency (KISA) for South Korea
– ARIA (Academia, Research Institute, and Agency) for South Korea
– GOST (GOsudarstvennyy STandart) for Russia


4. TDE Tablespace Offline Conversion

12.2 introduces new SQL commands to encrypt tablespace files in place with no storage overhead. You can do this on multiple instances across multiple cores. Using this feature requires downtime, because you must take the tablespace temporarily offline. With Data Guard configurations, you can either encrypt the physical standby first and switchover, or encrypt the primary database, one tablespace at a time. This feature provides fast offline conversion of existing clear data to TDE encrypted tablespaces. Use the following syntax:


5. Setting Future Tablespaces to be Encrypted


CLOUD_ONLY transparently encrypts the tablespace in the Cloud using the AES128 algorithm if you do not specify the ENCRYPTION clause of the CREATE TABLESPACE SQL statement: it applies only to an Oracle Cloud environment. ALWAYS automatically encrypts the tablespace using the AES128 algorithm if you omit the ENCRYPTION clause of CREATE TABLESPACE, for both the Cloud and premises scenarios.

6. Role-Based Conditional Auditing

Role-based conditional auditing provides the ability to define unified audit policies that conditionally audit users based on a role in addition to the current capability to audit by users. This feature enables more powerful policy-based conditional auditing by using database roles as the condition for auditing. For example, auditing for new users with the DBA role would begin automatically when they are granted the role:

AUDIT POLICY role_dba_audit_pol;

7. Strong Password Verifiers by Default and Minimum Authentication Protocols

The newer verifiers use salted hashes, modern SHA-1 and SHA-2 hashing algorithms, and mixed-case passwords.

The allowed_logon_version_server in the sqlnet.ora file is used to specify the minimum authentication protocol allowed when connecting to Oracle Database instances. 
Oracle notes that the term “version” in the allowed_logon_version_server parameter name refers to the version of the authentication protocol.  It does NOT refer to the Oracle release version.

– SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 generates all three password versions 10g, 11g, and 12c
– SQLNET.ALLOWED_LOGON_VERSION_SERVER=12 generates both 11g and 12c password versions, and removes the 10g password version
– SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a generates only the 12c password version

8. New init.ora parametercalled OUTBOUND_DBLINK_PROTOCOLS

Due to direct SQL*Net Access Over Oracle Cloud, existing applications can now use Oracle Cloud without any code changes. We can easily control the outbound database link options:

– OUTBOUND_DBLINK_PROTOCOLS specifies the allowed network protocols for outbound database link connections: this can be used to restrict database links to use secure protocols
– ALL_GLOBAL_DBLINKS allows or disallow global database links, which look up LDAP by default

9. SYSRAC – Separation of Duty in a RAC

SYSRAC is a new role for Oracle Real Application Clusters (Oracle RAC) management. This administrative privilege is the default mode for connecting to the database by the clusterware agent on behalf of the Oracle RAC utilities such as srvctl. For example, we can now create a named administrative account and grant only the administrative privileges needed such as SYSRAC and SYSDG to manage both Oracle RAC and Oracle Data Guard configurations.

10. Automatic Locking of Inactive User Accounts


Within a user profile, the INACTIVE_ACCOUNT_TIME parameter controls the maximum time that an account can remain unused. The account is automatically locked if a log in does not occur in the specified number of days. Locking inactive user accounts prevents attackers from using them to gain access to the database. The minimum setting is 15 and the maximum is 24855. The default for INACTIVE_ACCOUNT_TIME is UNLIMITED.

11. Kerberos-Based Authentication for Direct NFS

Oracle Database now supports Kerberos implementation with Direct NFS communication. This feature solves the problem of authentication, message integrity, and optional encryption over unsecured networks for data exchange between Oracle Database and NFS servers using Direct NFS protocols.

12. Lockdown Profiles

Lockdown profile is a mechanism used to restrict operations that can be performed by connections to a given PDB for both cloud and non-cloud.

There are three functionalities that you can disable:

Feature: it lets us enable or disable database features for say junior DBAs (or cowboy DBAs)
Option: for now, the two options we can enable/disable are “DATABASE QUEUING” and “PARTITIONING”
Statement: we can either enable or disable the statements “ALTER DATABASE”, “ALTER PLUGGABLE DATABASE”, “ALTER SESSION”, and “ALTER SYSTEM”. In addition, we can specify granular options along with these statements. Example:


But .. the most secure database is the database with no users connected to it.


In DBA, Security and auditing on May 25, 2017 at 07:10

Exactly one year from now, from May 25th 2018, all businesses that handle personal data will have to comply with the new General Data Protection Regulation (GDPR) legislation.

At 260 pages in length, with 99 Articles and over 100 pages of explanatory notes known as ‘Annexes’, the GDPR is roughly three times the length of the Data Protection Act 1998 it is replacing.

The requirements for databases are:

– Discovery
– Classification
– Masking
– Monitoring
– Audit reporting
– Incident response and notification

The maximum penalty for non-compliance is 4% of annual revenue or €20 million, whichever is higher. Lower fines of up to 2% are possible for administrative breaches, such as not carrying out impact assessments or notifying the authorities or individuals in the event of a data breach. This puts data protection penalties into same category as anti-corruption or competition compliance.

What DBAs should start with now is account and identify 100% of the private data located in all databases!

There are 4 major categories where DBAa will be involved. The details can be found in the appendix on page 19/23 entitled Mapping of Oracle Database Security Products to GDPR.

1. Assess (Article 35 and Recital 84)
2. Prevent (Articles 5,6,29,32 and Recitals 26,28,64,83)
3. Detect (Articles 30,33,34)
4. Maximum protection (Articles 25,32)

Article 25 is about data minimization, user access limits and limit period of storage and accessibility.
Article 32 is about pseudonymization and encryption, ongoing protection and regular testing and verification.
Article 33 and 34 are about data breach notification: there is 72 hour notification following discovery of data breach.
Article 35 is about the data protection impact assessment.
Article 44 treats data transfers to third country or international organizations where the allowed transfers are only to entities in compliance with the regulation.

As you can see, DBA job ads include nowadays the GDPR skills and responsibilities:

The main lawful bases for data processing are consent and necessity. Data can be recognized as a necessity if it:

• Relates to the performance of a contract
• Illustrates compliance with a legal obligation
• Protects the vital interests of the data subject or another person
• Relates to a task that’s in the public interest
• Is used for purposes of legitimate interests pursued by the controller or a third party (expect where overridden by the rights of the data subject)

Data subjects’ requests for access should be responded to within a month and without charge. This is new legislation within the GDPR and the same one month time frame applies to rectifying inaccurate data.

Breach notifications should be made within 72 hours of becoming aware. If this time frame isn’t met, a fine of 10M€, or 2% of global turnover, can be issued as a penalty. A breach is any failure of security leading to the destruction, loss, alteration, unauthorized disclosure of/access to personal data. Supervisory authorities must be notified if a breach results in a risk to the rights and freedoms of individuals.

Data held in an encrypted or pseudonymized form isn’t deemed to be personal data and falls outside of the scope of these new rules altogether. Despite this, data that’s encrypted and considered secure using today’s technology may become readable in the future. Therefore it’s worth considering format preserving encryption/pseudonymization which renders anonymous but stills allows selected processing of that data.

Here are few interesting articles meant mostly for DBAs:

Accelerate Your Response to the EU General Data Protection Regulation (GDPR)

Data Privacy and Protection GDPR Compliance for Databases

European Union GDPR compliance for the DBA

SQL Server 2016 – Always Encrypted and the GDPR

How Oracle Security Solutions Can Help the EU GDPR

Top 10 operational impacts of the GDPR

Reading Data in Oracle Database 12c

In Cloud, Consolidation, Database options, DBA, Security and auditing, SQL on December 1, 2014 at 18:02

1. For DBAs and Developers, the words READ and SELECT have been for years somehow synonyms. In 12c, is there now any difference?

2. Before pluggable databases, selecting data from the SALES table for instance meant selecting data from a table called SALES in a certain SCHEMA within the database. How about if a table called SALES belongs to several pluggable databases under the same schema name?

The aim of this blog post is to shed some light on these new concepts.


1. New READ privilege.

Until Oracle the SELECT object privilege allowed users to perform the following two operations in addition to just reading data from the SALES table:


These 2 commands enabled the users to lock the rows of the SALES table.

The READ object privilege does not provide these additional privileges. For better security, grant users the READ object privilege if you want to restrict them to performing queries only.

In addition to the READ object privilege, you can grant users the READ ANY TABLE privilege to enable them to query any table in the database.

When a user who has been granted the READ object privilege wants to perform a query, the user still must use the SELECT statement. There is no accompanying READ SQL statement for the READ object privilege.

The GRANT ALL PRIVILEGES TO user SQL statement includes the READ ANY TABLE system privilege. The GRANT ALL PRIVILEGES ON object TO user statement includes the READ object privilege.

If you want the user only to be able to query tables, views, materialized views, or synonyms, then grant the READ object privilege. For example:



2. Querying a table owned by a common user across all PDBs.

Consider the following scenario:

– The container database has several pluggable databases, i.e., it has a separate PDB for each different office location of the company.
– Each PDB has a SALES table that tracks the sales of the office, i.e., the SALES table in each PDB contains different sales information.
– The root container also has an empty SALES table.
– The SALES table in each container is owned by the same common user.

To run a query that returns all of the sales across the company connect to each PDB as a common user, and create a view with the following statement:


The common user that owns the view must be the same common user that owns the sales table in the root. After you run this statement in each PDB, the common user has a view named sales in each PDB.

With the root as the current container and the common user as the current user, run the following query with the CONTAINERS clause to return all of the sales in the sales table in all PDBs:


You can also query the view in specific containers. For example, the following SQL statement queries the view in the containers with a CON_ID of 3 and 4:


3. Delegate.

Something else: staring, when granting a role to a user, you can specify the WITH DELEGATE OPTION clause. Then the grantee can do the following two things:

A) Grant the role to a program unit in the grantee’s schema
B) Revoke the role from a program unit in the grantee’s schema


Oracle Database New Features

In Bugs, DBA, Golden Gate, OOW, Oracle database, Security and auditing on August 28, 2013 at 14:08

Eventually, is out although I always had the feeling that it will come before 12c. Patch number is 13390677. More than 5000 bugs have been fixed!


End date for is 31st of October 2013, which means that it is time to move to either or Or why not to Check the roadmap!

The release notes can be found here. As it will be the terminal patchset of 11gR2, the features introduced are the last we will see in 11g.

Check into detail the New Features of but most of all it is as follows:

1. Oracle Data Redaction is now part of 11gR2
2. The Trace File Analyzer (TFA) and Collector
3. RACcheck is now included
4. The OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to the new value of 11
5. Total Recall: there is a new OPTIMIZE DATA clause when creating or altering a flashback data archive
6. The DES, RC4, and MD5 algorithms are desupported
7. New sqlnet.ora parameter SSL_EXTENDED_KEY_USAGE
8. New init.ora parameter: when set to true ENABLE_GOLDENGATE_REPLICATION RDBMS services used by Oracle GoldenGateare enabled.

If you would like to check the bugs fixed in check MOS Patch Set – List of Bug Fixes by Problem Type (Doc ID 1562142.1)!

Note: is not yet available for Exadata.

And finally, here is all the information about my presentations at Oracle OpenWorld 2013, welcome!

Session ID: CON2131
Session Title: DBA Best Practices for Performance Tuning in a Pluggable World
Venue / Room: Moscone South – 308
Date and Time: 9/23/13, 12:15 – 13:15

Session ID: CON1759
Session Title: The Least-Known Features of Oracle Database, Part 2
Venue / Room: Moscone South – 200
Date and Time: 9/24/13, 12:00 – 13:00

Session ID: CON1715
Session Title: The Important Things DBAs Should Do Before and After an Oracle Database Upgrade
Venue / Room: Moscone South – 236
Date and Time: 9/25/13, 17:00 – 18:00

Session ID: CON9863
Session Title: Managing Oracle Engineered Systems
Marriott Marquis – Foothill F
Date and Time: 9/26/13, 15:30 – 16:30


Auditing vs. Performance in the Oracle Database

In Bugs, Database tuning, DBA, Oracle database, Security and auditing on May 12, 2011 at 02:24

You show this (part of a) AWR report to the DBA and he proudly concludes: disable auditing, it is killing the performance! And thus, quite often Oracle database auditing is not enabled. And here are the 3 main reasons why auditing is not turned on:

– DBAs, developers, etc. are not familiar with this feature: For those who are not familiar with auditing, I suggest Tim Hall’s and Pete Finnigan’s articles: Auditing in Oracle 10g Release 2 and Introduction to Simple Oracle Auditing.
– Security is not considered important and necessary: For those who do not consider auditing important, I wish them luck. They are anyway not interested in what I have to say..
– Performance is being hit by enabling auditing: For the ones having issues with performance when auditing is enabled, here is something.

There are 3 major reasons why performance suffers when auditing is enabled: too much is being audited, AUD$ still hangs in the SYSTEM tablespace and surprise, surprise: the Oracle bugs.

1. Too much is being audited. If it is a new database, spend some time with all parties involved on what to audit. The reality however is something like that: go-live day is getting closer, oh do we have auditing enabled? How do you enable it, can you give me the command please. And it should not go like that. You first decide on the value of audit_trail and then audit what is really needed, do not audit repetitive commands that generate too many inserts into the AUD$ table for it can grow very fast indeed.

Have a look at this thread from Pete Finnigan’s site called Performance Impact of Auditing.

If it is an existing database, check first what is being audited. To find out system audited stuff run the following:

select * from DBA_PRIV_AUDIT_OPTS
union all
select * from DBA_STMT_AUDIT_OPTS;

Note that the difference between the two views above is very small and I have not found yet a place with explanation about the difference. The documentation says that DBA_STMT_AUDIT_OPTS describes the current system auditing options across the system and by user while DBA_PRIV_AUDIT_OPTS describes the current system privileges being audited across the system and by user. Puzzled? Me too.



For the auditing options on all objects, check DBA_OBJ_AUDIT_OPTS.

Check the Oracle 11gR2 documentation for the Recommended Audit Settings.

2. AUD$ still hangs in the SYSTEM tablespace. The system tablespace might be fragmented. Starting 11gR2, Oracle supports moving the AUD$ table out of the SYSTEM tablespace. But first, noaudit your policy or stop the auditing.

If using 11.2.0 and above follow the documentation instruction.

If still running 11.1.0 or a below, here is how to do it:

create tablespace AUDIT_DATA datafile ...;
create table AUDX tablespace AUDIT_DATA as select * from AUD$;
rename AUD$ to AUD$$;
rename AUDX to AUD$;
create index i_aud2 on AUD$(sessionid, ses$tid) tablespace AUDIT_DATA;

Remember to purge the records on regular basis. Do not just delete them but move them to a centralized auditing repository. Use the new DBMS_AUDIT_MGMT package. Check Tim Hall’s instructionon how to purge audit trail records. In urgent cases, it is safe to run truncate table AUD$;

If you use FGA, remember to move also FGA_LOG$ away from the SYSTEM tablespace:

    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_DATA');

And I would recommend this article by Martin Widlake: Why is my SYSTEM Tablespace so Big?! The 3rd SYSTEM table after SYS.AUD$ and SYS.HISTGRM$ that I have seen to grow up is SYS.IDL_UB1$.

3. Oracle bugs. If you enable auditing you might get several bugs for free, most old ones should be fixed in, don’t know about the new ones 🙂

20 years ago, Bug 52646: audit trail degrades performances too much was solved in Oracle 6:

Well, you still have many similar ones. As of today, all these bugs have empty field for “Fixed in Product Version”. And this is not the complete list!

Bug 10347785: huge version count for insert into sys.aud$ causing library cache: mutex x/hang
Bug 504968: ora-600[17051] and database crash when writing to audit table
Bug 11901734: dbms_audit_mgmt audit trail cleanup cannot keep up with aud$ volume
Bug 8236755: ora-00060 occurs while updating aud$ table.
Bug 6159102: export session spins when logging off in repeated update sys.aud$ statements
Bug 6334058: deadlock with ora-00060 while updating sys.aud$ and auditing on select is enable
Bug 4405301: too many entries in aud$ when sequence is queried and audit is by session
Bug 1257564: noaudit does not turn off auditing of database (very nice indeed!)

I wish Oracle will create one bug called “Performance issues with the AUD$ table”, solve it so finally no one complains about the performance of one simple table which in my opinion is not even a real dictionary table.

Question: In RAC, sessions from both/all nodes are being audited. Will AUD$ hot blocks “stuffed” with new data be ping-ponged via the interconnect?