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 11.2.0.2 bugs are fixed in 11.2.0.4, 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, username||'@example.com',
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, username||'@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
