Oracle Users
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Data Dictionary Objects
ALL_TS_QUOTAS DBA_USERS USER_PASSWORD_LIMITS
ALL_USERS PROXY_INFO$ USER_RESOURCE_LIMITS
CDB_TS_QUOTAS PROXY_USERS USER_TS_QUOTAS
CDB_USERS USER$ USER_USERS
DBA_TS_QUOTAS    
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>;
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, password, 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>;
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 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;
Other user creation defaults SELECT username, initial_rsrc_consumer_group
FROM dba_users
ORDER BY 1;

conn uwclass/uwclass

col service_name format a20

SELECT schemaname, service_name
FROM gv$session
ORDER BY 1;
Container specific clause. To specify ALL the current container must be CDB$ROOT. To specify CURRENT the container must be a PDB. CREATE USER <user_name>
IDENTIFIED BY <password>
CONTAINER = <ALL | CURRENT>;
conn / as sysdba

CREATE USER  c##oracle11
IDENTIFIED BY oracle11
CONTAINER = CURRENT;
create user c##oracle11
            *
ERROR at line 1:
ORA-65094: invalid local user or role name


alter session set container = pdborcl;

CREATE USER  c##oracle11
IDENTIFIED BY oracle11
CONTAINER = CURRENT;
 
Creating Operating System Authenticated User
Changes to make for external authentication Step 1. Set the initSID.ora parameters:

remote_os_authent=TRUE
os_authent_prefix = "OPS$"

Step 2. Generate a new spfile

CREATE spfile FROM pfile='initorabase.ora';

Step 3. 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 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, password, 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.
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.
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 (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";
View Password Hashes Current Password:

SELECT name, password
FROM user$;
Previous Passwords (requires Profile verify function is active):

SELECT u.name, h.password, h.password_date
FROM user$ u, user_history$ h
WHERE u.user# = h.user#;
Grant Access to a Tablespace following user creation ALTER USER <user_name> QUOTA <quota_amount> ON <tablespace_name>;
conn sys@pdbdev as sysdba

ALTER USER uwclass QUOTA 100K ON xdb;
Revoke Access From 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;
Unlock An Account ALTER USER <user_name> ACCOUNT UNLOCK;
conn sys@pdbdev as sysdba

ALTER USER uwclass ACCOUNT UNLOCK;
Change Password Based on Hash ALTER USER <user_name> ACCOUNT IDENTIFIED BY VALUES '<password_hash'>;
conn sys@pdborcl 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
Enable / Disable Editioning ALTER USER <user_name> <ENABLE | DISABLE> EDITIONS [FORCE];
conn sys@pdborcl as sysdba

ALTER USER scott ENABLE EDITIONS;
ALTER USER scott DISABLE EDITIONS FORCE;
 
Proxy Clauses
Grant Proxy with Password

Note: the "using password" clause is 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@pdborcl 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;

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

conn c##mechid/oracle1@orabeta

-- create proxy for mechid
ALTER USER c##mechid GRANT CONNECT THROUGH c##scott;

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

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 / 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;
 
Special Tasks
Change the DBSNMP Password 1.0 Stop the standalone dbconsole
on UNIX/Linux
$ emctl stop dbconsole
on Windows stop the Oracle<oracle_home_name>DBConsole<SID> service or open a DOS Command Window and set the ORACLE_HOME and ORACLE_SID environment variables. Then:
C:\> emctl stop dbconsole

2.0 Verify the standalone dbconsole and the emagent are stopped
on Unix
$ emctl status dbconsole
$ emctl status agent
on Windows
C:\> emctl status dbconsole
C:\> emctl status agent

3.0 Connect to the database as a user with DBA privilege with SQL*Plus
and execute
SQL> alter user dbsnmp identified by <new_password>;

4.0 Verify the new password is valid
SQL> connect dbsnmp/<new_password>[@database_alias]

5.0 Go to $ORACLE_HOME/host_sid/sysman/emd
5.1 Save the file targets.xml to targets.xml.orig
5.2 Open the file targets.xml and search for the line:
<Property NAME="password" VALUE="<encrypted_string>" ENCRYPTED="TRUE"/>
Replace the encrypted value by the new password value
Replace TRUE by FALSE

6.0 Restart the standalone dbconsole
on Unix
$ emctl start dbconsole
on Windows
Start the Windows Service Oracle<oracle_home_name>DBConsole<SID> or
open a DOS Command Window and type:
C:\> set ORACLE_SID=<The SID of the database monitored by the dbconsole>
C:\> set ORACLE_HOME=<ORACLE_HOME of the database>
C:\> cd %ORACLE_HOME%/bin
C:\> emctl start dbconsole

7.0 Check that the password has been encrypted
Open the file targets.xml and search for the line:
<Property NAME="password" VALUE="<encrypted_string>" ENCRYPTED="TRUE"/>
Check that the password VALUE is encrypted
Check that the value of ENCRYPTED is TRUE
Change the SYSMAN Password 1.0 Stop the standalone dbconsole on Unix (or) $ emctl stop dbconsole on Windows
Stop the Windows Service Oracle<oracle_home_name>DBConsole or open a DOS Command Window and type:
C:\> emctl stop dbconsole

2.0 Check that the standalone dbconsole is stopped on Unix $ emctl status dbconsole
(or) on Windows check the status of the Windows Service Oracle<oracle_home_name>DBConsole
(or) open a DOS Command Window and type: C:\> emctl status dbconsole

3.0 Connect to the database as a user with DBA privilege with SQL*Plus and execute
SQL> alter user sysman identified by <new_password>;

4.0 Check the new password
SQL> connect sysman/<new_password>[@database_alias]

5.0 Go to $ORACLE_HOME/host_sid/sysman/config
5.1 Save the file emoms.properties to emoms.properties.orig
5.2 Edit the file emoms.properties
  a. Search for the line beginning with: oracle.sysman.eml.mntr.emdRepPwd=
     Replace the encrypted value by the new password value
  b. Search for the line: oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
     Replace TRUE by FALSE

6.0 Restart the standalone dbconsole on Unix (or) $ emctl start dbconsole on Windows
Start the Windows Service Oracle<oracle_home_name>DBConsole or open a DOS Command Window and type:
C:\> emctl start dbconsole

7.0 Check that the password has been encrypted
Edit the file emoms.properties
7.1 Search for the line beginning with: oracle.sysman.eml.mntr.emdRepPwd=
Check that the password is encrypted
7.2 Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwdEncrypted=
Check that the value is TRUE
 
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

Related Topics
Consumer Groups
Profiles
Roles
Sessions
SYS_CONTEXT Function
Tablespaces

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved