Oracle … as usual

Oracle by Laurent Leturgez

Category Archives: Uncategorized

How user/role password security is managed in Oracle vs. Postgres

First of all, I would like to say that I will not compare a licensed product (Oracle) vs. a freely distributed product (Postgres). This article is written to mention how password security is managed in both products, not to tell Oracle (or postgres) is the best product or vice versa.

Ok, that said, I have analyzed and worked on how password security is managed based on 4 topics: Password hashes, Salt used, password length and case sensitivity.

During all these tests, I used an Oracle 12.2 database (EE) and the last postgres server version 9.6.2 I built from sources.

HASHES

In Oracle and in Postgres, both rdbms use hash algorithms to store encrypted passwords.

Oracle

In Oracle 12.2, depending on the value of SQLNET.ALLOWED_LOGON_VERSION_SERVER in sqlnet.ora, the password can be stored using different hash algorithms.

To sum up:

  • Before Oracle 11gR1: an algorithm based on DES algorithm is used to generate password hash. This algorithm is weak due to the key length which is too short, and because the password generated is not case sensitive. The password hash is store in USER$.PASSWORD in the dictionnary.
  • From Oracle 11gR1: Oracle uses SHA1 algorithm. Nowdays, SHA1 has been broken and collisions are possible. SHA1 password hash is stored in USER$.SPARE4 (the hash is the string that starts with S:) .
  • Starting with Oracle 12c, Oracle started to use SHA2(512) combined with PBKDF2 function (See. https://en.wikipedia.org/wiki/PBKDF2). At the moment I wrote this blogpost, these algorithms haven’t been broken yet. SHA2(512) password hash is stored in USER$.SPARE4 (the hash is the string that starts with T:) .

Please note, that in Oracle 12.1, an HTML digest has been used but has been now removed in Oracle 12.2. By default, Oracle 12c stores SHA1 and SHA2(512) hashes:

SQL> create user laurent identified by laurent;

User created.

SQL> select name,spare4 from user$ where name='LAURENT';

NAME                 SPARE4
-------------------- ------------------------------------------------------------
LAURENT              S:5F0BAF84F6E102D0B1B7AEA78A5C1234E39F131EEC9C5C5E6B07CF3482
                     AF;T:B340F958041950CD2977301A15A2781D5EA3F71D51E25E9432EBC78
                     D369F13D9D568AC230422FDD2E28F1360D90F360E0A52B76B6F32522EF2B
                     3C1625489A5FBC93D710F50C4F0DDE7BFB8EB4CD4C729

Postgres

In postgres, password hashes are generated with the md5 algorithm. The hashed password is store in pg_authid table (in pg_catalog), the 3 first bytes are set to ‘md5’ followed by the hash:

postgres=# create role test with encrypted password 'test' login;

postgres=# select rolname,rolpassword from pg_authid where rolname='test';
 rolname |             rolpassword
---------+-------------------------------------
 test    | md505a671c66aefea124cc08b76ea6d30bb

Please note that, even if it’s not the default, password can be stored without encryption in the catalog.

postgres=# alter role test with unencrypted password 'passwd';
ALTER ROLE
postgres=# select rolname,rolsuper,rolpassword from pg_authid where rolname='test';
 rolname | rolsuper | rolpassword
---------+----------+-------------
 test    | f        | passwd

 

Match score

Oracle wins the hashes game because Oracle 12 is the only version that offer a strong hash algorithm (SHA2(512) + PBKDF2) that hasn’t been broken yet (SHA1 and MD5 are both weak algorithms).

SALT

As a remind, a salt is a random data that is used as an additional input to a function that hashes a password. As a result, the hashed password will be different each time it will be modified, even if the same password is used.

Oracle

  • Before Oracle 11g, Oracle don’t use a real salt. the algorithm (you can find it easily on the internet) uses DES encryption and a initial key “0x0123456789ABCDEF”.
  • From Oracle 11gR1, password is concatenated with a (supposed) random 10 bytes string and hashes with the SHA1 algorithm
  • With Oracle 12c onwards, a (supposed) random salt is used too.

As a result, if we set the same password than previous one, the password hash changes:

SQL> alter user laurent identified by passwd;

User altered.

SQL> select name,spare4 from user$ where name='LAURENT';

NAME                           SPARE4
------------------------------ ------------------------------------------------------------------------------------------------------------------------
LAURENT                        S:3F2A7144E07CE3631F88D00C9851703D107A4230B3C8A820BAC49372BFE6;T:47A8D24FFC2465100B8C5097278DDDF012EFD24303680DB8C7151ED
                               54D3DE0E2BD649A3788FDB50316B843903D686034BEA6E5FC51AF914F2C30547DCF89BF507CDEB5D9B14BA70B8572C35251DCF861


SQL> alter user laurent identified by passwd;

User altered.

SQL> select name,spare4 from user$ where name='LAURENT';

NAME                           SPARE4
------------------------------ ------------------------------------------------------------------------------------------------------------------------
LAURENT                        S:6073B325C321F6CAFD97297ECC5CD7C423F3F381A1E0111400AC8BFBA87F;T:003F805AA6CA9E86393D84FB09E0B66BEBDEC3CBBA0DCC4EE59E83F
                               85E980BF86AB262D05BC4C424392383EE6FA4A0B88A5E0C7D1A76A6A4EBE1900CF5B37C5F20ED1D42F0D2FA912DD222E72E716391

Postgres

If you have a look into the postgres C code, password is hashed in the src/backend/commands/user.c file inside a function called CreateRole or AlterRole depending on what you are doing on your role.

Let’s take the AlterRole function for example. In this function, a call is made to pg_md5_encrypt function. This function code is available in the src/backend/libpq/md5.c file.

By having a look to the pg_md5_encrypt function call and how it’s designed, it’s easy to concluded that role name is used as salt.

Postgres adds a string to the password before hashing it, but it’s not random. As a consequence, if you do the same operation as I did previously for Oracle, we can see below that the password hash remains the same which is … in my opinion, weak.

postgres=# alter role test with encrypted password 'passwd';
ALTER ROLE
postgres=# select rolname,rolsuper,rolpassword from pg_authid where rolname='test';
 rolname | rolsuper |             rolpassword
---------+----------+-------------------------------------
 test    | f        | md50104560cc42670b87acd04a5bba33344
(1 row)

postgres=# alter role test with encrypted password 'passwd';
ALTER ROLE
postgres=# select rolname,rolsuper,rolpassword from pg_authid where rolname='test';
 rolname | rolsuper |             rolpassword
---------+----------+-------------------------------------
 test    | f        | md50104560cc42670b87acd04a5bba33344

 

Match score

Oracle wins the salt match because it uses what is really defined as a salt … random data.

PASSWORD LENGTH

As we all know, the more the password is long the more it is difficult to guess (specially through a brute force attack).

Oracle

Oracle have a hard limit for password length which is set to 30 characters.

Above 30 characters length, an error is thrown:

SQL> alter user laurent identified by abcdefghijklmnopqrstuvwxyz0123;

User altered.

SQL> alter user laurent identified by abcdefghijklmnopqrstuvwxyz01234;
alter user laurent identified by abcdefghijklmnopqrstuvwxyz01234
                                 *
ERROR at line 1:
ORA-00972: identifier is too long

Postgres

For postgres, I was able to set a password with unlimited length (ALTER ROLE Statement ends correctly) but I could only login the database by using a password up to 995 characters, and only by using PGPASSWORD environment variable (see below). Above 995 characters, message “LOG:  invalid message length” is logged in the server logfile (I didn’t find any reference to this limit in the code, and but it deserves another analysis to go further):

Please note that, with the psql binary, this one uses the C function named “simple_prompt” to read the password entered in the standard input file, and this function call is made with a limit of 100 characters. So if you use psql to login, you cannot directly type a password with more than 100 characters. (See. src/bin/psql/startup.c, src/port/sprompt.c).

postgres=# alter role test with encrypted password 'abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123';
ALTER ROLE
postgres=# \q


laurent@pg:~/pgsrc/postgresql-9.6.2$ export PGPASSWORD=abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123

laurent@pg:~/pgsrc/postgresql-9.6.2$ psql -d laurent -U test
psql (9.6.2)
Type "help" for help.

laurent=>

Match score

On the field of password length, Postgres wins the game. Indeed, with PG you can set very long password and hacking a password can become a very long task to do (but be careful with the client tools you use). But, above 100 characters length, login to a postgres becomes painful depending on the client you use.

With Oracle or with postgres, the best thing to do is to use long password that contain all kind of characters (upper and lower case, number, special characters).

CASE SENSITIVITY

In this field, (modern) Oracle and Postgres are using case sensitive password.

But with Oracle

  • 10g
  • 11g with sec_case_sensitive_logon parameter set to FALSE
  • 12c with SQLNET.ALLOWED_LOGON_VERSION_SERVER set 8, 9, 10 or 11.

Passwords become case unsensitive which is not very secured.

 

DOAG 2016 … a great conference !

From 15th till 18th November, I was at the DOAG Conference in Nurnberg and it was a very great conference and a great opportunity for the Oracle Community.

Last year, I decided to attend (and to present) at different conferences in Europe and these conferences represent a good opportunity to develop your network (mainly technical for me), to learn a lot about technical stuff from worldwide Oracle experts, and to share your experiences.

More, you will share very good moments with people you certainly know by social networking (twitter, blogs etc.).

Let me explain how it was during DOAG and why I enjoyed it.

I arrived at my hotel the day before the conference, I didn’t know Nurnberg and as the conference center was not located in the town center, I feared to be alone in this area and to meet people exclusively during the conference days. But here comes the Oracle community …

Once installed in my room, I went to twitter to view the last news and I saw a tweet from Jan Karremans (http://www.jk-consult.nl/) who asked who was in his hotel (Yipiie !!! it’s mine too). So we plan to meet up in the hotel’s lobby in the evening. During this time, we found other people (Martin Widlake @MDWidlake, Neil Chandler @ChandlerDBA, Kamil Stawiarski @ora600pl etc.) that will enjoy to have a beer (or more 😉 ) in a pub just before the Oracle ACE dinner.

So we had a great time with those guys and I have a new opportunity to meet great people at the dinner which was organized by Oracle for its fellow advocates 😉

Day 1 : great conferences and a German Organization for a very nice event. After the conferences, comes the speaker dinner. With different guys, we had a beer (or two 🙂 ) before the dinner but once arrived at the restaurant, this one was full and we cannot join the speakers that came earlier. We were about 20 people and we decided to find a restaurant for our “unspeaker restaurant” event. Kamil found a great place and during this time, I had the pleasure to sit near Martin Widlake and Neil Chandler who made the demonstration of their “soooo” british sense of humor .. we had a lot of fun !! 😉

The last two days were more studious for me, I attended lots of sessions presented by so interesting and famous guys (Kerry Osborne: @KerryOracleGuy, Stefan Koehler: @OracleSK, Mauro Pagano: @Mautro, Franck Pachot: @FranckPachot, Ludovico Caldara: @ludodba etc.). The most interesting one was given by Toon Kooperlars and Bryn Llewellyn about the ThickDB concept. I’m specialized in infrastructure and this session was much oriented to development, it can be surprising but that’s the power of the community and the power to attend this kind of conference … you can discover new fields and develop your technical culture (even if I have some skills in database development 🙂 ).

Last day, I presented my session (about SIMD instructions) and I was very honored to receive some congratulations from Stefan Koehler who is one of the most interesting guy in the Oracle’s internal field (and member of the prestigious “Oak Table” network).

Thanks DOAG and thanks to all the speakers … it was a blast 😉

OTN Appreciation Day: SQL Patch

Feature: SQL Patch.

Why : Because, it can help you to use some hints on a non editable query (for example, in a packaged application). In order to stabilize performance of this kind of application, it’s very useful.

Why not: Because sometimes, having many execution plans for a SQL Statement is better than one.

If you don’t understand why such a short post, or why this post .. please read this: https://oracle-base.com/blog/2016/09/28/otn-appreciation-day/
Many thanks to Tim Hall (@oraclebase) for the Idea 😉

 

ITL slots high water mark

Recently, one of my customer had some performance issues on a batch ran every night.

After analyzing AWR, I found that the top wait event was “enq: TX – allocate ITL entry” on a particular table.

“enq: TX – allocate ITL entry” wait time is increased when an Interest Transaction List (ITL) can’t grow due to lack of space in the block header. (for more information, see this post from Arup Nanda : http://arup.blogspot.fr/2011/01/more-on-interested-transaction-lists.html). If you want to resolve it, you have to increase the INITRANS parameter of the object, and then rebuild it.

Another thing to point out: this wait event is counted by object (in V$SEGMENT_STATISTICS for example), but ITL allocation issues are specific to blocks, and ITL slots count can be different from a block to another one.

So far so good, but … but which value for initrans? To know it, you have to dump every block that contain data (block type : 0x06), and get the block that have the highest number of itl slot allocated. (you can make an arbitrary choice too … 😉 ).

Well, my customer ITL allocation issue was located on a table with more than 60000 blocks of data, so to get the best value for initrans, I had to write few lines of code.

First, I used some code from this blog post (from Oracle Diagnostician : http://savvinov.com/2015/02/26/analyzing-segment-content-by-block-type/) to dump all the blocks of my object:

begin
 for rec in (select file_id, block_id start_block, block_id + blocks - 1 end_block from dba_extents where segment_name = '&segname' and owner = '&ownname') loop
 execute immediate 'alter system dump datafile ' || rec.file_id || ' block min ' || rec.start_block || ' block max ' || rec.end_block;
 end loop;
end;
/

Note : This operation can take some time to be executed, be patient (and don’t forget to check space in your ADR trace directory)

Now, I have a look into my ADR trace directory to get the dump file (which is quite huge).

Then, I wrote a small perl script to analyze this dump file and print high water mark of itl slots.

This very simple perl script can be downloaded here : https://app.box.com/s/ghvb86rg8mk3sb2tieadnie9hbrhxgdf

Here’s a little demo of this script upon a block dump file (which contains many dump blocks)

$ ./cnt_itl.pl p orcl_ora_14878.trc
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0011.01f.00000095  0x00c0364a.00c6.11  ----    1  fsc 0x03fe.00000000
0x02   0x0018.00a.00000044  0x00c0317b.0038.17  ----    1  fsc 0x03fe.00000000
0x03   0x0015.01b.0000004a  0x00c03b10.0044.02  ----    1  fsc 0x03fe.00000000
0x04   0x000e.007.00000077  0x00c03716.008f.03  ----    1  fsc 0x03fe.00000000
0x05   0x0010.020.00000081  0x00c030de.00b1.0d  ----    1  fsc 0x03fe.00000000
0x06   0x0019.01c.00000043  0x00c03555.0042.1c  ----    1  fsc 0x03fe.00000000
0x07   0x0014.01b.0000004c  0x00c036b1.005f.35  ----    1  fsc 0x03fe.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0013.01b.00000052  0x00c039f8.008b.23  --U-    7  fsc 0x0000.00480702
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0013.01b.00000052  0x00c039f8.008b.24  --U-    6  fsc 0x0000.00480702
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

$ ./cnt_itl.pl c orcl_ora_14878.trc
7

Now I can set INITRANS to a value of over 8 (and don’t forget to rebuild it to set this on all data blocks) :

SQL> alter table spl initrans 8;

Table altered.

SQL > alter table spl move tablespace users;

Table altered.

 

I’m Back

A quick post to inform readers that I was a little bit busy last month because of my removal and because I have started a new job on a French Oracle consulting company DIGORA.

So I will write new posts next days !!!

See you soon !