The primary goal in securing Oracle is to stop people connecting to the database. If you can do that and only allow in the exact number of users necessary to do their job to connect. This can be achieved by network controls, Oracle network controls such as validnode checking and even database controls such as login triggers or Database Vault CONNECT command rules... If you then ensure that each account has only the exact rights necessary to do their job and no more then you are on your way to good data security and lock down. You can then lock down the access controls to the data itself and even use context based security such as VPD or OLS, DV, redaction, masking, TSDP and more. This is least privileges.
Then you have some level of initial control. An attack then must take place on one of a small number of general ways:
- The user accounts that are allowed to connect can exploit some other feature of the database or data controls. This means that if you have a valid account and can connect to the database you then need to find a gap in the database settings, data access controls or exploit the fact you didn't really have least rights
- The attacker could exploit a remote vulnerability in the database access protocols (TNS, OLOGON etc) and find a way to access the database without a username or with a username and no password. This could be as simple as guessing a default users password or as extreme as a network stack buffer overflow, or...
- The attacker could find an exploit in the application stack and tunnel to the database. For instance SQL Injection in application code.
- Others, possibly....
So, back to my first two points. We want to stop people connecting, for those that we do allow to connect we limit what they can use (programs), we have strong password controls and we then aim for least rights and finally proper data domains, data access controls and even context based security BUT we are put in a difficult position by many default accounts existing that we maybe don't need and those tens of thousands of PUBLIC grants.
If we could have a database with no default accounts and no PUBLIC grants then we would have a much stronger starting point. Unfortunately we get 44K, 45K in a stock 12.2c, 18c, 19c database.
So as part of this discussion and quest I wanted to look at the SYSTEM user. Most sites I go to the DBAs use SYSDBA to do their daily work or if not they use SYSTEM as the default DBA account. But what does SYSTEM have. First lets connect to my 11.2.0.4 database:
SQL> sho user
USER is "SYS"
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
Now what objects does SYSTEM have:
SQL> select count(*),object_type from dba_objects where owner='SYSTEM' group by object_type;
COUNT(*) OBJECT_TYPE
---------- -------------------
64 INDEX PARTITION
20 SEQUENCE
45 TABLE PARTITION
4 QUEUE
1 PROCEDURE
25 LOB
1 PACKAGE
1 PACKAGE BODY
2 TRIGGER
167 TABLE
253 INDEX
COUNT(*) OBJECT_TYPE
---------- -------------------
8 SYNONYM
14 VIEW
4 FUNCTION
9 TYPE
15 rows selected.
SQL>
What are the main PL/SQL objects owned by SYSTEM:
SQL> col object_name for a30
SQL> col object_type for a30
SQL> l
1* select object_name,object_type from dba_objects where owner='SYSTEM' and object_type in('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')
SQL> /
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
ORA$_SYS_REP_AUTH PROCEDURE
DBMS_REPCAT_AUTH PACKAGE
LOGMNR$TAB_GG_TABF_PUBLIC FUNCTION
LOGMNR$COL_GG_TABF_PUBLIC FUNCTION
LOGMNR$SEQ_GG_TABF_PUBLIC FUNCTION
LOGMNR$KEY_GG_TABF_PUBLIC FUNCTION
DBMS_REPCAT_AUTH PACKAGE BODY
7 rows selected.
SQL>
These are REPCAT and LogMiner objects. Interesting. What about tables:
SQL> select object_name,object_type from dba_objects where owner='SYSTEM' and object_type ='TABLE';
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_SESSION_EVOLVE$ TABLE
LOGMNR_GLOBAL$ TABLE
LOGMNR_GT_TAB_INCLUDE$ TABLE
LOGMNR_GT_USER_INCLUDE$ TABLE
LOGMNR_GT_XID_INCLUDE$ TABLE
LOGMNR_UID$ TABLE
LOGMNRGGC_GTLO TABLE
LOGMNRGGC_GTCS TABLE
LOGMNRC_DBNAME_UID_MAP TABLE
LOGMNR_LOG$ TABLE
LOGMNR_PROCESSED_LOG$ TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_SPILL$ TABLE
LOGMNR_AGE_SPILL$ TABLE
LOGMNR_RESTART_CKPT_TXINFO$ TABLE
LOGMNR_ERROR$ TABLE
LOGMNR_RESTART_CKPT$ TABLE
LOGMNR_INTEGRATED_SPILL$ TABLE
LOGMNR_FILTER$ TABLE
LOGMNR_SESSION_ACTIONS$ TABLE
LOGMNR_PARAMETER$ TABLE
LOGMNR_SESSION$ TABLE
LOGMNRT_MDDL$ TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW$_ADV_WORKLOAD TABLE
MVIEW$_ADV_BASETABLE TABLE
MVIEW$_ADV_SQLDEPEND TABLE
MVIEW$_ADV_PRETTY TABLE
MVIEW$_ADV_TEMP TABLE
MVIEW$_ADV_FILTER TABLE
MVIEW$_ADV_LOG TABLE
MVIEW$_ADV_FILTERINSTANCE TABLE
MVIEW$_ADV_LEVEL TABLE
MVIEW$_ADV_ROLLUP TABLE
MVIEW$_ADV_AJG TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW$_ADV_FJG TABLE
MVIEW$_ADV_GC TABLE
MVIEW$_ADV_CLIQUE TABLE
MVIEW$_ADV_ELIGIBLE TABLE
MVIEW$_ADV_OUTPUT TABLE
MVIEW$_ADV_EXCEPTIONS TABLE
MVIEW$_ADV_PARAMETERS TABLE
MVIEW$_ADV_INFO TABLE
MVIEW$_ADV_JOURNAL TABLE
MVIEW$_ADV_PLAN TABLE
AQ$_QUEUE_TABLES TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
AQ$_QUEUES TABLE
AQ$_SCHEDULES TABLE
AQ$_INTERNET_AGENTS TABLE
AQ$_INTERNET_AGENT_PRIVS TABLE
OL$ TABLE
OL$HINTS TABLE
OL$NODES TABLE
DEF$_ERROR TABLE
DEF$_DESTINATION TABLE
DEF$_CALLDEST TABLE
DEF$_DEFAULTDEST TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
DEF$_LOB TABLE
DEF$_PROPAGATOR TABLE
DEF$_ORIGIN TABLE
DEF$_PUSHED_TRANSACTIONS TABLE
REPCAT$_REPCAT TABLE
REPCAT$_FLAVORS TABLE
REPCAT$_REPSCHEMA TABLE
REPCAT$_SNAPGROUP TABLE
REPCAT$_REPOBJECT TABLE
REPCAT$_REPCOLUMN TABLE
REPCAT$_KEY_COLUMNS TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_GENERATED TABLE
REPCAT$_REPPROP TABLE
REPCAT$_REPCATLOG TABLE
REPCAT$_DDL TABLE
REPCAT$_REPGROUP_PRIVS TABLE
REPCAT$_PRIORITY_GROUP TABLE
REPCAT$_PRIORITY TABLE
REPCAT$_COLUMN_GROUP TABLE
REPCAT$_GROUPED_COLUMN TABLE
REPCAT$_CONFLICT TABLE
REPCAT$_RESOLUTION_METHOD TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_RESOLUTION TABLE
REPCAT$_RESOLUTION_STATISTICS TABLE
REPCAT$_RESOL_STATS_CONTROL TABLE
REPCAT$_PARAMETER_COLUMN TABLE
REPCAT$_AUDIT_ATTRIBUTE TABLE
REPCAT$_AUDIT_COLUMN TABLE
REPCAT$_FLAVOR_OBJECTS TABLE
REPCAT$_TEMPLATE_STATUS TABLE
REPCAT$_TEMPLATE_TYPES TABLE
REPCAT$_REFRESH_TEMPLATES TABLE
REPCAT$_USER_AUTHORIZATIONS TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_OBJECT_TYPES TABLE
REPCAT$_TEMPLATE_REFGROUPS TABLE
REPCAT$_TEMPLATE_OBJECTS TABLE
REPCAT$_TEMPLATE_PARMS TABLE
REPCAT$_OBJECT_PARMS TABLE
REPCAT$_USER_PARM_VALUES TABLE
REPCAT$_TEMPLATE_SITES TABLE
REPCAT$_SITE_OBJECTS TABLE
REPCAT$_RUNTIME_PARMS TABLE
REPCAT$_TEMPLATE_TARGETS TABLE
REPCAT$_EXCEPTIONS TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_INSTANTIATION_DDL TABLE
REPCAT$_EXTENSION TABLE
REPCAT$_SITES_NEW TABLE
LOGMNR_DICTSTATE$ TABLE
LOGMNRC_GTLO TABLE
LOGMNRC_GTCS TABLE
LOGMNRC_SEQ_GG TABLE
LOGMNRC_CON_GG TABLE
LOGMNRC_CONCOL_GG TABLE
LOGMNRC_IND_GG TABLE
LOGMNRC_INDCOL_GG TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNRC_GSII TABLE
LOGMNRC_GSBA TABLE
LOGMNR_SEED$ TABLE
LOGMNR_DICTIONARY$ TABLE
LOGMNR_OBJ$ TABLE
LOGMNR_TAB$ TABLE
LOGMNR_COL$ TABLE
LOGMNR_ATTRCOL$ TABLE
LOGMNR_TS$ TABLE
LOGMNR_IND$ TABLE
LOGMNR_USER$ TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_TABPART$ TABLE
LOGMNR_TABSUBPART$ TABLE
LOGMNR_TABCOMPART$ TABLE
LOGMNR_TYPE$ TABLE
LOGMNR_COLTYPE$ TABLE
LOGMNR_ATTRIBUTE$ TABLE
LOGMNR_LOB$ TABLE
LOGMNR_CON$ TABLE
LOGMNR_CDEF$ TABLE
LOGMNR_CCOL$ TABLE
LOGMNR_ICOL$ TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_LOBFRAG$ TABLE
LOGMNR_INDPART$ TABLE
LOGMNR_INDSUBPART$ TABLE
LOGMNR_INDCOMPART$ TABLE
LOGMNR_LOGMNR_BUILDLOG TABLE
LOGMNR_NTAB$ TABLE
LOGMNR_OPQTYPE$ TABLE
LOGMNR_SUBCOLTYPE$ TABLE
LOGMNR_KOPM$ TABLE
LOGMNR_PROPS$ TABLE
LOGMNR_ENC$ TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_REFCON$ TABLE
LOGMNR_PARTOBJ$ TABLE
LOGMNRP_CTAS_PART_MAP TABLE
SCHEDULER_PROGRAM_ARGS_TBL TABLE
SCHEDULER_JOB_ARGS_TBL TABLE
LOGSTDBY$PARAMETERS TABLE
LOGSTDBY$EVENTS TABLE
LOGSTDBY$APPLY_PROGRESS TABLE
LOGSTDBY$APPLY_MILESTONE TABLE
LOGSTDBY$SCN TABLE
LOGSTDBY$FLASHBACK_SCN TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGSTDBY$PLSQL TABLE
LOGSTDBY$SKIP_TRANSACTION TABLE
LOGSTDBY$SKIP TABLE
LOGSTDBY$SKIP_SUPPORT TABLE
LOGSTDBY$HISTORY TABLE
LOGSTDBY$EDS_TABLES TABLE
DEF$_AQCALL TABLE
DEF$_AQERROR TABLE
SQLPLUS_PRODUCT_PROFILE TABLE
HELP TABLE
MVIEW$_ADV_INDEX TABLE
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW$_ADV_PARTITION TABLE
MVIEW$_ADV_OWB TABLE
167 rows selected.
SQL>
Again a lot of LogMiner, repcat and even outlines in OL$. What roles does SYSTEM have granted directly; not roles granted to roles etc but just the direct grants:
SQL> select granted_role from dba_role_privs where grantee='SYSTEM';
GRANTED_ROLE
------------------------------
AQ_ADMINISTRATOR_ROLE
DBA
MGMT_USER
SQL>
So, finally what grants are made on the SYSTEM objects:
SQL> col privilege for a30
SQL> col grantee for a30
SQL> col table_name for a30
SQL> set lines 220
SQL> select p.privilege,p.grantee,p.table_name,o.object_type from dba_tab_privs p, dba_objects o where p.owner='SYSTEM' and o.owner=p.owner and o.object_name=p.table_name;
PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
DELETE PUBLIC OL$ TABLE
INSERT PUBLIC OL$ TABLE
SELECT PUBLIC OL$ TABLE
UPDATE PUBLIC OL$ TABLE
DELETE PUBLIC OL$HINTS TABLE
INSERT PUBLIC OL$HINTS TABLE
SELECT PUBLIC OL$HINTS TABLE
UPDATE PUBLIC OL$HINTS TABLE
DELETE PUBLIC OL$NODES TABLE
INSERT PUBLIC OL$NODES TABLE
SELECT PUBLIC OL$NODES TABLE
PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
UPDATE PUBLIC OL$NODES TABLE
SELECT SYS REPCAT$_REPSCHEMA TABLE
SELECT SYS REPCAT$_REPPROP TABLE
SELECT SYS DEF$_CALLDEST TABLE
SELECT SYS DEF$_ERROR TABLE
SELECT SYS DEF$_DESTINATION TABLE
SELECT SELECT_CATALOG_ROLE SCHEDULER_PROGRAM_ARGS VIEW
SELECT SELECT_CATALOG_ROLE SCHEDULER_JOB_ARGS VIEW
SELECT SELECT_CATALOG_ROLE SCHEDULER_PROGRAM_ARGS_TBL TABLE
SELECT SELECT_CATALOG_ROLE SCHEDULER_JOB_ARGS_TBL TABLE
SELECT SYS DEF$_AQCALL TABLE
PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
SELECT SYS DEF$_AQCALL QUEUE
SELECT PUBLIC PRODUCT_PRIVS VIEW
SELECT PUBLIC HELP TABLE
DELETE PUBLIC MVIEW$_ADV_OWB TABLE
INSERT PUBLIC MVIEW$_ADV_OWB TABLE
SELECT PUBLIC MVIEW$_ADV_OWB TABLE
UPDATE PUBLIC MVIEW$_ADV_OWB TABLE
DELETE PUBLIC MVIEW$_ADV_PARTITION TABLE
INSERT PUBLIC MVIEW$_ADV_PARTITION TABLE
SELECT PUBLIC MVIEW$_ADV_PARTITION TABLE
UPDATE PUBLIC MVIEW$_ADV_PARTITION TABLE
PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
DELETE PUBLIC MVIEW$_ADV_INDEX TABLE
INSERT PUBLIC MVIEW$_ADV_INDEX TABLE
SELECT PUBLIC MVIEW$_ADV_INDEX TABLE
UPDATE PUBLIC MVIEW$_ADV_INDEX TABLE
DELETE OLAPSYS MVIEW$_ADV_OUTPUT TABLE
INSERT OLAPSYS MVIEW$_ADV_OUTPUT TABLE
SELECT OLAPSYS MVIEW$_ADV_OUTPUT TABLE
UPDATE OLAPSYS MVIEW$_ADV_OUTPUT TABLE
DELETE OLAPSYS MVIEW$_ADV_PARTITION TABLE
INSERT OLAPSYS MVIEW$_ADV_PARTITION TABLE
SELECT OLAPSYS MVIEW$_ADV_PARTITION TABLE
PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
UPDATE OLAPSYS MVIEW$_ADV_PARTITION TABLE
DELETE OLAPSYS MVIEW$_ADV_INDEX TABLE
INSERT OLAPSYS MVIEW$_ADV_INDEX TABLE
SELECT OLAPSYS MVIEW$_ADV_INDEX TABLE
UPDATE OLAPSYS MVIEW$_ADV_INDEX TABLE
49 rows selected.
SQL>
So, SYSTEM looks like a DBA account as it has the DBA role and its well known for many years as the default DBA account in an Oracle but it also has objects such as OL$ and also LogMiner objects. This is a classic case of a conflict. Is it a DBA or is it a schema?. Well like the rest of us who create Oracle databases and create users and schemas Oracle seems to have muddied the waters a bit with SYSTEM.
Ideally no one should use SYSTEM; its a default and it is the opposite of Least Rights in the database so clearly does not serve a purpose in a well designed and secure database. So can we drop SYSTEM:
SQL> drop user system cascade;
drop user system cascade
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped
SQL>
No, we are stuck with SYSTEM; this is bad news. Ideally we should be able to have an Oracle database with just SYS and no other users and no PUBLIC grants; but that is never going to happen. It should and then we have a chance of Least Rights in our databases.
OK, more soon!
Bye from WFH
