Oracle Users

Check

To list all users...

SELECT username

  FROM dba_users 

ORDER BY username;

To list all users that were not created by Oracle itself (12c+)...

SELECT username 

  FROM dba_users 

 WHERE oracle_maintained = 'N' 

 ORDER BY username;

To list all users in the CDB and all PDBs (Multi-Tenant)...

SELECT username, common, con_id

  FROM cdb_users 

ORDER BY username;

For common users, you can check container data object scope using...

SET LINESIZE 100

COLUMN username FORMAT A20

COLUMN owner FORMAT A20

COLUMN object_name FORMAT A20

COLUMN container_name FORMAT A20

SELECT username,

       owner,

       object_name,

       all_containers,

       container_name

  FROM cdb_container_data

 WHERE username = 'C##MY_USER'

 ORDER BY 1,2,3;

Maximum Concurrent Users

SELECT dbid,

       version,

       highwater

  FROM DBA_HIGH_WATER_MARK_STATISTICS

 WHERE name = 'SESSIONS';

Current User

SELECT SYS_CONTEXT('userenv','current_user') FROM dual;

SELECT SYS_CONTEXT('userenv','current_schema') FROM dual;

SELECT SYS_CONTEXT('userenv','authenticated_identity') FROM dual;

SELECT SYS_CONTEXT('USERENV','CON_NAME') FROM dual;

SELECT SYS_CONTEXT('USERENV','CON_ID') FROM dual;

SELECT SYS_CONTEXT('USERENV','SID') FROM dual;



Useful in the context of RAS

Multi-Tenant Container Name

Multi-Tenant Container ID

Session ID

RAS=Real Application Security

Users with no Objects

SELECT username

  FROM dba_users

 WHERE username NOT IN (SELECT owner

                          FROM dba_objects)

 ORDER BY username;

CREATE User

CREATE USER &user IDENTIFIED BY "&password"

DEFAULT TABLESPACE &&tspace

TEMPORARY TABLESPACE &temp

QUOTA UNLIMITED ON &&tspace

PROFILE &profile;

This works the same way in single-tenant and in any PDB of multi-tenant architecture.

Password Generation

MySQL has the "IDENTIFIED BY RANDOM PASSWORD" clause. It's still not in Oracle 21c so, in the interim we can do something like this...

TODO

Mutli-Tenant Considerations

Attempting to create a regular user (as above) in the Container Database (CDB) of a multi-tenant implementation usually results in an ORA-65096. Users created in the CDB rather than a PDB are called "common" users and need to be created using a specific naming standard. Common users exist is the CDB and all current and future PDBs

The exception to this is where "Oracle maintained" users are created using scripts provided by Oracle. There is a hidden parameter "_ORACLE_SCRIPT=true" that these scripts use to bypass the restrictions. You should never use this  parameter for your own purposes. See http://www.petefinnigan.com/weblog/archives/00001370.htm for full details.

CREATE USER c##myuser IDENTIFIED BY "&password"

The C## prefix is controlled by a hidden parameter called "_common_user_prefix". To avoid confusion for any future DBA of this database, it is not recommended to change this parameter.

To enable the common user to login (create a session) on the CDB and all PDBs...

GRANT CREATE SESSION TO c##myuser CONTAINER=ALL;

To enable the common user to login on the current container only...

GRANT CREATE SESSION TO c##myuser CONTAINER=CURRENT;

Container Data Object Scope

Many of the dynamic performance views and AWR Repository objects live in the CDB but show, by default, container specific information. We can use the CONTAINER_DATA option to change the way this works.

Default...

CONNECT c##myuser@CDB

SELECT name from v$datafile;

Shows all datafiles related to the CDB (e.g. for the SYSTEM tablespace etc).

CONNECT c##myuser@PDB

SELECT name from v$datafile;

Shows all datafiles related to the PDB (e.g. for user/schema specific tablespaces etc).

CONNECT c##myuser@CDB

ALTER USER c##myuser SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

SELECT name from v$datafile;

Shows ALL datafiles for ALL containers including the seed container.

To undo that...

CONNECT c##myuser@CDB

ALTER USER c##myuser SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT;

To limit to specific containers...

CONNECT c##myuser@CDB

ALTER USER c##myuser SET CONTAINER_DATA=(CDB$ROOT,PDB) CONTAINER=CURRENT;

CDB$ROOT must always be included

You can add/remove containers from the list using...

ALTER USER c##myuser ADD CONTAINER_DATA=(PDB$SEED) CONTAINER=CURRENT;

ALTER USER c##myuser REMOVE CONTAINER_DATA=(PDB$SEED) CONTAINER=CURRENT;

If we wanted the scope changes to apply only to v$datafile we could use something like...

ALTER USER c##myuser SET CONTAINER_DATA=(CDB$ROOT,PDB) FOR sys.v_$datafile CONTAINER=CURRENT;

Note that we have to use the object name not the commonly used synonym just as we would when making Grants

We have to explicity undo this change using...

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

(i.e. ALTER USER c##myuser SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT; does not work in this scenario)

DROP USER

To drop a user and all objects owned by that user...

DROP USER &user CASCADE;

Generate User DDL

@/u01/dba/dbuserinfo1

Recreate a User without changing Password (Without knowing the Password)

WITH t AS (SELECT TO_CHAR(dbms_metadata.get_ddl('USER','&USER')) ddl

             FROM dual)

SELECT REPLACE(SUBSTR(ddl,1,INSTR(ddl,'DEFAULT')-1),'CREATE','ALTER')||';'

  FROM t;

Bibliography