Administer users, roles, and privileges

Go back to OCM 12c Preparation Project 
Special Thanks to www.dbarj.com.br

In 12c we have now common and local users, this is the main point you need to understand the difference and play creating and granting privileges on the PDB or the CDB. You must clearly know:

[1] Managing Common and Local Users.
[2] Managing Local and Common Roles.
[3] Managing Local and Common Privileges.
[4] SYSASM / SYSBACKUP / SYSDG / SYSKM privileges.

You must also be able to create very fast a new user via SQLPlus / SQL Developer / EM Interface.

[1] Managing Common and Local Users.

===================
Create Common Users
===================
When creating a common user the following requirements must all be met.

1. You must be connected to a common user with the CREATE USER privilege.
2. The current container must be the root container( CDB$ROOT.).
3. The username for the common user must be prefixed with "C##" or "c##" 
and contain only ASCII or EBCDIC characters.
4. To explicitly designate a user account as a common user, in the 
CREATE USER statement, specify CONTAINER=ALL. 
If you are logged into the root, and if you omit the CONTAINER clause 
from your CREATE USER statement, 
then the CONTAINER=ALL clause is implied.
5. Do not create objects in the schemas of common users. Doing so can 
cause problems during plug-in and unplug operations.
6. The username must be unique across all containers.
7. The DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA and PROFILE must 
all reference objects that exist in all containers.
8. User-created schema objects owned by common users cannot be shared 
across PDB boundaries. 
9. Schema objects owned by Oracle-created common users are shared 
throughout the entire CDB.

The following example shows how to create common users with and without 
the CONTAINER clause from the root container.

CONN / AS SYSDBA

-- Create the common user using the CONTAINER clause.
CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;

-- Create the common user using the default CONTAINER setting.
CREATE USER c##test_user2 IDENTIFIED BY password1; 
<<< Default is CONTAINER=ALL
GRANT CREATE SESSION TO c##test_user2; 
<<<<< Other PDBs will not get the permission

Note: This behavior is same for 12.1.0.2 / 12.2.0.1 database.

--------------------------------------------------------------------
Doc. Example: CREATE USER Statement for Creating a Common User Account
=============
CREATE USER c##hr_admin
IDENTIFIED BY password
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp_ts
CONTAINER = ALL;

GRANT SET CONTAINER, CREATE SESSION TO c##hr_admin CONTAINER = ALL;
--------------------------------------------------------------------
===================
Create Local Users
===================
When creating a local user the following requirements must all be met.

1. To create a local user account, you must be connected to the PDB in 
which you want to create the account, 
and have the CREATE USER privilege.
2. The username for the local user must not be prefixed with "C##" or "c##".
3. The username must be unique within the PDB.
4. You can either specify the CONTAINER=CURRENT clause, or omit it, 
as this is the default setting when the current container is a PDB.
5. Both common and local users connected to a PDB can create local user 
accounts, as long as they have the appropriate privileges.

The following example shows how to create local users with and without 
the CONTAINER clause from the PDB container.

CONN / AS SYSDBA

-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdb1;

-- Create the local user using the CONTAINER clause.
CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;

-- Connect to a privileged user in the PDB.
CONN system/password@pdb1

-- Create the local user using the default CONTAINER setting.
CREATE USER test_user4 IDENTIFIED BY password1; <<< Same behavior
GRANT CREATE SESSION TO test_user4; <<< Same behavior

------------------------------------------------------------------------
Doc. Example : CREATE USER Statement for Creating a Local User Account
CREATE USER kmurray
IDENTIFIED BY password
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp_ts
PROFILE hr_profile
CONTAINER = CURRENT;
------------------------------------------------------------------------
ALTER USER Statement for Altering Common or Local User Accounts

The following example shows how to use the ALTER USER statement to restrict 
user c##hr_admin’s ability to view V$SESSION rows 
to those that pertain to sessions that are connected to CDB$ROOT, and to 
the emp_db and hr_db PDBs.

ALTER USER c##hr_admin
DEFAULT TABLESPACE data_ts
TEMPORARY TABLESPACE temp_ts
QUOTA 100M ON data_ts
QUOTA 0 ON test_ts
SET CONTAINER_DATA = (emp_db, hr_db) FOR V$SESSION 
CONTAINER = CURRENT;

Note: SET CONTAINER_DATA enables user c##hr_admin to have access to 
data related to the emp_db and hr_db PDBs as well as the root 
when he queries the V$SESSION view from the root.
HOL : How to use SET CONTAINER_DATA Clause. 

Note: 
[1] Oracle Only allow to use "set container_data" clause during alter user statement.
If you try to use this clause with create user then you will got 
ORA-00922: missing or invalid option
[2] ORA-02030: can only select from fixed tables/views
[3] ORA-65057: CONTAINER_DATA attribute must always include the current container

-bash-4.1$ sql
SQL> create user c##hr_admin
  2  identified by oracle_4U
  3  set container_data=PDB1 FOR V$SESSION;
set container_data=PDB1 FOR V$SESSION
*
ERROR at line 3:
ORA-00922: missing or invalid option

SQL> create user c##hr_admin identified by oracle_4U
  2  set container_data= PDB1 FOR V$SESSION;
set container_data= PDB1 FOR V$SESSION
*
ERROR at line 2:
ORA-00922: missing or invalid option

SQL> create user c##hr_admin identified by oracle_4U
  2   set container_data= ( PDB1 ) FOR V$SESSION;
 set container_data= ( PDB1 ) FOR V$SESSION
 *
ERROR at line 2:
ORA-00922: missing or invalid option

SQL> create user c##hr_admin identified by oracle_4U
  2  container_data= ( PDB1 ) FOR V$SESSION;
container_data= ( PDB1 ) FOR V$SESSION
*
ERROR at line 2:
ORA-00922: missing or invalid option

SQL> create user c##hr_admin identified by oracle_4U
  2  default tablespace users temporary tablespace temp;

User created.

SQL> alter user c##hr_admin
  2  set container_data = ( PDB1, PDB2) FOR V$SESSION;
alter user c##hr_admin
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> alter user c##hr_admin
  2  set container_data = ( PDB1, PDB2) FOR
  3  v_$session;
alter user c##hr_admin
*
ERROR at line 1:
ORA-65057: CONTAINER_DATA attribute must always include the current container
	
SQL> drop user c##hr_Admin;

User dropped.

SQL> create user c##hr_admin identified by orcle_4U
  2  default tablespace users
  3  temporary tablespace temp;
User created.

SQL> grant create session to c##hr_admin;
Grant succeeded.

SQL> alter user c##hr_admin
  2  set container_data=(CDB$ROOT,PDB1) FOR V_$SESSION
  3  CONTAINER=CURRENT;
User altered.

SQL> grant select on V_$SESSION to c##hr_admin;
Grant succeeded.

SQL> exit
From Terminal-A
sqlplus c##hr_admin/orcle_4U@orcl12c
SQL>select con_id,count(*) from v$session
    group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         1          4
         0         36

From Terminal-B
-bash-4.1$ sqlplus C##TEST/test@pdb1
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> exit

From Terminal-A
sqlplus c##hr_admin/orcle_4U@orcl12c

SQL>select con_id,count(*) from v$session
    group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         1          1
         4          1
         0         36

 

HOL : #Thank you oracle-base for detail hands on for SET CONTAINER_DATA clause 

Identifying Container Data Objects
==================================
SQL> CONN / AS SYSDBA
Connected.
SQL> select count(view_name) FROM cdb_views WHERE container_data = 'Y';

COUNT(VIEW_NAME)
----------------
           13325

SQL> select count(table_name) FROM cdb_tables WHERE container_data = 'YES';

COUNT(TABLE_NAME)
-----------------
                0
			
Default Behaviour
=================
SQL> CREATE USER c##my_user IDENTIFIED BY MyPassword1;

User created.

SQL> GRANT CREATE SESSION, DBA TO c##my_user CONTAINER=ALL;

Grant succeeded.

SQL> CONN c##my_user/MyPassword1@orcl12c
Connected.
SQL>
SQL> SELECT name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl12c/system01.dbf
/u01/app/oracle/oradata/orcl12c/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/undotbs01.dbf
/u01/app/oracle/oradata/orcl12c/users01.dbf

SQL> CONN c##my_user/MyPassword1@pdb1
Connected.
SQL> SELECT name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl12c/undotbs01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/system01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/users.dbf

All Container Data Objects in All Containers
============================================

SQL> CONN / AS SYSDBA
Connected.
SQL>
SQL>
SQL> ALTER USER c##my_user SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

User altered.

SQL> SET LINESIZE 100
COLUMN username FORMAT A20
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A20
COLUMN container_name FORMAT A20SQL> SQL> SQL> SQL>
SQL> SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;  2    3    4    5    6    7    8

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER                                                     Y

SQL> CONN c##my_user/MyPassword1@orcl12c
Connected.
SQL>
SQL> SELECT name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl12c/system01.dbf
/u01/app/oracle/oradata/orcl12c/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/undotbs01.dbf
/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
/u01/app/oracle/oradata/orcl12c/users01.dbf
/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/mypdb4/system01.dbf
/u01/app/oracle/oradata/orcl12c/mypdb4/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/mypdb4/mypdb4_users01.dbf
/u01/app/oracle/oradata/orcl12c/mypdb10/system01.dbf
/u01/app/oracle/oradata/orcl12c/mypdb10/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/mypdb10/users01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/system01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/users.dbf
/u01/app/oracle/oradata/orcl12c/pdb2/system01.dbf
/u01/app/oracle/oradata/orcl12c/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/pdb2/users.dbf

18 rows selected.

SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER USER c##my_user SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT;

User altered.

SQL> SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;
  2    3    4    5    6    7    8
no rows selected

All Container Data Objects in Specific Container
================================================

SQL> ALTER USER c##my_user SET CONTAINER_DATA=(CDB$ROOT,PDB1) 
CONTAINER=CURRENT;

User altered.

SQL> SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;  2    3    4    5    6    7    8

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER                                                     N PDB1
C##MY_USER                                                     N CDB$ROOT

SQL> CONN c##my_user/MyPassword1@orcl12c
Connected.
SQL>
SQL> SELECT name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl12c/system01.dbf
/u01/app/oracle/oradata/orcl12c/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/undotbs01.dbf
/u01/app/oracle/oradata/orcl12c/users01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/system01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/orcl12c/pdb1/users.dbf

7 rows selected.

SQL> ALTER USER c##my_user ADD CONTAINER_DATA=(PDB$SEED) CONTAINER=CURRENT;

User altered.

SQL> SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;  2    3    4    5    6    7    8

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER                                                     N CDB$ROOT
C##MY_USER                                                     N PDB1
C##MY_USER                                                     N PDB$SEED

SQL> ALTER USER c##my_user REMOVE CONTAINER_DATA=(PDB$SEED) CONTAINER=CURRENT;

User altered.

SQL> SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;  2    3    4    5    6    7    8

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER                                                     N PDB1
C##MY_USER                                                     N CDB$ROOT

SQL> ALTER USER c##my_user SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT;

User altered.

SQL> SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;  2    3    4    5    6    7    8

no rows selected

Specific Container Data Objects
===============================

SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER USER c##my_user SET CONTAINER_DATA=(CDB$ROOT,PDB1) FOR sys.v_$datafile CONTAINER=CURRENT;

User altered.

SQL> SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;  2    3    4    5    6    7    8

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER           SYS                  V_$DATAFILE          N CDB$ROOT
C##MY_USER           SYS                  V_$DATAFILE          N PDB1

SQL> ALTER USER c##my_user SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT;

User altered.

SQL> SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;
  2    3    4    5    6    7    8
USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER           SYS                  V_$DATAFILE          N CDB$ROOT
C##MY_USER           SYS                  V_$DATAFILE          N PDB1

SQL> ALTER USER c##my_user SET CONTAINER_DATA=DEFAULT FOR sys.v_$datafile CONTAINER=CURRENT;

User altered.

SQL> SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;  2    3    4    5    6    7    8

no rows selected
[2] Managing Local and Common Roles.

===================
Create Common Roles
===================
In a multitenant environment, privileges can be granted commonly for the 
entire CDB or they can granted locally, to a specific PDB.

Similar to users described previously, roles can be common or local. 
All Oracle-supplied roles are common and therefore available in the root 
container and all PDBs. 
Common roles can be created, provided the following conditions are met.

1. You must be connected to a common user with CREATE ROLE and the 
SET CONTAINER privileges granted commonly.
2. The current container must be the root container.
3. The role name for the common role must be prefixed with "C##" or "c##" 
and contain only ASCII or EBCDIC characters.
4. The role name must be unique across all containers.
5. The role is created with the CONTAINER=ALL clause.

Doc.
----
+A privilege that is granted commonly can be used in every existing and 
future container.
+Only common users can grant privileges commonly, and only if the grantee is common.
+A common user can grant privileges to another common user or to a common role.
+The grantor must be connected to the root and must specify 
CONTAINER=ALL in the GRANT statement.
+Both system and object privileges can be commonly granted. 
(Object privileges become actual only with regard to the specified object.)
+When a common user connects to or switches to a given container, this 
user's ability to perform various activities (such as creating a table) 
is controlled by both the commonly granted and locally granted privileges 
this user has.
+Do not grant privileges to PUBLIC commonly.


The following example shows how to create a common role and grant it to 
a common and local user.

CONN / AS SYSDBA

-- Create the common role.
CREATE ROLE c##test_role1;
GRANT CREATE SESSION TO c##test_role1;

-- Grant it to a common user.
GRANT c##test_role1 TO c##test_user1 CONTAINER=ALL;

-- Grant it to a local user.
ALTER SESSION SET CONTAINER = pdb1;
GRANT c##test_role1 TO test_user3;

Only common operations can be granted to common roles. 
When the common role is granted to a local user, the privileges are 
limited to that specific user in that specific PDB.

-------------------------------------------------------------------------
Doc.
Run the CREATE ROLE statement with the CONTAINER clause set to ALL.
For example:

CREATE ROLE c##sec_admin IDENTIFIED BY password CONTAINER=ALL; 
-------------------------------------------------------------------------
==================
Create Local Roles
==================
Local roles are created in a similar manner to pre-12c databases. 
Each PDB can have roles with matching names, 
since the scope of a local role is limited to the current PDB. 
The following conditions must be met.

1. You must be connected to a user with the CREATE ROLE privilege.
2. If you are connected to a common user, the container must be set to 
the local PDB.
3. The role name for the local role must not be prefixed with "C##" or "c##".
4. The role name must be unique within the PDB.

Doc.
----
+A privilege granted locally can be used only in the container in which 
it was granted. When the privilege is granted in the root, it applies 
only to the root.
+Both common users and local users can grant privileges locally.
A common user and a local user can grant privileges to other common or 
local roles.
+The grantor must be connected to the container and must specify 
CONTAINER=CURRENT in the GRANT statement.
+Any user can grant a privilege locally to any other user or role 
(both common and local) or to the PUBLIC role.


The following example shows how to create local a role and grant it to a
common user and a local user.

CONN / AS SYSDBA

-- Switch container.
ALTER SESSION SET CONTAINER = pdb1;

-- Alternatively, connect to a local or common user
-- with the PDB service.
-- CONN system/password@pdb1

-- Create the local role.
CREATE ROLE test_role1;
GRANT CREATE SESSION TO test_role1;

-- Grant it to a common user.
GRANT test_role1 TO c##test_user1;

-- Grant it to a local user.
GRANT test_role1 TO test_user3;

When a local role are granted to common user, the privileges granted via 
the local role are only valid when the common user has its 
container set to the relevant PDB.

------------------------------------------------------------------------------
Doc.

Run the CREATE ROLE statement with the CONTAINER clause set to CURRENT.
For example:

CREATE ROLE sec_admin CONTAINER=CURRENT;
------------------------------------------------------------------------------

[3] Managing Local and Common Privileges.

=======================================================
Granting Roles and Privileges to Common and Local Users
=======================================================
The basic difference between a local and common grant is the value used 
by the CONTAINER clause.

-- Common grants.
CONN / AS SYSDBA

GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_role1 CONTAINER=ALL;
GRANT c##test_role1 TO c##test_user1 CONTAINER=ALL;

-- Local grants.
CONN system/password@pdb1
GRANT CREATE SESSION TO test_user3;
GRANT CREATE SESSION TO test_role1;
GRANT test_role1 TO test_user3;

------------------------------------------------------------------------
Doc.
-----

Example1: The following example shows how to commonly grant a privilege 
to the common user c##hr_admin.

GRANT CREATE ANY TABLE TO c##hr_admin CONTAINER=ALL;

Example2: The following example shows how to grant an object privilege to 
the common user c##hr_admin so 
that he can select from the user_data table in the current PDB.

GRANT READ ON user_data TO c##hr_admin CONTAINER=CURRENT;

Example3: Granting a Privilege in a Multitenant Environment

GRANT CREATE TABLE TO c##hr_admin CONTAINER=ALL;

------------------------------------------------------------------------

[4] SYSASM / SYSBACKUP / SYSDG / SYSKM privileges.

When you create an Oracle database, the following users are automatically 
created to facilitate separation of duties for database administrators: 
SYSBACKUP, SYSDG, and SYSKM.

The SYSBACKUP, SYSDG, or SYSKM user accounts cannot be dropped.

A predefined DBA role is automatically created with every Oracle Database 
installation. This role contains most database system privileges. Therefore, 
the DBA role should be granted only to actual database administrators.

The DBA role does not include the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or 
SYSKM system privileges. These are special administrative privileges that 
allow an administrator to perform basic database administration tasks, 
such as creating the database and instance startup and shutdown. 
These administrative privileges are 
discussed in "Administrative Privileges".

Starting with Oracle Database 12c, the SYSBACKUP, SYSDG, and SYSKM 
administrative privileges are available. Each new administrative privilege 
grants the minimum required privileges to complete tasks in each area of 
administration. The new administrative privileges enable you to avoid 
granting SYSDBA administrative privilege for many common tasks.

SYSKM
To perform Transparent Data Encryption keystore operations.

SYSDG
To perform Data Guard operations. with either Data Guard Broker or the 
DGMGRL command-line interface.

SYSBACKUP
To perform backup and recovery operations either 
from Oracle Recovery Manager (RMAN) or SQL*Plus.

SYSOPER
This privilege allows a user to perform basic operational tasks, but 
without the ability to view user data.

Perform STARTUP and SHUTDOWN operations
CREATE SPFILE
ALTER DATABASE: open, mount, or back up
ALTER DATABASE ARCHIVELOG
ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete 
recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting 
as SYSDBA.)
Includes the RESTRICTED SESSION privilege

SYSDBA
This administrative privilege allows most operations, including the 
ability to view user data. It is the most powerful administrative privilege.

Perform STARTUP and SHUTDOWN operations
ALTER DATABASE: open, mount, back up, or change character set
CREATE DATABASE
DROP DATABASE
CREATE SPFILE
ALTER DATABASE ARCHIVELOG
ALTER DATABASE RECOVER
Includes the RESTRICTED SESSION privilege
-bash-4.1$ sqlplus c##hr_admin as sysdba
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
SYS

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
SYS

SQL> exit
-bash-4.1$ sqlplus c##hr_admin as sysbackup
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
SYS

SQL>  SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
SYSBACKUP

SQL> exit
-bash-4.1$ sqlplus c##hr_admin as sysdg
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
SYS

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
SYSDG

SQL> exit
-bash-4.1$ sqlplus c##hr_admin as syskm

SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
SYSKM

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
SYSKM

-bash-4.1$ sqlplus c##hr_admin/orcle_4U as sysoper

SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
PUBLIC

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
PUBLIC
=====================
Path to Documentation
=====================

Oracle Database SQL Language Reference 
-> 17 SQL Statements --> CREATE USER 
-->15 SQL Statements -->CREATE ROLE
Oracle Database Administrator's Guide 
-> 7 Managing Users and Securing the Database

36 Overview of Managing a Multitenant Environment
36.1.2 Common Users and Local Users

Database Security Guide
Creating User Accounts
2 Managing Security for Oracle Database Users
Creation of a Common User or a Local User

Database Security Guide
4 Configuring Privilege and Role Authorization
Managing Common Roles and Local Roles
Rules for Creating Common Roles
Creating a Common Role
Rules for Creating Local Roles
Creating a Local Role

Database Security Guide
7 Managing Security for a Multitenant Environment in Enterprise Manager
Managing Common and Local Users in Enterprise Manager
Managing Common and Local Roles and Privileges in Enterprise Manager

Database Administrator’s Guide
1 Getting Started with Database Administration
1.5.2 Administrative User Accounts
1.6.2 Operations Authorized by Administrative Privileges
1.6.4.1 Operating System Groups
1.6.4.3 Connecting Using Operating System Authentication

Thank you for visiting this blog.

Advertisements