Tuesday 4 November 2014

Predefined Roles for Oracle 12C


When we run standard scripts that are part of database creation, Oracle 12C automatically defines few roles which are listed below

Predefined Roles for Oracle 12C

ADM_PARALLEL_EXECUTE_TASK
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
AUDIT_ADMIN
AUDIT_VIEWER
AUTHENTICATEDUSER
CAPTURE_ADMIN
CDB_DBA, CONNECT
CSW_USR_ROLE
CTXAPP
CWM_USER
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
DBA
DBFS_ROLE
DELETE_CATALOG_ROLE
EJBCLIENT
EM_EXPRESS_ALL
EM_EXRESS_BASIC
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
GLOBAL_AQ_USER_ROLE
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_ROLE
HS_ADMIN_SELECT_ROLE
IMP_FULL_DATABASE
JAVADEBUGPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVAUSERPRIV
JAVA_ADMIN
JAVA_DEPLOY
JMXSERVER
LBAC_DBA
LOGSTDBY_ADMINISTRATOR
OEM_ADVISOR
OEM_MONITOR
OLAP_DBA
OLAP_USER
OLAP_XS_ADMIN
OPTIMIZER_PROCESSING_RATE
ORDADMIN
PDB_DBA
PROVISIONER
RECOVERY_CATALOG_OWNER
RESOURCE
SCHEDULER_ADMIN
SELECT_CATALOG_ROLE
SPATIAL_CSW_ADMIN
SPATIAL_WFS_ADMIN
WFS_USR_ROLE
WM_ADMIN_ROLE
XDBADMIN
XDB_SET_INVOKER
XDB_WEBSERVICES
XDB_WEBSERVICES_OVER_HTTP
XDB_WEBSERVICES_WITH_PUBLIC
XS_CACHE_ADMIN
XS_NSATTR_ADMIN
XS_RESOURCE
XS_SESSION_ADMIN

SYS user table USER$ in Oracle Database

SYS.USER$ Internal table in Oracle Database

# PTIME provides the date the password was last changed.
# LCOUNT provides the number of failed logins.
# CTIME provides the date the user was created.
# LTIME provides the date the user was last locked.

In the case of LTIME column the value will not be set to null if the user will be unlocked.

Below is an example of how to query user$ table :-

select ctime,ltime,ptime from user$ where name = 'SCOTT';

In Oracle database 12C the SELECT ANY DICTIONARY Privilege does not allows users to access some SYS Data Dictionary Tables like for example the sys schema tables USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS, DEFAULT_PWD$, ENC$, LINK$, and USER$. So thats a new enhancement for 12C security, there are lots of them and i will be publishing them too soon on this blog on Label Oracle Security.