Create and manage users, roles, and privileges

Overview

Create and manage users, roles, and privileges

References

• Oracle^® 12.1 Database Reference
  • Part II Static Data Dictionary Views
    • 5.255 DBA_ROLE_PRIVS
    • 6.39 DBA_SYS_PRIVS
• Oracle^® 12.1 Database Security Guide
  • 2 Managing Security for Oracle Database Users
    • Creating User Accounts
    • Altering User Accounts
    • Configuring User Resource Limits
    • Dropping User Accounts
    • Database User and Profile Data Dictionary Views
  • 4 Configuring Privilege and Role Authorization
    • About Privileges and Roles
      • Managing Commonly and Locally Granted Privileges
        • Enabling Common Users to View CONTAINER_DATA Object Information
          • Viewing Data About the Root, CDB, and PDBs While Connected to the Root
          • Enabling Common Users to Query Data in Specific PDBs
• Oracle^® 12.1 Database SQL Language Reference
  • ALTER SESSION
  • ALTER USER
  • CREATE USER
  • GRANT

Notes

Create Users

See Creating User Accounts.

Common Users Supplied by Oracle

Although the standard is for common usernames to be prefixed by 'C##', those supplied by Oracle do not follow this standard as revealed by the following script:

SELECT

     username

 FROM

     dba_users

 WHERE

         common = 'YES'

     AND

         oracle_maintained = 'Y'

 ORDER BY 1; 

The result is:

Manage Users

Create Common User

Use the following commands to create a common user.

• Must be in the root container (CDB$ROOT)
• CONTAINER=ALL is the default for the root container.

ALTER SESSION SET CONTAINER=cdb$root;

CREATE USER "C##DOUG"

  PROFILE "DEFAULT"

  IDENTIFIED BY "&PW."

  DEFAULT TABLESPACE "USERS"

  TEMPORARY TABLESPACE "TEMP"

  QUOTA UNLIMITED ON "USERS"

  ACCOUNT UNLOCK

  CONTAINER=ALL; 

Manage Privileges

Use the following commands to allow a common user to connect to all containers.

• Must be in the root container (CDB$ROOT)
• CONTAINER=ALL is the default for the root container.

ALTER SESSION SET CONTAINER=cdb$root; GRANT "CONNECT" TO "C##DOUG" CONTAINER=ALL;

Use the following commands to allow a common user to see some dynamic views.

• Must be in the root container (CDB$ROOT)
• ADD CONTAINER_DATA requires CONTAINER = CURRENT

ALTER SESSION SET CONTAINER=cdb$root;

ALTER USER "C##DOUG" ADD CONTAINER_DATA = ( "PLUM" ) CONTAINER = CURRENT; 

The dynamic views available to C##DOUG on PLUM are:

This table was generated by the following query:

WITH

   gv_views AS (

     SELECT

         view_name,

         substr(view_name,4)

           AS stub

       FROM

         all_views

       WHERE

           owner = 'SYS'

         AND

           regexp_like(view_name,'^gv_\$','i')

     ),

   v_views AS (

     SELECT

         view_name,

         substr(view_name,3)

           AS stub

       FROM

         all_views

       WHERE

           owner = 'SYS'

         AND

           regexp_like(view_name,'^v_\$','i')

     )

SELECT

     v_views.view_name

       AS v_view_name,

     gv_views.view_name

       AS gv_view_name

   FROM

       gv_views

     FULL OUTER JOIN

       v_views

     USING (

       stub

       )

   ORDER BY

     v_view_name

; 

Isolate Common User to a Single PDB

To isolate a common user to a single PDB, run the following SQL*Plus commands:

ALTER SESSION SET CONTAINER=plum;

GRANT CREATE SESSION TO "C##FRED" CONTAINER=CURRENT; 

This is confirmed through the following SQL:

SELECT * FROM cdb_sys_privs WHERE grantee = 'C##FRED'; 

The output is:

CDB_SYS_PRIVS has the same columns as DBA_SYS_PRIVS.

Manage Roles

Grant Role to Common User in the Current Container

Create and grant role, C##YAOCM, to common user, C##DOUG, in the current container, CDB$ROOT, using the following SQL:

ALTER SESSION SET CONTAINER=cdb$root;

CREATE ROLE c##yaocm CONTAINER=ALL;

GRANT c##yaocm TO c##doug; 

Display role privileges for common user using the following SQL:

SELECT * FROM cdb_role_privs WHERE grantee = 'C##DOUG';

The output is:

CDB_ROLE_PRIVS has the same columns as DBA_ROLE_PRIVS.

Grant Role to Common User in All Containers

Use the following SQL to grant the C##YAOCM to the user, C##DOUG in all containers:

ALTER SESSION SET CONTAINER=cdb$root;

GRANT c##yaocm TO c##doug CONTAINER=ALL; 

Display role privileges for common user using the following SQL:

SELECT * FROM cdb_role_privs WHERE grantee = 'C##DOUG'; 

The output is:

CDB_ROLE_PRIVS has the same columns as DBA_ROLE_PRIVS.

The new rows are highlighted in PINK.