Overview
Create and manage users, roles, and privileges
References
- Oracle ® 12.1 Database Reference
- 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
- Oracle ® 12.1 Database SQL Language Reference
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:
| USERNAME |
|---|
| ANONYMOUS |
| APEX_040200 |
| APEX_PUBLIC_USER |
| APPQOSSYS |
| AUDSYS |
| CTXSYS |
| DBSNMP |
| DIP |
| DVF |
| DVSYS |
| FLOWS_FILES |
| GSMADMIN_INTERNAL |
| GSMCATUSER |
| GSMUSER |
| LBACSYS |
| MDDATA |
| MDSYS |
| OJVMSYS |
| OLAPSYS |
| ORACLE_OCM |
| ORDDATA |
| ORDPLUGINS |
| ORDSYS |
| OUTLN |
| SI_INFORMTN_SCHEMA |
| SPATIAL_CSW_ADMIN_USR |
| SPATIAL_WFS_ADMIN_USR |
| SYS |
| SYSBACKUP |
| SYSDG |
| SYSKM |
| SYSTEM |
| WMSYS |
| XDB |
| XS$NULL |
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:
| V_VIEW_NAME | GV_VIEW_NAME |
|---|---|
| V_$ACTIVE_INSTANCES | GV_$ACTIVE_INSTANCES |
| V_$ACTIVE_SESS_POOL_MTH | GV_$ACTIVE_SESS_POOL_MTH |
| V_$ADVISOR_CURRENT_SQLPLAN | GV_$ADVISOR_CURRENT_SQLPLAN |
| V_$ADVISOR_PROGRESS | |
| V_$AW_LONGOPS | GV_$AW_LONGOPS |
| V_$BACKUP_NONLOGGED | GV_$BACKUP_NONLOGGED |
| V_$BH | GV_$BH |
| V_$BLOCKING_QUIESCE | GV_$BLOCKING_QUIESCE |
| V_$COPY_NONLOGGED | GV_$COPY_NONLOGGED |
| V_$DIAG_CRITICAL_ERROR | |
| V_$DIAG_INFO | GV_$DIAG_INFO |
| V_$EDITIONABLE_TYPES | GV_$EDITIONABLE_TYPES |
| V_$FLASHBACK_TXN_GRAPH | |
| V_$FLASHBACK_TXN_MODS | |
| V_$KXFTASK | |
| V_$LOADISTAT | GV_$LOADISTAT |
| V_$LOADPSTAT | GV_$LOADPSTAT |
| V_$LOCK_ACTIVITY | GV_$LOCK_ACTIVITY |
| V_$MAX_ACTIVE_SESS_TARGET_MTH | GV_$MAX_ACTIVE_SESS_TARGET_MTH |
| V_$NLS_PARAMETERS | GV_$NLS_PARAMETERS |
| V_$NLS_VALID_VALUES | GV_$NLS_VALID_VALUES |
| V_$NONLOGGED_BLOCK | GV_$NONLOGGED_BLOCK |
| V_$OPTION | GV_$OPTION |
| V_$PARALLEL_DEGREE_LIMIT_MTH | GV_$PARALLEL_DEGREE_LIMIT_MTH |
| V_$PQ_SESSTAT | GV_$PQ_SESSTAT |
| V_$PQ_TQSTAT | GV_$PQ_TQSTAT |
| V_$QUEUEING_MTH | GV_$QUEUEING_MTH |
| V_$REPLAY_CONTEXT | GV_$REPLAY_CONTEXT |
| V_$REPLAY_CONTEXT_LOB | GV_$REPLAY_CONTEXT_LOB |
| V_$REPLAY_CONTEXT_SEQUENCE | GV_$REPLAY_CONTEXT_SEQUENCE |
| V_$REPLAY_CONTEXT_SYSDATE | GV_$REPLAY_CONTEXT_SYSDATE |
| V_$REPLAY_CONTEXT_SYSGUID | GV_$REPLAY_CONTEXT_SYSGUID |
| V_$REPLAY_CONTEXT_SYSTIMESTAMP | |
| V_$RESTORE_POINT | GV_$RESTORE_POINT |
| V_$RSRC_CONSUMER_GROUP | GV_$RSRC_CONSUMER_GROUP |
| V_$RSRC_CONSUMER_GROUP_CPU_MTH | |
| V_$RSRC_CONS_GROUP_HISTORY | GV_$RSRC_CONS_GROUP_HISTORY |
| V_$RSRC_PLAN | GV_$RSRC_PLAN |
| V_$RSRC_PLAN_CPU_MTH | GV_$RSRC_PLAN_CPU_MTH |
| V_$RSRC_PLAN_HISTORY | GV_$RSRC_PLAN_HISTORY |
| V_$RSRC_SESSION_INFO | GV_$RSRC_SESSION_INFO |
| V_$SESSION_CONNECT_INFO | |
| V_$SESSION_LONGOPS | GV_$SESSION_LONGOPS |
| V_$SQLFN_ARG_METADATA | GV_$SQLFN_ARG_METADATA |
| V_$SQLFN_METADATA | GV_$SQLFN_METADATA |
| V_$SQLPA_METRIC | |
| V_$TEMPORARY_LOBS | GV_$TEMPORARY_LOBS |
| V_$TIMEZONE_FILE | GV_$TIMEZONE_FILE |
| V_$TIMEZONE_NAMES | GV_$TIMEZONE_NAMES |
| V_$VERSION | GV_$VERSION |
| V_$XS_SESSION_NS_ATTRIBUTES | GV_$XS_SESSION_NS_ATTRIBUTES |
| V_$XS_SESSION_ROLES | GV_$XS_SESSION_ROLES |
| GV_$RSRC_CONSUME_GROUP_CPU_MTH | |
| GV_$REPLAYCONTEXT_SYSTIMESTAMP |
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:
| GRANTEE | PRIVILEGE | ADMIN_OPTION | COMMON | CON_ID |
|---|---|---|---|---|
| C##FRED | CREATE SESSION | NO | NO | 3 |
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:
| GRANTEE | GRANTED_ROLE | ADMIN_OPTION | DELEGATE_OPTION | DEFAULT_ROLE | COMMON | CON_ID |
|---|---|---|---|---|---|---|
| C##DOUG | C##YAOCM | NO | NO | YES | NO | 1 |
| C##DOUG | CONNECT | NO | NO | YES | YES | 1 |
| C##DOUG | CONNECT | NO | NO | YES | YES | 3 |
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:
| GRANTEE | GRANTED_ROLE | ADMIN_OPTION | DELEGATE_OPTION | DEFAULT_ROLE | COMMON | CON_ID |
|---|---|---|---|---|---|---|
| C##DOUG | CONNECT | NO | NO | YES | YES | 3 |
| C##DOUG | C##YAOCM | NO | NO | YES | YES | 3 |
| C##DOUG | C##YAOCM | NO | NO | YES | NO | 1 |
| C##DOUG | CONNECT | NO | NO | YES | YES | 1 |
| C##DOUG | C##YAOCM | NO | NO | YES | YES | 1 |
CDB_ROLE_PRIVS has the same columns as DBA_ROLE_PRIVS .
The new rows are highlighted in PINK .