CIS Oracle12c Security Benchmark v2.1

Center for Internet Security (CIS) has just released Oracle12c Database Security Benchmark version 2.1. As an editor for CIS Oracle12c security benchmarks, I made significant contributions to the release of the benchmarks.

This new version includes additional security controls and clarifications/corrections to the existing controls. CIS Oracle12c benchmark provides a checklist of security configurationguidelines/recommendations to safeguard Oracle databases. These recommendationsare security best practices that should be implemented to protect and secureOracle databases against cyber threats.

CIS Oracle 12c benchmarks, like other 100+ benchmarks published by CIS, are unique for the following reasons:

• CIS benchmarks are consensus-based. They are developed by security professionals world-wide.
• CIS benchmarks are completely free. They are available in PDF, Exce and Word formats. CIS benchmarks can be downloaded from https://learn.cisecurity.org/benchmarks
• CIS benchmarks are universally accepted by the government, public corporations, privare businesses and academic institutes.
• CIS Benchmarks are available for 100+ platforms and technologies, including Desktops, Browsers, Networking Devices, Databases, Operating Systems and Virtualization platforms.

Securely Grant SYS objects to Database Users

My article published by the Scene magazine of UKOUG. There are lots of good articles by the experts in the magazine.

The below is the text of the article:

As a DBA, you may be asked to grant SELECT and/or EXECUTE privileges on the Oracle data dictionary objects which are SYS objects. For example, a security auditor needs access to the data dictionary objects to perform security compliance audits; a Jr. DBA needs access to learn more about the database environment; an application DBA needs access to DBA_ views; a performance analyst who is helping your organization optimize your applications, needs access to the performance views and tools; third-party tools that your organization has procured require access to the data dictionary, etc.

As a DBA, what are your options? What roles and privileges should you grant? Should you grant SELECT_ANY_CATALOG role or SELECT ANY DICTIONARY privilege? You are concerned about security implications of granting access on SYS objects. This article explores various options your options that you have at your disposal.

SELECT_CATALOG_ROLE Role

SELECT_CATALOG_ROLE is the first option that may be considered. This is an Oracle-supplied pre-defined role. The primary purpose of this role is to grant SELECT privileges on the Oracle data dictionary (catalog) views that include DBA_, GV_$, V_$ views and CDB_ views(12c only). Please note that underlying SYS tables such as TAB$, OBJ$, etc. are not granted through this role. X$ views are not granted as well. In addition, the grantees of this role won't be able to create stored PL/SQL objects such as procedures, packages or views on the underlying data dictionary objects granted through this role. Just an FYI that you will need to grant direct privileges on the underlying objects for this purpose.

This pre-defined role is quite handy when a user needs SELECT access on catalog views, however, you should carefully consider security implications before granting this role.

• The primary security consideration that you need to weigh is the metadata about your database that this role could reveal. As mentioned above, this role grants SELECT on most DBA_ views which would pretty much reveal all the metadata about your database. Many, if not most of these views granted trough this role are sensitive. For example, DBA_USERS reveals information about your database users. Good news is that DBA_USERS no longer shows password hashes in 11g/12c, but it did in 10g. DBA_SOURCE and DBA_TRIGGERS reveal your PL/SQL code. You would be surprised what these two views may reveal, including business logic, security framework, encryption/decryption keys, packages susceptible to injection vulnerabilities, etc. DBA_AUDIT_ reveals your database audit logs. 12c unified auditing views are not included in this role, but are covered by unified audit specific roles (AUDIT_ADMIN and AUDIT_VIEWER). There are other catalogs views as well that could potentially reveal sensitive information.

• The second security consideration is the number of objects that this role grants. The number of objects granted through this role in my 12.2.0.1 database in 3,625. It grants 2,300+ objects in my 11gR2 database. As one would expect, the number of objects granted through this role varies from version to version and options that are installed in your database. A query on DBA_TAB_PRIVS provides you the names of the objects being granted through this role. The sheer number of objects included in this role goes on to show you how powerful this role is.

Although most objects granted through this role are DBA_, V$, GV$ and CDB_, there are few odd balls with this role as well. Although the name of this role implies SELECT privileges, EXECUTE grant on few packages is included in this role (3 packages and 2 functions in my 12c database). Along with majority of SYS views, few objects (tables and views) owned by SYSTEM, XDB and OUTLN are also granted. SELECT_CATALOG_ROLE includes HS_ADMIN role which includes two more role - HS_ADMIN_SELECT_ROLE and HS_ADMIN_EXECUTE_ROLE.

SELECT ANY DICTIONARY Privilege

The second option is SELECT ANY DICTIONARY system privilege. The primary purpose of this system privilege is to grant SELECT access to the Oracle data dictionary, however, this privilege is more powerful than SELECT_CATALOG_ROLE. Like SELECT_CATALOG_ROLE, this privilege grants SELECTs on DBA_, GV_$, V_$ views, and 12c CDB_ views but also overcomes two limitations of SELECT_CATALOG_ROLE:

• This privilege grants SELECTs on underlying SYS tables such as TAB$, OBJ$ etc.
• This privilege grants SELECT on X$ views.
• This privilege also allows grantees to create stored PL/SQL objects such as procedures, packages or views on the objects it grants.

Since this is a system privilege, the names of objects and types of privileges being granted is not documented in Oracle documentation. It is easy to obtain such list for SELECT_CATALOG_ROLE through DBA_TAB_PRIVS, but no such documentation is available for this privilege. However, Oracle documentation mentions that underlying SYS tables with password hashes such as USER$, DEFAULT_PWD$, USER_HISTORY$ and ENC$ cannot be accessed through this privilege in 12c. In 11g, such access was granted. This privilege allowed SELECT access on LINK$ until Oracle10g, but this access has been removed since then.

Two security considerations mentioned above - very high number of SYS objects being granted and sensitivity of the metadata being revealed, apply to this privilege as well. In addition, you need to consider whether there is any need to grant (a) SELECT on underlying SYS tables and (b) privilege to create PL/SQL code on underlying SYS objects.

SELECT ANY DICTIONARY privilege may be a quick solution to meet your users’ requirements. As described here, SELECT ANY DICTIONARY privilege is more powerful than SELECT_CATALOG_ROLE and hence caution must be exercised before granting this privilege. I would recommend weighing in security considerations before granting this privilege.

SELECT ANY TABLE Privilege with o7_dictionary_accessibility = true

SELECT ANY TABLE is a system privilege that grants SELECT on all non-SYS objects. Since our requirement is to grant SELECT access on SYS objects, this privilege won't be sufficient unless you have set o7_dictionary_accessibility = true. SELECT_ANY_TABLE alone, without o7_dictionary_accessibility init.ora parameter, doesn't grant SELECT access on the data dictionary. In other words, this option is a combination of granting SELECT ANY TABLE privilege and setting o7_dictionary_accessibility init.ora parameter to TRUE.

I strongly recommend against using this option for the following reasons:

• Although with this option, SYS objects can be queried, it also opens up the doors for other security issues. o7_dictionary_accessibility parameter extends the access to SYS schema for other *ANY* privileges as well. For example, if you have CREATE ANY TABLE privilege, then you can create tables in SYS schema. If you have DELETE ANY TABLE privilege, then one can delete from SYS tables. There are quite few *ANY* privileges that would be extended to include SYS objects, and hence it is quite dangerous.
• Center for Internet Security (CIS) Oracle Benchmarks and many other industry standard security best practices recommend that o7_dictionary_accessibiity parameter be set to false.
• This parameter was primarily implemented to facilitate Oracle7 to Oracle8 migration and should not be used now.
• The side effect of SELECT ANY TABLE privilege is that it would allow grantee to select all the tables/views in the database, including applications/users data. The requirement was to grant SELECT on the SYS objects, not SELECT on all tables within the database. 

Execute_Catalog_Role

EXECUTE_CATALLOG_ROLE may be an option when you need to grant EXECUTE privilege on Oracle-supplied PL/SQL packages. This is an Oracle-supplied role. As the name implies, this role grants EXECUTE privilege on the Oracle data dictionary (catalog) packages to the grantees. The number of objects granted through this role in my 12.2.0.1 database in 110. The number was 80 in Oracle10g database. As one would expect, the number of objects granted through this role varies from version to version and installed options. A query on DBA_TAB_PRIVS provides you the names of the packages being granted through this role.

This role grants EXECUTE privilege on very powerful PL/SQL packages to the grantee. Just to name a few categories of packages being granted, this role includes packages related advanced queuing, log miner, redaction, SQL redefinition, replication, streams, row level security, etc.

This is a very powerful role. Before granting this role to anyone, please review the requirements and then make a determination whether this role should be granted on not.

Delete_Catalog_Role

There is one more role which sounds pretty dangerous - DELETE_CATELOG_ROLE. Your first reaction would be that this role allows deletes on the data dictionary objects, but don't worry - no objects are granted through this role. In Oracle11g/10g, this role includes only one privilege - DELETE on FGA_LOG$. DELETE on AUD$ was included in 9i but has been removed since then. This role has been deprecated in 12c.

Create your Own Roles based on specific requirements

As described in this article, you have various options to grant access to the data dictionary. Both SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY privilege offer a quick solution but security considerations should be weighed in. These role and privilege are very powerful and can be misused. In addition, the number of objects granted through these roles and privileges is quite large. Your users may not need access to all the objects that are granted trough these roles and privileges. SELECT ANY TABLE with o7_DICTIONARY_ACCESSIBILITY is not an option that I would recommend. My recommendation is to create a new role specific to your requirements, grant objects that are needed to this role and then grant the new role to the user.