But, that said, even if this is a free developer release should we accept weak security settings?
Well, no, of course not but this is the story I see though in a lot in customers databases. Security should be applied in all databases in an organisation so that the development, test and pre-production databases are all secured to the same level as production. If we don't we are maintaining two different security models, why? also if data ends up in pre-production databases then if it has weak security then that data is also put in danger.
Let's have a look at the shipped profiles in the Oracle 23c Free and see how secure they are for use:
SQL> @profiles
profiles.sql: Release 1.0.0.0.0 - Production on Tue May 23 11:22:15 2023
Copyright (c) 2007, 2009 PeteFinnigan.com Limited. All rights reserved.
F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
USER Profile F T S L M G L V
================================================================================
SYS DEFAULT 10 U U 1 U 7 U NULL
SYSTEM DEFAULT 10 U U 1 U 7 U NULL
APEX_LISTENE DEFAULT 10 U U 1 U 7 U NULL
APEX_PUBLIC_ DEFAULT 10 U U 1 U 7 U NULL
APEX_REST_PU DEFAULT 10 U U 1 U 7 U NULL
AV DEFAULT 10 U U 1 U 7 U NULL
PDBADMIN DEFAULT 10 U U 1 U 7 U NULL
SYSRAC DEFAULT 10 U U 1 U 7 U NULL
HR DEFAULT 10 U U 1 U 7 U NULL
VE DEFAULT 10 U U 1 U 7 U NULL
ORDS_PUBLIC_ DEFAULT 10 U U 1 U 7 U NULL
ORDS_METADAT DEFAULT 10 U U 1 U 7 U NULL
BI DEFAULT 10 U U 1 U 7 U NULL
VC DEFAULT 10 U U 1 U 7 U NULL
OE DEFAULT 10 U U 1 U 7 U NULL
PM DEFAULT 10 U U 1 U 7 U NULL
VA DEFAULT 10 U U 1 U 7 U NULL
VF DEFAULT 10 U U 1 U 7 U NULL
VB DEFAULT 10 U U 1 U 7 U NULL
HRREST DEFAULT 10 U U 1 U 7 U NULL
VG DEFAULT 10 U U 1 U 7 U NULL
IX DEFAULT 10 U U 1 U 7 U NULL
PFCLSCAN DEFAULT 10 U U 1 U 7 U NULL
SH DEFAULT 10 U U 1 U 7 U NULL
VJ DEFAULT 10 U U 1 U 7 U NULL
XS$NULL DEFAULT 10 U U 1 U 7 U NULL
LBACSYS DEFAULT 10 U U 1 U 7 U NULL
OUTLN DEFAULT 10 U U 1 U 7 U NULL
DBSNMP DEFAULT 10 U U 1 U 7 U NULL
APPQOSSYS DEFAULT 10 U U 1 U 7 U NULL
APEX_220200 DEFAULT 10 U U 1 U 7 U NULL
DBSFWUSER DEFAULT 10 U U 1 U 7 U NULL
GGSYS DEFAULT 10 U U 1 U 7 U NULL
ANONYMOUS DEFAULT 10 U U 1 U 7 U NULL
FLOWS_FILES DEFAULT 10 U U 1 U 7 U NULL
CTXSYS DEFAULT 10 U U 1 U 7 U NULL
DVSYS DEFAULT 10 U U 1 U 7 U NULL
DVF DEFAULT 10 U U 1 U 7 U NULL
AUDSYS DEFAULT 10 U U 1 U 7 U NULL
GSMADMIN_INT DEFAULT 10 U U 1 U 7 U NULL
GGSHAREDCAP DEFAULT 10 U U 1 U 7 U NULL
OLAPSYS DEFAULT 10 U U 1 U 7 U NULL
MDSYS DEFAULT 10 U U 1 U 7 U NULL
XDB DEFAULT 10 U U 1 U 7 U NULL
WMSYS DEFAULT 10 U U 1 U 7 U NULL
GSMCATUSER DEFAULT 10 U U 1 U 7 U NULL
MDDATA DEFAULT 10 U U 1 U 7 U NULL
SYSBACKUP DEFAULT 10 U U 1 U 7 U NULL
REMOTE_SCHED DEFAULT 10 U U 1 U 7 U NULL
GSMUSER DEFAULT 10 U U 1 U 7 U NULL
OJVMSYS DEFAULT 10 U U 1 U 7 U NULL
DIP DEFAULT 10 U U 1 U 7 U NULL
SYSKM DEFAULT 10 U U 1 U 7 U NULL
DGPDB_INT DEFAULT 10 U U 1 U 7 U NULL
SYS$UMF DEFAULT 10 U U 1 U 7 U NULL
SYSDG DEFAULT 10 U U 1 U 7 U NULL
================================================================================
USER Profile F T S L M G L V
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
Hmm, this is not good. There is no verify function used and the lifetime is unlimited so the passwords can never be forced to change and even if they are changed they can be reset back to the same weak values. There are no other profiles used on any user. The Grace time is defaulted to 7 days, why??. What profiles have been shipped:
SQL> @prof
F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
PROFILE F T S L M G L V
================================================================================
ORA_CIS_PROFILE 5 365 10 1 20 5 90 ORA12C_VERIFY_FUNCTION
ORA_STIG_PROFILE 3 175 D U 5 0 35 ORA12C_STIG_VERIFY_FUNCTION
DEFAULT 10 U U 1 U 7 U NULL
================================================================================
PROFILE F T S L M G L V
PL/SQL procedure successfully completed.
SQL>
Just the DEFAULT profile and the CIS and STIG profiles. These are better than the DEFAULT profile but they are not your designs. If we compare 21c we get:
C:\scripts>sqlplus sys/oracle1@//192.168.56.33:1539/xepdb1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 23 12:59:59 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
SQL> @profiles
profiles.sql: Release 1.0.0.0.0 - Production on Tue May 23 12:59:52 2023
Copyright (c) 2007, 2009 PeteFinnigan.com Limited. All rights reserved.
F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
USER Profile F T S L M G L V
================================================================================
SYS DEFAULT 10 U U 1 U 7 180 NULL
SYSTEM DEFAULT 10 U U 1 U 7 180 NULL
XS$NULL DEFAULT 10 U U 1 U 7 180 NULL
LBACSYS DEFAULT 10 U U 1 U 7 180 NULL
OUTLN DEFAULT 10 U U 1 U 7 180 NULL
DBSNMP DEFAULT 10 U U 1 U 7 180 NULL
APPQOSSYS DEFAULT 10 U U 1 U 7 180 NULL
GGSYS DEFAULT 10 U U 1 U 7 180 NULL
ANONYMOUS DEFAULT 10 U U 1 U 7 180 NULL
DBSFWUSER DEFAULT 10 U U 1 U 7 180 NULL
CTXSYS DEFAULT 10 U U 1 U 7 180 NULL
DVSYS DEFAULT 10 U U 1 U 7 180 NULL
DVF DEFAULT 10 U U 1 U 7 180 NULL
AUDSYS DEFAULT 10 U U 1 U 7 180 NULL
GSMADMIN_INT DEFAULT 10 U U 1 U 7 180 NULL
OLAPSYS DEFAULT 10 U U 1 U 7 180 NULL
MDSYS DEFAULT 10 U U 1 U 7 180 NULL
XDB DEFAULT 10 U U 1 U 7 180 NULL
WMSYS DEFAULT 10 U U 1 U 7 180 NULL
GSMCATUSER DEFAULT 10 U U 1 U 7 180 NULL
USER05 DEFAULT 10 U U 1 U 7 180 NULL
FEED01 DEFAULT 10 U U 1 U 7 180 NULL
MDDATA DEFAULT 10 U U 1 U 7 180 NULL
AA DEFAULT 10 U U 1 U 7 180 NULL
ATKD DEFAULT 10 U U 1 U 7 180 NULL
SYSBACKUP DEFAULT 10 U U 1 U 7 180 NULL
PDBADMIN DEFAULT 10 U U 1 U 7 180 NULL
GSMUSER DEFAULT 10 U U 1 U 7 180 NULL
EMIL DEFAULT 10 U U 1 U 7 180 NULL
REMOTE_SCHED DEFAULT 10 U U 1 U 7 180 NULL
DEV02 DEFAULT 10 U U 1 U 7 180 NULL
USER03 DEFAULT 10 U U 1 U 7 180 NULL
PETE DEFAULT 10 U U 1 U 7 180 NULL
USE DEFAULT 10 U U 1 U 7 180 NULL
SYSRAC DEFAULT 10 U U 1 U 7 180 NULL
C##ATKD DEFAULT 10 U U 1 U 7 180 NULL
FRED DEFAULT 10 U U 1 U 7 180 NULL
BACK01 DEFAULT 10 U U 1 U 7 180 NULL
SI_INFORMTN_ DEFAULT 10 U U 1 U 7 180 NULL
ERIC DEFAULT 10 U U 1 U 7 180 NULL
IMPORTER DEFAULT 10 U U 1 U 7 180 NULL
OJVMSYS DEFAULT 10 U U 1 U 7 180 NULL
SCH DEFAULT 10 U U 1 U 7 180 NULL
JIM DEFAULT 10 U U 1 U 7 180 NULL
DIP DEFAULT 10 U U 1 U 7 180 NULL
RISK01 DEFAULT 10 U U 1 U 7 180 NULL
USER07 DEFAULT 10 U U 1 U 7 180 NULL
C##ATKR DEFAULT 10 U U 1 U 7 180 NULL
DEV DEFAULT 10 U U 1 U 7 180 NULL
ORDPLUGINS DEFAULT 10 U U 1 U 7 180 NULL
BILL DEFAULT 10 U U 1 U 7 180 NULL
ZULIA DEFAULT 10 U U 1 U 7 180 NULL
'x.hack();' DEFAULT 10 U U 1 U 7 180 NULL
VA DEFAULT 10 U U 1 U 7 180 NULL
FACADM DEFAULT 10 U U 1 U 7 180 NULL
BATCH01 DEFAULT 10 U U 1 U 7 180 NULL
USER06 DEFAULT 10 U U 1 U 7 180 NULL
SYSKM DEFAULT 10 U U 1 U 7 180 NULL
DEV03 DEFAULT 10 U U 1 U 7 180 NULL
C##ATKA DEFAULT 10 U U 1 U 7 180 NULL
DGPDB_INT DEFAULT 10 U U 1 U 7 180 NULL
ORDDATA DEFAULT 10 U U 1 U 7 180 NULL
ORABLOG DEFAULT 10 U U 1 U 7 180 NULL
BB DEFAULT 10 U U 1 U 7 180 NULL
ATKA DEFAULT 10 U U 1 U 7 180 NULL
ORACLE_OCM DEFAULT 10 U U 1 U 7 180 NULL
SYS$UMF DEFAULT 10 U U 1 U 7 180 NULL
PFCL_VD DEFAULT 10 U U 1 U 7 180 NULL
ATK DEFAULT 10 U U 1 U 7 180 NULL
ATKR DEFAULT 10 U U 1 U 7 180 NULL
USER01 DEFAULT 10 U U 1 U 7 180 NULL
SYSDG DEFAULT 10 U U 1 U 7 180 NULL
ORDSYS DEFAULT 10 U U 1 U 7 180 NULL
USER02 DEFAULT 10 U U 1 U 7 180 NULL
USER04 DEFAULT 10 U U 1 U 7 180 NULL
ORASCAN DEFAULT 10 U U 1 U 7 180 NULL
C##ATK DEFAULT 10 U U 1 U 7 180 NULL
UU DEFAULT 10 U U 1 U 7 180 NULL
DEV01 DEFAULT 10 U U 1 U 7 180 NULL
PFCL_VP DEFAULT 10 U U 1 U 7 180 NULL
================================================================================
USER Profile F T S L M G L V
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
The only difference in the default profile is that the password lifetime is still 180 days in 21c. This doesnt make sense and has been a value shipped in the default profile for years. 180 days lifetime without a verify function to enforce a password that may outlive a password cracker is meaningless. The shipped profiles in 21c are:
SQL> @prof
F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
PROFILE F T S L M G L V
================================================================================
ORA_CIS_PROFILE 5 365 10 1 20 5 90 ORA12C_VERIFY_FUNCTION
ORA_STIG_PROFILE 3 175 D U 5 0 35 ORA12C_STIG_VERIFY_FUNCTION
DEFAULT 10 U U 1 U 7 180 NULL
================================================================================
PROFILE F T S L M G L V
PL/SQL procedure successfully completed.
SQL>
The STIG and CIS profiles that are shipped are the same in 21c as in 23c. These default settings in the same form have been around since Oracle 10g/11g. These settings are not good enough; they are not designed by you to suit your security requirements. You should never use these default settings and should instead design your own profiles. I normally advise people to design multiple profiles as follows for these types of users:
- Oracle Default Users
- Application schemas
- Admin type users such as DBA and support
- General users who can direct connect to the database
Each of these should have settings suitable to the users type. For instance schemas can have a failed login of 1, so that only one attempt is allowed to log into a schema. Or the life time of an admin user should be short and a verify function to enforce strong passwords that can last longer than the life time buy a substantial amount.
Also ensure that the DEFAULT profile has the strongest individual settings from each of the other profiles. This means that if a user is created and is not assigned to its correct profile then it will still have the strongest rules.
Ensure that each user is assessed and assigned to the correct group. This means assigning the correct profile.
Just coming up with profile settings and designs is not trivial and some thought has to be used in these designs based on existing company standards, risk and use of each user type. If you need more groups of users create them, the above is just an example. The example of the failed logons for schemas is a good one. If we enforce a failed logins of 1 for schemas then they should only ever be accessed via a proxy for release or changes to the schema and the schema should not be used as the "connection user" for the application. This way we can enforce the strong settings we need.
Oracle have provided weak out of the box profiles for all users in 23c BUT its your job to change that and design profiles that you need.
