Security


What is the Dell BSAFE Micro-Edition Suite MES ?

On the April 2021 Oracle Critical Patch Update, the top vulnerability affects “Oracle Database – Enterprise Edition Security (Dell BSAFE Micro Edition Suite)”.

But what exactly is this Dell MES ? The answer comes on MES v4.1.6 to v4.5 update 18c / 19c databases (Doc ID 2746801.1) note on My Oracle Support. It explains:

” The BSAFE MES is the underlying encryption libraries used by the Database and associated technologies and products to encrypt data at rest and in transit.  Upgrading to MES 4.5 resolves a number of vulnerabilities and provides the latest and most secure encryption ciphers.”

Please note that already on the January 2021 Oracle Release Update (19.10 or 18.13 there was an update of this component and “Anonymous RC4 Cipher” is not supported and does not work anymore.

Other ciphers are deprecated and should not be used anymore, as they are not fully secure:

  • Unix Crypt (MD5crypt) Password verified (used with Oracle Internet Directory OID)
  •  MD4, MD5, DES, and RC4-related algorithms used for instance in Network encryption (recommended to use AES)
  • SHA-1 used in DBMS_CRYPTO and Checksum in SQLNET (recommended to use SHA-2)
  • TLS 1.0 and TLS 1.1 used to network connection authentication to the database using certificates (recommended to use TLS 1.2)

In my case I had problems not with the database, but with an old OEM Agent, that could not communicate anymore to OEM (after the January RU on OEM). The solution was to remove all deprecated algoritms and find common accepted ones between to old agent and patched OEM.


Get unlock from PDB Lockdown profiles

PDB Lockdown profiles allow, on a multitenant database, to limit what a user can do inside a PDB.

One use case is when we want to avoid high privileged users (like Application DBAs) to perform ALTER SYSTEM or ALTER SESSION commands.

Quickly we would think we can do a PDB Lockdown profile like:

SQL> create lockdown profile lock_test;
SQL> alter lockdown profile lock_test disable statement=('ALTER SESSION');
SQL> alter lockdown profile lock_test disable statement=('ALTER SYSTEM');

The problem of this simple profile is that we can lock ourselves, also as common user, inside the lock profile.

Image that you want to enable this profile on several PDBs:

SQL> alter session set container=pdb01;
Session altered.

SQL> alter system set pdb_lockdown=lock_test;
System altered.

SQL> alter session set container=samplepdb;
ERROR:
ORA-01031: insufficient privileges

Oups, you cannot anymore change the active container!

(more…)

Attention OPatch 12.2.0.1.19: ‘opatch util cleanup’ command deletes files from /etc, /bin, /lib 2

On January 28th 2020 I reported to Oracle on a Severy 1 SR that the latest OPatch version tries to delete system files from /etc, /bin, /lib :

I wrote in the SR the use case, provided the logs. On my case, the opatch cleanup command “just” deleted the configuration files of my backup client in /etc (opatch deletes files that are writable by the user who calls the command – ‘oracle’/’grid’ in most of the cases).

The location from where ‘opatch cleanup util’ tries to delete files depends from where you call it! So, if you call from / or from $ORACLE_BASE or from $ORACLE_HOME/OPatch you’ll get different behaviours!

It took two months until I got an answer to that SR, saying a bug was created and it will be fixed on the next version of OPatch:

Today there was a release of April 2020 Security patches, the latest Oracle Patch Bundles. To my surprise, for Oracle versions 12.2, 18c and 19c, there is the requirement of using OPatch 12.2.0.1.19 version, the exact same version that has this dangerous bug!

The bug reported end of January was not yet fixed! At least, on OPatch readme there is now a warning:

So, be careful until then and do not use ‘opatch util cleanup’. Anyway, this command is not anymore needed, as a cleanup (without deleting any wrong file) of the $ORACLE_HOME/.patch_storage directory is automatically done at the end of the patching.


Oracle October 2015 CPU – quick and dirty install on 11.2.0.4 and 12.1.0.2 4

[Update 17.11.2015 – Thanks Lars Johan Ulveseth for the extra testing and comment – see below. In fact I’ve only tested the PSU installation on a non-CDB 12c database. There is a missing step, added now, when you are in a multitenant configuration with pluggable databases. Script updated based on Lars comments.]

Following my January post, here are basic instructions to install the latest Oracle security patch. While one should always read the documentation, below you can find how to install CPU Oct 2015  on single instance. Please count about 5 minutes downtime on your database (a bit more if it is the first time you are doing this).

If you are single instance database (no RAC) and either 11.2.0.4 or 12.1.0.2 here are the quick summary of the installation. Tested on Linux and AIX:

Oracle 12.1.0.2 – October 2015 CPU installation

OPatch: You need to update the OPatch tool, minimum version is 12.1.0.1.7. Direct download from Oracle support.

Bundle 12.1.0.2 – Patch 21520444 – Combo OJVM PSU 12.1.0.2.5 and Database PSU 12.1.0.2.5 for UNIX

  • Patch 21359755 – Database Patch Set Update 12.1.0.2.5 (Oct2015) –> RAC-Rolling Installable
  • Patch 21555660 – Oracle JavaVM Component 12.1.0.2.5 Database PSU (OCT2015) –> Non RAC-Rolling Installable
export PATCH_LOC="/tmp"

1. Download + Upgrade OPatch (Opatch download direct link)

unzip ${PATCH_LOC}/p6880880_121010_Linux-x86-64.zip -d $ORACLE_HOME

2. Stop database+listeners+agent

3. Apply DB PSU (21359755 ) but do NOT run DB PSU post install steps

cd ${PATCH_LOC}
unzip p2*
cd 2*
cd 21359755/
$ORACLE_HOME/OPatch/opatch apply

3. Apply OJVM PSU patch

cd ../21555660/
$ORACLE_HOME/OPatch/opatch apply

4. Restart database [and open pluggable databases] in upgrade mode

SQL> startup upgrade;
SQL> alter pluggable database all open upgrade;

5. Run post install steps

cd $ORACLE_HOME/OPatch
./datapatch -verbose

6. Restart database [and open pluggable databases] in normal mode

SQL> shutdown;
SQL> startup;
SQL> alter pluggable database all open;


Oracle 11.2.0.4 – October 2015 CPU installation

Bundle 11.2.0.4 – Patch 21744335 – Combo OJVM PSU 11.2.0.4.5 and Database SPU 11.2.0.4 (CPUOct2015)

  • Patch 21352646 – Database Security Patch Update 11.2.0.4.0 (CPUOCT2015) –> RAC-Rolling Installable
  • Patch 21555791 – Oracle JavaVM Component 11.2.0.4.5 Database PSU (OCT2015) –> Non RAC-Rolling Installable
export PATCH_LOC="/tmp"
cd ${PATCH_LOC}
unzip p2*.zip
cd 2*

1. Shutdown databases and services
2. Apply DB PSU (21352646) but do NOT run DB PSU post install steps

cd 21352646
$ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate

3. Apply OJVM PSU patch

cd ../21555791/
$ORACLE_HOME/OPatch/opatch apply

4. Run the OJVM PSU post install steps followed by the DB PSU (or equivalent) post install steps.

cd $ORACLE_HOME/sqlpatch/21555791
sqlplus / as sysdba 
SQL> startup upgrade
SQL> @postinstall.sql
SQL> shutdown immediate
SQL> exit;
cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL> startup
SQL> @catbundle.sql cpu apply
SQL> @utlrp.sql
SQL> exit;

 


Oracle wallet on Windows registry – share with other users

I installed Oracle wallet to access passwordless to the remote databases, so I could do backups without specifying the password on any script.

The sqlnet.ora has the following information concerning the wallet:

WALLET_LOCATION =
(SOURCE =
(METHOD = reg)
(METHOD_DATA = (KEY = DEFAULT)))

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE

The other day, I got the need of giving access to remote databases to another windows user, so he could run a refresh schema/database script.

He was getting the following error when trying to access any database:

ORA-12578: TNS:wallet open failed

While I could just have set a different TNS_ADMIN variable pointing to a sqlnet.ora with

SQLNET.WALLET_OVERRIDE = FALSE

this was not the solution for him to use an automatic script.

The solution was to export/import the following branch of the registry from my windows user to the other windows user:

\\HKEY_CURRENT_USER\SOFTWARE\ORACLE\WALLETS

Note that these entries contain the encrypted password to the wallet, allowing the user to add/remove/modify/see the contents, including passwords, of the current wallet.


Oracle January 2015 CPU – quick and dirty install on 11.2.0.4 and 12.1.0.2 1

While one should always read the documentation accompanying the patches, the new January 2015 (and in some extend also October 2014) are more complex to install with a java and a db components.

If you are single instance database (no RAC) and either 11.2.0.4 or 12.1.0.2 here are the quick summary of the installation. Tested on Linux and Solaris SPARC:

Oracle 12.1.0.2 – January 2015 CPU installation

Bundle 12.1.0.2 – Patch 20132434

  • Patch 19769480 – Database Patch Set Update 12.1.0.2.2 (Jan2015) –> RAC-Rolling Installable
  • Patch 19877336 – Oracle JavaVM Component 12.1.0.2.2 Database PSU (JAN2015) –> Non RAC-Rolling Installable
export PATCH_LOC="/tmp"

1. Download + Upgrade OPatch

unzip ${PATCH_LOC}/p6880880_121010_Linux-x86-64.zip -d $ORACLE_HOME

2. Stop database+listeners+agent

3. Apply DB PSU (19769480) but do NOT run DB PSU post install steps

cd ${PATCH_LOC}
unzip p2*
cd 2*
cd 19769480/
$ORACLE_HOME/OPatch/opatch apply

3. Apply OJVM PSU patch

cd ../19877336/
$ORACLE_HOME/OPatch/opatch apply

4. Restart database

5. Run post install steps

cd $ORACLE_HOME/OPatch
./datapatch -verbose

 

Oracle 11.2.0.4 – January 2015 CPU installation

 

Bundle 11.2.0.4 – Patch 20132517

  • Patch 19854503 – Database Security Patch Update 11.2.0.4.0 (CPUJAN2015) –> RAC-Rolling Installable
  • Patch 19877440 – Oracle JavaVM Component 11.2.0.4.2 Database PSU (JAN2015) –> Non RAC-Rolling Installable
export PATCH_LOC="/tmp"
cd ${PATCH_LOC}
unzip p20132517_112040_Linux-x86-64.zip
cd 2*

1. Shutdown databases and services
2. Apply DB PSU (19854503) but do NOT run DB PSU post install steps

cd 19854503
$ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate

3. Apply OJVM PSU patch

cd ../19877440/
$ORACLE_HOME/OPatch/opatch apply

4. For 11.2.0.3 and 11.2.0.4 run the OJVM PSU post install steps followed by the DB PSU (or equivalent) post install steps.

cd $ORACLE_HOME/sqlpatch/19877440
sqlplus / as sysdba
SQL> startup upgrade
SQL> @postinstall.sql
SQL> shutdown immediate
SQL> exit;
cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL> startup
SQL> @catbundle.sql cpu apply
SQL> @utlrp.sql
SQL> exit;

 


Oracle 12.1 – Proxy only connect user property 1

This yet undocumented feature allows to define application schemas which can only be accessed through a proxy user. It makes a very useful to assure that no user connects directly to the application schema, even by knowing its password.

Here how it works:

SQL> CREATE USER app_user IDENTIFIED BY xyz;
User created.

SQL> GRANT CREATE SESSION TO app_user;
Grant succeeded.

SQL> ALTER USER app_user PROXY ONLY CONNECT;
User altered.

SQL> CREATE USER personal_user IDENTIFIED BY prx1;
User created.

SQL> ALTER USER app_user GRANT CONNECT THROUGH personal_user;
User altered.

SQL> CONNECT app_user/xyz;
ERROR:
ORA-28058: login is allowed only through a proxy

SQL> CONNECT personal_user[app_user]/prx1;
Connected.

SQL> SELECT user FROM dual;
USER
------------------------------
APP_USER

The information that app_user accepts to be connected only through proxy user can be seen at the new DBA_USERS column PROXY_ONLY_CONNECT.

As usual, the use of undocumented features are not supported by Oracle. The syntax to rollback the change is:

SQL> ALTER USER app_user CANCEL PROXY ONLY CONNECT;

Oracle password verify function

This week I had to implement a password verify function at a client.

My sources of inspiration were both:

– Stefan Oehrli blog entry on [Oracle 12c new password verify function]

– Mike Smithers blog entry on [Mama Mia ! Oracle Database Password Complexity and Seventies Euro-Pop]

 

From Mike Smithers I specially like the small PL/SQL to test the password function. I dare to reproduce here:

set lines 130
set serveroutput on size unlimited
spool verify_test.log
DECLARE
--
-- script to test check_password_fn
--
    TYPE typ_passwords IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
    tbl_passwords typ_passwords;
     
    l_old_password VARCHAR2(30) := 'Waterloo1';
     
    l_dummy BOOLEAN;
BEGIN
    --
    -- setup list of passwords to test with...
    --
    tbl_passwords(1) := 'simple'; -- too short
    tbl_passwords(2) := 'mike1234'; -- same as user
    tbl_passwords(3) := '4321ekim'; -- mike backwards
    tbl_passwords(4) := '1oolretaW'; -- old password backwards
    tbl_passwords(5) := 'Waterloo18'; -- too similar to old password
    tbl_passwords(6) := 'Brotherhood_0f_Man'; -- dictionary
    tbl_passwords(7) := 'P455w0rd'; -- simple
    tbl_passwords(8) := 'Sm0king_15_0nly_Vice'; -- should pass.
    tbl_passwords(9) := 'm1K31234'; -- should fail - too similar to user
    tbl_passwords(10) := 'W4terl001'; -- should fail - too similar to old password
    tbl_passwords(11) := 'the_day_before_you_came'; -- should fail - does not have any uppercase or numbers
    tbl_passwords(12) := 'ONLYSeventeen'; -- should fail - no numbers
    tbl_passwords(13) := '48840000'; -- should fail - no letters
    tbl_passwords(14) := 'ABBA0000'; -- should fail - no lowercase letters
    tbl_passwords(15) := 'abba0000'; -- should fail - no uppercase letters
    tbl_passwords(16) := 'Mamma_Mia1'; -- should pass
 
--  tbl_passwords(1) := 'W4terl001'; 
--    tbl_passwords(2) := 'P455w0rd'; -- simple
 
    --
    -- Nested block required to account for failures...
    --
    FOR i IN 1..tbl_passwords.COUNT LOOP
        BEGIN
            l_dummy := check_password_fn(
                username => 'MIKE',
                old_password => l_old_password,
                new_password => tbl_passwords(i));
            DBMS_OUTPUT.PUT_LINE('Test '||i||' - password allowed.');
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Test '||i||' ERROR : '||SQLERRM);
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Test run completed.');
END;
/
spool off

One the requirements I had was:

– The password should include three of the four following sets: English uppercase letters, English lowercase letters, base 10 digits, special characters like ‘$’,’#’,’!’,’?’

 

The password function provided by Oracle does not allows to say this choice of “three out of four”, so I developed myself and added to the Oracle default function the following code:

--
-- Finally, make sure that the password contains
-- characters from 3 of the four categories
-- UPPERCASE, lowercase, numbers and punctuation characters.
--
IF 
    SIGN(REGEXP_INSTR(new_password, '[[:upper:]]'))+
    SIGN(REGEXP_INSTR(new_password, '[[:lower:]]')) +
    SIGN(REGEXP_INSTR(new_password, '[[:digit:]]')) +
    SIGN(REGEXP_INSTR(new_password, '[[:punct:]]')) < 3
THEN
   RAISE_APPLICATION_ERROR( -20006,'Password must contain characters from three of the following four categories: English uppercase, lowercase, base 10 digits, non-alphabetic characters like ! $ # %');
END IF;


Oracle DBA role granted privileges per version

Below you can find the Oracle defaults
DBA role privileges per database version since Oracle 10.2. It is based on Enterprise Edition with Java and XML installed (on 12.1 you cannot anymore choose what to install).
On Oracle
12.1 there are 3 new roles : CAPTURE_ADMIN, EM_EXPRESS_ALL and
OPTIMIZER_PROCESSING_RATE. Comments about these new roles/privileges can be found below.

admin_option
comment
CAPTURE_ADMIN
yes
>12.1
Provides
the privileges necessary to create and manage privilege analysis policies.
DATAPUMP_EXP_FULL_DATABASE
>11.1
DATAPUMP_IMP_FULL_DATABASE
>11.1
DELETE_CATALOG_ROLE
yes
EM_EXPRESS_ALL
>12.1
Enables
users to connect to Oracle Enterprise Manager (EM) Express and use all the
functionality provided by EM Express (read and write access to all EM Express
features). The EM_EXPRESS_ALL role includes the EM_EXPRESS_BASIC role
EXECUTE_CATALOG_ROLE
yes
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
IMP_FULL_DATABASE
JAVA_ADMIN
JAVA_DEPLOY
OPTIMIZER_PROCESSING_RATE
>12.1
Provides privileges to execute the
GATHER_PROCESSING_RATE, SET_PROCESSING_RATE, and DELETE_PROCESSING_RATE
procedures in the DBMS_STATS package. These procedures manage the
processing rate of a system for automatic degree of parallelism (Auto DOP).
Auto DOP uses these processing rates to determine the optimal degree of
parallelism for a SQL statement.
SCHEDULER_ADMIN
yes
SELECT_CATALOG_ROLE
yes
WM_ADMIN_ROLE
XDBADMIN
XDB_SET_INVOKER
>11.1
XDBWEBSERVICES
<10.2

The query used is:
set pages 20 lines 200
col grantee for a10
col granted_role for a40
select * from dba_role_privs where grantee=’DBA’ order by 1,2;

Oracle 11g default passwords and hashes

Today I was asked to get the list of Oracle default password hashes. Immediately thought about Pete Finigan website which talks about all Oracle security issues.

Quickly I found this page with the list of hashes, but I was not convinced this would work on Oracle 11.1 and 11.2. This because with the latest Oracle versions the information can be in different columns (password, spare4 from sys.user$).

But then looking at MOS, I found the note Script to Check for Default Passwords Being Used for Common Usernames [ID 227010.1] where an interesting snipped is found:

The default password checker no longer exists in 11g, instead there is a
table in the data dictionary SYS.DEFAULT_PWD$ which contains the default
passwords and there is a view DBA_USERS_WITH_DEFPWD which shows which users
on the system are still using default passwords.

So it was now important to see if on my databases all the accounts which still have the defaults are locked (or better, deleted). A simple sql gave the answer:

SQL> select username , account_status
  2  from DBA_USERS_WITH_DEFPWD
  3  natural join dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
APPQOSSYS                      EXPIRED & LOCKED
CTXSYS                         EXPIRED & LOCKED
EXFSYS                         EXPIRED & LOCKED
MDDATA                         EXPIRED & LOCKED
MDSYS                          EXPIRED & LOCKED
OLAPSYS                        EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
ORDDATA                        EXPIRED & LOCKED
ORDPLUGINS                     EXPIRED & LOCKED
ORDSYS                         EXPIRED & LOCKED
OUTLN                          EXPIRED & LOCKED
OWBSYS                         EXPIRED & LOCKED
SCOTT                          EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
SPATIAL_CSW_ADMIN_USR          EXPIRED & LOCKED
SPATIAL_WFS_ADMIN_USR          EXPIRED & LOCKED
WMSYS                          EXPIRED & LOCKED
XDB                            EXPIRED & LOCKED
XS$NULL                        EXPIRED & LOCKED

19 rows selected.