I chose to download the Oracle Virtualbox VM and had that up and running quickly; and its only about 7 gig. Here I am logged in with an 11.2.0.4 client remotely as that is what I had available on the PC:
C:\mac_nov_2019\audit_class_2_day\2_day_master\V2.21.02\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 6 07:35:19 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL>
This version 23c release is amazing as far as I can tell its an update on the Free XE style model with a name change. It has got install limits, size etc but you can use it internally for free - see the limitations here. This 23c free version is now called "FREE" instead of XE. Also its released first instead of later after the EE and SE releases. This is so developers and the community can download and develop their applications against 23c early and have them ready before the final Enterprise or Standard Edition releases.
This means we can all be beta testers without getting onto the beta program and most importantly we can talk about it!!
I connected to my 23c VM and ran some commands and also used a PC client of 11.2.0.4 and also tried a 19c client. The first simple update that is noticeable is the change to no longer need DUAL.
SQL> select sys_context('userenv','con_name');
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
FREEPDB1
1 row selected.
SQL>
You can still use DUAL of course:
SQL> select sys_context('userenv','con_name') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
FREEPDB1
1 row selected.
SQL>
It was covered in various posts that 21c XE included cost options for free under the same restrictions as XE itself such as Database Vault or Oracle Label Security and Real Application Security were included in the XE version. For instance Paul Bullen covered this in his article on linkedin. It is unclear if the 23c Free also includes all the same features for free. I did a quick check in 23c FREE:
C:\mac_nov_2019\audit_class_2_day\2_day_master\V2.21.02\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 5 15:06:54 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL>
SQL> col comp_name for a40
SQL> col status for a10
SQL> col version for a15
SQL> set lines 220
SQL> select comp_name,status,version from dba_registry;
COMP_NAME STATUS VERSION
---------------------------------------- ---------- ---------------
Oracle Database Catalog Views VALID 23.0.0.0.0
Oracle Database Packages and Types VALID 23.0.0.0.0
Oracle Real Application Clusters OPTION OFF 23.0.0.0.0
JServer JAVA Virtual Machine VALID 23.0.0.0.0
Oracle XDK VALID 23.0.0.0.0
Oracle Database Java Packages VALID 23.0.0.0.0
OLAP Analytic Workspace VALID 23.0.0.0.0
Oracle XML Database VALID 23.0.0.0.0
Oracle Workspace Manager VALID 23.0.0.0.0
Oracle Text VALID 23.0.0.0.0
Oracle OLAP API VALID 23.0.0.0.0
COMP_NAME STATUS VERSION
---------------------------------------- ---------- ---------------
Spatial VALID 23.0.0.0.0
Oracle Label Security VALID 23.0.0.0.0
Oracle APEX VALID 22.2.0
Oracle Database Vault VALID 23.0.0.0.0
15 rows selected.
SQL>
and Database Vault and Label Security seem to be there for instance. If we check v$option we can see that Database Vault and Label security are FALSE:
SQL> select parameter from v$option where value=FALSE;
PARAMETER
----------------------------------------------------------------
Real Application Clusters
Parallel backup and recovery
Parallel execution
Change Data Capture
Managed Standby
Database resource manager
Automatic Storage Management
Enterprise User Security
Oracle Data Guard
Oracle Label Security
Streams Capture
PARAMETER
----------------------------------------------------------------
Oracle Database Vault
Real Application Testing
Active Data Guard
Server Flash Cache
Management Database
I/O Server
ASM Proxy Instance
Exadata Discovery
Global Data Services
Cache Fusion Lock Accelerator
Data Guard for Pluggable Databases
PARAMETER
----------------------------------------------------------------
SQL Firewall
23 rows selected.
SQL>
Interestingly SQL Firewall, the new feature is also FALSE. Can we use it and test it? is it even there? I don't know yet but I will check
A quick check of the users in the PDB shows 49 users installed:
SQL> col username for a20
SQL> col account_status for a15
SQL> col password_versions for a10
SQL> col read_only for a3
SQL> col dictionary_protected for a3
SQL> set lines 220
SQL> col protected for a3
SQL> col mandatory_profile_violation for a3
SQL> select username,account_status,password_versions,read_only,dictionary_protected, protected, mandatory_profile_violation from dba_users;
USERNAME ACCOUNT_STATUS PASSWORD_V REA DIC PRO MAN
-------------------- --------------- ---------- --- --- --- ---
SYS OPEN 11G 12C NO NO NO NO
SYSTEM OPEN 11G 12C NO NO NO NO
APEX_LISTENER OPEN 11G 12C NO NO NO NO
APEX_PUBLIC_USER OPEN 11G 12C NO NO NO NO
APEX_REST_PUBLIC_USE OPEN 11G 12C NO NO NO NO
R
AV OPEN 11G 12C NO NO NO NO
PDBADMIN OPEN 11G 12C NO NO NO NO
SYSRAC OPEN NO YES NO NO
HR OPEN 11G 12C NO NO NO NO
USERNAME ACCOUNT_STATUS PASSWORD_V REA DIC PRO MAN
-------------------- --------------- ---------- --- --- --- ---
ORDS_PUBLIC_USER OPEN 11G 12C NO NO NO NO
ORDS_METADATA OPEN NO NO NO NO
BI OPEN 11G 12C NO NO NO NO
OE OPEN 11G 12C NO NO NO NO
PM OPEN 11G 12C NO NO NO NO
HRREST OPEN 11G 12C NO NO NO NO
IX OPEN 11G 12C NO NO NO NO
PFCLSCAN OPEN 11G 12C NO NO NO NO
SH OPEN 11G 12C NO NO NO NO
XS$NULL LOCKED NO YES NO NO
LBACSYS LOCKED NO YES NO NO
USERNAME ACCOUNT_STATUS PASSWORD_V REA DIC PRO MAN
-------------------- --------------- ---------- --- --- --- ---
OUTLN LOCKED NO NO NO NO
DBSNMP LOCKED NO NO NO NO
APPQOSSYS LOCKED NO NO NO NO
APEX_220200 LOCKED NO NO NO NO
DBSFWUSER LOCKED NO NO NO NO
GGSYS LOCKED NO NO NO NO
ANONYMOUS LOCKED NO NO NO NO
FLOWS_FILES LOCKED NO NO NO NO
CTXSYS LOCKED NO YES NO NO
DVSYS LOCKED NO YES NO NO
DVF LOCKED NO YES NO NO
USERNAME ACCOUNT_STATUS PASSWORD_V REA DIC PRO MAN
-------------------- --------------- ---------- --- --- --- ---
AUDSYS LOCKED NO YES NO NO
GSMADMIN_INTERNAL LOCKED NO YES NO NO
GGSHAREDCAP LOCKED NO YES NO NO
OLAPSYS LOCKED NO NO NO NO
MDSYS LOCKED NO NO NO NO
XDB LOCKED NO YES NO NO
WMSYS LOCKED NO NO NO NO
GSMCATUSER LOCKED NO NO NO NO
MDDATA LOCKED NO NO NO NO
SYSBACKUP LOCKED NO YES NO NO
REMOTE_SCHEDULER_AGE LOCKED NO NO NO NO
USERNAME ACCOUNT_STATUS PASSWORD_V REA DIC PRO MAN
-------------------- --------------- ---------- --- --- --- ---
NT
GSMUSER LOCKED NO NO NO NO
OJVMSYS LOCKED NO NO NO NO
DIP LOCKED NO NO NO NO
SYSKM LOCKED NO YES NO NO
DGPDB_INT LOCKED NO NO NO NO
SYS$UMF LOCKED NO NO NO NO
SYSDG LOCKED NO YES NO NO
49 rows selected.
SQL>
Some interesting things here. This is a higher number of default accounts in this 23c PDB than previous versions; 48 really as I created one user PFCLSCAN. There are of course the samples that are talked about for developers to use BUT this is a backwards step in terms of security as these were not installed by default in 21c XE. There is a new column added from 21c MANDATORY_PROFILE_VIOLATION which I will look at in a later post and also a READ_ONLY column not there in 21c and is now here in 23c. There are no users that are marked as READ_ONLY by default. The column is also not described in the 23c documentation but I will look at it in a later post. Also there are two columns, PROTECTED and DICTIONARY_PROTECTED. There are no users that are PROTECTED but some users are DICTIONARY_PROTECTED. I will discuss both of these as well in the next post on more details.
Profiles are interesting as the settings are worse than earlier:
SQL> @profiles
profiles.sql: Release 1.0.0.0.0 - Production on Thu Apr 06 06:42:49 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
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
OE DEFAULT 10 U U 1 U 7 U NULL
PM DEFAULT 10 U U 1 U 7 U NULL
HRREST 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
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>
For instance the lifetime of 180 is gone but the grace time of 7 days is still there. Both settings make no sense anyway. I will discuss profiles in more details in the next posts.
Well, that's it for now. I just wanted to get a 23c post out there quickly but I will post in much more details about Oracle database security in 23c.
#23c
#dbsec
#oracle
#database
#security
#oracleace
