Ilmar Kerm

Oracle, databases, Linux and maybe more

My use case for this: one big database, where applications have some shemas. But also, quite a lot of developers need access to the database, to see the production data and security requires each user to have his/her own personal account and of course also requires password expiration and complexity. Authenticating database users against organizations central Active Directory would be just perfect for this case.

Oracle offers this solution for Enterprise Edition, but its an extra cost option – Oracle Advanced Security. If these are not an option for you, then its also possible to achieve the same task, in its simplest form, using user profile password verification function and database logon trigger.

Update: Thanks to Job Miller for pointing out that password authentication does not require licensing ASO option anymore, but requires licensing Oracle Internet Directory: Link to 10.2 Oracle® Database Licensing Information – 2 Options and Packs

Basically, the procedure works as follows:

  • Create a new profile for AD-authenticated users with password verification function.
  • Assign this new profile to a database user.
  • When the database user changes password, the password verification function will try to connect to AD with the user specified password, if AD authentication is successful, then the password is correct and can be changed and stored by Oracle.
  • Every time the user connects to the database, after logon trigger will query AD for the user status: does the user still exist, is the user disabled or expired, when did the user last change password. If the user status is changed in AD, the trigger will lock or expire the database user accordingly.

The code

My solution consists of a PL/SQL package, that I create under my database administration account, named DBAUSER. The package will be called from password verification function and the logon trigger. The DBAUSER account needs network ACLs to permit it to connect to the AD server (11g+!) and the following system privileges:

GRANT create job TO dbauser;
GRANT alter user TO dbauser;
GRANT select ON sys.user$ TO dbauser;

Now the package:

NB! Look into the package body script! There are configuration parameters on top, that you MUST look over and change!

Now, create the password verification function under SYS schema, that will only call out the created package:

CREATE OR REPLACE FUNCTION sys.ad_profile_verify_function
  (username varchar2, password varchar2, old_password varchar2)
  RETURN boolean IS
BEGIN
  RETURN DBAUSER.PROFILE_AD_AUTH.VERIFY_PASSWORD(username, password);
END;
/

Next, create a new profile setting the password verification function and also would be good to set parameter PASSWORD_LIFE_TIME to the same value AD has for password expiration. An example profile:

CREATE PROFILE ad_user LIMIT 
SESSIONS_PER_USER 2 
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LIFE_TIME 90
PASSWORD_VERIFY_FUNCTION ad_profile_verify_function;

And finally the logon trigger, that will fire the user verification function for all users having the ad_user profile. Modify it for your own needs before executing. The following procedure also needs SELECT privilege on SYS.DBA_USERS granted to SYSTEM.

GRANT execute ON dbauser.profile_ad_auth TO system;

CREATE OR REPLACE TRIGGER system.AD_USER_LOGON AFTER LOGON ON DATABASE
DECLARE
  i NUMBER;
BEGIN
  IF user NOT IN ('SYS','SYSTEM','APPUSER1','APPUSER2') and sys_context('USERENV','AUTHENTICATION_METHOD') = 'PASSWORD' THEN
    -- Check if user belongs to a specific profile
    SELECT count(*) INTO i 
    FROM sys.dba_users
    WHERE username = user AND profile = 'AD_USER';
    -- Execute verification procedure
    IF i > 0 THEN
      dbauser.profile_ad_auth.verify_user(user);
    END IF;
  END IF;
END;
/

To create a user that will be authenticated from AD, first create a regular user with a temporary password, then issue ALTER USER to set the profile. This is needed because if profile is set in CREATE USER command, then the initial password will also be validated with the verification function.

CREATE USER ilmar IDENTIFIED BY test123;
ALTER USER ilmar PROFILE ad_user PASSWORD EXPIRE;

NB! 10g passwords are not case sensitive, so subsequent logins will be allowed with passwords using the wrong case. In 11g, be sure to enable initialization parameter SEC_CASE_SENSITIVE_LOGON, to benefit from case sensitive passwords.

I have not yet used it in production, so all feedback is welcome! I’ve tested the code on 10.2 EE and 11.2 EE, should work on standard edition also.

Update: Take a look at Oracle-L mailing list thread about the same subject: Click here.

Update: SQL Developer supports changing password when OCI/Thick driver is used. To make it easier for users, set g_expired_password parameter to not null value. In this case users do not need to remember their previous password and could use a known preset password.

Update: Due to differences in 11gR2 and 10g dbms_scheduler, I added ” and sys_context(‘USERENV’,’AUTHENTICATION_METHOD’) = ‘PASSWORD'” to logon trigger. So that the trigger is only executed when user is authenticated with password, not session created by scheduler.

14 comments

  1. sameer says:

    When ever I connect to database with AD_user , I get below error

    Enter user-name: user/password
    ERROR:
    ORA-12532: TNS:invalid argument

    Is there any method to test PROFILE_AD_AUTH package and LDAP settings

  2. Ilmar Kerm says:

    This comment has been removed by the author.

  3. Ilmar Kerm says:

    You can try calling verify_password(username varchar2, password varchar2) function directly, but first comment out the exception block in that function. Then you should see the LDAP error directly.

  4. Daniel Gomes says:

    Hello, I tried download the package to test but the page got not found .
    where I can download the package !!

    Thanks and best regards by very nice solution

  5. Ilmar Kerm says:

    Hi, sorry about this, I was rearranging my online storage. It is online again.

  6. Thanks for the idea on this one. Do you have any thoughts on this. So far I haven’t been able to replicate any insecurities with our app using this strategy, including both null user names and passwords. I believe ACLs were introduced in 10gR2.

  7. npo says:

    Hello and many thanks for your website. I’ve finally found the script I need.

    I have a question about LDAP groups. What is the correct syntax you want on g_login_group parameter and how can I be sure that the value of memberof is the path you want ?

    2nd question: if the group contains spaces, is there any mistakes that can happen ?

    Many thanks for your help

    1. ilmarkerm says:

      It will be the full LDAP DN of that group (the value of memberof attribute on that user). I recommend you use Apache Directory Studio to take a look into the Active Directory LDAP structure and see what the exact value of memberof attribute is for the user.
      I don’t think that space in the group name will be a problem.

      1. npo says:

        Ok So it will be for example ‘CN=GROUP name, OU=Groups, DC=mydomain,DC=com’

        I test it but it does not work correctly. it says ‘not in LDAP group’ and the account is locked

        So I have to see what is the real value of memberof. How can i do that not using Apache but command line for example ?

        many thanks for your precious help.

  8. Peter says:

    Where you define ldap servername?

    1. ilmarkerm says:

      It is in the package body:
      g_server_name VARCHAR2(30):= g_domain_name;

  9. Shadi says:

    once execute the trigger the below error message appears

    Warning: Trigger created with compilation errors.

    SQL> sho err
    Errors for TRIGGER SYSTEM.AD_USER_LOGON:

    LINE/COL ERROR
    ——– —————————————————————–
    6/5 PL/SQL: SQL Statement ignored
    7/14 PL/SQL: ORA-00942: table or view does not exist

    And When trying to logging

    ERROR:
    ORA-01017: invalid username/password; logon denied

    Warning: You are no longer connected to ORACLE.

    while when executing the following

    SQL> declare
    2 t_value boolean;
    3 BEGIN
    4 t_value:=dbauser.PROFILE_AD_AUTH.verify_password(‘gisapp’,’xxx’) ;
    5 IF t_value THEN
    6 dbms_output.put_line (‘TRUE’);
    7 ELSE
    8 dbms_output.put_line (‘FALSE’);
    9 END IF;
    10 END;
    11 /

    Results

    TRUE

    PL/SQL procedure successfully completed.

  10. srini says:

    Hi,

    Thanks for the information.

    Now I wanted to integrated oracle db to AD, what is the perquisite for this ?

    Thanks
    Srini

Leave a Reply to Peter Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.