Loading...

Follow Oracle … as usual - Oracle by Laurent Leturgez on Feedspot


Valid
or
Continue with Google
Continue with Facebook

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.


Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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.


Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

When you manage a lot of Oracle clients, it can be difficult to manage as much tnsnames.ora files as you have Oracle clients.

In that case, it can be useful to configure solutions to centralize only one tnsnames.ora. Another solution is to use a Microsoft Active Directory to store your TNS Entries (This solution is for computers running on MS Windows).

The job is done in two steps :

  • First one is to configure Active Directory (AD) server
  • Second one is to configure your Oracle clients to query the AD server

In my lab, I have many boxes:

  • a MS Windows 2008 R2 Server (ok ok it’s an old box … but it works fine and I assume it will work fine on a 2012 R2 server). This server acts as a controller domain for the domain example.com. The server name is windows1.example.com. It hosts the Active Directory for the domain example.com and a DNS server.
  • a MS Windows where an Oracle Client is installed. (Don’t try to do this configuration on a linux box … it’s not working)
Configure AD to store Oracle TNS entries.

To do that, you first have to install an Oracle Client on the server. I won’t describe how to do that … you’re smart enough to do it yourself :). You just have to install the administrative Client.

Then, create a work directory, in my server I did that in C:\AD.  And copy all the files from $ORACLE_HOME/ldap/schema/ad to this work directory.

PS C:\> md AD
PS C:\> copy C:\app\product\11.2.0\dbhome_1\ldap\schema\ad\* C:\AD
PS C:\> dir C:\AD


    Directory: C:\AD


Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---        09/11/2004     16:44        470 adContextCreate.lst
-a---        09/11/2004     16:44       2122 adContextCreateCommon.sbs
-a---        01/08/2001     04:16        591 adContextUpgradeFrom81600.lst
-a---        01/08/2001     04:17        407 adContextUpgradeFrom81600Common.sbs
-a---        23/05/2001     17:13        676 adDisplaySpecifiersCreate.lst
-a---        23/05/2001     17:13       5106 adDisplaySpecifiers_de.sbs
-a---        23/05/2001     17:13       5163 adDisplaySpecifiers_es.sbs
-a---        23/05/2001     17:13       5385 adDisplaySpecifiers_fr.sbs
-a---        23/05/2001     17:13       5092 adDisplaySpecifiers_it.sbs
-a---        23/05/2001     17:13       5562 adDisplaySpecifiers_ja.sbs
-a---        23/05/2001     17:13       5406 adDisplaySpecifiers_ko.sbs
-a---        23/05/2001     17:13      76035 adDisplaySpecifiers_other.sbs
-a---        23/05/2001     17:13       5453 adDisplaySpecifiers_pt_BR.sbs
-a---        23/05/2001     17:13       5069 adDisplaySpecifiers_us.sbs
-a---        23/05/2001     17:13       5225 adDisplaySpecifiers_zh_CN.sbs
-a---        01/08/2001     04:16        576 adSchemaCreate.lst
-a---        04/08/2003     17:51        219 adSchemaCreateAux.lst
-a---        13/11/2001     16:10        224 adSchemaCreateAux.sbs
-a---        09/11/2004     16:44       5445 adSchemaCreateBase.sbs
-a---        01/08/2001     04:17      11925 adSchemaCreateNet.sbs
-a---        01/08/2001     04:17       7462 adSchemaCreateRDBMS.sbs
-a---        01/08/2001     04:16        570 adSchemaUpgradeFrom81600.lst
-a---        01/08/2001     04:17        585 adSchemaUpgradeFrom81600Base.sbs
-a---        01/08/2001     04:17        509 adSchemaUpgradeFrom81600Net.sbs
-a---        01/08/2001     04:17        690 adSchemaUpgradeFrom81600RDBMS.sbs

Next, we will replace some values into some of these files. But before we need to note several DN (distinguished name).  :

  • DN of the root container. Usually this DN represents the domain where we will create what is called the “Oracle Context” (see below). In my case, my domain’s DN is : “DC=example,DC=com”.
  • DN of the Users branch in the AD container. In my case, it’s “CN=Users,DC=example,DC=com”.
  • DN of the user you are logged in. Basically, I will do that with the domain administrator, and its DN is: “CN=Administrator,CN=Users,DC=example,DC=com”
  • DN of the Oracle Context, in my case its DN is: “CN=OracleContext,DC=example,DC=com”. It’s in this Oracle Context that TNS Entries will be created.

I defined all these values in a set of PowerShell variables:

PS C:\> cd AD
PS C:\AD> $rootCont="DC=example,DC=com"
PS C:\AD> $usersBranch="CN=Users,DC=example,DC=com"
PS C:\AD> $userLogged="CN=Administrator,CN=Users,DC=example,DC=com"
PS C:\AD> $oracleContext="CN=OracleContext,DC=example,DC=com"

Then, all the files mentioned below will be “SEDed” to replace patterns with the correct values in new LDIF files (It has been done with Powershell, but you can do this with the tool you want: sed/cygwin, notepad etc).

PS C:\AD> cat adSchemaCreateBase.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaCreateBase.ldif -Encoding UTF8
PS C:\AD> cat adSchemaCreateNet.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaCreateNet.ldif -Encoding UTF8
PS C:\AD> cat adSchemaCreateRDBMS.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaCreateRDBMS.ldif -Encoding UTF8
PS C:\AD> cat adSchemaUpgradeFrom81600BASE.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaUpgradeFrom81600BASE.ldif -Encoding UTF8
PS C:\AD> cat adSchemaUpgradeFrom81600NET.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaUpgradeFrom81600NET.ldif -Encoding UTF8
PS C:\AD> cat adSchemaUpgradeFrom81600RDBMS.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaUpgradeFrom81600RDBMS.ldif -Encoding UTF8
PS C:\AD> cat adDisplaySpecifiers_us.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adDisplaySpecifiers_us.ldif -Encoding UTF8
PS C:\AD> cat adDisplaySpecifiers_other.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adDisplaySpecifiers_other.ldif -Encoding UTF8

PS C:\AD> cat adContextCreateCommon.sbs | %{$_ -replace “%s_OracleContextDN%" ,$oracleContext} `
>> | %{$_ -replace “%s_AdUsersDomainDN%", $usersBranch} `
>> | %{$_ -replace “%s_CurrentUserDN%", $userLogged} | Out-File adContextCreateCommon.ldif -Encoding UTF8
>>
PS C:\AD>

PS C:\AD> cat adContextUpgradeFrom81600Common.sbs | %{$_ -replace “%s_OracleContextDN%",$oracleContext} | Out-File adContextUpgradeFrom81600Common.ldif -Encoding UTF8

Please note that if your AD is installed in another supported language, you have to modify the DisplaySpecifier file related to the installation language (for example,  adDisplaySpecifiers_fr.sbs if it’s installed in French).

After that, a simple bunch of ldapmodify commands, and the entries for OracleContext will be created in the AD:

PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaCreateBase.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaCreateNet.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaCreateRDBMS.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaUpgradeFrom81600BASE.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaUpgradeFrom81600NET.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaUpgradeFrom81600RDBMS.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adDisplaySpecifiers_us.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adDisplaySpecifiers_other.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adContextCreateCommon.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adContextUpgradeFrom81600Common.ldif

During execution, I had this kind off output … you can safely ignore this issue (probably due to powershell encoding which is made by default in UTF-8-BOM (original file used UTF-8 encoding):

ldapmodify.exe: no attributes to change or add (entry ´╗┐#)

Once done, you can check you have the correct number of objects in your directory. I did this check with a basic ldapsearch command:

PS C:\AD> ldapsearch -D "cn=Administrator,cn=users,dc=example,dc=com" -b "CN=Schema,CN=Configuration,dc=example,dc=com" -w "YOUR_PASSWORD" "(&(objectClass=attributeSchema)(CN=orcl*))" DN | Measure-Object -line

 Lines    Words    Characters    Property
 -----    -----    ----------    --------
    37    


PS C:\AD> ldapsearch -D "cn=Administrator,cn=users,dc=example,dc=com" -b "CN=Schema,CN=Configuration,dc=example,dc=com" -w "YOUR_PASSWORD" "(&(objectClass=classSchema)(CN=orcl*))" DN | Measure-Object -line

 Lines    Words    Characters    Property
 -----    -----    ----------    --------
    14    
          

If the AD MMC users and computers plugin, a new branch appeared: “OracleContext” and three groups related to security management of the Oracle Context:

Then, If you want to add some entries, you have to create an ldap.ora file first, then create entries. TO finish this configuration steps, we have to grant permissions on specific branches to allow anonymous access on TNS entries. This is mandatory because Oracle client doesn’t bind the directory, and it doesn’t have to logon with a specific user to read the TNS Entry.

LDAP.ORA

This file has to be created in the $OH/network/admin. There must be 3 three parameters for the directory server name and port, the durectory server type, and the DN of the parent branch which contains the Oracle Context.
Here’s the content of my ldap.ora file (refer to Oracle documentation : Net Services Reference for more information).

DIRECTORY_SERVERS=windows1.example.com:389
DIRECTORY_SERVER_TYPE=AD
DEFAULT_ADMIN_CONTEXT="DC=example,DC=com"
CREATE TNS ENTRIES IN ACTIVE DIRECTORY

The easiest way to do that is to use Net Manager and add entry in the directory branch.

But, I prefer to import entries from a ldif file. To do that, you have to create a LDIF file which contains all the necessary properties for the TNS entry. Below, a sample of what it can contain:

PS C:\AD> cat orcl.ldif
dn: CN=orcl,CN=OracleContext,DC=example,DC=com
changetype: add
objectClass: top
objectClass: orclNetService
orclNetDescString: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=windows1.example.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))

dn: CN=coucou,CN=OracleContext,DC=example,DC=com
changetype: add
objectClass: top
objectClass: orclNetService
orclNetDescString:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.15)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))

And to finish, it’s easy to add them to the directory, the same way we import our ldif during AD configuration:

PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\orcl.ldif

adding new entry CN=orcl,CN=OracleContext,DC=example,DC=com

adding new entry CN=coucou,CN=OracleContext,DC=example,DC=com
SPECIFIC PERMISSIONS

Grant the permission “anonymous logon” on the entry we’ve created.

C:\> dsacls "CN=orcl,CN=OracleContext,DC=example,DC=com" /G "anonymous logon":GR
C:\> dsacls "CN=coucou,CN=OracleContext,DC=example,DC=com" /G "anonymous logon":GR
Client configuration

The client configuration is very easy, there’s only two lines to configure in SQLNET.ora file.

NAMES.DIRECTORY_PATH= (LDAP)
NAMES.LDAP_AUTHENTICATE_BIND=1

A call to tnsping will show you that trying to resolve the given alias will be done through your ldap configuration:

C:\>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 12-JAN-2017 15:41:49

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\app\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=windows1.example.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl
OK (0 msec)

Another way to verify it resolves the alias through LDAP is to enable SQLNET client tracing by addind these lines in the sqlnet.ora file:

TRACE_LEVEL_CLIENT=ADMIN
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON
TRACE_DIRECTORY_CLIENT=c:\temp\client_trace
LOG_DIRECTORY_CLIENT=c:\temp\client_trace
DIAG_ADR_ENABLED=OFF

In the trace file, we see this information that prooves LDAP usage to resolve names:

[12-JAN-2017 15:45:21:752] nnflilc:  Opening sync conn to windows1.example.com:389
[12-JAN-2017 15:45:21:752] nnflalc: entry
[12-JAN-2017 15:45:21:752] nnflalc: native bind CN=Administrator,CN=Users,DC=example,DC=com returns 0
[12-JAN-2017 15:45:21:752] nnflalc: bind CN=Administrator,CN=Users,DC=example,DC=com returns 0x0
.../...
[12-JAN-2017 15:45:21:752] nnflrne1: Quering the directory for dn: cn=orcl,cn=OracleContext,DC=example,DC=com
[12-JAN-2017 15:45:21:752] nnflqbf: entry
[12-JAN-2017 15:45:21:752] nnflqbf: Search: Attrs[0]: objectclass
[12-JAN-2017 15:45:21:752] nnflqbf: Search:  Base: cn=orcl,cn=OracleContext,DC=example,DC=com; Scope: 0; filter: (objectclass=*) returns 0x0
[12-JAN-2017 15:45:21:752] nnflqbf: exit
[12-JAN-2017 15:45:21:752] nnflgne: entry
[12-JAN-2017 15:45:21:752] nnflgne:   DN : cn=orcl,cn=OracleContext,DC=example,DC=com
[12-JAN-2017 15:45:21:752] nnflgne: exit
.../...
[12-JAN-2017 15:45:21:752] nigtrm: Count in the NI global area is now 1
[12-JAN-2017 15:45:21:752] nigtrm: Count in the NL global area is now 1
[12-JAN-2017 15:45:21:752] nigini: entry
[12-JAN-2017 15:45:21:752] nigini: Count in the NL global area is now 2
[12-JAN-2017 15:45:21:752] nigini: Count in NI gbl area now: 2
[12-JAN-2017 15:45:21:752] nigini: exit
[12-JAN-2017 15:45:21:752] niqname: Hst is already an NVstring.
[12-JAN-2017 15:45:21:752] niqname: Inserting CID.
[12-JAN-2017 15:45:21:752] nigtrm: Count in the NI global area is now 1
[12-JAN-2017 15:45:21:752] nigtrm: Count in the NL global area is now 1
[12-JAN-2017 15:45:21:752] nigini: entry
[12-JAN-2017 15:45:21:752] nigini: Count in the NL global area is now 2
[12-JAN-2017 15:45:21:752] nigini: Count in NI gbl area now: 2
[12-JAN-2017 15:45:21:752] nigini: exit
[12-JAN-2017 15:45:21:752] niqname: Hst is already an NVstring.
[12-JAN-2017 15:45:21:752] niqname: Inserting CID.
[12-JAN-2017 15:45:21:752] niotns: entry
[12-JAN-2017 15:45:21:752] niotns: niotns: setting up interrupt handler...
[12-JAN-2017 15:45:21:752] niotns: Not trying to enable dead connection detection.
[12-JAN-2017 15:45:21:752] niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=windows1.example.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=C:\app\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=clientWin)(USER=Administrator))))
[12-JAN-2017 15:45:21:752] nsgettrans_bystring: entry
[12-JAN-2017 15:45:21:752] nsgettrans_bystring: exit
[12-JAN-2017 15:45:21:752] nsmal: 280 bytes at 0x6baaf0
[12-JAN-2017 15:45:21:752] nscall: connecting...

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview