Oracle 12c新特性:ORACLE自动维护的Schema或默认创建的USER
程序员文章站
2024-03-14 13:41:58
...
ORACLE 12c有些小特性非常的实用,如Oracle 12c New Feature: Last Login Time for Non-Sys Users,可以列出非SYS用户的最后登录时间,该数据可以做为清理用户里的依据,同时前段时间应对安全检查, 数据库中扫出了一些弱口令,需要清理一部分长期不登录的用户或找到对应的责任人才可以修改密码,以评估修改修改对系统的影响。 如果找出哪些用户是ORACLE 系统用户在12C之前还是相对麻烦一些,因为我们可能知道像sys, system这些系统默认创建的用户,其它如果安装时选用较多的DB option时,往往不容易查找自动在创建数据库里脚本中创建的哪些用户。
在12c 中dba_user 字典视图引入了ORACLE_MAINTAINED 字段, 当数据库安装options时,oracle自己创建的用户会赋值为”Y”,我们自己创建的用户默认为”N”, 所以像之前的需求在12c中就变的简单,即可以使用LAST_LOGIN判断数据库的最后一次登录,又可以使用ORACLE_MAINTAINED =’N’ 查找我们创建的用户。
同时也可以查出这些schema是否可以exp,expdp,logical standby… , 下面是在我的12.2 CDB测试环境中查询结果
SQL> SELECT created,
username,
oracle_maintained,
common,
no_exp,
no_expdp,
no_sby,
default_password,
sysaux,
occupant_desc
FROM dba_users
LEFT OUTER JOIN (SELECT DISTINCT name username, 'Y' no_expdp
FROM sys.ku_noexp_tab
WHERE obj_type = 'SCHEMA')
USING (username)
LEFT OUTER JOIN
(SELECT DISTINCT name username, 'Y' no_exp FROM sys.exu8usr)
USING (username)
LEFT OUTER JOIN (SELECT DISTINCT name username, 'Y' no_sby
FROM SYSTEM.logstdby$skip_support
WHERE action IN (0, -1))
USING (username)
LEFT OUTER JOIN
(SELECT DISTINCT user_name username, 'Y' default_password
FROM sys.default_pwd$)
USING (username)
LEFT OUTER JOIN
( SELECT schema_name username,
'Y' sysaux,
DECODE (COUNT (*), 1, MIN (occupant_desc)) occupant_desc
FROM v$sysaux_occupants
GROUP BY schema_name)
USING (username)
ORDER BY created, username;
CREATED | USER | ORACLE MAINTAINED |
COM- MON |
NO EXP |
NO EXPDP |
NO SBY |
DEF PWD |
SYS AUX |
occupant_desc |
---|---|---|---|---|---|---|---|---|---|
2016-12-09 20:43:28 | SYS | Y | YES | Y | Y | Y | Y | Y | |
2016-12-09 20:43:29 | AUDSYS | Y | YES | Y | Y | Y | Y | Y | AUDSYS schema objects |
2016-12-09 20:43:29 | SYSBACKUP | Y | YES | Y | Y | Y | |||
2016-12-09 20:43:29 | SYSDG | Y | YES | Y | Y | Y | |||
2016-12-09 20:43:29 | SYSKM | Y | YES | Y | Y | Y | |||
2016-12-09 20:43:29 | SYSRAC | Y | YES | Y | Y | Y | |||
2016-12-09 20:43:29 | SYSTEM | Y | YES | Y | Y | Y | Y | ||
2016-12-09 20:43:37 | OUTLN | Y | YES | Y | Y | Y | |||
2016-12-09 20:52:01 | GSMADMIN_INTERNAL | Y | YES | Y | Y | Y | |||
2016-12-09 20:52:02 | GSMUSER | Y | YES | Y | Y | Y | |||
2016-12-09 20:52:34 | DIP | Y | YES | Y | Y | Y | |||
2016-12-09 20:53:38 | XS$NULL | Y | YES | Y | Y | Y | |||
2016-12-09 20:53:57 | REMOTE_SCHEDULER_AGENT | Y | YES | Y | Y | Y | Y | ||
2016-12-09 20:53:58 | DBSFWUSER | Y | YES | Y | Y | Y | Y | ||
2016-12-09 20:55:20 | ORACLE_OCM | Y | YES | Y | Y | Y | |||
2016-12-09 21:00:10 | SYS$UMF | Y | YES | Y | Y | Y | Y | ||
2016-12-09 21:04:32 | DBSNMP | Y | YES | Y | Y | Y | Y | Enterprise Manager Monitoring User | |
2016-12-09 21:04:34 | APPQOSSYS | Y | YES | Y | Y | Y | |||
2016-12-09 21:05:25 | GSMCATUSER | Y | YES | Y | Y | Y | |||
2016-12-09 21:05:30 | GGSYS | Y | YES | Y | Y | Y | |||
2016-12-09 21:08:03 | ANONYMOUS | Y | YES | Y | Y | Y | |||
2016-12-09 21:08:03 | XDB | Y | YES | Y | Y | Y | Y | XDB | |
2016-12-09 21:24:12 | WMSYS | Y | YES | Y | Y | Y | Y | Workspace Manager | |
2016-12-09 21:26:42 | OJVMSYS | Y | YES | Y | Y | Y | |||
2016-12-09 21:30:28 | CTXSYS | Y | YES | Y | Y | Y | Y | Oracle Text | |
2016-12-09 21:31:47 | ORDSYS | Y | YES | Y | Y | Y | Y | Oracle Multimedia ORDSYS Components | |
2016-12-09 21:31:48 | MDSYS | Y | YES | Y | Y | Y | Y | Oracle Spatial | |
2016-12-09 21:31:48 | ORDDATA | Y | YES | Y | Y | Y | Y | Oracle Multimedia ORDDATA Components | |
2016-12-09 21:31:48 | ORDPLUGINS | Y | YES | Y | Y | Y | Y | Oracle Multimedia ORDPLUGINS Components | |
2016-12-09 21:31:48 | SI_INFORMTN_SCHEMA | Y | YES | Y | Y | Y | Y | Oracle Multimedia SI_INFORMTN_SCHEMA Components | |
2016-12-09 21:44:53 | OLAPSYS | Y | YES | Y | Y | Y | Y | OLAP Catalog | |
2016-12-09 21:45:32 | MDDATA | Y | YES | Y | Y | Y | |||
2016-12-09 21:48:26 | SPATIAL_CSW_ADMIN_USR | Y | YES | Y | Y | Y | |||
2016-12-09 21:55:40 | LBACSYS | Y | YES | Y | Y | Y | |||
2016-12-09 21:55:59 | DVF | Y | YES | Y | Y | Y | |||
2016-12-09 21:55:59 | DVSYS | Y | YES | Y | Y | Y | |||
2017-04-11 17:07:21 | C##ANBOB | N | YES | Y |
别外也整理了一些在12c之前的版本中可能会创建的SCHEMA及用途, 如下:
User | Password | Purpose | Created by |
SYS | CHANGE_ON_INSTALL or INTERNAL | Oracle Data Dictionary/ Catalog | ?/rdbms/admin/sql.bsq and various cat*.sql scripts |
SYSTEM | MANAGER | The default DBA user name (please do not use SYS) | ?/rdbms/admin/sql.bsq |
OUTLN | OUTLN | Stored outlines for optimizer plan stability | ?/rdbms/admin/sql.bsq |
SCOTT | TIGER | Training/ demonstration users containing the popular EMP and DEPT tables | ?/rdbms/admin/utlsampl.sql |
ADAMS | WOOD | ||
JONES | STEEL | ||
CLARK | CLOTH | ||
BLAKE | PAPER | ||
HR (Human Resources) | HR | Training/ demonstration users containing the popular EMPLOYEES and DEPARTMENTS tables | ?/demo/schema/mksample.sql |
OE (Order Entry) | OE | ||
SH (Sales History) | SH | ||
DEMO | DEMO | User for Oracle Data Browser Demonstration (last version 9.2) | ?/rdbms/admin/demo.sql |
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 |
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 |
CSMIG | User for Database Character Set Migration Utility | ?/rdbms/admin/csminst.sql | |
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 |
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 |
DMSYS | DMSYS | Data Mining user | ?/rdbms/admin/odmcrt.sql, called from dminst.sql |
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 | |
LBACSYS | LBACSYS | Label Based Access Control owner when Oracle Label Security (OLS) option is used | ?/rdbms/admin/catlbacs.sql, called from catols.sql |
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 |
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 |
SI_INFORMTN_SCHEMA | The account that stores the information views for the SQL/MM Still Image Standard. See also ORDPLUGINS and ORDSYS. | ?/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 |
XDB | Owner of objects for XDB system | ?/rdbms/admin/catqm.sql | |
APEX_030200 | Part of the Oracle Application Express Suite – (Oracle APEX, previously named Oracle HTML DB) which is a freeware software development environment. It allows a fast development cycle to be achieved to create web based applications. The account owns the Application Expressschema and metadata. See also APEX_PUBLIC_USER andFLOW_FILES. | ?/apex/apexins.sql | |
APEX_PUBLIC_USER | |||
FLOW_FILES | |||
APPQOSSYS | Used for storing/managing all data and metadata required by Oracle Quality of Service Management. | ?/rdbms/admin/catqos.sql | |
BI | The account that owns the Business Intelligence schema included in the Oracle Sample Schemas. See also HR, OE, SH, IX and PM. | ?/demo/schema/bus_intelligence/bi_main.sql | |
IX | |||
PM | |||
MDDATA | The schema used by Oracle Spatial for storing Geocoder and router data. See also SPATIAL_CSW_ADMIN_USR , SPATIAL_WFS_ADMIN_USR and MDSYS. | ?/md/admin/catmd.sql | |
MGMT_VIEW | An account used by Oracle Enterprise Manager Database Control. Password is randomly generated at installation or database creation time. Users do not need to know this password. | ?/sysman/admin/emdrep/bin/RepManager | |
OLAPSYS | The account that owns the OLAP Catalog (CWMLite). This account has been deprecated, but is retained for backward compatibility. | ?/olap/admin/amdsys.sql | |
ORDDATA | This account contains the Oracle Multimedia DICOM data model. | ?/ord/admin/ordisysc.sql | |
OWBSYS | The account for administrating the Oracle Warehouse Builder repository. Access this account during the installation process to define the base language of the repository and to define Warehouse Builder workspaces and users. A data warehouse is a relational or multidimensional database that is designed for query and analysis. See also OWBSYS_AUDIT. | ?/owb/UnifiedRepos/cat_owb.sql | |
OWBSYS_AUDIT | This account is used by the Warehouse Builder Control Center Agent to access the heterogeneous execution audit tables in the OWBSYS schema. | ?/owb/UnifiedRepos/cat_owb.sql | |
SPATIAL_CSW_ADMIN_USR | The Catalog Services for the Web (CSW) account. It is used by the Oracle Spatial CSW cache manager to load all record type metadata, and record instances from the database into the main memory for the record types that are cached. See also SPATIAL_WFS_ADMIN_USR, MDDATA and MDSYS. | ?/md/admin/sdocswpv.sql | |
SPATIAL_WFS_ADMIN_USR | The Web Feature Service (WFS) account. It is used by the Oracle Spatial WFS cache manager to load all feature type metadata, and feature instances from the database into main memory for the feature types that are cached. See also SPATIAL_CSW_ADMIN_USR , MDDATA and MDSYS. | ?/md/admin/sdowfspv.sql | |
SYSMAN | The account used to perform Oracle Enterprise Manager database administration tasks. The SYS and SYSTEM accounts can also perform these tasks. Password is created at installation or database creation time. | Created as part of the dbconsole or Enterprise Manager build. | |
WMSYS | The account used to store the metadata information for Oracle Workspace Manager. | ?/rdbms/admin/owmctab.plb | |
WKPROXY | 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 is only assigned the “CREATE SESSION” privilege. None the less, this account is not locked by default and Oracle highly recommends that this default password be changed. | ?/ultrasearch/admin/wk0csys.sql |
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. | ?/ultrasearch/admin/wk0install.sql |
X$NULL | An internal account that represents the absence of a user in a session. Because XS$NULL is not a user, this account can only be accessed by the Oracle Database instance. XS$NULL has no privileges and no one can authenticate as XS$NULL, nor can authentication credentials ever be assigned to XS$NULL. | ?/rdbms/admin/sql.bsq |