Oralce 系统用户介绍

Oralce DB 系统用户介绍

Doc, My Note Add comments

二 262012

之前总是会有人向我问到数据库中的某某用户是系统用户还是什么，有什么作用，可不可以删除等之类的问题，今天就结合MOS里的一篇文档简单总结下，与各位分享。

我们通常最熟悉的用户有sys和system，sys是超级用户，通常DBA在进行数据库维护性操作时都是用的此用户；system的权限较sys次之，也是在日常工作中会经常用的。另外还有perfstat用户，perfstat是9I里的statspack组件的用户，hr和scott用户分别是Oracle9i和10g里的sample user。

另外一些其他的用户，见下：

List of users created at database creation

By default, are automatically created during database creation:

SCOTT by script $ORACLE_HOME/rdbms/admin/utlsampl.sql

OUTLN by script $ORACLE_HOME/rdbms/admin/sql.bsq

Optionally:

DBSNMP if Enterprise Manager Intelligent Agent is installed

MGMT_VIEW is part of the DB Control Repository

SYSMAN is part of the DB Control Repository and Grid

TRACESVR if Enterprise Manger is installed

AURORA$ORB$UNAUTHENTICATED \

AURORA$JIS$UTILITY$ – if Oracle Servlet Engine (OSE) is installed

OSE$HTTP$ADMIN /

MDSYS if Oracle Spatial option is installed

MDDATA if Oracle Spatial option is installed

ORDSYS if interMedia Audio option is installed

ORDPLUGINS if interMedia Audio option is installed

SI_INFORMIN_SCHEMA if interMedia option is installed

CTXSYS if Oracle Text option is installed

WKSYS if Oracle Ultra Search option is installed

WKUSER(9i)/WK_TEST(10g) if Oracle Ultra Search option is installed

REPADMIN if Replication Option is installed

LBACSYS if Oracle Label Security option is installed

DVF if Oracle Database Vault option is installed

DVSYS if Oracle Database Vault option is installed

ODM if Oracle Data Mining option is installed

ODM_MTR idem

DMSYS(10g) if Oracle Data Mining 10g option is installed

DMSYS in 10g version to replace ODM and ODM_MTR schemas

OLAPSYS if OLAP option is installed

WMSYS if Oracle workspace Manager script owmctab.plb is executed.

ANONYMOUS if catqm.sql catalog script for SQL XML management is executed

EXFSYS if the Expression Filter Feature repository

DIP for provisioning event processing

TSMSYS Transparent Session Migration

Note

If you have a already the 9i sample schemas(hr,oe,pm,etc.) and you don’t need them,you can removed them safely and without damage to the database.

a) The SCOTT user is created by default to provide sample user tables.You can alter the password.

To create the default tables,run the script

$ORACLE_HOME/sqlplus/demo/demobld.sql.

To drop the objects,run

$ORACLE_HOME/sqlplus/demo/demodrop.sql connected as SCOTT.

b) The OUTLN(OUTLiNes) users is automatically created during installation of Oracle8i and Oracle9i.OUTLN is the schema of Stored Outlines.

The package OUTLN_PKG is used to manage stored outlines and categories.OUTLN is the owner of tables OL$,OL$HINTS(Oracle8i) as well as OL$NODES(Oracle9i) used to store hints for stored outlines.

You can change the password for the OUTLN schema just as for the SYS and SYSTEM schemas.

c) The DBSNMP user can be dropped by running the catnsnmp.sql script.If you need to re-create it,run the catsnmp.sql script.Ensure that there are no jobs running or schedualed in Oracle Enterprise Manager before you run the script.

MGMT_VIEW is part of the DB Control Repository,it is created by running the script $OH/sysman/admin/emdrep/bin/RepManger,the password is autogenerated(it does not have a fixed or default value).

SYSMAN belongs to the same feature(see Note:270510.1 for details):it is the schema of the Grid OMS repository of the LOG Enterprise Manager Grid.

d) The TRACESVR user is created by $ORACLE_HOME/otrace/admin/otrcsvr.sql during the installation of OEM.

If you change the TRACESVR user’s password,you will not be able to do OTrace connections.This user is only used with 7.x Database where stored procedures are used by OTrace to start and stop data collections.The TRACESVR stored procedure mechanism is no longer used to control OTrace collections starting with Oracle 8.x Database.Therefore in this case you can change the password(or drop that user)

e) 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'.

-> jisorb.sql creates user AURORA$ORB$UNAUTHENTICATED.

-> jisbgn.sql creates user AURORA$JIS$UTILITY$

-> jishausr.sql creates user OSE$HTTP$ADMIN

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.

f) MDSYS, MDDATA, ORDSYS, CTXSYS, ORDPLUGINS and SI_INFORMTN_SCHEMA are created to support Oracle Intermedia. The default password for the ORDSYS and SI_INFORMTN_SCHEMA users during automatic installation is 'ORDSYS', and for ORDPLUGINS is 'ORDPLUGINS'.The schema MDDATA is used by Oracle Spatial for storing Geocoder and router data Geocoding is the process of converting tables of address data into standardized address, location, and possibly other data.

g) WKSYS is a privileged Oracle user who owns the Ultra Search data dictionary and internal objects. Ultra Search uses the WKSYS user for storing Ultra Search packages and metadata. WKUSER (9i)/WK_TEST (10g) is a test user.

h) REPADMIN user: it is usual to have a separate user for the replication administrator to protect master groups from being managed by snapshot administrators.This user configures the replicated environment and performs administration of all replicated schemas / groups.

i) LBACSYS user: the Oracle Label Security administrator username.

j) DVSYS schema stores the database objects needed to process Oracle data for Oracle Database Vault. This schema contains the roles, views, accounts, functions, and other database objects that Oracle Database Vault uses.

k) The DVF schema contains public functions to retrieve (at run time) the factor values set in the Oracle Database Vault access control configuration.

l) ODM user: who performs data mining operations. In 10g, a user can be created with a chosen name.

m) ODM_MTR user: the account associated with the data repository for data mining sample programs.

In 10g, DMSYS is the schema for the data repository.

n) OLAPSYS user: identity used to create OLAP metadata structures.

o) WMSYS user: used to store all the metadata information for Oracle Workspace Manager.

p) ANONYMOUS user: allows HTTP access to Oracle XML DB.

q) XDB user: Used for storing Oracle XML DB data and metadata.

r) EXFSYS is the Expression Filter Feature repository (see Note:258618.1): the user is created with the script exfsys.sql that asks for a password.

s) DIP is created in rdbms/admin/catdip.sql (password is DIP): for provisioning event processing

t) TSMSYS is created in rdbms/admin/cattsm.sql for Transparent Session Migration

u) The Oracle9i Sample Schemas provides installed schemas meant to be used for demonstration purposes only:

-> HR: Human Resources schema The Human Resources division tracks information on the company's employees and facilities.

-> OE: Order Entry schema requires "Oracle Spatial" option. The Order Entry division tracks product inventories and sales of the company?s products through various channels.

-> PM: Product Media schema requires "Oracle JVM" and "Oracle Intermedia" options.The Product Media division maintains descriptions and detailed information on each product sold by the company.

-> SH: Sales History schema requires "Oracle OLAP" set up. The Sales History division tracks business statistics to facilitate business decisions.

-> QS: Queued Shipping schema The Shipping division manages the shipping of products to customer.The sample company has decided to test the use of messaging to manage its proposed B2B applications.

-> QS_ES (Eastern Shipping)

-> QS_WS (Western Shipping)

-> QS_OS (Overseas Shipping)

-> QS_CB (Customer Billing)

-> QS_CS (Customer Service)

-> QS_ADM (Administration)

-> QS_CBADM (Customer Billing Administration)

If any of the user accounts is locked and expired upon installation and needs to be activated, unlock and assign a new meaningful password to that user account:

SQL> ALTER USER "schema_name" IDENTIFIED BY "newpass" ACCOUNT UNLOCK;

资料文档：Oracle Created Database Users: Password, Usage and Files References [ID 160861.1]