Oracle User Schema Owner

Oracle Users

Version 10.2

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

User Name	Default Password	Description
sys	change_on_install	Database administration tasks
system	manager	Database administration tasks
anonmous	?	?
bi	?	Business Intelligence
ctxsys	ctxsys	Oracle Text
dbsnmp	dbsnmp	Management Agent component of Oracle Enterprise Manager to monitor and manage the database
dip	?	?
dmsys	dmsys	Data Mining
dssys	dssys	Oracle Dynamic Services and Syndication Server
dump4	?	?
exfsys	?	?
flow_010500	?	Workflow
flow_files	?	Workflow
hr	hr	Human Resources
htmldb_public_user	?	HTML_DB
ix	ix	?
lbacsys	lbacsys	Label Security administrator
mddata	mddata	Spatial for storing Geocoder & router data
mdsys	mdsys	Spatial and interMedia Locator administrator account
mgmt_view	?	?
oe	oe	Order Entry
olapsys	manager	OLAP metadata structures. owns the OLAP Catalog (CWMLite)
ordplugins	ordplugins	Oracle and third-party intermedia plug-ins
ordsys	ordsys	interMedia administrator account
outln	outln	Plan stability: A role to centrally manage metadata associated with stored outlines
owb	?	Oracle Warehouse Builder
perfstat	perfstat	Statistics Package (STATSPACK)
pm	pm	?
scott	tiger	Demo schema
sh	sh	Sales History
si_informtn_schema	si_informtn_schema	Stores the information views for the SQL/MM Still Image Standard
sysman	change_on_install	Oracle Enterprise Manager database administration tasks
tracesvr	trace	Trace server
tsmsys	?	?
wkproxy	?	?
wksys	?	?
wk_test	?	?
wmsys	?	?
xdb	change_on_install	?

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 uwclass
IDENTIFIED BY uwclass;

Create User with Complex Password

CREATE USER <user_name>
IDENTIFIED BY "<password>"

CREATE USER uwclass
IDENTIFIED BY "N0t!4N0W"

Include Access To A Default Tablespace

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 uwclass
IDENTIFIED BY uwclass
DEFAULT TABLE uwdata;

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 uwclass
IDENTIFIED BY uwclass
DEFAULT TABLE uwdata
TEMPORARY TABLESPACE temp;

Include Quota On Tablespaces

CREATE USER <user_name>
IDENTIFIED BY <password>
DEFAULT TABLESPACE <tablespace_name>
TEMPORARY TABLESPACE <temp_tablespace_name>
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX
QUOTA <quota_amount> ON <tablespace_name>
QUOTA <quota_amount> ON <tablespace_name>
QUOTA <quota_amount> ON <tablespace_name>;

CREATE USER uwclass
IDENTIFIED BY "N0Way!"
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX
QUOTA UNLIMITED ON uwdata
QUOTA 10M ON data_med;

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;

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;

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(v_$session.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