Dontcheff

Archive for the ‘Security and auditing’ Category

The Power of Autonomous Database Security

In Autonomous, Cloud, Data, DBA, Security and auditing on September 9, 2019 at 13:24

“The most secure database is the one having no users connected.” You can quote me on that, I have learned it the hard way – from experience.

Database security in short means protecting the data. Markus Strauss’ article on Traditional Database Security reveals exactly the same.

Let us look into 3 recent examples of where data was not very well protected (to put it mildly), covered by CNN and CNBC:

An entire nation just got hacked: records of more than 5 million Bulgarians got stolen by hackers from the country’s tax revenue office.

A hacker gained access to 100 million Capital One credit card applications and accounts: in one of the biggest data breaches ever, a hacker gained access to more than 100 million Capital One customers’ accounts and credit card applications earlier this year.

Marriott says its Starwood database was hacked for approximately 500 million guests: “The Marriott just revealing a massive data breach involving guest reservation database at its Starwood brand. Marriott says it was unauthorized access since 2014. This includes up to 500 million guests… For approximately 327 million of these guests, the information that was exposed includes a combination of name, mailing address, phone number, email address, you ready for this – passport number… Starwood preferred guest account information, date of birth, gender, arrival and departure information… including reservation dates and communication preference. As you know, when you go to a hotel, especially internationally, they take your passport. Often times, they take a copy of your passport.”

So, granted traditional database security does not protect data well, how about looking into something new, innovative and at the same time something which has been developed and improved for more than 40 years? The Oracle Autonomous Database might be the answer (arguably is the answer). Tom Haunert’s interview with Vipin Samar (SVP of Database Security) gives an excellent overview of what Autonomous Database Security is all about.

Here is a list of 10 security benefits of Oracle’s Autonomous Database (benefits over any other database for all it matters):

1. There is no DBA access, no root access, no Operating System access… Still you can create users, roles, etc. just as before. But certain the commands are blacklisted.
2. There are no customer-managed keys: Oracle manages the keys.
3. Oracle automatically applies all security updates/patches to ensure data is not vulnerable to known attack vectors.
4. All data is encrypted using transparent data encryption.
5. Still database security features such as Virtual Private Database and Data Redaction are available.
6. Network connections from clients to the Autonomous Database are also encrypted using the client credentials wallet.
7. Data is encrypted everywhere: SQL*Net traffic, data in tablespaces and data in backups.
8. It is now possible to specify an access control list that blocks all IP addresses that are not in the list from accessing the database.
9. Oracle has been engaging with external assessment entities and independent auditors to meet a broad set of international and industry-specific compliance standards for service deployments in Oracle Cloud such as ISO 27001, SOC1, SOC2, PCI DSS, HIPAA/HITECH, and FedRAMP.
10. All operations are being audited.

The first one above is rather controversial point of debate among the DBA community. In order to ensure the security and the performance of the Autonomous Database, some SQL commands are restricted: ADMINISTER KEY MANAGEMENT, ALTER PROFILE, ALTER TABLESPACE, CREATE DATABASE LINK, CREATE PROFILE, CREATE TABLESPACE, DROP TABLESPACE. For DB links, you should use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links in ADB.

Several DBA statements are restricted: ALTER PLUGGABLE DATABASE, ALTER DATABASE, ALTER SYSTEM, ALTER SESSION, ALTER USER, ALTER TABLE, CREATE TABLE and CREATE USER. To ensure the security and the performance of Autonomous Database, some Oracle XML DB features are also restricted. Same holds for Oracle Text, Oracle Spatial and Graph and APEX.

Oracle ADB is a database which is both Autonomous and Secure and as Mike Faden says: from self-securing database cloud services to the new cloud perimeter, Oracle technology protects your most valuable investment—your data.

And here are the 4 Areas of Self-Securing of Autonomous Database:

– Self-securing starts with the security of the Oracle Cloud infrastructure and database service. Security patches are automatically applied every quarter or as needed, narrowing the window of vulnerability. Patching includes the full stack: firmware, operating system [OS], clusterware, and database. There are no steps required from the customer side.
– Oracle encrypt customer data everywhere: in motion, at rest, and in backups. The encryption keys are managed automatically, without requiring any customer intervention. And encryption cannot be turned off.
– Administrator activity on Oracle Autonomous Data Warehouse Cloud is logged centrally and monitored for any abnormal activities. Oracle have enabled database auditing using predefined policies so that customers can view logs for any abnormal access: UNIFIED_AUDIT_TRAIL
– Built upon Oracle Database Vault, unique to Oracle Cloud, operations personnel have privilege to do all administrative tasks without any ability to ever see any customer data.

And finally something about the passwords in the Oracle Autonomous Database. They still have to be carefully chosen. Because as people say, “passwords are like underwear: make them personal, make them exotic, and change them on a regular basis.”

Advertisements

ORA-56955: quarantined plan used

In DBA, New features, Oracle database, Security and auditing, SQL on May 29, 2019 at 13:15

“The way that worms and viruses spread on the Internet is not that different from the way they spread in the real world, and the way you quarantine them is not that different, either” – David Ulevitch

And now, in Oracle 19c, you can do the same with SQL:

 SQL> SELECT client, COUNT(*) OVER (PARTITION BY price) CLIENT_COUNT 
 FROM sales WHERE price IN (2, 91984);
  *
 ERROR at line 1:
 ORA-56955: quarantined plan used
 Elapsed: 00:00:00.00

Error “ORA-56955: quarantined plan used” is new in the Oracle database, it comes when the SQL run fulfills the quarantine conditions.

It is important to differentiate Oracle SQL Quarantines in 19c from Oracle Object Quarantines in 18c. There is also the concept of Offload Quarantines.

1. A good way to start understanding what SQL quarantines are about is to watch the following short video from Rich Niemiec:

2. Check also page 23 of the Optimizer In Oracle Database 19c white paper. “The new Oracle Database 19c feature SQL Quarantine can be used to eliminate the overhead of runaway queries. When DBRM detects a SQL statement is exceeding a resource or run-time limit, the SQL execution plan used by the statement is quarantined. If the SQL statement is executed again and it is using the same SQL execution plan then it will be terminated immediately. This can significantly reduce the amount of system resource that would otherwise be wasted.”

Think of SQL Quarantines as a way to prevent unnecessary SQL being run in the database, of course based on your own definition of unnecessary SQL. You can prevent the use of “bad” execution plans and exhausting the databases from resources.

In the database, there might be SQL statements with high utilization of CPU and IO: you can prevent them from being started so once they are quarantined they no longer consume system resources because they are terminated prior to their execution.

Note that SQL quarantines work only in 19c on Exadata and DBCS/ExaCS. Check out the Database Licensing Information User Manual:

3. You can quarantine a statement based on:

– SQL_ID and one of its execution plan
– SQL_ID and all of its executions plans
– specific SQL_TEXT

You quarantine a statement in 2 steps:

(A) create a SQL Quarantine by using (for example) DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID
(B) add thresholds by using DBMS_SQLQ.ALTER_QUARANTINE

Here are some examples and some more.

4. For some interesting and non-documented stuff check the article by Mahmoud Hatem entitled Oracle 19c : The QUARANTINE hint.

For instance, it shows how you can test by setting “_exadata_feature_on”=true in order to get SQL QUARANTINE feature to work on a non-Exadata box.

The following parameters can affect a quarantine kick off:

CPU_TIME
ELAPSED_TIME
IO_MEGABYTES
IO_REQUESTS
IO_LOGICAL
PHV

There is also the special value called ALWAYS_QUARANTINE.

5. All details can be of course found in the SQL Quarantine documentation.

The following columns of the V$SQL and GV$SQL views show the quarantine information of execution plans of SQL statements:

– SQL_QUARANTINE: This column shows the name of the quarantine configuration for an execution plan of a SQL statement.
– AVOIDED_EXECUTIONS: This column shows the number of times an execution plan of a SQL statement was prevented from running after it was quarantined.

There is a new view in 19c called DBA_SQL_QUARANTINE which displays information about quarantine configurations.

Good news also for admin users of the Autonomous Database: you have full access to the feature:

And note that a DBA can also transfer quarantine configurations from one database to another database using the DBMS_SQLQ package subprograms: CREATE_STGTAB_QUARANTINE, PACK_STGTAB_QUARANTINE, and UNPACK_STGTAB_QUARANTINE.

If you plan to visit Oracle OpenWorld this year (September 16-19, 2019), as of now, there are a couple of presentations on SQL Quarantine:

– Oracle Database 19c: SQL Tuning Using Plan Stability Methods SPM/SQL Quarantine: by Soumendra Paik, Senior Principal Technical Support Engineer, Oracle
– What’s New in Oracle Optimizer, by Nigel Bayliss, Senior Principal Product Manager, Oracle

Question: how do you quarantine a statement based on a sub-string of the query? Like, how can you quarantine statements starting with ‘select *‘?

What else besides Automatic Indexing is new in Oracle Database 19c?

In DBA, Init.ora, New features, Oracle database, Security and auditing, SQL on April 23, 2019 at 10:53

Automatic Indexing (AI) is probably the most important new feature of Oracle Database 19c and AI is arguably one of the best example of AI in the IT industry. But there is much more that came along with 19c.

Here is my choice of the top 10 least known (for now at least) new features of Oracle Database 19.2 (in no order whatsoever):

1. Security: if the database password file name or location has been changed and the metadata cache needs to be refreshed with the details of the updated database password file, you can do it simply with the following command:

SQL> alter system flush passwordfile_metadata_cache;

System altered.

In 19c, most of the Oracle Database supplied schema-only accounts now have their passwords removed to prevent users from authenticating to these accounts. But DBAs can still assign passwords to the default schema-only accounts.

2. SQL: the LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword.

There are also a new bitvector SQL operators can be used to speed up COUNT DISTINCT operations within a SQL query.

3. Active Data Guard DML redirection: You can now run DML on the Active Data Guard standby database. When run on the standby side, the update is passed to the Primary database where it is executed and the resulting redo of the transaction will update the standby after which control will be returned to the application.

4. There are 6 new init.ora parameters in 19c. DBAs can now tune in DataGuard the amount of wait time by using DATA_GUARD_MAX_IO_TIME and DATA_GUARD_MAX_LONGIO_TIME. You can check details for all six:

ADG_REDIRECT_DML
DATA_GUARD_MAX_IO_TIME
DATA_GUARD_MAX_LONGIO_TIME
ENABLE_IMC_WITH_MIRA
LOB_SIGNATURE_ENABLE
MAX_DATAPUMP_PARALLEL_PER_JOB

5. You can now create Materialized Views containing bitmap-based COUNT(DISTINCT) functions. This means that the MVs are based on SQL aggregate functions that use bitmap representation to express the computation of COUNT(DISTINCT) operations.

SQL> create materialized view MV_SALES as
select CLIENT, 
BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(PRICE),'RAW') bm_price
from SALES
group by CLIENT,BITMAP_BUCKET_NUMBER(PRICE);  

Materialized view created.

6. Looks like there is now automatic resolution of SQL plan regressions: “SQL plan management searches for SQL statements in the Automatic Workload Repository (AWR). Prioritizing by highest load, it looks for alternative plans in all available sources, adding better-performing plans to the SQL plan baseline. Oracle Database also provides a plan comparison facility and improved hint reporting.”

7. Real-Time Statistics is also a new cool feature. “Oracle automatically gathers online statistics during conventional DML operations. Statistics can go stale between execution of DBMS_STATS statistics gathering jobs. By gathering some statistics automatically during DML operations, the database augments the statistics gathered by DBMS_STATS. Fresh statistics enable the optimizer to produce more optimal plans.”

8. Hybrid Partitioned Tables: now large portions of a table can reside in external partitions, for example in the Oracle Cloud. With this new feature, you can also easily move non-active partitions to external files, such as Oracle Data Pump files, for a cheaper storage solution. Hybrid partitioned tables support all existing external table types for external partitions: ORACLE_DATAPUMP, ORACLE_LOADER, ORACLE_HDFS, ORACLE_HIVE.

9. Data Pump:

– Oracle Data Pump allows tablespaces to stay read-only during TTS import.
– Oracle Data Pump can work in test mode for transportable tablespaces.
– Oracle Data Pump supports resource usage limitations with the introduction of two new parameters: MAX_DATAPUMP_JOBS_PER_PDB and MAX_DATAPUMP_PARALLEL_PER_JOB.
– Data Pump no longer enables secure, password-protected roles by default. Beginning with 19c, you must explicitly enable password-protected roles for an individual export or import job. A new command-line parameter has been added, ENABLE_SECURE_ROLES=YES | NO that can be used to explicitly enable or disable these types of roles for an individual export or import job.
– The new Data Pump command-line parameter CREDENTIAL enables secure import into a managed service from dump files in the Oracle Object Store Service.

10. SQL Quarantine: “SQL statements that are terminated by Oracle Database Resource Manager due to their excessive consumption of CPU and I/O resources can be automatically quarantined. The execution plans associated with the terminated SQL statements are quarantined to prevent them from being executed again.”

Check out the new Oracle package DBMS_SQLQ – cool stuff!

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:

ALTER TABLESPACE system ENCRYPTION ONLINE ENCRYPT 
FILE_NAME_CONVERT=('system01.dbf','system01_enc.dbf'); 

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):

ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES192' ENCRYPT 
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

ALTER TABLE clients REKEY USING 'GOST256'; 

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:

ALTER TABLESPACE users ENCRYPTION OFFLINE ENCRYPT; 

5. Setting Future Tablespaces to be Encrypted

ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = CLOUD_ONLY; 

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:

CREATE AUDIT POLICY role_dba_audit_pol ROLES DBA CONTAINER = ALL; 
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

CREATE PROFILE time_limit LIMIT INACTIVE_ACCOUNT_TIME 60;

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:

ALTER LOCKDOWN PROFILE junior_dba_prof STATEMENT = ('ALTER SYSTEM') 
CLAUSE = ('SET')  OPTION= ('OPTIMIZER_INDEX_COST_ADJ');

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

GDPR for DBAs

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.

lock_read

1. New READ privilege.

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

LOCK TABLE sales IN EXCLUSIVE MODE;
SELECT ... FROM sales FOR UPDATE;

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:

GRANT READ ON SALES TO julian;

CDB_PDB

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:

CREATE OR REPLACE VIEW sales AS SELECT * FROM sales;

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:

SELECT * FROM CONTAINERS(sales);

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:

SELECT * FROM CONTAINERS(sales) WHERE CON_ID IN (3,4);

3. Delegate.

Something else: staring 12.1.0.2, 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

delegate

Oracle Database 11.2.0.4 New Features

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

Eventually, 11.2.0.4 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!

database_roadmap

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

11.2.0.4_2

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.

11.2.0.4_1

Check into detail the New Features of 11.2.0.4 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 11.2.0.4 check MOS 11.2.0.4 Patch Set – List of Bug Fixes by Problem Type (Doc ID 1562142.1)!

Note: 11.2.0.4 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

OOW_how_big

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 example, AUDIT SYSTEM belongs only to DBA_PRIV_AUDIT_OPTS while PROFILE, PUBLIC SYNONYM, DATABASE LINK, SYSTEM AUDIT, SYSTEM GRANT and ROLE belong only to DBA_STMT_AUDIT_OPTS.

On the other hand, CREATE PUBLIC DATABASE LINK, EXEMPT ACCESS POLICY, CREATE EXTERNAL JOB, DROP USER and ALTER DATABASE belong to both views, get it 🙂

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:

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_DATA');
END;
/

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 11.2.0.2, 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?