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
No responses yet