Create user in Oracle DB 12c

This post is older than a year. Consider some information might not be accurate anymore.

A new option for Oracle Database 12c, Oracle Multitenant delivers a new architecture that allows a multitenant container database (CDB) to hold many pluggable databases (PDB). An existing database can simply be adopted with no application changes required. Sadly some old sql scripts won’t work anymore. This post describes the basic changes.

If you connect as sysdba with sqlplus:

C:\Users\vinh>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mo Nov 14 10:54:54 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Verbunden mit:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

If you try to create an user, the ORA-01935 exception is returned.

SQL> CREATE USER 'RSS_ISS' IDENTIFIED BY 'riskshield';
CREATE USER 'RSS_ISS' IDENTIFIED BY 'riskshield'
            *
FEHLER in Zeile 1:
ORA-01935: Fehlender Benutzer- oder Rollenname

The reason for that is, that you are not on the pluggable database. Check this with

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

Or you can check with

SQL> select cdb from v$database;
CDB
---
YES

To create an user, you must apply the naming format. The common account naming format is similar to a normal account name - except that it starts with a special set of characters, C## by default.

Create user:

SQL> CREATE USER C##RSS_ISS IDENTIFIED BY riskshield;
Benutzer wurde erstellt.

Grant all privileges:

SQL> GRANT ALL PRIVILEGES TO C##RSS_ISS;
Benutzerzugriff (Grant) wurde erteilt.

The other option is to create a pluggable database and perform the changes on the PDB.