Category Archives: Security

Ad: Der Oracle DBA – Handbuch für die Administration der Oracle Database 12c

Der Oracle DBAThe 2nd revised edition of the Der Oracle DBA will finally be released in a couple of days. Pre-Orders are already possible. It took quite a while until the publication of this release. Work on the second edition began as early as a year ago. This edition is again written in German and published by Hanser.

Who are the authors? Several colleagues of mine at Trivadis Mirko Hotzy, Christian Antognini, Markus Flechtner, Andreas Karlin and Daniel Steiger as well as Andrea Held, Marek Adar, Ronny Egner und Angelika Gallwitz and myself.

For my part, I wrote the chapter on database security. A PDF with the preface, first chapter “Schnelleinstieg”, table of content and index is available as preview. Additional information, in particular on Oracle Database Security will be posted on this side.

DOAG Databank 2016

Just finished my presentation about Enterprise User Security at the DOAG Datenbank 2016 in Düsseldorf. It is about how to set up and use Enterprise User Security with Oracle Unified Directory. The slides are available for download  DOAG__EUS_mit_OUD_Oehrli.pdf. Thanks to Florian I can also offer some, ok one “impressions” from my presentation 🙂 As promised in my presentation, I’ll post a few more information from my engineering and tests on Oracle Unified Directory in the next weeks. All of them will be tagged with Oracle Unified Directory.

Foto_Praesentation

DOAG SIG Security Mannheim 2016

Bit more than two weeks ago I finished my presentation about Security Probleme und deren Risikobewertung at the DOAG SIG Security in Mannheim. It is about Database and Data Classification, Risk Assessment and how Risks could be minimized. The slides are available for download  DOAG_SIG_Security_Security_Wieviel_darf_es_sein.pdf.

Audit Vault and Database Firewall 12.2

Oracle has just released a new major Release of its Oracle Audit Vault and Database Firewall. The new release is immediately available on Oracle’s Software Delivery Cloud. But the OTN website have not been updated. Beside the upgrade of the OS and embedded Oracle Database to 12.1.0.2, Oracle added a bunch of Enterprise-Grade Features. Starting with this Release all new installation will have Oracle Transparent Data Encryption enabled for the audit data.

A few first impressions:

  • The AVDF Documentation has been improved. Beside more detailed setup and configuration information, there is now a concepts guide. The concept guide should be read definitely when planning a new AVDF deployment.
  • The Hardware requirements are slightly increased. A minimum of 220GB disk space is a bit more than for the last release. This will definitely a challenge for my VM setup on my notebook.
  • There are multiple ISO files to download. eg. for the AV Server, FW Server, Utility Files and a ISO with RPM’s for upgrading existing AVDF installations. I will analyze more closely how existing deployments can be upgraded.

Oracle Audit Vault and Database Firewall 12.2 New Features

According to the Release Notes, the following features are available as of 12.2:

  • A backup and restore utility for the Audit Vault Server has been integrated into the product.
  • Audit trails will automatically start when the Audit Vault Agent is restarted or when Oracle AVDF is upgraded.
  • The AVCLI command line utility can be used non-interactively by storing an administrator’s credentials in the AVCLI wallet.
  • You can adjust the number of Audit Vault Agent processing threads on a host to optimize performance.
  • You can configure Oracle Database In-Memory to speed up reports.
  • New (full) installations of Oracle AVDF 12.2 will have all audit data encrypted using Oracle Database Transparent Data Encryption (TDE).
  • When new audit trails collect data that is older than limits set in the retention (archiving) policy, that data will be automatically archived according to the policy.
  • You can change the certificate for the Audit Vault Server and Database Firewall Web UIs.
  • You can register hosts with a host name or a domain name.
  • You can change the logging levels of system components from the Web UI.
  • You can unlock user accounts from the Web UI.
  • New reports have been added including: the Oracle Database Vault report, summary reports, IRS compliance reports, and reports that correlate database audit events with OS users that used su or sudo to execute commands.
  • In the Administrator’s Web UI, the Hosts tab has new Host Monitor details, and added Audit Vault Agent details.
  • The Audit Vault Server’s high availability pairing UI has been improved for usability.
  • Support for IBM AIX secured targets has been added.
  • The Oracle AVDF auditor can create an alert syslog template.
  • The Oracle AVDF auditor can set a schedule for retrieval of audit data and entitlements from Oracle Database.
  • We have added Oracle Audit Vault and Database Firewall Concepts Guide to the documentation library.

References

Some links related to this post.

I’ll start to do some test with the new release of AVDF after the christmas time. So stay tuned…

Release of Audit Vault and Database Firewall 12.1.2 Bundle Patch 7

Today Oracle released the new Bundle Patch for Audit Vault and Database Firewall 12.1.2. The patch can be downloaded as usual on Oracle Metalink as Patchset 21920205 for existing installations. The full installation image for new installations is not yet available on Oracle eDelivery. I guess this will follow in a couple of days. Beside the Bundle Patch, Oracle will also updated the Backup Script to the latest Release. The scripts will be available via My Oracle Support Note 1556200.1

According the readme, the Release 12.1.2 BP7 just contains the October 2015 Patch Set Update for the database. The base platform has been updated with several not precisely specified bug fixes. These include security and stability fixes to Java and the underlying Linux operating system plus the bug fix for the following bug:

Bug Number Description
21395711 ALERT IS RESENT TO SYSLOG WHEN JFWK IS RESTARTED

Since the PSU for October 2015 does includes some critical but fixes for clusterserver (CVSS Rating 10). It is recommended to install this Bundle Patch.

Patch installation

The patch installation is rather simple. Most important is that the following directories have enough free space:

  • 5 GB in /var/lib/oracle
  • 5 GB in /var/tmp
  • 4.5 GB in /root

To install the patch just copy the iso to the AVDF server and run the ruby script. Alternatively you may also mount the iso directly on the Server instead of copy it first. eg. if you run your AVDF in a VM environment. Detailed installation instruction could be taken from the Patch Readme

[root@melete ~]# /bin/mount -oloop,ro /root/avdf-upgrade-12.1.2.7.0.iso /images
[root@melete ~]# yum -c /images/upgrade.repo clean all
Cleaning up Everything

[root@melete ~]# /usr/bin/ruby /images/upgrade.rb
Verifying upgrade preconditions
Mounting boot partition
Removing obsolete files and packages
Applying kernel upgrade
Upgrading system
Remove media and reboot now to fully apply changes.

[root@melete ~]# /sbin/reboot

Broadcast message from root (pts/0) (Mon Nov  9 14:51:46 2015):

The system is going down for reboot NOW!

AVDF Backup

Beside the Bundle Patch, Oracle will also updated the AVDF Backup Script to match the latest Release. The script itself is not yet available, but the new Version will be posted in My Oracle Support Note Audit Vault Server Backup and Restore for Release 12.1.2.5.0 and Prior [1556200.1].

References

Some links related to the Audit Vault and Database Firewall:

WALLET_LOCATION in sqlnet.ora for Container Databases

Recently I’ve setup Oracle Enterprise User Security (EUS) with Oracle Unified Directory (OUD) on my favorite linux test system. Among regular 11.2.0.4 and 12.1.0.2 databases I do also have a 12.1.0.2 Container Database. EUS work like a charm on the regular databases but not on the PDB.

SQL> conn soe
Enter password:
ERROR:
ORA-28305: WALLET_LOCATION IN sqlnet.ora file FOR container DATABASE IS NOT
supported.


Warning: You are no longer connected TO ORACLE.

The error seems to be a bit weird. So fare I’ve explicitly set the wallet location to make sure the wallet it somewhere I decided. I have a shared sqlnet.ora file, where I use $ORACLE_SID in the path for the different instances. An excerpt from my sqlnet.ora file

...
WALLET_LOCATION =
  (SOURCE =
    (METHOD = File)
    (METHOD_DATA = (DIRECTORY = /u00/app/oracle/admin/$ORACLE_SID/wallet)))

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=/u00/app/oracle/admin/$ORACLE_SID/tde_wallet/)))
...

The action described for the Oracle Error Message ORA-28305 is clear. Remove WALLET_LOCATION from sqlnet.ora to use EUS also for Container Databases.

SQL> conn soe
Enter password:
Connected.
SQL> @sousrinf
DATABASE Information
--------------------
- DB_NAME       : TDB12C
- DB_DOMAIN     :
- INSTANCE      : 1
- INSTANCE_NAME     : TDB12C
- SERVER_HOST       : o-sec
-
Authentification Information
----------------------------
- SESSION_USER      : C##SOE
- PROXY_USER        :
- AUTHENTICATION_METHOD : PASSWORD
- IDENTIFICATION_TYPE   : GLOBAL SHARED
- NETWORK_PROTOCOL  :
- OS_USER       : oracle
- AUTHENTICATED_IDENTITY: SOE
- ENTERPRISE_IDENTITY   : cn=soe,cn=Users,dc=trivadistraining,dc=com
-
Other Information
-----------------
- ISDBA         : FALSE
- CLIENT_INFO       :
- PROGRAM       : sqlplus@o-sec (TNS V1-V3)
- MODULE        : SQL*Plus
- IP_ADDRESS        :
- SID           : 39
- SERIAL#       : 47117
- SERVER        : DEDICATED
- TERMINAL      : pts/6

PL/SQL PROCEDURE successfully completed.

The corresponding Oracle Bug 17758886 has been rejected as “not a Bug”. Oracle® Database Net Services Reference 12c Release 1 (12.1) WALLET_LOCATION does not mention PDB’s. There is only some information in the Oracle® Database Reference 12c Release 1 (12.1) Using LDAP_DIRECTORY_ACCESS with PDBs.

Conclusion

It seems, that with PDB’s it is not possible to explicitly set a wallet location. If the default location is not appropriate for your database environment, you have to use soft links use an alternative location for your wallet.

By the way, the wallet for TDE or for Secure External Password Store (SEPS) is not affected. You may still set WALLET_LOCATION for SEPS or ENCRYPTION_WALLET_LOCATION for TDE.

References

Some links related to this topic.

If time permits, I’ll write a few blog post about setting up and configuring EUS with OUD.

Memory Leak in Network Checksum with new SHA-2 Functions

I’ve just stumbled over an issue with the new checksum algorithm introduced with Oracle 12c. It seams that in certain situation the new SHA-2 function cause a memory leak. A search on My Oracle Support revealed that there is a Bug on AIX. See Bug 19451972 MEMEORY LEAKS WITH SHA512, SHA384, SHA256 ENTRIES IN SQLNET.CRYPTO_CHECKSUM and the corresponding Note 1919000.1 SQLPlus 12c Memory usage Grows (Leaks) While Running Long Query.

Test Case

Nevertheless I have similar issues on a Exadata Machine and my Oracle VM. To verify my issue I’ve used a simple test case, where I start a SQL*Plus script which does:

  1. connect as SCOTT
  2. query some views eg. v$session_connect_info
  3. wait a few seconds
  4. query some views eg. v$session_connect_info
  5. start over with step 1

Since SQL*Plus does not support any loop I just use cat to generate a script with a bunch of connect and SELECT. For this I used the following Template (connect_scott_template.sql):

CONNECT scott/tiger@TDB12A
ALTER SESSION SET nls_date_format='DD.MM.YYYY HH24:MI:SS';
SELECT sysdate FROM dual;
SELECT sid, osuser, authentication_type, network_service_banner
FROM v$session_connect_info
WHERE sid=(SELECT sys_context('userenv','sid') FROM dual);
EXEC DBMS_LOCK.SLEEP(10);

Based on this template I’ve created my SQL script with a for loop.

for i in {1..720}; do cat connect_scott_template.sql >>connect_scott.sql ; done

If the script runs for a couple of minutes / hour you will see that RSS (real memory size / resident set size) does increase when network integrity check is enabled with SHA512. It remains on the same level for the same test without network integrity check. Below you see the output of my bash session history (with minor optimization for the web 😉 ):

cd /u00/app/oracle/admin/TDB12A/adhoc/nocksum
export TNS_ADMIN=$PWD

nohup sqlplus /nolog @connect_scott.sql &

PID=17185

while [[ $(ps $PID|wc -l) > 1 ]]
do
echo $(date "+%Y.%m.%d %H:%M:%S $(ps u $PID|tail -1)") >>connect_scott_nocksum_`date '+%Y%m%d'`.log
sleep 30
done

I’ve started sqlplus and the script with nohup. To collect the rss information I’ve just created a while loop and pipe the output of ps to a log file. For the test with checksum typ SHA512 used an alternative TNS_ADMIN directory with a different sqlnet.ora. My Test did run for about two hours. I’ve but the collected data in an Excel sheet to create the following chart. You see, that both SQL*Plus process require more real memory over time. Nevertheless the required memory for SQL*Plus with SHA512 is definitely higher.

MemoryLeak

Files and References

Below you find the scripts mentioned above as well some MOS references:

Conclusion

It seems that this Bug is a bit more generic than expected. Since the new SHA function would anyway just work for pure 12c environment, it is acceptable to use the old SHA1 Hash until this Bug is fixed.