martes, 15 de febrero de 2011

usuarios que se crean al instalar una base de datos oracle

'SYSTEM','SYS','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','EXFSYS','CTXSYS','XDB','ANONYMOUS','XS$NULL',
'ORDPLUGINS','ORDDATA','MDSYS','ORDSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MDDATA','SPATIAL_WFS_ADMIN_USR',
'SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW','FLOWS_FILES','APEX_PUBLIC_USER','APEX_030200','OWBSYS','OWBSYS_AUDIT','SCOTT'
,'XDB','TRACESVR ','TSMSYS'

--para sacar los usuarios que no son de oracle
select username, account_status, lock_date, expiry_date, created from dba_users
where username not in ('SYSTEM','SYS','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','EXFSYS','CTXSYS','XDB','ANONYMOUS','XS$NULL',
'ORDPLUGINS','ORDDATA','MDSYS','ORDSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MDDATA','SPATIAL_WFS_ADMIN_USR',
'SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW','FLOWS_FILES','APEX_PUBLIC_USER','APEX_030200','OWBSYS','OWBSYS_AUDIT','SCOTT'
,'XDB','TRACESVR ','TSMSYS');

Nota original
http://www.orafaq.com/wiki/List_of_default_database_users

Oracle creates a number of default database users or schemas when a new database is created. Below are a few of them:

User ↓ Password ↓ Purpose ↓ Created by ↓ Can change password? ↓ Can be dropped ↓
SYS CHANGE_ON_INSTALL or INTERNAL Oracle Data Dictionary/ Catalog ?/rdbms/admin/sql.bsq and various cat*.sql scripts Yes No
SYSTEM MANAGER The default DBA user name (please do not use SYS) ?/rdbms/admin/sql.bsq Yes No
OUTLN OUTLN Stored outlines for optimizer plan stability ?/rdbms/admin/sql.bsq Yes No
SCOTT
ADAMS
JONES
CLARK
BLAKE
TIGER
WOOD
STEEL
CLOTH
PAPER
Training/ demonstration users containing the popular EMP and DEPT tables ?/rdbms/admin/utlsampl.sql Yes Yes - Drop users cascade from all production environments
HR (Human Resources)
OE (Order Entry)
SH (Sales History)
HR
OE
SH
Training/ demonstration users containing the popular EMPLOYEES and DEPARTMENTS tables ?/demo/schema/mksample.sql Yes Yes - Drop users cascade from all production environments
DEMO DEMO User for Oracle Data Browser Demonstration (last version 9.2) ?/rdbms/admin/demo.sql Yes Yes - drop user cascade
ANONYMOUS invalid password Used by the PL/SQL gateway that enables a Web browser to invoke a PL/SQL stored procedure through an HTTP listener. ?/rdbms/admin/catqm.sql
Yes - drop user if XDB is not used through a Web browser
AURORA$ORB$UNAUTHENTICATED INVALID Used for users who do not authenticate in Aurora/ORB ?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql

AWR_STAGE AWR_STAGE Used to load data into the AWR from a dump file ?/rdbms/admin/awrload.sql Yes Yes - must be dropped in order to use awrload.sql script
CSMIG
User for Database Character Set Migration Utility ?/rdbms/admin/csminst.sql Password is given at script call Yes - drop user cascade (user is dropped and recreated by the script each time it is used)
CTXSYS CTXSYS Oracle interMedia (ConText Cartridge) administrator user ?/ctx/admin/dr0csys.sql

DBSNMP DBSNMP Oracle Intelligent agent ?/rdbms/admin/catsnmp.sql, called from catalog.sql Yes - put the new password in snmp_rw.ora file Yes - Only if you do not use the Intelligent Agents
DIP DIP Generic user account DIP for processing events propagated by DIP. This account would be used by all applications using the DIP provisioning service when connecting to the database ?/rdbms/admin/catdip.sql, called from catproc.sql Yes - using "oidprovtool" tool Yes - using "dbca" if DIP (Directory Integration and Provisioning) is no more used
DMSYS DMSYS Data Mining user ?/rdbms/admin/odmcrt.sql, called from dminst.sql
Yes - drop user cascade
DSSYS DSSYS Oracle Dynamic Services and Syndication Server ?/ds/sql/dssys_init.sql

EXFSYS
User to hold the dictionary, APIs for the Expression Filter ?/rdbms/admin/exfsys.sql, called from catexf.sql from catrul.sql from catproc.sql Yes - password given at script execution Yes - using using catnoexf.sql script
LBACSYS LBACSYS Label Based Access Control owner when Oracle Label Security (OLS) option is used ?/rdbms/admin/catlbacs.sql, called from catols.sql
Yes - using catnools.sql of OLS is no more used
MDSYS MDSYS Oracle Spatial administrator user ?/ord/admin/ordinst.sql

ORACLE_OCM ORACLE_OCM Owner of packages used by Oracle Configuration Manager ?/rdbms/admin/catocm.sql, called from dbmsocm.sql, called from catproc.sql Yes - account is created locked and password expired
ORDPLUGINS ORDPLUGINS Object Relational Data (ORD) User used by Time Series, etc. ?/ord/admin/ordinst.sql

ORDSYS ORDSYS Object Relational Data (ORD) User used by Time Series, etc. ?/ord/admin/ordinst.sql

PERFSTAT PERFSTAT Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT ?/rdbms/admin/statscre.sql

TRACESVR TRACE Oracle Trace server ?/rdbms/admin/otrcsvr.sql

TSMSYS TSMSYS User for Transparent Session Migration (TSM) a Grid feature ?/rdbms/admin/cattsm.sql, called from catproc.sql
Yes - drop user cascade
XDB
Owner of objects for XDB system ?/rdbms/admin/catqm.sql
Yes - using catnoqm.sql script if XDB is no more used

Remember to change the passwords for the SYS and SYSTEM users immediately after installation!

Except for the user SYS and users having SYSAUX as default tablespace, there should be no problem altering these users to use a different default and temporary tablespace. For user with SYSAUX default tablespace, query v$sysaux_occupants to know the procedure to change the tablespace and move the objects in another tablespace.