Oracle Created (Default) Database Users
by Jeff Hunter, Sr. Database Administrator
Overview
During database creation, Oracle creates several default database users or schemas. This article attempts to
provide some insight and explain each of these default database users/schemas.
Oracle User Account Details
Default Users
Default
Username Account Description
Password
All of the base tables and views for the database's data dictionary are
stored in the schema SYS. These base tables and views are critical for
the operation of Oracle. To maintain the integrity of the data dictionary,
tables in the SYS schema are manipulated only by Oracle; they should
SYS change_on_install never be modified by any user or database administrator, and no one
should create any tables in the schema of the user SYS.
The DBA should change the password for SYS immediately after
database creation!!!
The SYSTEM username creates additional tables and views that display
administrative information, and internal tables and views used by
Oracle tools. Never create in the SYSTEM schema tables of interest to
individual users.
SYSTEM is a little bit "weaker" user than SYS, for example, it has no
access to so called X$ tables (the very internal structure tables of
Oracle).
SYSTEM manager
Although in real life you may be in a situation when some product or
whatever you want to create objects in above mentioned user's schemas.
Be flexible, don't sacriface a product only because it will create some
objects in SYS or SYSTEM schema
The DBA should change the password for SYSTEM immediately after
database creation!!!
DBSNMP dbsnmp Supports Oracle SNMP (Simple Network Management Protocol).
The Oracle Intelligent Agent requires a database logon for each SID
that it manages. By default this account is called "DBSNMP" and the
password is "DBSNMP". The account name and/or password SHOULD
be changed from the default but you will need to make a few additional
modifications. In the examples below, you will need to replace any
information with brackets < > with the information from your system.
1. Remove all Jobs and Events currently registered against this
database.
2. Stop the Intelligent Agent
Oracle7 - Oracle8i
% lsnrctl dbsnmp_stop
Oracle9i
% agentctl stop
3. Edit the $ORACLE_HOME/network/admin/snmp_rw.ora file.
Add the following parameter:
[Link].<connect_string>.NAME=<username>
[Link].<connect_string>.PASSWORD=<passwor
d>
The variable <connect_string> is the exact listing of the
database name as it appears in the snmp_ro.ora file.
If <username> is the default (DBSNMP), there is no need to
specify the user here. Only the password is required.
On UNIX, set the following permission on the
"SNMP_RW.ORA" file:
% chmod 600 snmp_rw.ora
4. Change the DBSNMP password on the database. You can use
either Security Manager, Sqlplus, or Server Manager. If you
use SQLPlus or Server Manager, you can issue the following
command:
SQL> alter user "dbsnmp" identified by "<newpassword>";
5. Stop and restart the Intelligent Agent.
Oracle8i adds the OUTLN user schema to support Plan Stability. The
OUTLN user acts as a place to centrally manage metadata associated
with stored outlines.
This user has DBA role. It is used for plan stability ie. to keep the same
OUTLN outln execution plans for the same queries even if your system configuration
or statistics changes. Execution plans will be the same in different
Oracle releases with different optimizers.
The DBA should either lock the user account or change the password
for the OUTLN user immediately after database creation!!!
MDSYS mdsys Supports Oracle Spatial. Oracle Spatial is an integrated set of functions
and procedures that enables spatial data to be stored, accessed, and
analyzed quickly and efficiently in an Oracle8i database.
[..] The spatial attribute of a spatial feature is the geometric
representation of its shape in some coordinate space. This is referred to
as its geometry.
The DBA should either lock the user account or change the password
for the MDSYS user immediately after database creation!!!
Supports Oracle8i Time Series. Oracle8i Time Series (in previous
releases called the Oracle8 Time Series Cartridge) is an extension to
Oracle8i that provides storage and retrieval of timestamped data
through object types. Oracle8i Time Series is a building block for
applications rather than being an end-user application in itself. It
ORDSYS ordsys consists of data types along with related functions for managing and
processing time series data.
The DBA should either lock the user account or change the password
for the ORDSYS user immediately after database creation!!!
Supports Oracle interMedia. Oracle interMedia is a single product that
enables Oracle8i to store, manage, and retrieve text, documents,
geographic location information, images, audio, and video in an
integrated fashion with other enterprise information. Oracle interMedia
extends Oracle8i reliability, availability, and data management to text
ORDPLUGINS ordplugins and multimedia content in Internet, electronic commerce, and media-
rich applications as well as online Internet-based geocoding services for
locator applications.
The DBA should either lock the user account or change the password
for the ORDPLUGINS user immediately after database creation!!!
Supports Oracle ConText Cartridge. Oracle8 ConText Cartridge
provides powerful search, retrieval, and viewing capabilities for text
stored in an Oracle8 database. In addition, ConText provides advanced
CTXSYS ctxsys linguistic processing of English-language text.
The DBA should either lock the user account or change the password
for the CTXSYS user immediately after database creation!!!
Dynamic Services Secured Web Service. Dynamic Services Engine
(DS Engine) allows creation, aggregation and deployment of services
from a variety of content sources. At the moment, Dynamic Services
supports content access from databases (SQL/PLSQL) as well as
Internet applications (HTTP/HTTPS). DS Engine can interpret XML
and HTML content along with the result sets returned from database
DSSYS dssys access. DS Engine is integrated with Oracle Portal via a Web Provider
mechanism. This integration allows all the services registered with DS
Engine to be accessible as portlets.
The DBA should either lock the user account or change the password
for the DSSYS user immediately after database creation!!!
Oracle Statistics Package (STATSPACK) user that supersedes
UTLBSTAT/UTLESTAT. The PERFSTAT user will hold all of the
PERFSTAT perfstat tables and packages for the performance diagnostic tool STATSPACK.
Created By: $ORACLE_HOME/rdbms/admin/[Link]
Used to support Oracle's Ultrasearch option. This feature (and user) was
introduced in Oracle9i. The user account IS NOT locked by default is
only assigned the "CREATE SESSION" privilege. None the less, this
WKPROXY change_on_install account is not locked by default and Oracle highly recommends that
this default password be changed.
Created By: $ORACLE_HOME/ultrasearch/admin/[Link]
WKSYS change_on_install Used to support Oracle's Ultrasearch option. This feature (and user) was
introduced in Oracle9i. The user account IS NOT locked by default and
as you can see below, is granted the highly privileged role of DBA.
Given that this user is granted the DBA role and is not locked by
default, Oracle highly recommends that this default password be
changed.
This support account is assigned the following privileges in Oracle9i:
CONNECT
RESOURCE
DBA
ALL PRIVILEGES
CTXAPP
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE ANY VIEW
DROP ANY VIEW
CREATE ANY TABLE
DROP ANY TABLE
CREATE ANY INDEX
DROP ANY INDEX
CREATE ANY SEQUENCE
DROP ANY SEQUENCE
CREATE ANY TRIGGER
DROP ANY TRIGGER
JAVAUSERPRIV
JAVASYSPRIV
SELECT ON [Link]$
SELECT ON SYS.V_$PARAMETER
SELECT ON SYS.GV_$INSTANCE
SELECT ON SYS.V_$DATABASE
SELECT ON SYS.DBA_CONSTRAINTS
SELECT ON SYS.DBA_JOBS
SELECT ON SYS.DBA_DB_LINKS
SELECT ON SYS.DBA_ROLE_PRIVS
SELECT ON SYS.DBA_LOCK
SELECT ON SYS.DBMS_LOCK_ALLOCATED
SELECT ON [Link]$
SELECT ON SYS.DBA_TABLES
SELECT ON SYS.DBA_VIEWS
SELECT ON SYS.DBA_TAB_COLUMNS
EXECUTE ON SYS.DBMS_LOCK
EXECUTE ON SYS.DBMS_PIPE
EXECUTE ON SYS.DBMS_REGISTRY
The default tablespace for this user will be "DRSYS" while its
temporary tablespace will be "TEMP".
Created By: $ORACLE_HOME/ultrasearch/admin/[Link]
Used to store all the metadata information for Oracle Workspace
Manager. This user was introduced in Oracle9i and (like most Oracle9i
supporting accounts) is locked by default. The user account is locked
because we want the password to be public but restrict access to the
WMSYS wmsys account to the SYS schema. So, to unlock the account, DBA privileges
are required.
Created By: $ORACLE_HOME/rdbms/admin/[Link]
Used to support SQL XML management: XML DB. This user is
granted two roles: "RESOURCE" and "JAVAUSERPRIV". Oracle
recommends changing the password for this user after creation. This
XDB change_on_install user is configured with a default tablespace of "XDB" and a temporary
tablespace of "TEMP".
Created By: $ORACLE_HOME/rdbms/admin/[Link]
Used to support SQL XML management: XML DB. Allows HTTP
...IDENTIFIED access to Oracle XML DB. This user should only be used for HTTP
ANONYMOUS BY VALUES logins. The account is locked near the end of the [Link] script.
'anonymous'
Created By: $ORACLE_HOME/rdbms/admin/[Link]
Used to support Oracle Data Mining. In Oracle9i, this user is granted
the roles: "SELECT_CATALOG_ROLE", "HS_ADMIN_ROLE",
"AQ_USER_ROLE". Oracle recommends changing the default
password as the account IS NOT locked after creation. The default
ODM odm tablespace for this user is "ODM" with temporary tablespace "TEMP".
The "ODM" tablespace is populated with segments from users ODM
and ODM_MTR.
Created By: $ORACLE_HOME/dm/admin/[Link]
Used to support Oracle Data Mining. In Oracle9i, this user is granted
"SELECT_CATALOG_ROLE" and "HS_ADMIN_ROLE". Oracle
recommends changing the default password as the account IS NOT
locked after creation. The default tablespace for this user is "ODM"
ODM_MTR mtrpw with temporary tablespace "TEMP". The "ODM" tablespace is
populated with segments from users ODM and ODM_MTR.
Created By: $ORACLE_HOME/dm/admin/[Link]
OLAPSYS mtrpw This user is create if OLAP option is installed and is used to create
OLAP metadata structures. In Oracle9i, this user is granted
"SELECT_CATALOG_ROLE" and "HS_ADMIN_ROLE". Oracle
recommends changing the default password. The default tablespace for
this user is "ODM" with temporary tablespace "TEMP". The "ODM"
tablespace is populated with segments from users ODM and
ODM_MTR.
Created By: $ORACLE_HOME/dm/admin/[Link]
Oracle Trace server. Supports Oracle Trace for OEM in Oracle7.
Oracle Trace is used to collect a wide variety of data, such as
performance statistics, diagnostic data, system resource usage, and
TRACESVR trace business transaction details.
This user was last used in Oracle7 and can be dropped from databases
using Oracle8 and higher.
Replication user. This user is manually created by the DBA using
Managed by DBA CREATE USER... This user is also created in the scripts:
REPADMIN when user is $ORACLE_HOME/ldap/admin/[Link] and
created. $ORACLE_HOME/ldap/admin/[Link]. Oracle recommends
changing the default password if automatically created.
JSERV Accounts
The three JSERV accounts (AURORA$JIS$UTILITY$,
AURORA$ORB$UNAUTHENTICATED and OSE$HTTP$ADMIN) are used
internally by Enterprise Java Beans and CORBA Tools and created with randomly-
generated passwords 'INVALID_ENCRYPTED_PASSWORD'.
These 3 scripts are launched by init_jis.sql script to install the Oracle Servlet Engine
(OSE).
Changing their passwords would prevent the ORB from working. This is supposed to
change in a future version so that you can change their password.
Username Default Password Account Description
Description: Create
the public user for the
Aurora/ORB. This is
the identity any non-
validated ORB client
will run as. This is the
AURORA$ORB$UNAUTHENTICATED <Random> user for users who
don't authenticate in
the Aurora/ORB
Created
By: [Link]
AURORA$JIS$UTILITY$ <Random> Description: Create
the public user for the
Aurora/ORB. This is
the identity any non-
validated ORB client
will run as. This is the
user for users who
don't authenticate in
the Aurora/ORB
Created
By: [Link]
Description: Create
the public user for the
Aurora/ORB. This is
the identity any non-
validated ORB client
will run as. This is the
OSE$HTTP$ADMIN <Random> user for users who
don't authenticate in
the Aurora/ORB
Created
By: [Link]
Sample Schemas
Username Default Password Account Description
Well known and often referenced sample
schema. Everyone should know about the
magical emp and dept tables. VERY MANY
examples in Oracle docs and not only are based
SCOTT tiger on this schema so You should know it!
The user should be dropped in all production
databases.
ADAMS
JONES
CLARK
BLAKE
Oracle9i Sample Schemas
The Oracle9i Sample Schemas provides installed schemas meant to be used for
demonstration purposes only.
Username Default Password Account Details
Human Resources schema. The Human
HR hr Resources division tracks information on the
company's employees and facilities.
Order Entry schema requires "Oracle Spatial"
option. The Order Entry division tracks
OE oe
product inventories and sales of the
company's products through various channels.
Product Media schema requires "Oracle
JVM" and "Oracle Intermedia" options. The
PM pm Product Media division maintains
descriptions and detailed information on each
product sold by the company.
SH sh Sales History schema requires "Oracle OLAP
Services" set up. The Sales History division
tracks business statistics to facilitate business
decisions.
Queued Shipping schema The Shipping
division manages the shipping of products to
QS qs customer. The sample company has decided
to test the use of messaging to manage its
proposed B2B applications.
QS_ES qs_es (Eastern Shipping)
QS_WS qs_ws (Western Shipping)
QS_OS qs_os (Overseas Shipping)
QS_CB qs_cb (Customer Billing)
QS_CS qs_cs (Customer Service)
QS_ADM qs_adm (Administration)
QS_CBADM qs_cbadm (Customer Billing Administration)