Source Control for and deployment of your Database

I just read Jeff Smith's latest blog entry about exporting the DDL for your Oracle database with Oracle SQL Developer.  You can do a similar export with TOAD - both the original version for Oracle and the version for SQL Server.  Other tools have this ability too.  Jeff hints at why you might want to use this feature - to put your database under source control.

I have had the experience of walking into a shop where my predecessor database designer/developer had not done this, and I was shocked.  The application developers - the Java programmers, the JavaScript framework developers, the UI developers were all very careful to keep their source code in a source code repository, either with Subversion or Git, so I did not understand why the database guy/gal hadn't done the same with the code to create the database objects used by the application. DDL is SOURCE CODE, and it belongs in the source control system. So if you haven't done it already, follow Jeff's instructions (here) or the instructions for doing the same thing with your favorite database IDE and then put the resulting DDL code into your source code repository.  If you don't know how to use your shop's source code control tools, ask your fellow developers for help (by the way, this is a nice way to ingratiate yourself with the developers), or ask your favorite search engine.  What? Your shop doesn't do source control?  Convince the powers that be that this is ESSENTIAL to a good application development project and there are good tools available at no cost.  Do it NOW - I'll wait.

There is one more thing that goes in source control: scripts to load data into the look-up and reference tables.  These might be SQL scripts with INSERT commands. I do that, and the scripts go in a folder named "Data".  Or in Oracle, you might have data files (maybe CSV) with the SQL*Loader control files to load the data.  Your database IDE like SQL Developer can help you with this if the data is already in your development database.  If reference or look-up tables are ever changed by users, you will need to update this data and check the changes into source control.  You need a plan for propagating the latest changes to all environments.  The key is that you should consider the data in these tables as if it was DDL.

So now, you have captured all the DDL for the database objects your application uses and put it in source code control.  What next?  Most of the tools that let you export your DDL, also create a script to run the DDL.  This would let you re-create your objects in a new database, such as when it is time to deploy to the test database.  We always have at least four environments - development, test, staging (sometimes called pre-production), and production. I have been in shops where the production database is under the control of a production database administrator (DBA), and the development DBA (often me) does not have rights to run DDL in production.  The process is that when a production release is authorized, the production DBA does the database deployment.  So my deployment script is always thoroughly tested, and written so that even someone who knows NOTHING about the application can run it.  If your tool doesn't write a deployment script, you'll need to write it by hand.  I often use (in Windows) the DIR command to list the DDL files, spooling the output to a file, then edit the file. And yes, deployment scripts go to source control - mine go in a folder named "Scripts".

So what about database deployment?  The initial deployment is easy. The deployment script just runs ALL the DDL and data load scripts.  But subsequent deployments are a bit harder.  Views and stored procedures can use the CREATE OR REPLACE syntax (or CREATE OR ALTER in SQL Server) Once a table has been created and contains data, you can't just DROP it and CREATE a new version. You need to run ALTER commands to make some changes, and for bigger changes you might need to do something like:
CREATE TABLE my_table_new ...
INSERT INTO my_table_new (...)
  SELECT ...
    FROM my_table
DROP my_table;
RENAME my_table_new TO my_table;
I will usually name my deployment script for each new database release with the name of the release, like "myapp_2.1_DEPLOY.sql".  Since we use JIRA to manage tasks, I will have a script named after the JIRA task that does the changes associated with that task, such as "MYAPP-123.sql".  If the changes involve DDL for an object that can use "CREATE OR REPLACE" syntax, MYAPP-123.sql will simply call the new version of the DDL script for that object.  If not, MYAPP-123.sql will contain the ALTER (and other) commands needed.  When I create the release deployment script, it calls each of the scripts for the JIRA tasks included in that release.  All of these go in the Scripts folder.  I also tend to update the CREATE scripts for tables, even if deployment will not be recreating the table - that way I can create a new empty database without running all the ALTER commands.

By the way, if you are using Oracle 11gR2 or later, you probably want to use Edition Based Redefinition in your deployment scripts. This is not used as often as it should be, and will let you do your database deployments in production with no downtime.

Hope this inspires you to put you DDL under source control, and give you some ideas how to organize your database deployments.

Data Conversion in Oracle vs. SQL Server

In my last post, I talked about multiple databases in a single instance, and gave SQL Server points for doing this better, and doing it without the extra cost associated with Oracle 12c/18 Multi-Tenant option.  Today, I want to talk about something Oracle does better than SQL Server - data conversions.

By data conversions, I mean mostly conversions from strings that are human readable to numbers and dates and back to strings.  If you are one of those people who stores everything as strings (CHAR or VARCHAR) STOP IT.  This is a BAD idea in both SQL Server and Oracle.  But that's a subject for another post.

First of all, both databases will convert strings to numbers and dates implicitly if you don't include a conversion function.  This works fine - until it gives you unexpected results or awful performance.  I strongly recommend that you use a conversion function EVERY TIME you start with a number or date and want to show it on a report or screen in human readable form and EVERY TIME your get input from users and need to store it in its proper datatype.  This way, the data will be converted in a predictable and efficient way.  So what are the conversion functions?

ANSI SQL Conversion

Both Oracle and SQL Server are pretty compliant with the ANSI SQL standard, and so both support the ANSI SQL CAST function.  The syntax of CAST is:
CAST (expression AS datatype)
You can optionally include a size with the datatype, such as the length of a VARCHAR, or the precision and scale of a number.  This works well in both databases for number conversions between floating point and integer datatypes, or from string to number - just understand that if the string doesn't contain a valid number, CAST will fail.  SQL Server is nice in that it includes a TRY_CAST function that will return NULL for an invalid number rather than fail.  Since CAST is ANSI standard, it is certainly preferred if you are writing code that may need to work on any database that meets the standard.  But while CAST works for string to date and date to string conversions, the format of the string must be ANSI standard date format, which is the same as ISO_8601.  Not so good when you need to convert to or from MM/DD/YYYY - your expression would need to substring the original date to put it in ANSI format.

Proprietary Conversion

So Oracle and SQL Server both have proprietary non-standard functions to convert between strings and dates more flexibly.  SQL Server has:
CONVERT (datatype, expression, style)
Just as with CAST, the datatype parameter can include size information.  The style parameter is an integer that selects the data format of the input or output data from a table in the documentation - CAST and Convert.  Now, I will admit that this list includes the formats that I use the most frequently for dates.  For instance, style 1 is MM/DD/YY, and 101 is MM/DD/YYYY.  But it doesn't include full month names in the selected natural language, such as January 1, 2018 in English or 1 Enero 2018 in Spanish.

Oracle has separate functions for converting a string to a date or a date to a string:
TO_DATE (expression, format, nlsparam)
TO_CHAR (expression, format, nlsparam)
The format parameter is optional and defaults to the default format set up by your DBA in your database.  nlsparam is also optional and defaults to the NLS (natural language system) settings set by your DBA in your database.  I almost never let format default, but almost always let nlsparam default.  However, if you need to support languages other than English, this is what would let it appear as January for English speakers or Enero for Spanish.  Documentation for TO_DATE, and TO_CHAR includes a list of formatting codes.

To me, the formats provided by Oracle are both more flexible and more clear.  Which is easier to remember and easier to read:
CONVERT (VARCHAR(10), my_date, 101) - SQL Server
TO_CHAR (my_date, 'MM/DD/YYYY') - Oracle
Advantage - Oracle.

By the way, SQL Server's CONVERT function also works for numbers, but doesn't have a significant advantage over the CAST function for numbers.  Oracle has TO_NUMBER and TO_CHAR functions for numbers with the same parameters as TO_DATE and TO_CHAR for dates, and more flexible formats for strings converted to and from numbers.

Advantage - Oracle.

More on my Oracle to SQL Server Transition

I'm continuing to adjust to this new world of Microsoft SQL Server.  Remembering my great experiences in the Oracle user community, I've joined PASS, which is the international user group for SQL Server users.  One thing PASS offers is "SQL Saturday" which are one day (yes, on Saturday) training events held by local groups all over the world.  There are a few SQL Saturdays being held somewhere almost every weekend.  On March 24, I drove the 100 miles from my home to Glen Allen, VA (northwest of Richmond) for the Richmond SQL Saturday.  It was great, I got to meet a few people, and learned at least two things that will be useful in the work I'm doing right now.

About Table Storage and Indexes

Like Oracle, it is very important to have a Primary Key constraint on all of your tables in SQL Server.  By default, SQL Server wants to make this a clustered index.  "What is a clustered index?" my Oracle expert friends will ask.  In a clustered index, the data from the row is stored with the index entry.  That means that a table may only have one clustered index.  Oracle experts will recognize this as essentially the same as an Index-Organized Table (IOT).  IOTs aren't used that much in Oracle, but maybe they should be.  If you often query a table by its primary key, or often do UPDATE or DELETE by primary key, you might get a good performance boost by making it an IOT.

At SQL Saturday, Richmond, I went to a great presentation about indexing, "Indexing for Performance" by Jeffrey Garbus.  Mr. Garbus described very well the access path used for various WHERE conditions, with and without indexes, and differences between using a clustered and a non-clustered index.  A non-clustered index is a B-tree index in SQL Server, just like a normal index in Oracle and is used in a similar way.  In both databases, the optimizer decides whether to use an index or to do a full table scan according to similar criteria, and in fact, may decide not to use an index in situations such as the likelihood that more than a certain percentage of the rows will be returned by the query.

One very important difference between an IOT in Oracle and a table with a clustered index in SQL Server is that an IOT is always indexed with its primary key. While the primary key gets an unique clustered index by default in SQL Server, it is possible to specify that the primary key should get an unique non-clustered index.  This leaves it open to create a clustered index on another key, and that index need not be unique.  Since a clustered index makes the database store the rest of the data with the index, only one clustered index may be created on a table.  Mr. Garbus explained why, for certain tables, it might be better to cluster on a parent key.  For instance, in an Order-Entry application, line items for an order are rarely accessed any other way than by the parent order_id.  Therefore, it is often a good idea to cluster the order_line_item table on this foreign key, rather than its primary key (order_line_item_id).

This reminded me of another Oracle feature that I believe may be under-used.  While IOTs can only use a table's primary key, you can actually store master table data like the "order" table physically next to its detail rows in "order_line_item".  Oracle has clustering too, but the syntax and implementation is different.  Check out the CREATE CLUSTER command and its use in the Database Concepts Guide.

From Oracle Expert to SQL Server Newbie - First Impressions

Oops, I've done it again - I left you readers hanging when I published the first part of two on generating code for your lookup/reference/code tables.  I promise to get back to it.

But in the meantime:
I have a new job.  Yeah, it was pretty sudden - finished my old job at the end of the year, and jumped directly into the new one at the beginning of the new year.  And the biggest shock is that this old Oracle DBA and Database Architect is going to be designing a database to be implemented in Microsoft SQL Server.  No, I didn't lie about my credentials, but I've worked with this boss before, and he said, "Oh, you'll do fine.  You're a smart guy.  And SQL is SQL, right? RDBMS is RDBMS, right?"

So, here I am, the newbie, posing as an expert.  I've been learning all I can about SQL Server, and trying things out, watching tutorials (Microsoft has some really good video lessons, designed especially for the Oracle DBA transitioning.)  Here are some first impressions:

• SQL is SQL.  Yes, there are differences, but the basics of the SELECT, INSERT, UPDATE, DELETE, and most of the DDL commands too, are pretty close.
• T-SQL is NOT PL/SQL.  Think of Transact-SQL as more like a SQL*Plus script with some flow control statements like IF/ELSE.  Some of it is nice, like no difference between a substitution variable and a language variable.  I think (not sure) that a T-SQL variable does act as a bind variable where PL/SQL variables would, but you can do things that PL/SQL would have to put inside an EXECUTE IMMEDIATE.  Cool.
• I miss PL/SQL packages.
• All triggers are statement level triggers.  This means that you write triggers very differently. Hard to get used to.  That doesn't mean you can't do row-level stuff - instead of getting a pseudo-record with the contents of the pre-DML row (the :OLD record) and one with the contents of the post-DML row (the :NEW record) you get pseudo-tables containing the old and new rows.  I don't know for sure, but I think this means that you don't deal with mutating table problems.
• The real kicker is that most triggers are AFTER statement triggers. If you want to do BEFORE stuff, you write an INSTEAD OF trigger.  In Oracle, INSTEAD OF is just for views.  Just like Oracle, the INSTEAD OF trigger needs to do the DML on the underlying table(s).
• There are two SQL Developer-like GUI tools - the SQL Server Management Studio, and SQL Server Data Tools for Visual Studio.  These are nice, but I wish they had a good SQL formatting tool.
• SQL Developer CAN talk to a SQL Server database, which is nice. But it doesn't really understand T-SQL, so it can have problems with it.  I did use its formatting tool on some SELECTs, however, and it worked pretty well.  Also, if you export a table as INSERTs - it writes them as Oracle INSERTs - doesn't work for dates because SQL Server doesn't have TO_DATE.
• Oracle has nothing like the SQL Server Integration Server - this is nice but TOTALLY new to me.
• SQL Server Reporting Server and its report building tool are MUCH better than SQL Reports from Oracle.  But then, I have always hated Oracle's reporting tool.

A Work-Around for Security in ADF Essentials 12.2.1

In my previous post, I talked about ADF Essentials and configuring it for authentication and authorization.  And I reported a problem with the new 12.2.1 versions that raises an exception trying to initialize a UserProfile object.  I surmise that Oracle's newer versions call a facility that is only in Oracle WebLogic server, and NOT in the typical application server running ADF Essentials, notably Glassfish.  But, until Oracle fixes this (PLEASE Oracle) I have tested a workaround.  This was suggested by a comment on this post by Andrejus Baranovski (and if you are using ADF, you NEED to read Andrejus' blog - it is GREAT!)  http://andrejusb.blogspot.com/2012/10/adf-essentials-security-implementation.html.

The comment is by Ivan Melluso, who said:

I'm using JDeveloper 12.2.1, Glassfish 4.1.1, ADF Essentials 12.2.1;
I obtained
NoClassDefFoundError: Could not initialize class oracle.adf.share.security.identitymanagement.UserProfile
Then I discover the UserProfile class initialize a MDS session, that can't exists in GlassFish;

My solution:
- Decompile the class
- Rewrite it preserving package name and all public methods
- Create a new jar
- Place it in /lib directory

I restart the server 5 times to check the class conflict (same class exists in adf-share-ca.jar and my jar) and all 5 times I don't obtain the error

January 27, 2017 at 12:47 PM

So the work-around is to write our own version of UserProfile and get the application to call ours instead of Oracle's original.  Here is my version:

package oracle.adf.share.security.identitymanagement;

import java.io.Serializable;

import java.security.Principal;

import java.util.HashMap;

import javax.faces.context.FacesContext;

public class UserProfile implements Serializable {
    @SuppressWarnings("compatibility:-6264967054558894560")
    private static final long serialVersionUID = 1L;

    public UserProfile() {
        super();
    }

    java.util.HashMap properties = new HashMap();


    public Object getProperty(String propName) {
        return properties.get(propName);
    }

    public void setProperty(String name, Object value) {
        properties.put(name, value);
    }

    public void setBusinessCity(String businessCity) {
        setProperty("businessCity", businessCity);
    }

    public String getBusinessCity() {
        return (String)getProperty("businessCity");
    }

    public void setBusinessCountry(String businessCountry) {
        setProperty("businessCountry", businessCountry);
    }

    public String getBusinessCountry() {
        return (String)getProperty("businessCountry");
    }

    public void setBusinessEmail(String businessEmail) {
        setProperty("businessEmail", businessEmail);
    }

    public String getBusinessEmail() {
        return (String)getProperty("businessEmail");
    }

    public void setBusinessFax(String businessFax) {
        setProperty("businessFax", businessFax);
    }

    public String getBusinessFax() {
        return (String)getProperty("businessFax");
    }

    public void setBusinessMobile(String businessMobile) {
        setProperty("businessMobile", businessMobile);
    }

    public String getBusinessMobile() {
        return (String)getProperty("businessMobile");
    }

    public void setBusinessPager(String businessPager) {
        setProperty("businessPager", businessPager);
    }

    public String getBusinessPager() {
        return (String)getProperty("businessPager");
    }

    public void setBusinessPhone(String businessPhone) {
        setProperty("businessPhone", businessPhone);
    }

    public String getBusinessPhone() {
        return (String)getProperty("businessPhone");
    }

    public void setBusinessPOBox(String businessPOBox) {
        setProperty("businessPOBox", businessPOBox);
    }

    public String getBusinessPOBox() {
        return (String)getProperty("businessPOBox");
    }

    public void setBusinessPostalAddr(String businessPostalAddr) {
        setProperty("businessPostalAddr", businessPostalAddr);
    }

    public String getBusinessPostalAddr() {
        return (String)getProperty("businessPostalAddr");
    }

    public void setBusinessPostalCode(String businessPostalCode) {
        setProperty("businessPostalCode", businessPostalCode);
    }

    public String getBusinessPostalCode() {
        return (String)getProperty("businessPostalCode");
    }

    public void setBusinessState(String businessState) {
        setProperty("businessState", businessState);
    }

    public String getBusinessState() {
        return (String)getProperty("businessState");
    }

    public void setBusinessStreet(String businessStreet) {
        setProperty("businessStreet", businessStreet);
    }

    public String getBusinessStreet() {
        return (String)getProperty("businessStreet");
    }

    public void setDateofBirth(String dateofBirth) {
        setProperty("dateofBirth", dateofBirth);
    }

    public String getDateofBirth() {
        return (String)getProperty("dateofBirth");
    }

    public void setDateofHire(String dateofHire) {
        setProperty("dateofHire", dateofHire);
    }

    public String getDateofHire() {
        return (String)getProperty("dateofHire");
    }

    public void setDefaultGroup(String defaultGroup) {
        setProperty("defaultGroup", defaultGroup);
    }

    public String getDefaultGroup() {
        return (String)getProperty("defaultGroup");
    }

    public void setDepartment(String department) {
        setProperty("department", department);
    }

    public String getDepartment() {
        return (String)getProperty("department");
    }

    public void setDepartmentNumber(String departmentNumber) {
        setProperty("departmentNumber", departmentNumber);
    }

    public String getDepartmentNumber() {
        return (String)getProperty("departmentNumber");
    }

    public void setDescription(String description) {
        setProperty("description", description);
    }

    public String getDescription() {
        return (String)getProperty("description");
    }

    public void setDisplayName(String displayName) {
        setProperty("displayName", displayName);
    }

    public String getDisplayName() {
        return (String)getProperty("displayName");
    }

    public void setEmployeeNumber(String employeeNumber) {
        setProperty("employeeNumber", employeeNumber);
    }

    public String getEmployeeNumber() {
        return (String)getProperty("employeeNumber");
    }

    public void setEmployeeType(String employeeType) {
        setProperty("employeeType", employeeType);
    }

    public String getEmployeeType() {
        return (String)getProperty("employeeType");
    }

    public void setFirstName(String firstName) {
        setProperty("firstName", firstName);
    }

    public String getFirstName() {
        return (String)getProperty("firstName");
    }

    public void setGivenName(String givenName) {
        setProperty("givenName", givenName);
    }

    public String getGivenName() {
        return (String)getProperty("givenName");
    }

    public void setGUID(String GUID) {
        setProperty("GUID", GUID);
    }

    public String getGUID() {
        return (String)getProperty("GUID");
    }

    public void setHomeAddress(String homeAddress) {
        setProperty("homeAddress", homeAddress);
    }

    public String getHomeAddress() {
        return (String)getProperty("homeAddress");
    }

    public void setHomePhone(String homePhone) {
        setProperty("homePhone", homePhone);
    }

    public String getHomePhone() {
        return (String)getProperty("homePhone");
    }

    public void setInitials(String initials) {
        setProperty("initials", initials);
    }

    public String getInitials() {
        return (String)getProperty("initials");
    }

    public void setJpegPhoto(byte[] jpegPhoto) {
        setProperty("jpegPhoto", jpegPhoto);
    }

    public byte[] getJpegPhoto() {
        return (byte[])getProperty("jpegPhoto");
    }

    public void setLastName(String lastName) {
        setProperty("lastName", lastName);
    }

    public String getLastName() {
        return (String)getProperty("lastName");
    }

    public void setMaidenName(String maidenName) {
        setProperty("maidenName", maidenName);
    }

    public String getMaidenName() {
        return (String)getProperty("maidenName");
    }

    public void setManager(String manager) {
        setProperty("manager", manager);
    }

    public String getManager() {
        return (String)getProperty("manager");
    }

    public void setMiddleName(String middleName) {
        setProperty("middleName", middleName);
    }

    public String getMiddleName() {
        return (String)getProperty("middleName");
    }

    public void setName(String name) {
        setProperty("name", name);
    }

    public String getName() {
        return (String)getProperty("name");
    }

    public void setNameSuffix(String nameSuffix) {
        setProperty("nameSuffix", nameSuffix);
    }

    public String getNameSuffix() {
        return (String)getProperty("nameSuffix");
    }

    public void setOrganization(String organization) {
        setProperty("organization", organization);
    }

    public String getOrganization() {
        return (String)getProperty("organization");
    }

    public void setOrganizationalUnit(String organizationalUnit) {
        setProperty("organizationalUnit", organizationalUnit);
    }

    public String getOrganizationalUnit() {
        return (String)getProperty("organizationalUnit");
    }

    public void setPreferredLanguage(String preferredLanguage) {
        setProperty("preferredLanguage", preferredLanguage);
    }

    public String getPreferredLanguage() {
        return (String)getProperty("preferredLanguage");
    }

    public Principal getPrincipal() {
        Principal principal =
            FacesContext.getCurrentInstance().getExternalContext().getUserPrincipal();
        return principal;
    }

    public void setProperties(HashMap properties) {
        setProperty("properties", properties);
    }

    public HashMap getProperties() {
        return (HashMap)getProperty("properties");
    }

    public void setTimeZone(String timeZone) {
        setProperty("timeZone", timeZone);
    }

    public String getTimeZone() {
        return (String)getProperty("timeZone");
    }

    public void setTitle(String title) {
        setProperty("title", title);
    }

    public String getTitle() {
        return (String)getProperty("title");
    }

    public void setUIAccessMode(String UIAccessMode) {
        setProperty("UIAccessMode", UIAccessMode);
    }

    public String getUIAccessMode() {
        return (String)getProperty("UIAccessMode");
    }

    public void setUniqueName(String uniqueName) {
        setProperty("uniqueName", uniqueName);
    }

    public String getUniqueName() {
        return (String)getProperty("uniqueName");
    }

    public void setUserID(String userID) {
        setProperty("userID", userID);
    }

    public String getUserID() {
        return (String)getProperty("userID");
    }

    public void setUserName(String userName) {
        setProperty("userName", userName);
    }

    public String getUserName() {
        return (String)getProperty("userName");
    }

    public void setWirelessAcctNumber(String wirelessAcctNumber) {
        setProperty("wirelessAcctNumber", wirelessAcctNumber);
    }

    public String getWirelessAcctNumber() {
        return (String)getProperty("wirelessAcctNumber");
    }
   
    public void saveProfile () {
        // do nothing
    }

}

Now, how to package it, and where to put it.  We need to make sure that ours is used instead of Oracle's.  We could do what Ivan did, and package it in our own jar and add the jar to the lib folder.  Well, I'm not confident that it will always use mine instead of Oracle's - depends on what the class loader does, and I'm not really that experienced with how java and Glassfish really work.  So I got out my trusty 7-Zip and replaced UserProfile in Oracle's jar, adf-share-ca.jar with my version of the class.  This works, no more Exception.

The real question - is this legal? If I started using this in production, could Oracle come after me.  Honestly, I doubt that they would bother, but could they?  On the other hand, if Oracle would fix this, I wouldn't do it.  After all, to deploy my application, I have to deploy MY version of adf-share-ca.jar, and I have to make a new version of the jar anytime Oracle's changes.

Authentication and Authorization in ADF Essentials 12.2.1.x on Glassfish 4

I think I’ve said before how much I appreciate that Oracle provides a subset of ADF functionality for free, in the library called ADF Essentials.  ADF Essentials will let you write ADF applications that can be deployed to pretty much any JEE standard application server, and Glassfish is specifically supported.  I find that this is a good selling point for ADF because my clients may find Oracle WebLogic Server (WLS) a bit hefty for their needs, and may balk at the licensing and support costs for WLS.

One of the problems with the early versions of ADF Essentials is that they are intended for Glassfish 3.1.2 and may not work properly with the latest version, Glassfish 4.1.1.  So I was delighted when ADF 12.2.1.0 was released, and included ADF Essentials from the beginning.  And it was certified for Glassfish 4.  I set to work, and soon I had my standard test application on the HR sample schema working in Glassfish 4.1.1 with ADF Essentials 12.2.1.1.  It ran just as well on Glassfish as it did on the Integrated WLS, maybe even as little faster.  Glassfish 4.1.1 has a few bugs and quirks that I wish would be fixed, and I’m sorry to say that Oracle is phasing out its support for the Open Source Glassfish project – so members of the community are going to have to step forth to continue the work.  But generally, it works.

Now for the next stage of my test application which was adding authentication and authorization.  One of the missing pieces in the ADF Essentials subset that is part of full ADF is ADF Security.  This is missing, because it relies on some capabilities that are part of WLS, and not part of the JEE standard.  This doesn’t mean that you can’t write secure applications with ADF Essentials, but you must use the capabilities that are part of the JEE standard or a third party library like Spring Security.  I’d done this before with ADF 12.1.3 and Glassfish 3.2.1, so I had some experience with setting it up.  The steps are:

1. Add a security realm to Glassfish.  ADF defaults to using the name, “myrealm”, but you can change this and use pretty much any name you like.  For simple testing, you might want to use the “file” ream, which will allow you to add test users and their security groups in the Glassfish admin console.  Later, you can replace this with a realm based on an LDAP directory or some database tables.
2. Optionally, add one or more security roles in web.xml.  These roles are used for authorization to govern what users may do in your application.  If you don’t use roles, you’ll have to authorize by username, so I always do this.
3. Add one or more security constraints in web.xml.  In the simplest configuration, a constraint will protect particular pages in the application, and allow only certain users or users with certain roles to access these pages.  It can be more complex than that, but let’s stick with the simple case.  The application will require authentication only when the user tries to access a protected page, and has not yet been authenticated.
4. Configure authentication in web.xml with the login-config element.  This tells the application server how to get the username and password.  There are several ways to do this, and the most common way is with a login form, so you may have to add a page with the required login fields.
5. Add a glassfish-web.xml file in WEB-INF if you don’t already have one, and use it to map the security roles in web.xml to the security groups that will be returned from your Glassfish security realm.  You may need this file anyway to map the data sources used by your application to the data sources that you added to Glassfish.

So, I did these with my test application, re-deployed it, and tried it.  It showed the login page as expected, showed the error page if I typed in an incorrect username and/or password, but when I typed in the correct username and password, I saw the following error:

javax.servlet.ServletException: Filter execution threw an exception
root cause
java.lang.NoClassDefFoundError: Could not initialize class

 oracle.adf.share.security.identitymanagement.UserProfile 

After making sure that I had made no discernible error, I posted a question in the JDeveloper and ADF Forum on the Oracle Communities - Filter exception ADF Essentials 12.2.1.1 on Glassfish 4.  There was a considerable amount of discussion, and other people reported the same problem.  Several of us did some additional testing.  One thing I immediately learned is that the application works fine in the Integrated WebLogic Server.  This points to a problem that is related to running with ADF Essentials on Glassfish.

As far as I can determine, there has been a change to the ADF Controller that is part of ADF 12.2.1 including the one that is part of ADF Essentials.  When a user authenticates, the user’s name is in the userPrincipal property of the external context of the application.  The ADF Controller is probably picking up that this is not null and not equal “anonymous” and deciding that it needs to initialize a UserProfile for the user.  The library with this class is adf-share-ca.jar which IS part of ADF Essentials, but the constructor for this class must be calling something that is unique to WLS, and not available in Glassfish.  So it fails.  It does not matter how the external context property is set.  I tried it with Spring Security, which is an alternative to standard JEE authentication, but still does set the external context.  It had the same problem.

As it stands now, unless the ADF developers listen to our pleas to fix this, it is not possible to use authentication and authorization in an ADF Essentials 12.2.1.x application.  At least, it is not possible with an out-of-the-box deployment of the ADF Essentials libraries.  In my next post, I will detail a work-around.

Same blog, new location

This is the continuation of my blog at JJFlash's Oracle Development Journal - the story of an old developer learning new tricks.  I'll be posting here odds and ends about Relational Database design and development, especially Oracle RDBMS, Oracle Application Development Framework, Java, and whatever else comes to my mind as an IT expert.

I hope you enjoy it.