Oracle Users

Databases

Oracle

Oracle Security

Common users exist in the CDB and all PDBs and generally start with: C##
Local users exist only in the PDBs they have been created in
Externally identified users are authenticated by the OS and generally start with: OPS$

Check

DBA_USERS

To list all users...

SELECT username

FROM dba_users

ORDER BY username;

DBA_USERS

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

SELECT username

FROM dba_users

WHERE oracle_maintained = 'N'

ORDER BY username;

DBA_USERS

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

SELECT username, common, con_id

FROM cdb_users

ORDER BY username;

DBA_USERS

CDB_USERS show all users in CDB and PDBs. DBA_USERS shows information only for your current con_id (CDB or PDB)

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;

DBA_CONTAINER_DATA

CDB_CONTAINER_DATA show information from CDB and PDBs. DBA_CONTAINER_DATA shows information only for your current con_id (CDB or PDB)

Maximum Concurrent Users

SELECT dbid,

version,

highwater

FROM DBA_HIGH_WATER_MARK_STATISTICS

WHERE name = 'SESSIONS';

DBA_HIGH_WATER_MARK_STATISTICS

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;

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

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

Useful in the context of RAS

Multi-Tenant Container Name

Multi-Tenant Container ID

Session ID

Hostname (from which the client has connected)

Database Name

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;

DBA_USERS

DBA_OBJECTS

Users with a specific Privilege

SELECT *

FROM dba_sys_privs

WHERE PRIVILEGE = 'ALTER SYSTEM';

DBA_SYS_PRIVS

Users with default password

SELECT *

FROM dba_users_with_defpwd;

DBA_USERS_WITH_DEFPWD

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

Tablespace

TEMP Tablespace

Profile

Oracle Multitenant

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