I’ve screwed up somehow this post and created a new one Oracle Database Firewall Test Environment. This is mini post is just to make sure that an already published permalink does not points to /dev/null…
Slides SOUG Presentation on DBMS_AUDIT_MGMT
Just finished my presentation about audit management with DBMS_AUDIT_MGMT at the SOUG Special Interest Group Event in Baden. I’m glad having a MIFI so I can immediately upload the slides 🙂 and still have some time for the aperitif….
Slides Audit Management with DBMS_AUDIT_MGMT.pdf
I’m happy for any comment on the presentation or the slides. Feel free to add a comment or drop me a line by mail.
SOUG Presentation on DBMS_AUDIT_MGMT
On the 24 of March I’ll have a presentation on Database Audit and DBMS_AUDIT_MGMT at the SOUG SIG Event. The presentation is about 30 minutes and will cover a rough overview of DBMS_AUDIT_MGMT, latest Issues and Bugs as well some Ideas on how audit could be simplified.
Have a look at the SOUG Webpage for a detailed Agenda of the Event and the location. Looking forward to see you there.
I’ll post the presentation on this side right after the presentation.
Case Sensitive Passwords and Strong User Authentication
With 11g R1 Oracle introduced case sensitive passwords for database accounts based on the SHA1 hash algorithm. This feature can easily be enabled with the init.ora parameter SEC_CASE_SENSITIVE_LOGON. As soon as this parameter is set to true, all new passwords will be case sensitive. Existing passwords will remain case insensitive until they are changed.
The downside of this new feature is, that the passwords are also stored with the pre-11g database password hash. This is a potential security leak. The pre-11g password hash string from USER$ can be used to crack the case insensitive version of the password. All kind of tools, utilities, password lists etc are available to do this. As soon as the case insensitive version of the password is known, the case sensitive password can be guessed.
Case Sensitiv Passwords
First of all lets have a look at the parameter of an 11g R2 test database.
show parameter sec_case_sensitive_logon
NAME TYPE VALUE
------------------------- -------- ---------
sec_case_sensitive_logon boolean TRUE
The Column PASSWORD_VERSIONS in DBA_USERS shows the Database version in which the password was created or changed. The user TEST_10G shows only 10g which means that this user has been created before the database has been migrated to 11g and was never changed.
SELECT username, password_versions
FROM dba_users
WHERE username LIKE 'TEST%';
USERNAME PASSWORD
--------------- --------
TEST 10G 11G
TEST_11G 10G 11G
TEST_10G 10G
The Password hashes for both the 11g (SPARE4) and pre-11g hashes (PASSWORD)
set linesize 120
col name for a10
col password for a16
col spare4 for a50
select name,password,spare4 from user$ where name like 'TEST%';
NAME PASSWORD SPARE4
---------- ---------------- --------------------------------------------------
TEST 7A0F2B316C212D67 S:7D5C8604CDF7811E06DAA7C718ADB3684A883CE7521CF5C0
66721877D457
TEST_10G 48AFCE9CD794074D
TEST_11G AE6FC028DF3997FC S:CFD77E59711BC61589C6631C1F824CFC0966972D01599EF6
ED1558A2046F
As you can see user TEST and TEST_11G have a pre-11g Hash and the long 11g Hash. The user TEST_10G only have a pre-11g Hash. This indicates that the user has been created before the database was migrated to 11g and the password never has been changed. Therefor the password for this user is case insensitive even when the parameter SEC_CASE_SENSITIVE_LOGON is set to true.
To enable or disable case sensitive passwords just alter the init.ora parameter.
alter system set SEC_CASE_SENSITIVE_LOGON=true scope=spfile;
alter system set SEC_CASE_SENSITIVE_LOGON=false scope=spfile;
Increase Security
The Idea
The security can be increase when case sensitive password are used and logon’s are limited to the 11g authentication protocols. This can be achievement by setting the sqlnet parameter SQLNET.ALLOWED_LOGON_VERSION to 11. As soon as this has been done the pre-11g hashes can be removed from USER$.
In detail the following steps are required to enable Oracle Database 11g exclusive mode and increase database security.
- Make sure all application’s and clients are supporting the 11g authentication protocols
- After migration / database creation enable SEC_CASE_SENSITIVE_LOGON
- Change passwords on all database accounts. A minimum of 10 alphanumeric characters, special characters and mixed case is recommended
- Verify and test batch jobs or scripts to make sure the use of mixed chases is consistent
- Edit the sqlnet.ora parameter file and add the line SQLNET.ALLOWED_LOGON_VERSION=11 or change it to 11
- Remove the old password hash values from USER$
Remove the old password hash
To remove the old password hash values it is necessary to perform an update on a SYS table. Therefor a full backup of the database is highly recommended. As soon as you have a complete backup, connect to the Oracle Database with SYSDBA privileges to perform the following update.
update sys.user$ set password=NULL;
delete from user_history$;
commit;
From now the password column will be empty even when new user are created as you can see in the following example.
select name,password,spare4 from user$ where name like 'TEST%';
NAME PASSWORD SPARE4
---------- ---------------- --------------------------------------------------
TEST S:4C15788E25102B7B70713D3BD054EF2731E9664CA4FCFD67
BA224E15C5CA
TEST_10G S:35547B514FCA0C895259A4CAD1E50A88AFE086B9844ACF97
A6B9E211490B
TEST_11G S:CDE994528EF0CED53917790F412C1CE4280A05E5007CC0E1
0407337B407
create user test_new identified by Test001;
User created.
select name,password,spare4 from user$ where name like 'TEST%';
NAME PASSWORD SPARE4
---------- ---------------- --------------------------------------------------
TEST S:4C15788E25102B7B70713D3BD054EF2731E9664CA4FCFD67
BA224E15C5CA
TEST_10G S:35547B514FCA0C895259A4CAD1E50A88AFE086B9844ACF97
A6B9E211490B
TEST_11G S:CDE994528EF0CED53917790F412C1CE4280A05E5007CC0E1
0407337B407A
TEST_NEW S:D4808E96184DE35110A5CDFC83A7C496402147338C77185D
336748914299
Conclusion
The sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION provides an easy way to increase the overall database password security for 11g environments (Database and Client / Applications), but it does not replace a well-defined password policy. If user have simple passwords like username=password they still can easily be guessed.
Reference
Metalink Notes related to Case Sensitive Passwords and the Oracle Database 11g Exclusive Mode.
- 11g R1 New Feature: Case Sensitive Passwords and Strong User Authentication [429465.1]
- Instructions for Clearing pre-11g Database Password Hashes [463999.1]
Manage Audit Trails with DBMS_AUDIT_MGMT
I’ve recently wrote an DOAG article about Managing Audit Trails with dbms_audit_mgmt, which has been officially introduced in Oracle 11g R2. The same article will also be in the upcoming SOUG newsletter. The article itself is written in german and is also available on this side in the publication section ( Audit Management mit DBMS_AUDIT_MGMT.pdf)
dbms_audit_mgmt has been officially released as a 11g R2 feature. For earlier releases there is a patch available or it is part of a patchset (10.2.0.5, 11.1.0.7). More information to the availability of this package can be found in the Metalink Note New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information [731908.1].
In the article I’ve mentioned the licensing, but could not provide a clear statement. In the meantime I’ve been in contact with Oracle to bring a bit of light into the licensing situation (ok just for dbms_audit_mgmt not for the whole oracle licensing 😉 ). As mentioned in the Metalink Note [731908.1] it is mandatory to have a valid Oracle Audit Vault license when using this package in any versions before 11.2. Starting with 11.2 the dbms_audit_mgmt package is part of the regular release and can be used in the Standard and Enterprise Edition without further licensing.
The official feedback:
… I now have some further feedback from the audit development team, and can confirm this package is available with SE and EE starting with 11.2. No further license is required.
As soon as the remaining bugs are fixed (see Current issues Known Issues When Using: DBMS_AUDIT_MGMT [804624.1] we have a nice and smooth way to handle the audit trails. I’ll probably post a few ideas and examples in one of my next posts.
Oracle Critical Patch Update January 2011
It’s not the latest news, but still in the same week 😉 …
On January 19th Oracle released the first Critical Patch Update (CPU) for 2011. This CPU includes up to 66 security fixes across all product families. The number looks quite high, but thats just because SUN Products like Open Office and the SUN Product Suite covered as well by this CPU. For database server issues there are only 7 security fixes, where 5 fixes are for the Oracle Database Server, 1 is for Oracle Secure Backup and 1 for Oracle Audit Vault. The one for Oracle Audit Vault may be remote exploit without any authentication and has a CVSS score of 10. The highest CVSS score of the 5 security fixes for the Oracle Database Server is 7.5 which is still fairly high. I would therefor highly recommended to apply this security fix on Audit Vault installation and recommend to install it as well on Oracle Database Server as soon as possible.
In the next day’s I will install the CPU’s on some databases to test if there are any issues.
More information on the CPU and Oracle Security:
- Critical Patch Updates and Security Alerts)
- Oracle Critical Patch Update Advisory – January 2011
- Patch Set Update and Critical Patch Update January 2011 Availability Document [1263374.1]
- CERT Technical Cyber Security Alert TA06-109A
This entry was posted in Critical Patch Update, Oracle Database, Patch Set Update, Security, Security Patch Update and tagged Trivadis Content on .
If time permits, I try to have a look once a day at the Knowledge Articles listed on my My Oracle Support (MOS) Dashboard. I’ve configured my Dashboard to show recently updated knowledge articles and alerts. Before Metalink was migrated to the flash based My Oracle Support, it was possible to configure a regularly e-mail newsletter with similar information but with MOS this somehow does not work anymore for me. Nevertheless going through the notes and alerts is a simple way to learn everyday something new about Oracle. Especially reading the alerts about bug you will find from time to time interessting internal stuff. Tanel Poder wrote about this topic in his blog quite a while ago. During my daily MOS review I realized, that there are since a while more and more notes around with titles like Master Note for… or All about…. These notes provides often a rough overview and a good starting point to dig a bit deeper into a particular topic. For my first technical post I thought it would be a good idea to providing a list with some of these notes. Due to the fact that my blog audience is not yet that big, the list is at least a nice personel reference to some notes 🙂 The list is not concluding, I only pick a few which a read or plan to read. There are much more… Not all of the notes have the same quality but nevertheless they are a good staring point into a certain topic. For a bether overview I’ve put them in a few different categories. Some of the notes would fit into more than just one category. For now I just post this list as a reference. I will probably look a bit deeper into some of the notes in a later post. By the way, you probably have to log into MOS before you click on one of theese links. Database Security and Security Feature related Notes. Master notes related to backup & recovery and availability. A few master notes on database components and features. A few master notes related to database troubleshooting and performance tuning. Master notes for Grid Control, DB Console and related Tools. A lot of other notes which did not fit in one of my 3-4 sections. If you do not like the flash version of MOS there is also a HTML version around. The functionality is a bit limited, but you still can access all important information. Very handy if you access MOS from a device without flash (e.g. iPad). The HTML version is available through the link http://supporthtml.oracle.com. More information to the HTML version can be found in the article Master Note for My Oracle Support – TUGBU Tools and Tips (1151721.1] or My Oracle FAQ [747242.5] Yes, somewhen even I have a blog. After having accounts on gmail, twitter, xing, facebook and all the other networks, platforms etc. I thought it’s time to share my technical knowhow with anybody who want’s to see it. As senior Oracle DBA I will mainly focus on general DBA stuff, Oracle Security, Backup & Recovery, Grid Control and probably also on some internal stuff. But the future will show how and when some I find time to write a post. The whole webpage is written in english, but… I’m a swiss guy so somewhen I will also find some information in german. If you think it could be interesting for you, but you german is not bullet proof do not hesitate to get in contact with me. Probably I have a english version around which is not ready for publishing. I’m wondering if my syntax highlight plugin is working… … should look like SQL Code. To start with it looks ok. But I’m not really happy with the border. I’ve screwed up somehow this post and created a new one Case Sensitive Passwords and Strong User Authentication. This mini post is just to make sure that an already published permalink does not points to /dev/null… I’ve screwed up somehow this post and created a new one Manage Audit Trails with DBMS_AUDIT_MGMT. This is mini post is just to make sure that an already published permalink does not points to /dev/null…
Metalink Notes, some favorites
Security
Backup & Recovery, Availability and related Features
Database Feature, Options and Components
Troubleshooting and Performance Tuning
Oracle Enterprise Manager Grid Control
Miscellaneous
One more thing…
…somewhen even I have a blog
select sysdate from dual;
Case Sensitive Passwords and Strong User Authentication
Managing Audit Trails