iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 12c New Features

Online Oracle Training

LESSON 16

How to manage users in a CDB (Multitenant Container) and PDBs (Pluggable Databases)

 

There are two types of users: common users and local users.

The common users will be created in the root container and start with prefix of c## or C##. To create a local use, you need to login to the pluggable database using net service name and create user not start with c## or C## prefix.

 

Let us create a common user in the root container and see how other pluggable database containers will see it.

 

Create a common user in the root container.

# . ./.profile

# sqlplus /nolog

SQL> connect / as sysdba

--Just to make sure where you are

Get the container name

SQL> show con_name

 

CON_NAME

MYCDB

 

SQL> CREATE USER c##user4all IDENTIFIED BY mypass;

 

Let us view the default tablespaces were assigned for it in all containers.

SQL> SELECT username, default_tablespace, temporary_tablespace, con_id

   2   FROM cdb_users

   3   WHERE username = ‘c##user4all’

   4   ORDER by 4;

 

USER_NAME

DEFAULT_TABLESPACE

TEMPORARY_TABLESPACE

CON_ID

c##user4all

USERS

TEMP

1

c##user4all

MYTBS

TEMP4 MYPDB1

3

c##user4all

USERS

TEMP

4

c##user4all

USERS

TEMP

5

c##user4all

USERS

TEMP

6

Notice that the default tablespace and temporary tablespace for the mypdb1 container are not USERS and TEMP because we changed them.

 

Notice that you cannot create a local user in the root container. A local user or role is not allowed in CDB$ROOT.

 

A common user can access to all containers with the same password unless you go to different container and change or alter its password for that container.

 

SQL> connect c##user4all/mypass@mypdb1

Connected.

SQL> connect c##user4all/mypass@mypdb2

Connected.

 

Changing its password in the mypdb1 container

SQL> connect sys/mypass@mypdb1 as sysdba

SQL> ALTER USER c##user4all IDENTIFIED BY nomypass;

 

To create a local user (mylocal_user)

Connect to the container then create a local user.

SQL> connect sys/mypass@mypdb1 as sysdba

 

Create a local user

SQL> CREATE USER mylocal_user IDENTIFIED BY mypass;

User created.

 

Check mylocal_user default tablespace and temporary tablespace.

SQL> SELECT username, default_tablespace, temporary_tablespace

   2   FROM dba_users

   3   WHERE username = ‘mylocal_user’;

 

USER_NAME

DEFAULT_TABLESPACE

TEMPORARY_TABLESPACE

mylocal_user

MYTBS

TEMP4 MYPDB1

 

To change its default tablespace to USERS

SQL> ALTER USER mylocal_user DEFAULT TABLESPACE users;

 

Check mylocal_user default tablespace and temporary tablespace again.

SQL> SELECT username, default_tablespace, temporary_tablespace

   2   FROM dba_users

   3   WHERE username = ‘mylocal_user’;

 

USER_NAME

DEFAULT_TABLESPACE

TEMPORARY_TABLESPACE

mylocal_user

USERS

TEMP4 MYPDB1

Notice that the default tablespace was changed to users.

 

Good Luck!

 

 

Google
 
Web web site