How to create Oracle Multitenant database user accounts

Common users get all-access database pass

Oracle Multitenant also supports common users, which are created in the root container of a CDB and are able to access and manage it and all pluggable databases, provided they're given the appropriate privileges in each PDB.

Some common users are automatically generated by the Oracle software. For example, when a CDB is created, Oracle's Database Configuration Assistant utility will prompt you for passwords for the SYS and SYSTEM users, two administrative accounts that are set up at the root container level and propagated through the connected PDBs.

In my testbed environment for this series on managing an Oracle Multitenant database system, a database administrator (DBA) with the right privileges can connect as the SYSTEM user to both the CDB$ROOT root container and the HR_PROD pluggable database that I added to the architecture in the first article, which covered how to create a container database and PDBs.

You also can create your own common users in Oracle Multitenant. The names of all common users other than the Oracle-supplied ones must begin with "c##" or "C##" as a prefix. You can change that by modifying the COMMON_USER_PREFIX database parameter, but Oracle advises DBAs not to do so.

It would be very rare for applications to use common user accounts. Typically, they're for DBAs who need to connect to the PDBs in a system to do their administrative work. Let's look at how I created a common user for all of the containers in my CDB and gave it the CREATE SESSION system privilege and DBA role so it can be used to log in to and manage HR_PROD and the other PDBs.

Here's the code I entered into Oracle's SQL-Plus command-line interface, along with confirmations that the actions were completed:

SQL> create user c##my_dba identified by Password1 container=all;
User created.
SQL> grant create session to c##my_dba container=all;
Grant succeeded.
SQL> grant dba to c##my_dba container=all;
Grant succeeded.
SQL> grant select any dictionary to c##my_dba container=all;
Grant succeeded.
SQL> alter user c##my_dba set container_data=all container=current;
User altered.

As shown above, I also granted the SELECT ANY DICTIONARY system privilege and altered the user so it can see all of the container data in the Oracle data dictionary, which contains database schema object definitions, default column values, usernames and other database information.

Data dictionary changes in Oracle Multitenant

In order for Oracle Multitenant to work, the data dictionary did have to change somewhat to accommodate the container database and its PDBs. We'll close out the series by discussing how the views in the data dictionary have been modified to support the presence of multiple tenants in Oracle database systems.

When Oracle created the Multitenant architecture, it wanted the environment to be as transparent to users and applications as possible, meaning that the data dictionary views should return the same results as if the containers in a CDB weren't part of a multi-tenant database.

If you're in a PDB, the ALL_, USER_ and DBA_ data dictionary views show information for only that specific database. The same applies if you use conventional views in the root container. For example, in the SQL-Plus code section below, separate DBA_TABLES views show the number of tables in the root container and two of the PDBs in my Oracle Multitenant database testbed.

SQL> alter session set container=cdb$root;
Session altered.
SQL> select count(*) From dba_tables;
  COUNT(*)
----------
      1888
SQL> alter session set container=hr_prod;
Session altered.
SQL> select count(*) From dba_tables;
  COUNT(*)
----------
      5490
SQL> alter session set container=firstpdb;
Session altered.
SQL> select count(*) From dba_tables;
  COUNT(*)
----------
     23862

Expanded views of Multitenant databases

However, if you're in the root container, you can also use new CDB_ views. All of the existing DBA_ views have a corresponding CDB_ one in Oracle Multitenant. The difference is that using the CDB_ views in CDB$ROOT gives you the requested information for all containers. Here's an example showing that the containers in my database system hold a combined total of more than 900,000 tables:

SQL> select count(*) from cdb_tables;
  COUNT(*)
----------
    918165

The CDB_ views also contain an additional CON_ID column that includes the container ID for each row of data. You can join this column to the CON_ID column of V$PDBS, a new data dictionary view that shows all of the pluggable databases in a root container, to see the names of the various PDBs.

There are a few other new views in the data dictionary that DBAs will want to become familiar with for managing Oracle Multitenant database deployments. The brief descriptions below assume that you're querying the views from the root container:

• V$CONTAINERS shows all pluggable databases plus the root container.
• DBA_PDB_HISTORY shows when a PDB was cloned and from where.
• PDB_PLUG_IN_VIOLATIONS shows any issues arising from when a PDB was plugged into the root container.