Language : English
Paperback : 644 pages [ 235mm x 191mm ]
Release Date : June 2010
ISBN : 1847194524
ISBN 13 : 978-1-847194-52-7
Authors : Arie Geller, Matthew Lyon

In June, just before the official release of APEX 4.0 and #APEX4 Twitter messages popping up on my desktop every minute, I noticed a tweet from one of the authors of this book saying something like: “...bad timing…just released our book APEX 3.2 – Essentials and More …”. Well, I agree. Just looking at the books title, you might really think: Bad Timing. But, you should never just judge a book by its cover (or Title, in this case).

Just let´s forget about APEX 4.0 for a moment.

This book covers almost all aspects you probably will encounter when developing with APEX. The authors start the book explaining the concepts of APEX and explaining the “APEX Angle” of related (or required) technologies like HTML, CSS, Javascript and AJAX. Once the basics are covered, Application development with APEX is explained in great detail. And by this, I mean: In great detail! No OTN Hands-On like tutorials (step-by-step-click-through-without-further-explanation). Instead, sparse code samples, no more then necessary screenshots and lots, lots, lots of explanations. That´s exactly what I like about this book. It´s not a tutorial and more then just a reference. The authors explain the essence of developing with APEX and beyond. The book concludes in a “Best Practice” Chapter which I think is an excellent subject to finish.

Although the book covers almost all aspects, some aspects are covered just a bit too brief. The chapter about Themes an Templates, for example, is covering only the very basics and is not inviting to start creating a template or theme on your own. Extending APEX with JavaScript libraries like JQuery could have had more attention, as this is common practice nowadays.

I like this book. It is a great companion for ever APEX developer.

But what about the 3.2 / 4.0 bad-timing thing?

Well, there are still lots of environments and applications based on APEX 3.2. It will take a while until all those have been upgraded.  Apex 4.0 is a great step forward, but the basic principles are still the same as in 3.2. Almost all 3.2 techniques and functionality still is there. So this book will help you too when learning and developing in APEX 4.0. If you are aware of the new functionality and keep in mind that the screenshots and examples might look a bit different, this book is still a valid reference. Anyway, I don´t think you will find an APEX 4.0 book this extensive just yet.

Anonymous Pro is a fixed-width font developed specifically for developers by Mark Simonson. Best of all - it's free under the Open Font License.

From it's home page:  "Characters that could be mistaken for one another (O, 0, I, l, 1, etc.) have distinct shapes to make them easier to tell apart in the context of source code."

I installed it on my Mac and set SQL Developer's default font to it, and the text is a little crisper than Courier, my previous default.

Anyone else have a favorite "coding" font?

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.

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:
Package
Package Body
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') 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.

One big problem... SQL Developer still does not support changing passwords and user must change the password from SQLPlus. What do you think may be a good way around it? When the procedure detects, that AD password has changed, then execute ALTER USER to expire the password, but do not raise an exception? Then the user must remember to change the password using ALTER USER, but of course, it means allowing authentication with the old password once...

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.