There is an interesting W3C Draft, that enables websites to just simply ask web browser to report the users geographical location, and then the web browser will try the best available location method, like GeoIP, WIFI location or GPS. I have currently tested it on Firefox 3.6 and Google Chrome; Internet Explorer 8.0 does not support it yet.

W3C Geolocation API Draft
Mozilla documentation for Geolocation

How to use it in APEX?

If you are just interested in recording the users location, then using an on-demand application process should be the easiest solution:

First, create two application items: USER_LOC_LATITUDE and USER_LOC_LONGITUDE. They are used for storing users location.

Then, create an On Demand application process SAVE_USER_LOCATION. Create your necessary application logic in that process to handle the user location. The user location is available through application items USER_LOC_LATITUDE and USER_LOC_LONGITUDE.

And finally, include the following HTML code to your page. This uses APEX AJAX JavaScript API to call the created application process as soon as the users location becomes available for the browser. Please note also, that the browser asks for users permission for reporting the location.

<script type="text/javascript">

  if(navigator.geolocation) {
    navigator.geolocation.getCurrentPosition(function(position) {

      var get = new htmldb_Get(null, $x('pFlowId').value, 
        'APPLICATION_PROCESS=SAVE_USER_LOCATION', 0);
      get.add('USER_LOC_LATITUDE', position.coords.latitude);
      get.add('USER_LOC_LONGITUDE', position.coords.longitude);
      gReturn = get.get();
      get = null;

    });
  }

</script>

To continuously monitor user position, use the function navigator.geolocation.watchPosition instead of navigator.geolocation.getCurrentPosition.

Resolving coordinates to location name

Here is one package, that uses GeoNames.org database for resolving the location name. The package requires Oracle 11.2.

The geolocation package
One helper package, HTTP_UTIL, for downloading XML over HTTP

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:

• 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') 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.

I started to play around with DBFS (Database Filesystem), a new interesting feature in Oracle 11.2.

There are some excellent guides on how to set it up:
http://www.oracle-base.com/articles/11g/DBFS_11gR2.php
http://blog.ronnyegner-consulting.de/2009/10/08/the-oracle-database-file-system-dbfs/

But both of them had one “not very clean” part in them – compiling FUSE kernel driver from source. Actually this is not necessary and there are precompiled FUSE rpm packages available:
http://dag.wieers.com/rpm/packages/fuse/

After RPM package is installed, you need to add the OS user who will mount the filesystem (for example oracle) to OS group fuse.

usermod -a -G fuse oracle

A small SQL code sample this time, how to read RSS file in SQL. I’m using it to display and aggregate some RSS feeds in APEX application.
All columns are directly read from XML (plus some additinal code to get the time zone correct), except pubdate_local that is pubdate converted to date datatype in local time zone. The first example is reading from a local file localnews.rss under directory DIR1.

SELECT title,
       link,
       description,
       author,
       pubdate,
       CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
  FROM (    SELECT title,
                   link,
                   description,
                   author,
                   TO_TIMESTAMP_TZ (
                      REPLACE(pubdate, 'PDT', 'PST PDT'),
                      CASE
                         WHEN REGEXP_LIKE (pubdate, '[[:digit:]]{4}$')
                         THEN
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZHTZM'
                         ELSE
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZR TZD'
                      END,
                      'NLS_DATE_LANGUAGE = American')
                      pubdate
              FROM XMLTABLE (
                      '/rss/channel/item'
                      PASSING xmltype (BFILENAME ('DIR1', 'localnews.rss'),
                                       NLS_CHARSET_ID ('AL32UTF8'))
                      COLUMNS title VARCHAR2 (250 CHAR) PATH 'title',
                              link VARCHAR2 (250 CHAR) PATH 'link',
                              author VARCHAR2 (250 CHAR) PATH 'author',
                              description CLOB PATH 'description',
                              pubdate VARCHAR2 (50) PATH 'pubDate'));

And the same SQL when reading RSS directly from internet over HTTP (BFile is replaced with HttpUriType).

SELECT title,
       link,
       description,
       author,
       pubdate,
       CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
  FROM (    SELECT title,
                   link,
                   description,
                   author,
                   TO_TIMESTAMP_TZ (
                      REPLACE(pubdate, 'PDT', 'PST PDT'),
                      CASE
                         WHEN REGEXP_LIKE (pubdate, '[[:digit:]]{4}$')
                         THEN
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZHTZM'
                         ELSE
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZR TZD'
                      END,
                      'NLS_DATE_LANGUAGE = American')
                      pubdate
              FROM XMLTABLE (
                      '/rss/channel/item'
                      PASSING HttpUriType('http://feeds.feedburner.com/DougsOracleBlog').getXML()
                      COLUMNS title VARCHAR2 (250 CHAR) PATH 'title',
                              link VARCHAR2 (250 CHAR) PATH 'link',
                              author VARCHAR2 (250 CHAR) PATH 'author',
                              description CLOB PATH 'description',
                              pubdate VARCHAR2 (50) PATH 'pubDate'));

Thanks to Daniel Morgans session today at EMEA Harmony, I now have a personal goal to get the Oracle ACE title 🙂 Need to get working towards it and become more active in the community.

An excellent joint conference for Estonian, Finnish, Latvian and Russian Oracle User Groups in Tallinn, 20. – 21. May 2010.
Speakers also include Tom Kyte, Tanel Põder, Chris J. Date and Steven Feuerstein.

Read the agenda here and register in OUGF home page (250€+VAT registration fee).

In addition, just before the conference, 17.-18. May, Chris Date will perform his “How to Write Correct SQL and Know It: A Relational Approach to SQL” seminar in Helsinki. More info here and more detailed information here.