Oracle Users

Oracle Users Version 11.2.0.3

General Information

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

Simple Password

CREATE USER <user_name>
IDENTIFIED BY <password>;

CREATE USER oracle1
IDENTIFIED BY oracle1;

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 oracle2
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 oracle3
IDENTIFIED BY oracle3
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 oracle4
IDENTIFIED BY oracle4
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 oracle5
IDENTIFIED BY oracle5
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 oracle6
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 oracle7
IDENTIFIED BY oracle7
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 oracle8
IDENTIFIED BY oracle8
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 oracle9
IDENTIFIED BY oracle9
ENABLE EDITIONS;

SELECT username, editions_enabled
FROM dba_users
ORDER BY 2,1;

Other user creation defaults

set linesize 121

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;

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>;

ALTER USER uwclass QUOTA 100K ON xdb;

Revoke Access From A Tablespace

ALTER USER <user_name> QUOTA 0 ON <tablespace_name>;

ALTER USER uwclass QUOTA 0 ON XDB;

Change the Default Tablespace

ALTER USER <user_name> DEFAULT TABLESPACE <tablespace_name>;

ALTER USER uwclass DEFAULT TABLESPACE users;

Lock An Account

ALTER USER <user_name> ACCOUNT LOCK;

ALTER USER uwclass ACCOUNT LOCK;

Unlock An Account

ALTER USER <user_name> ACCOUNT UNLOCK;

ALTER USER uwclass ACCOUNT UNLOCK;

Change Password Based on Hash

ALTER USER <user_name> ACCOUNT IDENTIFIED BY VALUES '<password_hash'>;

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]

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 / 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>;

DROP USER uwclass;

Drop User With Objects

DROP USER <user_name> CASCADE;

DROP USER uwclass CASCADE;

Drop Proxy User

DROP USER <user_name> REVOKE CONNECT THROUGH <app_server_userid>;

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

set linesize 121
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.