Oracle Database 12c: How to create users in a multitenant database?
- Common user: is the user who have access to the container database and the pluggable databases. A common user is a database user that has the same identity in the root and in every existing and future PDB.
- Local user: is the user who can access to a specific pluggable database.
What are the requirements of common user?
The name of every user-created common user must begin with the characters c## or C##.
Also refer to Oracle Database 12c Documentation for more info.
select distinct USERNAME from CDB_USERS where common = 'YES';
SQL> create user c##dba1 identified by dba container=all; SQL>grant connect, resource to c##dba1; Grant succeeded. SQL> grant dba to c##dba1 container=all; Grant succeeded. SQL> connect c##dba1/dba Connected.
Why I get "ORA-01031: insufficient privileges" when running "alter session set container=PDB1"?
Question Details: I have granted the dba role to the common user using "grant dba to c##dba1"
Answer: You have to add the "container=all" in Oracle Database 12c if you want that common user.
SQL> alter session set container=pdbdemo; Session altered. SQL> create user demo identified by demo container=current; User created. SQL> grant connect, resource to demo; Grant succeeded. SQL> connect demo/demo@localhost:1522/pdbdemo.us.oracle.com Connected.
Can I create local user when the current container is CDB$ROOT?
You can't create for the current container with "container=current". You get the following error: "ORA-65049: creation of local user or role is not allowed in CDB$ROOT".