Oracle User Schema Owner

Search the Reference Library pages:

Oracle Users

Version 11.1

Creating Oracle (Password) Authenticated User

Dependencies

user$
dba_users	all_users	user_users
dba_ts_quotas	all_ts_quotas	user_ts_quotas

proxy_users - users who can assume the identity of other users

resource_cost - lists the cost for each resource.

user_resource_limits - resource limits for the current user

user_password_limits - password parameters assigned by profile

v_$session - user session information

v_$sesstat - user session statistics

v_$statname - decoded statistic names for v_$sesstat

System Privileges

create user

alter user

drop user

become user - allows grantee to act as any other user

Default users and passwords

col user_name format a30
col pwd_verifier format a20

SELECT *
FROM default_pwd$
ORDER BY 1;

Changing 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

Changing SYSMAN Password

1.0 Stop the standalone dbconsole
on Unix
$ 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
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
$ 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

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;

Other user creation defaults

set linesize 121

SELECT username, initial_rsrc_consumer_group, editions_enabled
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

1. Set the initSID.ora parameters:

remote_os_authent=TRUE
os_authent_prefix = "OPS$"

2. Generate a new spfile

CREATE spfile FROM pfile='initorabase.ora';

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

2 - Create a user in Windows named USER.

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.

Note:

Automatic logins by PC, Apple MacIntosh, and OS/2 users 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 strongly recommends you disable the ops$ logins in the listener.ora.

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.

DBA

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.

ORA_DBA

Alter User

Change The Password

ALTER USER <user_name>
IDENTIFIED BY <new_password>;

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

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

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;

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

Proxy Clause

Grant Proxy with Password

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

ALTER USER app_user GRANT CONNECT THROUGH uwweb
AUTHENTICATED USING PASSWORD;

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 Proxy User

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

ALTER USER app_user REVOKE CONNECT THROUGH uwweb;

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;

User Related Queries

View Memory Use for Each User Session

SELECT username, 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';

Active Connected Users

SELECT COUNT(*) "ACTIVE USERS"
FROM v_$session
WHERE username IS NOT NULL;

Currently Connected Users

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 s.username;

User Information

set linesize 121
col username format a10
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 Current Session

SELECT user, osuser
FROM gv_$session
WHERE sid = (
  SELECT sid
  FROM gv$mystat
   WHERE rownum = 1);

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;

User Information

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;

Connection Information

set linesize 121

SELECT sid, authentication_type, osuser, network_service_banner
FROM gv_$session_connect_info;

Privileged Users

SELECT * FROM gv$pwfile_users;

/* However, for that to be meaningful, you must be using a password file
to authenticate privileged users. You could instead choose to use O/S
authentication, in which case it's membership of the relevant O/S group
that confers 'super user' status on a person (and I've no idea how to code a procedure that would query group memberships for ORA_DBA group (Windows) or dba group (Unix)), if it were actually possible in the first place.

What's more, the check of gv$pwdfile_users is only relevant if you're
using an exclusive password file (ie, remote_login_passwordfile in the
init.ora is set to EXCLUSIVE). If it is instead set to SHARED, then SYS
is, and can be, the only privileged user. You then typically let the
relevant people know what SYS's password is... and there really isn't a
stored procedure which can determine what you happen to have mentioned to assorted members of the DBA team.
*/

Related Topics

-----