Has the problem of default users got bigger or smaller in 12cR1. I have some figures that I have collected over the years from various versions of the Oracle database (these figures are for different versions of Oracle where I have taken them each from a seed database. This is to get consistency and also often reflects reality as customer systems tend to have either very few default schemas (rarer) or this sort of amount (more common)):
- 9iR2 @ 30 by default
- 10gR2 @ 27 by default
- 11g R1 @ 35 by default
- 11g R2 @ 36 by default
- 12c R1 @ 35 by default
On the face of it 11.1, 11.2 and 12.1 all look pretty static. If I run my user analysis high level script against 11.2 then we can see:
SQL> @use_anl
use.sql: Release 1.0.2.0.0 - Production on Fri Jul 05 12:23:13 2013
Copyright (c) 2007, 2009 PeteFinnigan.com Limited. All rights reserved.
OUTPUT FLAG [A|O|C] [A]: A
NUMBER OF USERS [200]:
Typ Rol RSO Sys Ob Tab PL USER
================================================================================
ADM 53 200 9 954 1417 SYS
ADM 3 5 211 156 3 SYSTEM
DEF 1 3 1 3 1 OUTLN
DEF 0 1 0 0 0 DIP
DEF 0 1 4 0 6 ORACLE_OCM
DEF 1 4 4 25 9 DBSNMP
--- 0 3 8 2 0 APPQOSSYS
DEF 3 29 14 43 52 WMSYS
DEF 1 9 8 47 72 EXFSYS
DEF 2 7 52 47 139 CTXSYS
DEF 3 10 16 33 78 XDB
DEF 0 1 11 0 0 ANONYMOUS
DEF 1 1 1042 5 87 ORDSYS
--- 0 1 0 73 0 ORDDATA
DEF 0 0 2 0 10 ORDPLUGINS
DEF 0 1 0 0 0 SI_INFORMTN_SCHEMA
DEF 2 19 31 120 245 MDSYS
DEF 2 13 43 126 89 OLAPSYS
DEF 2 1 0 0 0 MDDATA
DEF 3 8 257 0 0 SPATIAL_WFS_ADMIN_USR
DEF 3 8 144 0 0 SPATIAL_CSW_ADMIN_USR
ADM 3 4 23 728 407 SYSMAN
DEF 1 0 4 0 0 MGMT_VIEW
APX 0 1 6 1 0 FLOWS_FILES
APX 0 1 10 0 0 APEX_PUBLIC_USER
--- 2 B,2,9,0 26 105 360 401 APEX_030200
DEF 10 22 44 1 0 OWBSYS
--- 0 2 0 0 0 OWBSYS_AUDIT
SAM 2 1 0 4 0 SCOTT
SAM 1 7 1 7 2 HR
SAM 2 7 14 10 1 OE
SAM 5 17 11 17 0 IX
SAM 3 12 4 17 0 SH
SAM 2 1 10 2 0 PM
SAM 1 9 23 0 0 BI
DEF 0 0 0 0 0 XS$NULL
Typ Rol RSO Sys Ob Tab PL USER
================================================================================
PL/SQL procedure successfully completed.
For updates please visit /use.sql
SQL>
|
11.2 shows 36 default users installed and compared to 11.1 that’s just an increase of 1 default account so not bad for an update. There was a drop of sorts for 10g from 9i so things went in the right direction. For 12cR1 it seems that we have dropped to 35 again from 36:
SQL> @use_anl
use.sql: Release 1.0.2.0.0 - Production on Thu Jul 04 11:02:55 2013
Copyright (c) 2007, 2009 PeteFinnigan.com Limited. All rights reserved.
OUTPUT FLAG [A|O|C] [A]: A
NUMBER OF USERS [200]: 200
Typ Rol RSO Sys Ob Tab PL USER
================================================================================
ADM 75 219 10 1221 1622 SYS
--- 0 1 0 1 0 AUDSYS
ADM 2 5 161 178 7 SYSTEM
DEF 1 3 1 3 1 OUTLN
--- 0 8 20 19 12 GSMADMIN_INTERNAL
--- 1 B,2,2,12 0 0 0 0 GSMUSER
DEF 0 1 1 0 0 DIP
DEF 0 4 9 0 6 ORACLE_OCM
DEF 3 5 7 20 7 DBSNMP
--- 0 3 12 4 0 APPQOSSYS
DEF 3 12 486 29 94 XDB
DEF 0 1 11 0 0 ANONYMOUS
--- 4 B,7,19,73 0 4 0 0 GSMCATUSER
DEF 1 37 13 40 47 WMSYS
--- 1 B,1,8,0 1 0 6 0 OJVMSYS
DEF 2 11 59 53 147 CTXSYS
DEF 1 2 1225 5 91 ORDSYS
--- 0 1 0 90 0 ORDDATA
DEF 0 1 6 0 10 ORDPLUGINS
DEF 0 1 0 0 0 SI_INFORMTN_SCHEMA
DEF 2 21 24 130 265 MDSYS
DEF 2 11 30 2 0 OLAPSYS
DEF 2 0 0 0 0 MDDATA
DEF 3 7 268 0 0 SPATIAL_WFS_ADMIN_USR
DEF 3 7 144 0 0 SPATIAL_CSW_ADMIN_USR
--- 2 B,2,8,1 20 42 22 77 LBACSYS
APX 0 1 6 1 0 FLOWS_FILES
APX 0 1 10 0 0 APEX_PUBLIC_USER
--- 2 B,2,10,0 26 154 453 545 APEX_040200
--- 1 B,1,2,0 1 0 0 19 DVF
--- 15 B,29,22,588 13 44 39 95 DVSYS
--- 1 B,2,0,3652 13 15 0 0 SYSBACKUP
--- 0 4 7 0 0 SYSDG
--- 0 1 13 0 0 SYSKM
DEF 0 0 0 0 0 XS$NULL
Typ Rol RSO Sys Ob Tab PL USER
================================================================================
PL/SQL procedure successfully completed.
For updates please visit /use.sql
SQL>
|
But that is not true as the seed database (for the better) has no sample accounts. The 11.2 database had 7 sample accounts such as SCOTT and SH and OE and BI etc but these are missing in 12cR1. So in 12cR1 we have a net increase of 6 new default accounts added to a seed database. Even this is not a good analysis of the default accounts added to 12cR1. If we compare the accounts side by side:
12cR1 11gR2
ANONYMOUS ANONYMOUS
APEX_040200 APEX_030200
APEX_PUBLIC_USER APEX_PUBLIC_USER
APPQOSSYS APPQOSSYS
AUDSYS .
. BI
CTXSYS CTXSYS
DBSNMP DBSNMP
DIP DIP
DVF .
DVSYS .
. EXFSYS
FLOWS_FILES FLOWS_FILES
GSMADMIN_INTERNAL .
GSMCATUSER .
GSMUSER .
. HR
. IX
LBACSYS .
MDDATA MDDATA
MDSYS MDSYS
. MGMT_VIEW
. OE
OJVMSYS .
OLAPSYS OLAPSYS
ORACLE_OCM ORACLE_OCM
ORDDATA ORDDATA
ORDPLUGINS ORDPLUGINS
ORDSYS ORDSYS
OUTLN OUTLN
. OWBSYS
. OWBSYS_AUDIT
. PM
. SCOTT
. SH
SI_INFORMTN_SCHEMA SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR SPATIAL_WFS_ADMIN_USR
SYS SYS
SYSBACKUP .
SYSDG .
SYSKM .
. SYSMAN
SYSTEM SYSTEM
WMSYS WMSYS
XDB XDB
XS$NULL XS$NULL
|
The difference between 11.2 and 12.1 on the surface seems to be one less default account in 12.1 but this is not true. If we look and compare 11.2 and 12.1 we can see that there are 22 differences in default installed accounts in 12c. 12c doesn’t include the sample accounts (6 of them) which is good but it also includes DV and OLS and GSM accounts that are not in 11.2. 12c also has the additional SYS accounts BUT it no longer installs SYSMAN or MGMT_VIEW. 12c also brings an audit user AUDSYS. Apex is also installed in both database versions; which for me is not good as I am not using it in this database.
So in simple terms 12c looked the same on the surface to 11gR2, if slightly better but in real terms even if you create a bare bones database in 12c it is likely that more default schemas are going to be needed and will remain in the 12c database such as the AUDSYS, the SYS??? Users and also the DV and OLS users.
A final point is the massive increase in PUBLIC privileges in 12cR1. For my simple seed database tests this number is massive:
SQL> select count(*) from dba_tab_privs
2 where grantee='PUBLIC';
COUNT(*)
----------
36866
1 row selected.
SQL>
|
This has grown from 28k in 11gR2.
