Pete Finnigan's Oracle Security Weblog

In this blog post is to look at some high level things to do with database users (for me) in terms of planning security assessments in the 12c database. This is to get some ground rules down when considering looking at Oracle database 12c security; initially this is in release 1 but going forwards the ground rules will change from 12cR1 and later releases from now as the multitenant container/pluggable architecture means that any review of users and their roles and privileges must take into account the fact that some are now global (common) and some are specific to the pluggable database. What do I mean by the last section, well testing a database in 11g is simpler than 12c for users and security because in 11g we have no containers/pluggable databases/roots/con_ids and more.

When we consider Oracle database security in any database (I have been doing database audits of Oracle databases since Oracle 7.3 and Oracle 8.0 and more regularly from 8i then through 9i and then into 10g and 11gR1 and 11gR2. Audits tend to follow real life in that unless it is a government system or someone has just bought Exadata it is unlikely that I will audit many 12cR1 databases straight away. Companies tend to not have the latest bleeding edge databases in production straight away. So my bread and butter work will almost certainly still be 10.2.0.5, 11.1.0.7 and 11.2.0.3 for some time to come with most tending towards 11.2 before 12cR1 becomes the norm.

This tendency doesn’t stop me looking at 12cR1 now so that when we do them for real we have a really good grounding in 12c.

I installed 12cR1 Standard Edition One first as we have licenses for that version and it’s simpler; as soon as the Windows download appears we will update our personal Oracle installations from 11gR2 to 12cR1 and use 11gr2 on XE for simple testing. When I installed 12cR1 I chose "Standard Edition One" to have a seed database and in the installer it asked for the name of the pluggable database; or rather it defaulted the global database name to orcl.localdomain and the "create as container" database is checked and the name of the pluggable database is "pbdorcl" so the installer thinks it’s OK to create a single pluggable database in standard edition one. Also I found this page in a post by Hans Forbrich about personal edition Oracle on Linux on the oracle-l list ; scroll down to the feature by addition section and it says this:

"Extra cost option for up to 252 pluggable databases. The multitenant architecture with one pluggable database (single tenant) is available in all editions."

So it seems to me that container databases (pluggable database) - multi-tenant are fundamental to 12cR1 and Oracle want us all to use this architecture and as such they must allow at least one pluggable databases in all additions. It will be interesting if a 12c XE edition comes out as to whether that will also include multi-tenant architecture.

So if we audit a 12cR1 database for security and if it is a “non” container database then essentially the “views” of users and privileges are most likely similar to 11g and 10g (although I have not tested this yet �" the views will still exist with con_id’s for instance but they should be “equal” as there are no containers).

In 12cR1 with containers enabled then it is not the same. Now we must contend with common users (global across all containers) and users in each pluggable database. Common users are known across the root container and also all pluggable databases. Their identity and password are the same across all but common users can have different privileges granted in different containers. This makes for complex analysis of privileges. Also if Database Vault (DV) is enabled in a pluggable container then it can block privileges if so configured; such as the restriction to stop SYSTEM from creating users for instance.

The common users created by Oracle itself are identified as “ORACLE_MAINTAINED” in the dba_users or cdb_users views. When a non-cdb database is plugged in to a CDB database as a PDB then it becomes complicated. Also if a CDB PBD is plugged into as CDB then also it becomes complex. There are rules to define what happens to a common users status (locked or not) and also its privileges and how they are merged into the CDB or PDB depending on whether common users are added to a new PDB or merged.

There are also “local users”, these are created or exist in a single pluggable container. These users can have system privileges but they apply only to the container that they exist in unlike global (common) users who can have privileges across all containers and also specifically in a container (pluggable). A local user can create other local users. A common user can create common users or local users or grant privileges to either (having the correct privileges itself of course). A local user cannot create common accounts or grant common privileges. Local users can have common roles such as DBA or RESOURCE but these apply only in the local users container. What is also strangest of all is that a local user is only unique in its own container. That is you can create a local user ERIC in one container and create another local user ERIC in another container they are not the same user but each pluggable container. It is also possible to grant access to common users objects to a local user.

Also editions can be enabled on a local user but not common users. In terms of creating users you still need to have the CREATE USER privilege as you did in all other releases of Oracle but this time the container comes into effect. To create common users you must have the common CREATE USER privilege. If you have the common CREATE USER privilege then you can create common or local users. To create a local user in a PDB you can have the privilege granted locally or commonly.

Back to the plot.... If we want to audit a database for security issues or potential issues in 12cR1 then we must understand how to view users and privileges across the common level and the local level as a whole.

There are some nuances and extras to consider in user analysis in 12cR1. Common users are identified by the new COMMON column on the views DBA_USERS, CDB_USERS, ALL_USERS or USER_USERS. The same information is available as flags in SYS.USER$ but access to SYS.USER$ is not available anymore to users with SELECT ANY DICTIONARY. This is a new security feature of 12cR1. If an account such as DIP, SYSTEM, DBSNMP or SYS is created by Oracle (the database) then it retains its name. If you create your own common user then the name must start with “C##” or “c##”. The Oracle created accounts are exempt from this of course. To create a common user you must also be connected to the root container CDB$ROOT. Also default tablespaces, temporary tablespaces and profiles all must exist in all containers. That means that profiles for instance can exist in different containers (pluggable databases). The final nuance related to users is the addition of a CON_ID to the user to show which container it exists for. This implies that a user is unique when combined with its container ID.

When you connect to the database the database session records your con_id in the session and this is linked to users/privileges/objects etc throughout the database. This certainly looks like a version of label security on some level.

So, that is enough background. In the next post I am going to create some users and have a dig around in the dictionary. Watch for more posts soon.