Today I held my presentation about Oracle security best practice “Oracle passwords, but secure!” at the virtual UKOUG event. Unfortunately, this year the beautiful view of Brighton beach and the active exchange with colleagues was missing. Ok, on the other hand I was able to enjoy the first snow in Switzerland with my children. 😊
The following blog post is a summary of my presentation with some examples, notes, references and slides.
Oracle Password Hashes
The different Oracle Database releases do provide various password verifiers. Although the older password verifiers are no longer state of the art, they are still used relatively frequently. It is therefore essential to take the appropriate measures to make password-based authentication secure. Oracle currently offers the following password hash functions:
Oracle 10g Hash Function based on DES and an Oracle specific algorithm. It is case insensitive and does use a weak password salt i.e. the username is used as salt.
MD5 based Hash Function used for digest authentication in XDB
Oracle 11g Hash Function based on the SHA1 hash algorithm. But since 2005 SHA1 is no longer considered as safe. The hash function does supports case sensitive and multibyte character passwords.
Oracle 12c Hash Function based on a de-optimised algorithm involving PBKDF2 and SHA-512. It supports case sensitive and multibyte character passwords.
The different password verifiers can be controlled by SQLNET.ALLOWED_LOGON_VERSION_SERVER respectively SQLNET.ALLOWED_LOGON_VERSION_CLIENT or by setting the passwords explicitly using ALTER USER ... IDENTIFIED BY VALUES.
Create different users with different password verifiers
CREATE USER test_10g IDENTIFIED BY VALUES 'AF310E4D20D06950';
CREATE USER test_11g IDENTIFIED BY VALUES 'S:6702B83E88D277BFC378AD6B22DD1AE01895A254470F8124A9D3C5347056';
CREATE USER test_12c IDENTIFIED BY VALUES 'T:45738A7B75C9E31ED0C533BCF4931084658A143FD7CF826B980A88EA6C4F0BE66C28DA7085BCAE386723029BA967DC4F45E9C146F6FA7C22E44BA2C1BD2F56F8C22291D417E26D4B810003F3F055EDFF';
CREATE USER test_all IDENTIFIED BY Welcome1;
In DBA_USERS you will see the different password versions
SET LINESIZE 160 PAGESIZE 200
COL username FOR a10
COL password_versions FOR a20
SELECT username, password_versions
FROM dba_users WHERE username LIKE 'TEST_%';
USERNAME PASSWORD_VERSIONS
----------- --------------------
TEST_10G 10G
TEST_11G 11G
TEST_ALL 10G 11G 12C
TEST_12C 12C
Or in USER$ you can find the corresponding hashes:
SET LINESIZE 160 PAGESIZE 200
COL name FOR a10
COL password FOR a16
COL spare4 FOR a64
SELECT name,password,spare4
FROM user$ WHERE name LIKE 'TEST_%' ORDER BY 1;
NAME PASSWORD SPARE4
---------- ---------------- ----------------------------------------------------------------
TEST_10G AF310E4D20D06950
TEST_11G S:6702B83E88D277BFC378AD6B22DD1AE01895A254470F8124A9D3C5347056
TEST_12C T:45738A7B75C9E31ED0C533BCF4931084658A143FD7CF826B980A88EA6C4F0B
E66C28DA7085BCAE386723029BA967DC4F45E9C146F6FA7C22E44BA2C1BD2F56
F8C22291D417E26D4B810003F3F055EDFF
TEST_ALL 4932A1B4C59EC3D0 S:ABF25107166264C8EAFE72BF02152DE17000F359CB5BAF21A6AF41477633;T
:62FEE108652A56D940813F54EC72D1494ACAD99F2BBDD0A578BF1F97FAB4A7E
B468A98B6B553E460DE21E57F6C35A930DEE027D20B33ED13D56EA0ECACB1CEA
94EEC8AC389561346052BB0BFF2C06647
Manually create a Oracle 10g password verifier:
SQL> @create_password_hash.sql system ieShae0
Username : system
Password : ieShae0
Hash : 0AD56CF5F1CB8D2A
SQL : alter user system identified by values '0AD56CF5F1CB8D2A';
PL/SQL procedure successfully completed.
Testing the Password Verifier
There are a couple of possibilities and tools to “verify” password hashes. Among the best known are the tools Hashcat and John the Ripper. These tools doe support a wide range of hashes as well attack methods. Below you find an example of a brute force attack for the Oracle hash we created above.
--increment will start to brute force with shorter length e.g 4 characters
–-custom-charset1 to define numbers and characters
Here are a few good practices on Oracle passwords.
Keep your Oracle Clients and Server up to date. Stay updated by following Critical Patch Updates, Security Alerts and Bulletins. Install security fixes in a reasonable time frame
Consider using strong Authentication like Kerberos and SSL based authentication.
Don’t use legacy password verifier
Use Oracle password file version 12.2
Explicitly configure ALLOWED_LOGON_VERSION_SERVER to 12a and exclusively use 12c hash values
Start using PBKDF2 SHA-512 for directory-based password authentication with EUS and CMU
Revise your password policies
NIST, CIS, STIG and other standards are continuously adjusted.
Does the complexity rule still make sense or does it just reduce the amount of possibilities.
User awareness training. Make sure your user know the principle of good and bad Use of phase phrase rather than password
Since I’m always short of time for a longer blog post, I’ll just try a short one. Intended as a mini-series, I will show different configuration examples for Oracle Enterprise User Security. Today I’ll start with the configuration of EUS based proxy privileges. The environment I use is DOE, my Docker based Oracle Engineering environment. In particular the EUS configuration. For more information, see the corresponding GitHub repository oehrlis/doe respectively in the folder eus for the EUS specific environment.
Background
Database proxy privileges are used relatively often to give certain users rights to access a different schema. The user authenticates himself with his credentials and becomes a proxy user in the database. Below an example where the user RMAN, gets access to a different schema, specifically an other RMAN catalog schema (see also blog post about SEPS and RMAN).
CREATE USER rman IDENTIFIED BY welcome1;
CREATE USER rman19000 NO AUTHENTICATION QUOTA UNLIMITED ON rman_data;
GRANT RECOVERY_CATALOG_OWNER TO rman19000;
ALTER USER rman19000 GRANT CONNECT THROUGH rman;
ALTER USER rman19000 DEFAULT TABLESPACE rman_data;
The following users were created
RMAN1900 is the schema owner for an Oracle 19c RMAN catalog stored in the tablespace RMAN_DATA. The user is created without any authentication but with a proxy privilege for the user RMAN.
RMAN is the user which will be used to connect to the catalog. There are other catalogs as well but not shown in this example
SQL> connect rman[RMAN19000]/welcome1@CATALOG
Connected.
SQL> show user
USER is "RMAN19000"
SQL> SELECT sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;
PROXY_USER SESSION_USER
---------- ---------------
RMAN RMAN19000D
With pure database authentication or authorisation, the configuration of proxy users is easy. With Enterprise User Security, proxy privileges are no longer managed in the database but in the directory. Let’s take a look at that.
Database Configuration
For Enterprise User Security based proxy privileges, only ENTERPRISE USERS is specified in the database. The rest is done in the OracleContext of the directory. See also ALTER USER in Oracle® Database SQL Language Reference 19c.
ALTER USER scott GRANT CONNECT THROUGH ENTERPRISE USERS;
Enterprise User Security Configuration
The configuration can be either done via Oracle Enterprise Manager Cloud Control as documented in Oracle® Database Enterprise User Security Administrator’s Guide 19c or with the command line utility eusm. I prefer the command line utility as I often do not have an OEM by hand.
Let’s test the permissions and connect as user KING.
Regular connection to the database as schema owner SCOTT.
SQL> connect SCOTT/tiger@TEUS01
Connected.
SQL> show user
USER is "SCOTT"
SQL> select sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;
PROXY_USER SESSION_USER
--------------- ---------------
SCOTT
Regular connection to the database as KING.
SQL> connect king/welcome1@TEUS01
Connected.
SQL> show user
USER is "KING"
SQL> select sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;
PROXY_USER SESSION_USER
--------------- ---------------
KING
Proxy connection to the database
SQL> connect king[SCOTT]/welcome1@TEUS01
Connected.
SQL> show user
USER is "SCOTT"
SQL> select sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;
PROXY_USER SESSION_USER
--------------- ---------------
KING SCOTT
Conclusion
Configuration of proxy permissions in connection with Oracle Enterprise User Security is not as complicated as you might think. It is also useful if shared global users need access to certain schemas. For example, a power user is allowed to access the application schema.
When it comes to the conception and implementation of a central user administration of Oracle databases, authentication is one of the central topics. Often there is a need for integration with an existing directory service or IAM solution. Whereby usually MS Active Directory is involved. But Oracle Databases and MS Active Directories are not yet best friends. In this blog post we will explain why this is so, with a focus on authentication.
A few Basics
Oracle Databases does provide a couple of authentication methods. This includes among others the following:
Password authentication
OS authentication
Kerberos authentication
SSL authentication
All methods have their advantages and disadvantages and thus their justification. But now let’s speak about password authentication. This works basically always the same way, no matter if database or directory based. The picture below shows the schematic diagram of the password authentication process.
Schematic flow of password authentication
The user does send the logon request with its username to the database.
The database generates a session key, to encrypt communication.
The client generates the password hash and sends it encrypted to the DB server.
The database now compares the password hashes.
either the hash from USER$
or the hash from the directory server
The key aspect is that the database always verifies the password hashes. Either with the hash in the database or, in case of directory-based authentication, with the hash from the directory. The process is used when using Oracle Centrally Managed Users (CMU) but also with Oracle Enterprise User Security in combination with an Oracle Directory e.g. Oracle Unified Directory EUS AD Proxy. In case of a regular LDAP directory, the hash is read from userPassword or another attribute. However, this is not possible in MS Active Directory, where passwords are stored internally in the Security Account Manager (SAM) and cannot be read directly. This is one of the reasons why Active Directory is not fully LDAP v3 compliant. But that is an other story 😉
This is now the moment where the Oracle password filter comes into play. Microsoft does provide a functionality within Windows called password filter. These filters provide a way to implement password policies and change notification. When a password change request is made, the Local Security Authority (LSA) calls the password filters registered on the system. Each password filter is called twice: first to validate the new password and then, after all filters have validated the new password, to notify the filters that the change has been made. The following illustration shows this process.
Password filter and change notification
Oracle Password Filter
The Oracle password filter solves relatively simply the problem that the hash cannot be read. The filter uses the password change notification and stores the password accordingly in an additional LDAP attribute. The database or directory server on the other hand is then able to read the user password hash. Oracle Databases and Active Directory starts to like each other 🤓. But usually Windows or Security Admins are not so happy any more. The fact that a foreign DLL has to be installed on the domain controller sometimes causes headaches or just endless discussions…
The latest version of the password filter is delivered as EXE file opwdintg.exe. It is part of any Oracle Database binaries as of release 18c. Older version of Oracle Database, Oracle Internet Directory and Oracle Unified Directory do also include the password filter in an other form e.g. setup.exe or a jar file. Nevertheless it is crucial, that you get the latest version which is right now part of Oracle Database 19.8.0.0. This is also the valid version when you use OUD or OID, see MOS Note 2640135.1How to Get the Latest oidpwdcn.dll (New Name orapwdfltr.dll). Alternatively you can also download a generic patch 23191994 for fusion middleware.
But what exactly happens when you install the Oracle password filter? Oracle performs the following steps during installation:
Add an Active Directory schema extension for an additional user attribute orcleCommonAttribute. Once installed a schema extension can not be removed any more.
Create some generic groups to control the password filter plugin. The filter will only update the orcleCommonAttribute attribute for users which are part directly or indirectly of one of the group.
ORA_VFR_MD5 is required when the Oracle Database WebDAV client is used
ORA_VFR_11G enables the use of the Oracle Database 11G password verifier
ORA_VFR_12C enables the use of the Oracle Database 12C password verifier
Install the Oracle password filter DLL orapwdfltr.dll. This requires a reboot of the domain controller.
The following screenshots show the installation of the Oracle password filer.
Schema Extension done by the Oracle Password Filter
DLL Installation by the Oracle Password Filter
After a reboot the installation of the Oracle password filter is finished. Now let’s see what’s new there. First we review the AD schema change. This can be done by starting the Microsoft Management Console (MMC) and open the Active Directory Schema Snap-In. See the old documentation install the Schema Snap-In if the snap-in is not available. The following screenshot does show the details about the new attribute.
New orcleCommonAttribute
In the registry we see under LSA an additional entry for the notification packages. orapwdfltr the name of the DLL installed on the domain server.
New LSA notification packages
And finally the new groups and the new attribute orcleCommonAttribute.
New generic Oracle groups
Although the attribute orcleCommonAttribute in the picture below does only get propagated after a password reset. The user KING is part of the group Trivadis LAB Users. This group itself is member of ORA_VFR_11G.
Attributes of user KING
Yeah, but it’s an Oracle tool…
In one of my many conversations with customers about these password filters I was asked if they could examine the source code. Mmm, no! It is quite common that neither Oracle nor Microsoft publish their source code. In this case Oracle uses an API or functionality defined and documented by Microsoft. But this does not convince everyone. That’s why I have tried to investigate this in detail. One of my first attempts was a test if I can decompile the DLL. This would be possible if it is written in .net or something similar, but not with C or C++. You can use an online disassembler, but the result will not help you.
Analysis of the executable installation file opwdintg.exe with exiftool, reveal that it is only a self extracting cabinet.
You can invoke the executable with two additional parameters C and T to extract the content into the directory specified with T.
c:\vagrant>opwdintg.exe /C /T:c:\vagrant\opwdintg
In the directory you will find three files:
instpflt.bat Batch file used to install the password filter.
etadschm.bat Batch file used to do the schema extension for orcleCommonAttribute and create the 3 AD groups.
orapwdfltr.dll the Oracle password filter dll itself.
Even if you cannot decompile orapwdfltr.dll, you can still examine the batch files. As expected, the batch files do exactly what we have already verified graphically above. Schema extension, create groups and register Oracle password filter.
With pev, a PE file analysis toolkit, we can check other stuff like the functions exported by the DLL. As you can see in the output below, the functions correspond to Microsoft’s specifications for password filters. An indication that the DLL does what it should. However, pev provides other tools to analyse the DLL, hashes, import functions etc. But we will skip that at this point.
readpe --exports orapwdfltr.dll
Exported functions
Library
Name: orapwdfltr.dll
Functions
Function
Ordinal: 1
Address: 0x1080
Name: InitializeChangeNotify
Function
Ordinal: 2
Address: 0x2ea0
Name: PasswordChangeNotify
Function
Ordinal: 3
Address: 0x1080
Name: PasswordFilter
A few words about Security
But what about security? There are basically two aspects. First, the fact that the DLL is a rather critical component. There are known malware that exploit exactly this method to get the passwords. It is therefore a best practice to configure LSA security to allow only signed DLLs for LSA. Ok, besides that you should also know which DLL you have installed and why. But here we are at the point where it gets a bit difficult. Oracle has forgotten to sign orapwdfltr.dll in the past. Therefore, if LSA security is enabled the password filter will not work. See also MOS note 2612535.1 or 2616566.1. Among other things, Oracle has proposed to turn off the LSA security. Certainly not the way to go. But luckily there is already a bug 31134430 and patch 23191994 available for this issue. The fix does include a signed version of the orapwdfltr.dll, as you can see in the following code block.
signtool.exe verify /pa /v orapwdfltr.dll
Verifying: orapwdfltr.dll
Signature Index: 0 (Primary Signature)
Hash of file (sha256): 2A14712107D424FF5577EF5C3D111CF66DB40F6226047ADC4F31389D69F437EB
Signing Certificate Chain:
Issued to: VeriSign Class 3 Public Primary Certification Authority - G5
Issued by: VeriSign Class 3 Public Primary Certification Authority - G5
Expires: Wed Jul 16 16:59:59 2036
SHA1 hash: 4EB6D578499B1CCF5F581EAD56BE3D9B6744A5E5
Issued to: Symantec Class 3 Extended Validation Code Signing CA - G2
Issued by: VeriSign Class 3 Public Primary Certification Authority - G5
Expires: Sun Mar 03 16:59:59 2024
SHA1 hash: 5B8F88C80A73D35F76CD412A9E74E916594DFA67
Issued to: Oracle America Inc.
Issued by: Symantec Class 3 Extended Validation Code Signing CA - G2
Expires: Wed Jan 27 16:59:59 2021
SHA1 hash: 1CB08E9B70B917E64407A4F2665799D58B171F89
The signature is timestamped: Wed Apr 22 18:33:05 2020
Timestamp Verified by:
Issued to: DigiCert Assured ID Root CA
Issued by: DigiCert Assured ID Root CA
Expires: Sun Nov 09 17:00:00 2031
SHA1 hash: 0563B8630D62D75ABBC8AB1E4BDFB5A899B24D43
Issued to: DigiCert SHA2 Assured ID Timestamping CA
Issued by: DigiCert Assured ID Root CA
Expires: Tue Jan 07 05:00:00 2031
SHA1 hash: 3BA63A6E4841355772DEBEF9CDCF4D5AF353A297
Issued to: TIMESTAMP-SHA256-2019-10-15
Issued by: DigiCert SHA2 Assured ID Timestamping CA
Expires: Wed Oct 16 17:00:00 2030
SHA1 hash: 0325BD505EDA96302DC22F4FA01E4C28BE2834C5
Successfully verified: orapwdfltr.dll
Number of files successfully Verified: 1
Number of warnings: 0
Number of errors: 0
Alternatively you can also check the windows property of orapwdfltr.dll.
Properties of orapwdfltr.dll
The other security challenge is the password hash itself. In a regular LDAP, ACIs are usually defined to restrict access to password attributes. However, no ACIs are defined when installing the Oracle password filter. It is therefore strongly recommended to restrict access to this attribute. Generally only the Oracle service accounts, which are used to setup Oracle AD integration, has to read it.
Conclusion
When my workmate Martin Berger published his blog post about the issue with LSA and the password filter, there was no official solution beside disabling LSA security. Fortunately it is a bit better in the meantime. The bug fix did found its way in the latest release of Oracle Database 19c (19.8.0.0) and in the generic fusion middleware patch 23191994. This official signed version of the password filter can be used for either Oracle Centrally Managed Users (CMU), Oracle Enterprise User Security (EUS) or Oracle Unified Directory DIP. It is a fact that this password filter means a change on the domain server. Every change represents a potential risk. Nevertheless, this change is comprehensible and is, according to Microsoft, a documented procedure. By carefully assigning the Oracle groups (ORA_VFR_11g, ORA_VFR_11C, etc), you can ensure that only those users who need the hash in orcleCommonAttribute have set it. It is also recommended to define ACIs to limit access to orcleCommonAttribute restrictively.
SSL and Kerberos authentication are basically secure methods. Additionally these authentication methods allow Single Sign On. Unfortunately, practice shows that many tools cannot handle this. Password authentication on the other hand offers greater flexibility. The Oracle password filter is not bad nor dangerous. In my humble opinion it is worth to consider this solution.
Oracle Support Note 2640135.1OUD 12c – How to Get the Latest oidpwdcn.dll (New Name orapwdfltr.dll)
Oracle Support Note 2612535.1EUS Login Failure of AD Users Proxied by OUD: LdapErr: DSID-0C090CE0, comment: Error in attribute conversion operation
Oracle Support Note 2616566.1OUD 11g – OIDPWDCN.DLL Plug-in Fails On AD 2012 R2 With Error “The password notification DLL oidpwdcn failed to load with error 577”
Oracle Bug 31134430 need to have orapwdfltr.dll signed by Microsoft.
Oracle Patch 23191994 “signed” version of the oidpwdcn.dll for Oracle Unified Directory.
One of my biggest problems when I started to look into Kerberos Authentication, Oracle Centrally Managed Users as well Oracle Enterprise User Security was the availability of an Active Directory to setup test cases. It is usually not the core business of an Oracle DBA to configure an Active Directory server. 🙂 Using the productive AD is generally not a good choice either. One day I set up a Virtualbox VM with Windows 2016 and Active Directory. Great, but the VM went the way that many test VMs go and got screwed up. Set up a VM from scratch is cumbersome and time consuming. In particular when you would have to do it regular. Just doing backup and snapshot does work, but does not help to share the VMs with colleagues. Besides that the disk space on my notebook is limited. Then I did started to look into Vagrant. Then I did started to look into Vagrant. Not only for Oracle Database VMs but also for my Active Directory server. I have successfully used this environment in the past for several lectures and trainings at SOUG, DOAG and AOUG.
The aim of this blog post is to introduce the Trivadis LAB environment. In particular, the vagrant based setup of the Windows Server for Active Directory. I will discuss some basic steps to create such a VM, but also a few configuration details so that you can use it in your own engineering project. I myself use the Windows VM together with DB VMs (oehrlis/trivadislabs.com) but also with my Docker based engineering environment (oehrlis/doe). So lets get ready to rumble…
Trivadis LAB Environment
As you can see in the following figure, the entire Trivadis LAB environment contains VMs for Oracle databases and Oracle Unified Directory in addition to the VM for Active Directory. However, these are not included in this blog post. We just focus on Windows.
Trivadis LAB Environment
To allow a more or less practical use of the directory, a simple structure was created for the fictitious company Trivadis LAB. The following graphic shows the organisation chart including departments and employees for Trivadis LAB. All the users listed can be used as test users. The login name corresponds to the last name in lower case. The password for all users is set to a default password (see configuration files)
Organisation Chart Trivadis LAB
The fictitious company has the following departments:
The first time you provision a Windows Server VM, the basis Vagrant Box is loaded from the Vagrant Cloud, which may take a while. If preferred, you can download this VM in advance with Vagrant. Enclosed the example for Windows Server 2019
Setup of the Vagrant VM is straight forward. You have to decide if you would like to setup a Windows Server 2019 (win2019ad) or Windows Server 2016 (win2019ad). The steps below are for Windows Server 2019.
Clone this repository git clone https://github.com/oehrlis/trivadislabs.com
Adjust configuration in trivadislabs.com/common/config/vagrant.yml
Change into the trivadislabs.com/win2019ad directory
Run vagrant up
The first time you run this it will provision everything and may take a while (20-40min). Ensure you have a good internet connection as the scripts will download a couple of tools via Chocolatey.
The installation can be customised, if desired (see below).
Connect to the VM using vagrant rdp as vagrant or administrator user. Default password is either store in vagrant.yml or default_pwd_windows.txt.
If necessary, run the Windows Update manually.
You can shut down the VM via the usual vagrant halt and then start it up again via vagrant up
Enclosed an excerpt from the vagrant up command:
user@host:~/trivadislabs.com/win2019ad/ [ic19300] time vagrant up
Bringing machine 'win2019ad' up with 'virtualbox' provider...
==> win2019ad: Importing base box 'StefanScherer/windows_2019'...
==> win2019ad: Matching MAC address for NAT networking...
==> win2019ad: Checking if box 'StefanScherer/windows_2019' version '2020.07.17' is up to date...
==> win2019ad: Setting the name of the VM: win2019ad.trivadislabs.com
==> win2019ad: Clearing any previously set network interfaces...
==> win2019ad: Preparing network interfaces based on configuration...
...
win2019ad: This Computer SID is S-1-5-21-1473420208-2468469534-
win2019ad: =========================================================
win2019ad: Successfully finish setup AD VM
win2019ad: Host : win2019ad
win2019ad: Domain : trivadislabs.com
win2019ad: =========================================================
real 34m7.109s
user 1m4.814s
sys 0m32.222s
Configuration
The Vagrantfile is preconfigured for the Trivadis LAB domain. I.e. host name, domain name, user etc. are predefined. Generally there is no need to adjust the Vagrant file itself. To ensure that all VMs in Trivadis LAB always work with the same configurations, a central YAML file is used for Vagrant. The file is locate in common/config/vagrant.yml. For the Window VM you find the following configuration:
# Configuration valid for all VM's
common:
default_password: LAB01schulung
domain_name: trivadislabs.com
company_name: Trivadis LAB
gateway: 10.0.0.1
dns: 10.0.0.4
public_dns1: 8.8.8.8
public_dns2: 4.4.4.4
# Configuration valid for Windows 2019 AD server
win2019ad:
box: StefanScherer/windows_2019
vm_name: win2019ad
domain_mode: WinThreshold
people_ou_name: People
groups_ou_name: Groups
mem_size: 2048
cpus: 1
public_ip: 10.0.0.4
A short explanation of the settings and possibilities:
common YAML common section
win2019ad YAML section for Windows Server 2019
default_password Password used to initialise the AD Server. It is recommended to change this as soon as possible. Default is LAB01schulung
domain_name Network domain name for the environment. Depending on this, the directory tree in AD and the AD domain itself is named. Default value is trivadislabs.com
company_name Company Name for the Trivadis LAB. This is used to name the AD groups.
network configuration for gateway (10.0.0.1), dns (10.0.0.4), public_dns1 (8.8.8.8), public_dns2 (4.4.4.4)
box the base Vagrant box used to setup the VM
vm_name Name of the VM win2019ad
domain_mode Active Directory domain mode
people_ou_name Active Directory OU for the users
groups_ou_name Active Directory OU for the groups
mem_size Memory size for the VM
cpus Number of CPUs for the VM
public_ip Public IP address of the VM
The different scripts use the settings for initialising the AD server. So it is possible to change the AD domain, host domain name etc.
Access
There are several option to access the VM. You either can directly start the Console on Virtualbox. If you run the VM in headless mode you will either run vagrant rdp or use Microsoft Remote Desktop directly. Running vagrant rdp finally starts only Microsoft Remote Desktop as well.
Remote Desktop Configuration for win2019adMicrosoft Remote Desktop Session on win2019ad
From here the environment is ready for all kinds of engineering. Depending on what you want to do, you may need some customisation, e.g. creating service users, generating keytab files, installing the Oracle password filter etc.
Behind the scenes
Setting up ad Active Directory server cannot be done in one swing. You need several steps and server reboot. Therefore the configuration is divided into different PowerShell scripts. The following list gives an overview of the different scripts. They are executed in the listed sequence. After the script 22_install_chocolatey.ps1 and 27_config_cmu.ps1 the VM is restarted by Vagrant.
21_install_ad.ps1 PowerShell script to install Active Directory server role
27_config_cmu.ps1 PowerShell script to configure CMU on Active Directory
28_config_misc.ps1 PowerShell script to configure NAT zone records for AD domain
29_sum_up_ad.ps1 PowerShell script to display a summary of Active Directory Domain and install Windows updates
reset_ad_users.ps1 PowerShell script to reset all domain user password. This script is not executed automatically.
Conclusion
It took some effort to prepare the environment. Nevertheless, the effort was worth it. I was quite happy to have a Test AD at hand in some projects. Testing Oracle Enterprise User Security, Oracle Centrally Manage Users or Kerberos is not possible without an Active Directory server. In particular if you like to test the Oracle Passwort Filter for Active Directory. But this will be an other blog post.
It is way too long ago since my last blog post. These were or are busy weeks for me. Any way, I finally found some time to start writing a blog post about a special setup for kerberos authentication of Oracle databases. It is about configuring kerberos authentication for multiple database servers with only one active directory account and corresponding Service Priciple Names (SPN). Additionally there is an challenge, that the keytab file should only be created with ktutil directly on the DB server. Access to a Windows server and use of ktpass.exe is not possible. I did setup a nice test case on a couple of compute instances on Oracle cloud infrastructure. During the verification of the test setup I had to realise that the kerberos authentication does not work as planned. Until now it is not possible to create a keytab file with ktutil that I can use successfully with Active Directory. The same kerberos configuration with a keytab create with ktpass.exe on the AD server does work. But that’s on other story…
The aim of this blog post is to sum up a couple of troubleshooting actions I came across. Kerberos itself is around since a couple of decades. Therefore you will find various documentation, RFC, etc. But it is not always easy to recognise what is still relevant and what not. Mainly because the implementation of Kerberos at both Oracle and Microsoft is not necessarily the same or 100% MIT Kerberos compliant. The fact that there are different versions of Oracle, MS AD and Kerberos makes it even more exciting 🙂
Basics
A basic requirement for Kerberos is the network and time configuration.
Problem:okinit does fail with clock skew too great
Cause: The systems involved must be synchronous in terms of system time e.g. using a NTP service to configure date / time. If the system times differ to much you will receive this error when using okinit.
Solution: Configure proper system times using NTP service. Small time drifts can be covered by setting SQLNET.KERBEROS5_CLOCKSKEW=300 in sqlnet.ora
Problem: Miscellaneous errors due to wrong / missing network configuration.
Cause: Using CNAME rather A records, no DNS configuration, no revers lookkup etc
Solution: Configure proper DNS name resolution for database service as well MS active directory service. Each system must be able to be resolved by name or IP address. Kerberos will look for service principle names based on A records.
oracle@db:/u00/app/oracle/network/admin/ [TDB190S] cd
oracle@db:~/ [TDB190S] nslookup win2016ad.trivadislabs.com
Server: 10.0.1.4
Address: 10.0.1.4#53
Name: win2016ad.trivadislabs.com
Address: 10.0.1.4
oracle@db:~/ [TDB190S] nslookup 10.0.1.4
4.1.0.10.in-addr.arpa name = win2016ad.trivadislabs.com.
oracle@db:~/ [TDB190S] nslookup db
Server: 10.0.1.4
Address: 10.0.1.4#53
db.trivadislabs.com canonical name = ol7db19.trivadislabs.com.
Name: ol7db19.trivadislabs.com
Address: 10.0.1.6
oracle@db:~/ [TDB190S] nslookup 10.0.1.6
6.1.0.10.in-addr.arpa name = ol7db19.trivadislabs.com.
Trace and Log Files
Kerberos Trace
As of Oracle 12c release 2 it is possible to enable kerberos tracing by setting KRB5_TRACE to a trace file. This logs the Kerberos calls in the current session.
export KRB5_TRACE=/u00/app/oracle/network/admin/kerberos.trc
oracle@db:~/ [TDB190S] okinit king
Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 08-JUN-2020 20:54:16
Copyright (c) 1996, 2019 Oracle. All rights reserved.
Configuration file : /u00/app/oracle/network/admin/krb5.conf.
Password for king@TRIVADISLABS.COM:
A sample output of a kerberos trace file:
oracle@db:~/ [TDB190S] head -10 /u00/app/oracle/network/admin/kerberos.trc
[5645] 1591649656.590082: Getting initial credentials for king@TRIVADISLABS.COM
[5645] 1591649656.590084: Sending unauthenticated request
[5645] 1591649656.590085: Sending request (199 bytes) to TRIVADISLABS.COM
[5645] 1591649656.590086: Resolving hostname ad.trivadislabs.com
[5645] 1591649656.590087: Sending initial UDP request to dgram 10.0.1.4:88
[5645] 1591649656.590088: Received answer (196 bytes) from dgram 10.0.1.4:88
[5645] 1591649656.590089: Sending DNS URI query for _kerberos.TRIVADISLABS.COM.
[5645] 1591649656.590090: No URI records found
[5645] 1591649656.590091: Sending DNS SRV query for _kerberos-master._udp.TRIVADISLABS.COM.
[5645] 1591649656.590092: Sending DNS SRV query for _kerberos-master._tcp.TRIVADISLABS.COM.
Oracle SQLNet tracing
For Kerberos troubleshooting with Oracle SQLNet it is helpful to disable ADR tracing. Not mandatory, but makes life a bit easier. Set DIAG_ADR_ENABLED in sqlnet.ora to OFF.
DIAG_ADR_ENABLED=OFF
Before KRB5_TRACE was available, okinit calls could only be traced with sqlnet.ora and TRACE_LEVEL_OKINIT. See also MOS note 162668.1. The parameter does not make sense when you already use KRB5_TRACE.
For further analysis you usually have to switch on SQLNet Tracing. Don’t even thing about setting an other level than SUPPORT (16). Kerberos calls are only available with the highest level.
The errors in the trace files are not always obvious. You can find a few infos and hint in MOS note 185897.1. But most of the time there is no way around searching for the corresponding error or function call in Oracle Support or the search engine of choice.
Network Tracing
The next level is to trace the network calls. Depending on the environment you can directly use Wireshark. But it is much easier to first create a network dump via command line and to analyse it later using Wireshark. I use tcpdump on my OCI environment and download the trace file to my MacBook, where I then use Wireshark.
Get the available interfaces:
sudo tcpdump -D
1.nflog (Linux netfilter log (NFLOG) interface)
2.nfqueue (Linux netfilter queue (NFQUEUE) interface)
3.usbmon1 (USB bus number 1)
4.ens3
5.any (Pseudo-device that captures on all interfaces)
6.lo [Loopback]
Keep it running until while testing the kerberos authentication. As soon as done copy the trace file to the client an open it using Wireshark. The following picture does show a trace dump where the kerberos protocol has been selected.
A part of the kerberos packet is encrypted and not visible as you can see in following picture.
Kerberos does use the service’s secret key to encrypt these messages. You can import the keytab file into Wireshark to decrypt the messages. For this purpose the keytab file must be specified in Wireshark in the preferences. Click Edit > Preferences > Protocols > KRB5.
You now see the message content of the packet. This is in particular useful when you have to analyse issues related to ticket size, missing groups etc.
Conclusion
Unfortunately my Kerberos problem is still not solved. Nevertheless I did get the opportunity to practice a couple of Kerberos tracing methods. The introduction of KRB5_TRACE did simplify tracing a bit, but in most case you still have to use SQLNet or network tracing to find the root cause of you Kerberos problem. A direct solution is unfortunately not always found with tracing. At least you have all the relevant information to search My Oracle Support, open a service request or try your luck at googling for a solution.
Today I did have my first presentation at the UKOUG TechFest 2019 in Brighton. Looking back it was a great day with many interesting lectures and good conversations with colleagues and partners. After a long and exhausting day I’ll take a few minutes to sum up my presentation about the PDB isolation and Security.
When you start using Oracle Multitenant, it makes sense to consider a few specific security topics. In general, the same security principles apply to Oracle Multitenant databases as to regular single tenant databases. But depending on the purpose of PDBs, it will be relatively important to implement a few security measures which are beyond of the scope of general database security.
PDB Isolation and Security at a glance
In operation, Oracle container databases use shared resources. These include a backup LAN, management LAN and other system resources. On the other hand, individual Oracle features do access system resources as user oracle. In a common environment, this is basically a security risk. In a DBAAS environment, these can be the following risks.
PDB admin use privilege escalation.
Excessive use of shared resources.
Access sensitive data via shared resources e.g. backup or management LAN.
Break out of PDB and get OS access as oracle.
Gain access to the root container (cdb$root)
Gain access to other PDBs.
Gain access to the network.
Use of critical features like.
Administration features
Oracle JVM
DBMS_SCHEDULER
External table pre-processor
In principle, the owner of each PDB has DBA privileges or equivalent privileges on his PDB. Various measures are taken to ensure security and separation from the operating system and other PDBs. This includes the following measures:
Provide DBA role respectively customized DBA role to PDB_ADMIN
Managing OS Access using PDB_OS_CREDENTIAL.
Manage File Access using PDB PATH_PREFIX and CREATE_FILE_DEST.
Restrict user operation in PDBs in an Oracle multitenant container database using lockdown profiles.
The following figure shows a diagram of the container database and security measures.
Demo and Engineering Environment
The easiest way to verify the various measures for PDB security, is to use a couple of examples. The demo and test environment for PDB isolation and security presented here is based on Oracle database in Docker containers. The Docker images are build according to the build script in the GitHub repository https://github.com/oehrlis/docker. With a few adjustments the scripts can be run in any Oracle container database. If you follow the Docker-based approach, the following basic images are required:
Oracle Database 12 Release 2 RU April 2019
Oracle Database 19c Release Update October 2019 (19.5.0.0)
If the appropriate docker images are available, an adequate lab environment can be set up within a very short time. Just make sure you update and adjust the docker-compose.yml file to fit your environment, before you setup the docker container. The compose file does contain two database services. One for Oracle 12c Release 2 and on for Oracle 19c (19.5.0.0).
Run docker-compose to create the Docker containers.
docker-compose up -d
Check the progress of you container and database creation
docker-compose logs -f
As soon as you the the following message your database is ready to use.
tvd122 | ---------------------------------------------------------------
tvd122 | - DATABASE TTVD122 IS READY TO USE!
tvd122 | ---------------------------------------------------------------
...
tvd190 | ---------------------------------------------------------------
tvd190 | - DATABASE TTVD190 IS READY TO USE!
tvd190 | ---------------------------------------------------------------
Test which PDB OS credential require some OS user. To create these user log into either of the container as root and run 01_add_pdb_os_user.sh.
docker exec -it -u root tvd190 bash --login
bash-4.2# /u01/config/scripts/01_add_pdb_os_user.sh
Found passwd utility
Skip, group orapdb exists.
Skip, user orapdb exists.
Changing password for user orapdb.
passwd: all authentication tokens updated successfully.
Add PDB OS user orapdbsec:
Changing password for user orapdbsec.
passwd: all authentication tokens updated successfully.
Add PDB OS user orapdb1:
Changing password for user orapdb1.
passwd: all authentication tokens updated successfully.
Add PDB OS user orapdb2:
Changing password for user orapdb2.
passwd: all authentication tokens updated successfully.
Add PDB OS user orapdb3:
Changing password for user orapdb3.
passwd: all authentication tokens updated successfully.
Your now ready to use you Docker base PDB isolation and security environment.
Setup regular Database Environment
Of course, the scripts can also be tested in a regular database environment. You only have to make the following adjustments.
Create a tnsnames.ora entry for you PDB. e.g. PDBSEC.
Create the run_id.sh scripts and folders according to 00_prepare_pdb_env.sh Script.
Create dedicated OS user for the PDB_OS_CREDENTIAL test using the scripts 01_add_pdb_os_user.sh.
Adjust the demo script to match your environment e.g. directory path, pdb name etc.
Demo and Test Scripts
The demo scripts are located in the script folder. In general, they work for both 12.2 and 19c, except the lockdown profile create script.
00_prepare_pdb_env.sh Script to add a tnsname.ora entry and other stuff for the PDB PDBSEC.
01_add_pdb_os_user.sh Script to add a PDB OS user.
10_create_pdb.sql Create a PDB (pdbsec) used for PDB security engineering.
20_create_directories.sql Script to create directories.
30_create_datafile.sql Script to create datafiles.
40_create_PDB_OS_CREDENTIAL.sql Script to configure PDB_OS_CREDENTIAL.
41_create_ext_table.sql Script to configure table pre-processors.
42_create_scheduler_job.sql Script to configure external OS jobs.
50_create_lockdown_profiles_12.2.sql Script to create lockdown profiles for 12.2.
50_create_lockdown_profiles.sql Script to create lockdown profiles.
51_lockdown_trace_view.sql Script to create lockdown profiles for trace files.
53_lockdown_external_table.sql Script to create lockdown profiles external table.
60_dbms_sys_sql_test.sql Script to verify DBMS_SYS_SQL
90_drop_pdb.sql Drop PDB (pdbsec) used for PDB security engineering
ld_profiles.sql Displays information about lockdown profiles.
ld_rules.sql Displays information about lockdown rules in the current container.
lpdb.sql List PDBS
Slides of the lecture
The slides for the lecture have been uploaded to slideshare. They do provide a few information about the idea and the concept behind the PDB isolation and security.
Recently I came across the situation where I have to configure Enterpriser User Security for a database server with multiple databases for different directories. This is quite tricky when using a shared Oracle Home and a central TNS_ADMIN directory for SQLNet configuration. A common TNS_ADMIN also implies the use of only one ldap.ora file. Several ldap servers can be registered in one ldap.ora, but this is primarily used for failover configuration in a high-availability LDAP server architecture. The use of multiple EUS contexts in different LDAP servers is not supported. At least not in one single file. But there are some workarounds.
Oracle does look for the ldap.ora file in a few different places. The following sequence is maintained:
$LDAP_ADMIN environment variable setting
$ORACLE_HOME/ldap/admin directory
$TNS_ADMIN environment variable setting
$ORACLE_HOME/network/admin directory
Notes on this order can be found at different places in the Oracle documentation e.g Oracle Database Database Net Services Reference 19c Overview of Directory Server Usage File, Oracle Database Security Guide 19c About Using a dsi.ora File or in the Oracle Support Note 363283.1 What Is The Search Order For The LDAP.ORA File?
If you don’t trust the documentation, you can also verify the search order with strace. First define a few values for TNS_ADMIN and LDAP_ADMIN to be sure we do not use the default values.
If we make sure, that LDAP is the first names resolution in sqlnet.ora we could use strace with tsnping.
oracle@eusdb:~/ [TEUS01] grep -i NAMES.DIRECTORY_PATH $TNS_ADMIN/sqlnet.ora
NAMES.DIRECTORY_PATH=(LDAP, TNSNAMES, EZCONNECT )
oracle@eusdb:~/ [TEUS01] strace -o /u01/config/tnsping.txt tnsping TEUS01
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-NOV-2019 20:47:33
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eusdb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEUS01)))
OK (30 msec)
Checking the output does show the different directories
oracle@eusdb:~/ [TEUS01] grep -i ldap.ora /u01/config/tnsping.txt
stat("/u01/config/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/ldap/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/network/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/network/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/network/admin/ldap.ora", 0x7ffd149f6be0) = -1 ENOENT (No such file or directory)
As you can see the search order does match the documented search order. But how does that help us? It’s actually relatively simple. To use different LDAP server configuration per database, we do have to make sure, that each database has an individual ldap.ora file. This can be ensured by one the following points:
Have a dedicated Oracle Home for each database with an individual ldap.ora file in each Oracle Home
Define an environment variable for LDAP_ADMIN for each database.
Both methods have their advantages and disadvantages, but aren’t optimal. As often, several paths lead to the goal. The third option uses a static listener configuration for the database with an ENVS parameter. Unfortunately, the information about ENVS has disappeared in the latest version of the Oracle documentation. At least in the Oracle Database Database Net Services Reference 19c. But you can search for ENVS in the Oracle Database Bookshelf. The parameter ENVS can be used to specify environment variables for the listener to set prior to executing (as a child process) a dedicated server program or an executable specified with the PROGRAM parameter. This allows a static listener entry to be defined for each database, where LDAP_ADMIN or TNS_ADMIN is explicitly set. Below you see an excerpt of listener.ora for ORACLE_SID TEUS01.
The database TEUS01 does use a dedicated ldap.ora. If you add the listener entry just for this DB, you can keep the default ldap.ora in the regular TNS_ADMIN directory. Thus one can apply the following principle:
Use a generic ldap.ora configuration in the TNS_ADMIN directory. e.g which is valid for most of the database on this server
Add a static listener configuration for each database which does have to use a dedicate Oracle Enterprise User Security configuration and therefor a dedicated ldap.ora.
This method is not only helpful when configuring LDAP server or Oracle Enterprise User Security, but also in other SQLNet use cases. In particular the following:
Define TNS_ADMIN and use a dedicate sqlnet.ora configuration for Kerberos authentication. e.g. when you want to use or engineer Kerberos authentication for just one database in an shared environment.
Define TNS_ADMIN and use dedicate sqlnet.ora configuration for network encryption.
Define LDAP_ADMIN for dedicated ldap.ora or dsi.ora files for engineering centrally managed users.
And a couple more…
References
A few links and references related to this blog post
Oracle Support Document 728043.1Use of DIRECTORY_SERVERS in LDAP.ORA & Known issues
Oracle Support Document 363283.1What Is The Search Order For The LDAP.ORA File?
If you’ve landed here, you’ve probably followed one of my tweets with the URL url.oradba.ch/30hnNDL. I did use the MarsEdit functionality to download and update the side template. MarsEdit creates a new posts, which will be deleted immediately. The problem is when new posts are automatically published directly on twitter. Of course, the tweet is not deleted automatically, but points to a dead end.
A crucial aspect of any database audit concept is the management and maintenance of audit trails. Depending on the defined audit policies and the database activity, an audit trail can grow relatively quickly. Oracle Multitenant environments increase the operational effort because the root container and each PDB uses their own audit trail. Ok, for a simple CDB with 2-3 PDB this effort is manageable, but what about CDB’s with 40 or more PDB’s?
Let’s look into the different possibilities.
Common Purge Job for all PDB’s
Oracle allow’s to initiate an audit trail clean up for all PDB’s using dbms_audit_mgmt.clean_audit_trail procedure by specifying dbms_audit_mgmt.container_all. The following example initiate a clean up in the root container for all PDB’s without considering the last archive timestamp.
ALTER SESSION SET CONTAINER=cdb$root;
BEGIN
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
container => dbms_audit_mgmt.container_all,
use_last_arch_timestamp => false);
END;
/
The problem with this method are the closed PDB’s. First let’s check how much audit records we do have per PDB.
COLUMN con_id FORMAT 999999
COLUMN count FORMAT 99999
COLUMN name FORMAT a8
SELECT
v.con_id, v.name, v.open_mode, COUNT(u.event_timestamp) count
FROM
cdb_unified_audit_trail u
FULL OUTER JOIN v$containers v ON u.con_id = v.con_id
GROUP BY
v.con_id, v.name, v.open_mode
ORDER BY
v.con_id;
CON_ID NAME OPEN_MODE COUNT
====== ======== =========== =====
1 CDB$ROOT READ WRITE 644
2 PDB$SEED READ ONLY 0
3 PDB1 MOUNTED 0
4 PDB2 READ WRITE 36
5 PDB3 MOUNTED 0
6 PDBSEC READ WRITE 27
Running the clean up on this container database will end with an ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB.
BEGIN
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
container => dbms_audit_mgmt.container_all,
use_last_arch_timestamp => false);
END;
/
BEGIN
*
ERROR at line 1:
ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 2137
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 1409
ORA-06512: at line 2
The error ORA-46273 is confusing. When we check the audit records, we see that the entries have been effectively be deleted. Here it would be more understandable if Oracle simply issue a warning rather than an error.
COLUMN con_id FORMAT 999999
COLUMN count FORMAT 99999
COLUMN name FORMAT a8
SELECT
v.con_id, v.name, v.open_mode, COUNT(u.event_timestamp) count
FROM
cdb_unified_audit_trail u
FULL OUTER JOIN v$containers v ON u.con_id = v.con_id
GROUP BY
v.con_id, v.name, v.open_mode
ORDER BY
v.con_id;
CON_ID NAME OPEN_MODE COUNT
====== ======== =========== =====
1 CDB$ROOT READ WRITE 5
2 PDB$SEED READ ONLY 0
3 PDB1 MOUNTED 0
4 PDB2 READ WRITE 1
5 PDB3 MOUNTED 0
6 PDBSEC READ WRITE 1
The same does apply when we run the clean up task as a job. The job will always fail if one PDB is in MOUNT stat. This is annoying when monitoring scheduler jobs.
SQL> BEGIN
2 DBMS_SCHEDULER.RUN_JOB(job_name => '"AUDSYS"."TVD_TEST"');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 2137
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 1409
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 2
There is an other issue related to dbms_audit_mgmt.clean_audit_trail in Oracle 12.1 to 18c. The procedure does create a wrong scheduler job. The container_all is not set for the scheduler job, which results in the job running only in the root container. The issue is addressed in Oracle bug 27527173 – ISSUE WITH CREATE PURGE JOB FOR UNIFIED_AUDIT_TRAIL WITH CONTAINER_ALL. A bugfix is available for Oracle 12.1.0.2, 12.2.0.1 and 18.0.0.0. Alternatively you can workaround this issue by manually create a scheduler job to purge the audit trail rather than using dbms_audit_mgmt.clean_audit_trail. The issue is permanently fixed in Oracle 19.0.0.0.
dbms_audit_mgmt or Scheduler Job
Considering the above problems, it seems better to directly create a regular scheduler job for deleting audit trails. All right, this does work as expected, but these kind of jobs will never be visible in cdb_audit_mgmt_cleanup_jobs view. Technically this is not a problem, but it is good practice when audit-related clean up jobs are visible where they should.
The following example creates a regular scheduler job.
Insights when creating audit purge job via dbms_scheduler:
Job is not visible in cdb_audit_mgmt_cleanup_jobs
Start date can be defined when the job is created
Job can be created in any schema, which has the appropriate rights.
The following example does create an audit trail clean up job for unified audit trail using dbms_audit_mgmt and adjusting the start time to 01:00 with dbms_scheduler.
BEGIN
-- Create regular purge job
dbms_audit_mgmt.create_purge_job(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_purge_interval => 12,
audit_trail_purge_name=> 'tvd_unified_audit_purge_job',
container => dbms_audit_mgmt.container_current,
use_last_arch_timestamp => true);
-- Adapt start date of dbms_audit_mgmt purge job
dbms_scheduler.set_attribute (
name => 'tvd_unified_audit_purge_job',
attribute => 'start_date',
value => trunc(sysdate) + ( 1 / 24 ) );
END;
/
Insights when creating audit purge job via dbms_audit_mgmt:
Job is visible in cdb_audit_mgmt_cleanup_jobs
Start date can not be defined when the job is created with dbms_audit_mgmt. The start date has to be changed later using dbms_scheduler.set_attribute. This also applies to other adjustments to the job.
For Oracle 18c and 19c the job will be created in the AUDSYS schema. In Oracle 12c and earlier it will be created in the SYS schema.
Audit Window
In the above examples I did set use_last_arch_timestamp=>true. This means that only audit trails older than the archive timestamp will be deleted. The archive timestamp is usually set by the Oracle audit vault agent, when it reads the audit records. But what happens a third party tool like splunk collects the audit data? These tools will generally just read the data without setting a corresponding archive timestamp. Oracle will then never delete old audit records. Therefor it is crucial, that the archive timestamp is set by the tool as soon as the data as been collected. Additionally to this, it is good practice to define an audit window. This is nothing else than set up a job which regularly set an archive timestamp e.g. set the timestamp to sysdate-30. This way all audit records older than 30 days will be removed. The database is thus always nicely tidied up. :-The following example creates such an archive timestamp job. Every 12 hours the archive timestamp is set to sysdate-30.
The administration of audit records works well in Oracle Multitenant environments as well. dbms_audit_mgmt helps with the automatic and manual management of audit trails. Nevertheless, there are a few issues and best practices to consider:
Audit data should always be stored in a separate tablespace. However, depending on the Oracle version, there may be issues related to the relocation of the audi trail. For example, not all partitions are moved correctly, LOB and index partitions will continue to be created in the old tablespace. See also bug 27576342 – dbms_audit_mgmt.set_audit_trail_location does not move lob and index partitions or MOS note 2428624.1Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions
Adjusting the partition size or the interval when a new audit partition has to be created, has be done according to the audit quantity and database activity..
The different jobs for the clean up as well as the setting of the archive timestamp should run in the same scheme. This increases the transparency. As of Oracle 18c < dbms_audit_mgmt creates the jobs under the user AUDSYS. Older versions usually use the SYS user
In Oracle Multitenant environments a central clean up job is the easiest, but makes no sense, depending on the state of the PDB’s it always finishes with an error. Here it is recommended to define one job for each CDB / PDB. In this way, the frequency of the deletion can be individually adapted to the database activity and compliance requirements of the particular PDB.
So far we just discussed unified audit. If the database does run in pure unified mode there is no need to define a housekeeping for the legacy audit trails (AUD$, FGA$ etc). Although it is a good practice to define a minimal housekeeping for the legacy audit trail.
To define a time window in which audit records are keep in the database is usually a easy method to hold a certain level of audit information in the database. Third party tools like splunk do have enough time to collect the relevant information.
For successful database audit concept it is crucial to define the different users and roles who can access or maintain audit information.
References
Some links related to this blog post:
Oracle® Database PL/SQL Packages and Types Reference 19c DBMS_AUDIT_MGMT
Master Note For Oracle Database Auditing [1299033.1]
Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions [2428624.1]
Bug 27576342 – dbms_audit_mgmt.set_audit_trail_location does not move lob and index partitions
Bug 22859443 – CONTAINER_ALL IS IGNORED IN CREATE_PURGE_JOB
Patch 27527173 – ISSUE WITH CREATE PURGE JOB FOR UNIFIED_AUDIT_TRAIL WITH CONTAINER_ALL (Patch)
I am looking forward to a busy second half of 2019. I will be represented with my lectures at various conferences and events. Enclosed a short overview, at which events I will participate:
The comprehensive list is listed under public appearances. I’ll update the events as soon as more information is available. Slides of the presentation will usually be posted on this side.
