Oracle | Users and Schemas

Oracle Users Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

User Security User identification and authentication are essential topics too often ignored in the commercial marketplace by Oracle Database Administrators. Use the following guidelines to improve your security practice.

Zero Trust Architecture, as specified in NIST 800-207, requires a connection not be trusted unless and until it is authenticated by a "Policy Decision / Enforcement Point."

Users authenticated by password, whether that password is hashed and stored in the database or in the Password File are insufficient protection. Users authenticated externally by the operating system, for example an OPS$ account are insufficient,

Data Dictionary Objects

ALL_TS_QUOTAS	DBA_USERS	USER_PASSWORD_LIMITS
ALL_USERS	DEFAULT_PWD$	USER_RESOURCE_LIMITS
CDB_TS_QUOTAS	PROXY_INFO$	USER_TS_QUOTAS
CDB_USERS	PROXY_USERS	USER_USERS
DBA_TS_QUOTAS	USER$

Oracle Maintained Users Created On Default Installation

ANONYMOUS	GGSYS	OUTLN
APEX_050100	GSMADMIN_INTERNAL	PDBADMIN
APEX_INSTANCE_ADMIN_USER	GSMCATUSER	REMOTE_SCHEDULER_AGENT
APEX_LISTENER	GSMUSER	SCOTT
APEX_PUBLIC_USER	LBACSYS	SI_INFORMTN_SCHEMA
APEX_REST_PUBLIC_USER	MDDATA	SYS
APPQOSSYS	MDSYS	SYS$UMF
AUDSYS	OJVMSYS	SYSBACKUP
CTXSYS	OLAPSYS	SYSDG
DBSFWUSER	ORACLE_OCM	SYSKM
DBSNMP	ORDDATA	SYSRAC
DIP	ORDPLUGINS	SYSTEM
DVF	ORDSYS	WMSYS
DVSYS	ORDS_METADATA	XDB
FLOWS_FILES	ORDS_PUBLIC_USER	XS$NULL

Exceptions

Error Code	Reason
ORA-65096	invalid common user or role name

System Privileges

ALTER USER	CREATE USER	DROP USER
BECOME USER

Create User Authenticated by Password

Common User with Simple Password CREATE USER c##<user_name> IDENTIFIED BY <password>;

conn / as sysdba

      

      CREATE USER oracle1

      IDENTIFIED BY oracle1;

      create user oracle1 identified by oracle1

      *

      ERROR at line 1:

      ORA-65096: invalid common user or role name

      

      CREATE USER c##oracle1

      IDENTIFIED BY oracle1;

PDB User with Simple Password CREATE USER <user_name> IDENTIFIED BY <password>;

CREATE USER oracle2

      IDENTIFIED BY oracle2;

      

      SELECT username,  created, password_versions

      FROM dba_users

      ORDER BY 1;

Create User with Complex Password CREATE USER <user_name> IDENTIFIED BY "<password>";

CREATE USER oracle3

      IDENTIFIED BY "N0t!4N0W";

Include Access To A Default Tablespace

Thanks Teresa Robinson for the correction CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name>;

SELECT tablespace_name

      FROM dba_tablespaces

      WHERE contents NOT IN ('TEMPORARY', 'UNDO')

      AND tablespace_name NOT IN (

        SELECT tablespace_name

        FROM dba_rollback_segs)

      AND tablespace_name NOT LIKE 'SYS%';

      

      CREATE USER oracle4

      IDENTIFIED BY oracle4

      DEFAULT TABLESPACE uwdata;

      

      SELECT username, default_tablespace

      FROM dba_users

      ORDER BY 1;

Include Access To A Temporary Tablespace CREATE USER <user_name> IDENTIFIED BY <password> TEMPORARY TABLESPACE <temporary_tablespace_name | temporary_tablespace_group>;

SELECT tablespace_name

      FROM dba_tablespaces

      WHERE contents = 'TEMPORARY';

      

      CREATE USER oracle5

      IDENTIFIED BY oracle5

      DEFAULT TABLE uwdata

      TEMPORARY TABLESPACE temp;

      

      SELECT username, default_tablespace, temporary_tablespace

      FROM dba_users

      ORDER BY 1;

Include Quota On Tablespaces CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> QUOTA <quota_amount> ON <tablespace_name> QUOTA <quota_amount> ON <tablespace_name>;

CREATE USER oracle6

      IDENTIFIED BY oracle6

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp

      QUOTA 0 ON SYSTEM

      QUOTA 0 ON SYSAUX

      QUOTA UNLIMITED ON uwdata

      QUOTA 10M ON data_med;

      

      SELECT username, tablespace_name, max_bytes, max_blocks

      FROM dba_ts_quotas

      ORDER BY 1;

Include Profile

Follow PROFILE link at page bottom for more information CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name>;

SELECT DISTINCT profile

      FROM dba_profiles;

      

      CREATE USER oracle7

      IDENTIFIED BY "N0Way!"

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp

      QUOTA 0 ON SYSTEM

      QUOTA 0 ON SYSAUX

      QUOTA UNLIMITED ON uwdata

      PROFILE monitoring_profile;

      

      SELECT username, profile

      FROM dba_users

      ORDER BY 1;

Expire the password on creation forcing the first user connecting to set their own password CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> PASSWORD EXPIRE;

CREATE USER oracle8

      IDENTIFIED BY oracle8

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp

      QUOTA UNLIMITED ON uwdata

      PASSWORD EXPIRE;

      

      SELECT username, expiry_date, account_status

      FROM dba_users;

Lock or unlock the account on creation CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> ACCOUNT <LOCK | UNLOCK>;

CREATE USER oracle9

      IDENTIFIED BY oracle9

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp

      QUOTA UNLIMITED ON uwdata

      ACCOUNT LOCK;

      

      SELECT username, lock_date, account_status

      FROM dba_users;

Enable editions on user creation CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> ACCOUNT <LOCK | UNLOCK> <DISABLE | ENABLE> EDITIONS;

SELECT username, editions_enabled

      FROM dba_users

      ORDER BY 2,1;

      

      CREATE USER oracle10

      IDENTIFIED BY oracle10

      ENABLE EDITIONS;

      

      SELECT username, editions_enabled

      FROM dba_users

      ORDER BY 2,1;

Create user without authentication CREATE USER <user_name> NO AUTHENTICATION DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> <DISABLE | ENABLE> EDITIONS;

CREATE USER oracle11

      NO AUTHENTICATION;

Other user creation defaults SELECT username, initial_rsrc_consumer_group FROM dba_users ORDER BY 1; conn uwclass/uwclass@pdbdev col service_name format a20 SELECT schemaname, service_name FROM gv$session ORDER BY 1;

To specify an Application owner that can log into an Application Root container as well as PDBs plugged into the Application Root specify ALL CREATE USER <user_name> IDENTIFIED BY <password> CONTAINER = <ALL | CURRENT>;

-- in the following example uwapp is an Application Root container

      conn sys@uwapp as sysdba

      

      CREATE USER  uwapp_owner

      IDENTIFIED BY oracle11

      CONTAINER = ALL;

Container specific clause. To specify a local user in a single container specify CURRENT CREATE USER <user_name> IDENTIFIED BY <password> CONTAINER = <ALL | CURRENT>;

-- in the following example uwapp is an Application Root container

conn sys@uwapp as sysdba

      

      CREATE USER  uwapp_user1

      IDENTIFIED BY oracle12

      CONTAINER = CURRENT;

Creating Operating System and Globally Authenticated User

Required changes to allow external authentication ... and changes we do not recommend the use of O/S authentication ALTER SYSTEM SET remote_os_authent=TRUE SID='*' SCOPE=SPFILE; ALTER SYSTEM SET os_authent_prefix="OPS$" SID = '*' SCOPE=SPFILE; -- restart the database -- add the following to the sqlnet.ora sqlnet.authentication_services = (NTS)

The syntax for CREATE USER where authentication is performed by the operating system on the server and, again, we do not recommend the use of O/S authentication CREATE USER <user_name> IDENTIFIED EXTERNALLY;

Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:

      

      CREATE USER ops$oracle  IDENTIFIED EXTERNALLY;

      

      SELECT username,  external_name

      FROM dba_users

      ORDER BY 1;

      

      GRANT create session TO ops$oracle;

      

      Step 2: Create a user in the operating system named oracle if one does not already exist.

      

      Step 3: Go to command line (terminal window in UNIX, cmd in Windows. Type 'sqlplus' (without the single quotes).
      You should be connected to the database without having to enter username/password.

The syntax for CREATE USER where authentication is performed by the operating system on the client CREATE USER <machine_name\user_name> IDENTIFIED EXTERNALLY;

Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:

      

      CREATE USER "PC100\USER"  IDENTIFIED EXTERNALLY;

      

      where PC100 is the name of the client computer. Then:

      GRANT CREATE SESSION TO "PC100\USER";

      

      Step 2 - Create a user in Windows named USER.

      

      Step
      3 - Log on Windows as USER and go to the C:\> command line.

      

      Type 'sqlplus' (without the single quotes). You should be connected to your database without having to enter any username/password.

Global Users CREATE USER <user_name> IDENTIFIED GLOBALLY AS 'cn=<connection_name>,dc=<data_center_name>';

CREATE USER safeadmin IDENTIFIED GLOBALLY AS 'cn=safeadmin,cn=Users,dc=dbsecworx,dc=com';

Group membership in UNIX Operating system accounts that are members of the operating system's DBA group are not required to provide a userid and password when logging in.

Group membership in Windows Operating system accounts that are members of the operating system's ORA_DBA group are not required to provide a userid and password when logging in.

Read Only Users CREATE USER <user_name> IDENTIFIED BY <password> READ ONLY;

CREATE USER rptwriter IDENTIFIED BY "N0t!A!Chance" READ ONLY;

Windows logins Automatic logins with the Windows operating system are not secure. Anyone can edit the Oracle configuration file and change their user ID. For security reasons, if users of these systems are logging in over the network, Oracle Corporation recommends that you disable the ops$ logins in the listener.ora.

Alter User

Change Password ALTER USER <user_name> IDENTIFIED BY <new_password>;

ALTER USER SYS IDENTIFIED BY "N0t!A!Chance";

Change the password following implementation of Password Verification if the original password was not saved ALTER USER <user_name> IDENTIFIED BY <new_password> REPLACE <old_password>;

ALTER USER SYS IDENTIFIED BY "New$Pwd" REPLACE "Orig$Pwd";

Grant access to a tablespace after user creation ALTER USER <user_name> QUOTA <quota_amount> ON <tablespace_name>;

conn sys@pdbdev as sysdba

      

      ALTER USER uwclass QUOTA 10M  ON xdb;

Revoke access to a Tablespace ALTER USER <user_name> QUOTA 0 ON <tablespace_name>;

conn sys@pdbdev as sysdba

      

      ALTER USER uwclass QUOTA 0 ON XDB;

Change the Default Tablespace ALTER USER <user_name> DEFAULT TABLESPACE <tablespace_name>;

conn sys@pdbdev as sysdba

      

      ALTER USER uwclass DEFAULT TABLESPACE users;

Lock An Account ALTER USER <user_name> ACCOUNT LOCK;

conn sys@pdbdev as sysdba

      

      ALTER USER uwclass  ACCOUNT LOCK;

Read Only ALTER USER <user_name> READ ONLY;

CREATE USER rptwriter READ ONLY;

Read Write ALTER USER <user_name> READ WRITE;

CREATE USER rptwriter READ 
      WRITE;

Unlock An Account ALTER USER <user_name> ACCOUNT UNLOCK;

conn sys@pdbdev as sysdba

      

      ALTER USER uwclass  ACCOUNT UNLOCK;

Change Password Based on Hash: This is valid in 12.1.0.2 and earlier releases but has finally been made non-functional as of 12.2.0.1 because the hashes are no longer visible in 12.2.0.1 ALTER USER <user_name> ACCOUNT IDENTIFIED BY VALUES '<password_hash'>;

conn sys@pdbdev as sysdba

      

      SELECT password

      FROM user$

      WHERE name = 'SCOTT';

      

      ALTER USER scott IDENTIFIED BY XYZ;

      

      SELECT password

      FROM user$

      WHERE name = 'SCOTT';

      

      ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67';

      

      conn scott/tiger@pdbdev

Enable / Disable Editioning ALTER USER <user_name> <ENABLE | DISABLE> EDITIONS [FORCE];

conn sys@pdbdev as sysdba

      

      ALTER USER scott ENABLE EDITIONS;

      ALTER USER scott DISABLE EDITIONS FORCE;

Proxy Clauses

Grant Proxy with Password

Note: the "using password" clause was deprecated as of 11.2 If you do not specify the AUTHENTICATION REQUIRED clause, then Oracle Database uses either the AUTHENTICATED USING CERTIFICATE clause or the AUTHENTICATED USING DISTINGUISHED NAME clause

ALTER USER <user_name> GRANT CONNECT THROUGH <app_server_userid> [AUTHENTICATED USING PASSWORD];

conn sys@pdbdev as sysdba

      

      -- create a common user

      CREATE USER c##mechid

      IDENTIFIED BY oracle1

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp;

      

      GRANT create session TO c##mechid;

      GRANT alter user TO c##mechid;

      

      ALTER USER c##scott PROXY ONLY CONNECT;

      
      

      AUDIT CONNECT BY c##scott
      ON BEHALF OF c##mechid;

      

      conn c##mechid/oracle1@pdbdev

      

      -- create proxy for mechid

      ALTER USER c##mechid GRANT CONNECT THROUGH c##scott;

      

      conn c##scott[C##MECHID]/tiger@pdbdev

      

      sho user

      SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

      SELECT sys_context('USERENV', 'CURRENT_USER') FROM dual;

      SELECT sys_context('USERENV', 'PROXY_USER') FROM dual;

      

      conn sys@pdbdev as sysdba

      

      SELECT * FROM sys.proxy_info$;

AUTHENTICATION REQUIRED Clause ALTER USER <user_name> GRANT CONNECT THROUGH <app_server_userid> AUTHENTICATION REQUIRED;

ALTER USER app_user GRANT CONNECT THROUGH uwweb

      AUTHENTICATION REQUIRED;

Grant Proxy with Distinguished Name ALTER USER <user_name> GRANT CONNECT THROUGH <app_server_userid> AUTHENTICATED USING DISTINGUISHED NAME;

ALTER USER app_user GRANT CONNECT THROUGH uwweb

      AUTHENTICATED USING DISTINGUISHED NAME;

Grant Proxy with Role ALTER USER <user_name> GRANT CONNECT THROUGH <app_server_userid> WITH ROLE <role_name>;

ALTER USER app_user GRANT CONNECT THROUGH uwweb

      WITH ROLE CONNECT;

      

      or

      

      ALTER USER app_user GRANT CONNECT THROUGH uwweb

      WITH ROLE ALL EXCEPT payroll;

Grant Proxy based on Authenticating Certificate ALTER USER <user_name> GRANT CONNECT THROUGH <app_server_userid> AUTHENTICATED USING CERTIFICATE TYPE <name> VERSION <version_no>;

ALTER USER appuser GRANT CONNECT THROUGH uwweb

      AUTHENTICATED USING CERTIFICATE TYPE 'X.509' VERSION '3';

Drop User

Drop User Without Objects DROP USER <user_name>;

conn sys@pdbdev as sysdba

      

      DROP USER uwclass;

Drop User With Objects DROP USER <user_name> CASCADE;

conn sys@pdbdev as sysdba

      

      DROP USER uwclass CASCADE;

Drop Proxy User Access DROP USER <user_name> REVOKE CONNECT THROUGH <app_server_userid>;

conn sys@pdbdev as sysdba

      

      ALTER USER app_user REVOKE CONNECT THROUGH uwweb;

Queries

Active Connected Users SELECT sid, username, action FROM v$session WHERE username IS NOT NULL AND status = 'ACTIVE';

Get Connection Information set linesize 141 col authentication_type format a20 col osuser format a20 col client_charset format a20 col network_service_banner format a60 desc gv$session_connect_info; SELECT sid, authentication_type, osuser, network_service_banner FROM gv$session_connect_info; SELECT sid, client_charset, client_connection, client_oci_library, client_version FROM gv$session_connect_info;

Get Idle Time col SID format 999 col IDLE format a20 col PROGRAM format a20 col USERNAME format a20 SELECT sid, osuser, username, status, TO_CHAR(logon_time, 'DAY HH24:MI:SS') LOGON_TIME, FLOOR(last_call_et/3600) || ':' || FLOOR(MOD(last_call_et, 3600) / 60) || ':' || MOD(MOD(last_call_et, 3600), 60) IDLE, program FROM v$session WHERE username IS NOT NULL ORDER BY last_call_et;

Get User Memory Usage SELECT username, program, value || 'bytes' "Current UGA memory" FROM v$session sess, v$sesstat sstat, v$statname sname WHERE sess.sid = sstat.sid AND sstat.statistic# = sname.statistic# AND sname.name = 'session uga memory';

Get User Waits SELECT SUBSTR(s.USERNAME,1,15) USERNAME, SUBSTR(s.status,1,8) STATUS, SUBSTR(s.server,1,10) SERVER, SUBSTR(s.type,1,10) TYPE, SUBSTR(s.event,1,20) "WAIT EVENT", DECODE(s.command, 1,'Create Table', 2,'Insert', 3,'Select', 6,'Update', 7,'Delete', 8,'Drop', 9,'Create Index', 10,'Drop Index', 12,'Drop Table', 17,'Grant', 26,'Lock Table', 42,'Alter Session', 43,'Alter User', 44,'Commit', 45,'Rollback', s.command) COMMAND FROM v$session s, v$session_wait w WHERE s.sid = w.sid AND s.username != 'SYS' ORDER BY 1;

Identify the current session SELECT user, osuser, terminal, program FROM gv$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

Identify current users col name format a20 col process format a12 col program format a17 SELECT sid, serial#, SUBSTR(username,1,10) NAME, SUBSTR(machine,1,10) COMPUTER, command, status, SUBSTR(osuser,1,8) OSUSER, process, program FROM v_$session ORDER BY name;

Identify current users col username format a21 col profile format a10 col "tmp tbs" format a10 SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS", u.profile, r.granted_role, r.admin_option, r.default_role FROM sys.dba_users u, sys.dba_role_privs r WHERE u.username = r.grantee (+) GROUP BY u.username, u.default_tablespace, u.temporary_tablespace, u.profile, r.granted_role, r.admin_option, r.default_role;

Identify default users and passwords col user_name format a30 col pwd_verifier format a20 SELECT * FROM default_pwd$ ORDER BY 1;

Identify privileged users SELECT * FROM gv$pwfile_users;

-- for this to be meaningful you must be using an exclusive password file to authenticate privileged users

Failed login attempts by user

Note: This will not track failures due to phishing for user/schema names conn uwclass/xyz@pdbdev ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. conn / as sysdba Connected. ALTER SESSION SET CONTAINER=PDBDEV; Session altered. SELECT name, lcount FROM user$ WHERE lcount <> 0; NAME LCOUNT ------------------------------ ------- UWCLASS 1

Addendum

Direct Database Connection SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 15 21:52:24 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL>