To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation.
You probably were discouraged by the long long route to kerberos, where the 184.108.40.206 bugs are fixed in 220.127.116.11, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that won’t be welcome by your sysadmins / winadmins.
Okay, to partly cover the password expiration issue, you could check in a profile function that the password is the one from AD.
Firstly, without SSL
CREATE OR REPLACE FUNCTION pw_function_AD (username varchar2, password varchar2, old_password varchar2) RETURN boolean IS sess raw(32); rc number; BEGIN sess := DBMS_LDAP.init( 'example.com',dbms_ldap.PORT); rc := DBMS_LDAP.simple_bind_s( sess, firstname.lastname@example.org', password); rc := DBMS_LDAP.unbind_s(sess); RETURN(TRUE); EXCEPTION WHEN OTHERS THEN rc := DBMS_LDAP.unbind_s(sess); raise; END; / GRANT EXECUTE ON pw_function_ad TO PUBLIC; CREATE PROFILE AD LIMIT PASSWORD_VERIFY_FUNCTION pw_function_AD; ALTER PROFILE AD LIMIT PASSWORD_LIFE_TIME 30; ALTER PROFILE AD LIMIT PASSWORD_REUSE_MAX UNLIMITED; alter user lsc profile AD;
When the password expires, the user must change it to its AD Password.
If I try with a dummy password, the profile will reject this
SQL> conn lsc/pw1 ERROR: ORA-28001: the password has expired Changing password for lsc New password:anypassword Retype new password:anypassword ERROR: ORA-28003: password verification for the specified password failed ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials. 80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 52e, v1db1 Password unchanged Warning: You are no longer connected to ORACLE.
I need to enter my Windows password
SQL> conn lsc/pw1 ERROR: ORA-28001: the password has expired Changing password for lsc New password: mywindowspassword Retype new password: mywindowspassword Password changed Connected.
Secondly, with SSL.
Maybe simple bind without SSL is not possible (check http://support.microsoft.com/kb/935834). And for sure it is better to not send unencrypted plain text password over the network.
Create a wallet with password with the ROOT Certification Authority that signed your AD. You probably could download this in your trusted root certification authorities in Internet Explorer.
Internet Explorer – Tools – Internet Options – Content – Certificates – Trusted root.
Then you create a ewallet.p12 with orapki. No need for user certificate and no need for single-sign-on. Only import the trusted root (and intermediaries if applicable).
Here is the modified code
CREATE OR REPLACE FUNCTION pw_function_AD (username varchar2, password varchar2, old_password varchar2) RETURN boolean IS sess raw(32); rc number; BEGIN sess := DBMS_LDAP.init( 'example.com',dbms_ldap.SSL_PORT); rc := DBMS_LDAP.open_ssl( sess, 'file:/etc/wallet/MSAD', 'welcome1', 2); rc := DBMS_LDAP.simple_bind_s( sess, email@example.com', password); rc := DBMS_LDAP.unbind_s(sess); RETURN(TRUE); EXCEPTION WHEN OTHERS THEN rc := DBMS_LDAP.unbind_s(sess); raise; END; /
If you get SSL Handshake, be prepared, it could be anything! Check your wallet, your certificate, your permission, your wallet password.
One step further could be to expire users as soon as they change their password in AD or when they expire there.
For instance with powershell goodies for active directory
PS> (Get-ADuser lsc -properties PasswordLastSet).PasswordLastSet Montag, 6. Oktober 2014 08:18:23 PS> (Get-ADuser king -properties AccountExpirationDate).AccountExpirationDate Mittwoch, 16. Juli 2014 06:00:00
And in the database
SQL> SELECT ptime FROM sys.user$ WHERE name ='LSC'; PTIME ------------------- 2014-11-10_10:33:08
If PTIME is less than PasswordLastSet or if AccountExpirationDate is not null, expire the account.
In conclusion : if you do not want to use Kerberos, nor Oracle “OctetString” Virtual Directory ovid nor Oracle Internet directory oid, this workaround may help to increase your security by addressing the “shared” and “expired” accounts problematic
There an additional hidden benefit. You could set up a self-service password reset function and send a generated expired password per mail, that the user won’t be able to change without its AD password