Skip navigation.

The Anti-Kyte

Syndicate content The Anti-Kyte
Oracle - for when it was like that when you got there
Updated: 11 hours 41 min ago

Implementing a Database Authentication Scheme in APEX

Sun, 2014-12-14 12:26

The following tangential opening was written especially for Scott Wesley in the hope that he’ll be minded to point out any errors in what follows. The same applies to Jeff Kemp ( although I don’t know if he’s into the AFL).
Unlike me, both of these guys are APEX experts.

Football. It’s a term that means different things to different people.
To a European, it’s most likely to be a reference to good old Association Football ( or Soccer).
To an American, it’s more likely to be the Grid-iron game.
A New Zealander will probably immediately think of Rugby Union.
An Australian ? Well, it’s probably a fair bet that they’ll think of Aussie Rules Football.

On the face of it, the rules appear rather arcane to an outsider. 18-a-side teams kicking, catching and punching something that resembles a Rugby ball around a pitch that resembles a cricket oval. Then there is the scoring system.
“Nice Behind”, to an AFL player is more likely to be taken as a compliment of their skill at the game than an appreciation of their anatomy.

Then again, it’s easy to scoff at any sport with which you are unfamiliar.
For example, Rugby could be characterised as 30 people chasing after an egg. Occasionally, they all stop and half of them go into some strange kind of group hug. I wonder if the backs ever get paranoid because they think the forwards are talking about them ?

As for soccer, even afficionados will acknowledge that there’s something a bit odd about a game where 22 millionares spend lots of time chasing after one ball…when they’re not rolling around in apparent agony after appearing to trip over an earth worm. I mean, the ball isn’t that expensive, surely they can afford one each ?

The point of all of this ? Well, what is considered to be obscure, eccentric, or just plain odd often depends on the perspective of the observer.

Take APEX authentication schemes for example.
Whilst not the default, Database Authentication is a scheme that is readily available. However, there doesn’t seem to be much written on this subject.

In contrast, there is a fair bit out there about APEX Custom Authentication. A lot of it would appear to re-enforce the idea that implementing security by hand is fraught with difficulty.
Just one example can be seen here.

If we were to approach this topic from the perspective of looking to migrate an elderly Oracle Forms application – where each user has their own database account – to APEX, we might be attracted to the idea of a Database Authentication Scheme and want to find out more.

What follows is my adventure through setting up such an Authentication Scheme.
Specifically, I’m going to cover :

  • Creating an APEX Database Authentication Scheme
  • Default behaviour
  • Adding a Verification Function to restrict access to a sub-set of Database Users
  • The vexed question of password resets

Why use Database Authentication

The Oracle documentation states :

“Database Account Credentials is a good choice if having one database account for each named user of your application is feasible and account maintenance using database tools meets your needs.”

If we’re migrating an application from Oracle Forms, then chances are that this is what we’re doing now, so a Database Authentication Scheme should save us a fair bit of work.
The other major advantage is that utilising the Database’s built-in User and Security management means that we don’t have to try and re-invent the wheel.
So, the objective here is to implement Authentication in our new Application without having to :

  • Create and maintain extra tables
  • Write lots of extra code
  • Figure out a secure way of storing passwords
The Application

Firing up my trusty XE 11g installation, I’ll be using a simple APEX application that consist of a standard login page and, initially at least, a Home Page with two read only fields in an HTML Region called WHOAMI.
These are :

  • Application User – the APP_USER that I’m connected to APEX as
  • Database User – the actual user connected to the database

For the P1_APPLICATION_USER, the Source Type is Item (application or page item name).
The source value is APP_USER.
For the P1_DATABASE_USER, the Source Type is set to SQL Query(return single value).
The source value is simply the query :

select user from dual
A Note on the Design

In this example, I’ve taken the approach that the code required to implement this functionality is included in the parsing schema ( HR in this case). As a consequence, the privileges required to execute this code are also granted to the parsing schema.
I’ve done this for the purposes of clarity.
Careful consideration needs to be given to this design decision if you’re planning to implement it in a “proper” production environment.

Creating a Database Authentication Scheme

After navigating to the Application in Application Builder, rather than do anything to the Application itself, we need to create a Shared Component…

db_auth1

The type of component we want is an Authentication Scheme.

NOTE – Authentication Scheme – controls login to the Application.
Authorisation Scheme – governs which bits of the Application the user can see…once they’re connected.

Anyway, in the Security Region, select Authentication Scheme :

db_auth2

…and then hit the Create button…

db_auth3

We want to create a scheme “Based on a pre-configured scheme in the gallery” …

db_auth4

In the next screen :

Name : HR_DB
Scheme Type : Database Accounts

db_auth5

And finally, we click the Create Authentication Scheme button and…

db_auth6

We can see from this that HR_DB is now the Authentication Scheme currently being used by any Application in the Workspace.

Anyway, now to test it.

To this point, I haven’t setup any users for this application.

So, Can I log in as a user that does exist in the database ?
Well, I have a user called MIKE :

select 1
from dba_users
where username = 'MIKE'
/

         1
----------
         1

SQL> 

So, if I now run my application and try to connect using my database credentials…

run_app2

… I can connect using my database credentials.

db_auth_ok1

It’s worth noting that, despite this, the actual database connection from APEX is as the ANONYMOUS user.
If you’re using the APEX Listener instead of the Embedded PL/SQL Gateway (the default in XE), then it’ll probably be APEX_PUBLIC_USER.

So, in order to login to my application, you now have to be a database user.
All the messy password encryption stuff is handled by Oracle and I can now get on with polishing my finely crafted APEX Application….or so you might think.

Just consider this :

run_app_system

…also let’s you connect :

We're not fussy, we'll let anyone in !

We’re not fussy, we’ll let anyone in !

Now, my imaginary Forms application – remember, that’s the one I want to migrate to APEX – may be sitting on a Database Instance with a number of other Applications. So, how do I restrict access to my application to a subset of the users in the database ?
Time for a bit of a re-think then…

The verify function

What we need is a means of identifying a database user as an Application user.
At this point it may well be worth revisiting the role of database roles in APEX applications.
Hang on, you’re thinking, last time you said they were pretty much useless in APEX.
Well, bear with me.

Roles as Privileges, sort of

What we’re going to do here is to simply create an empty role and assign it to a database user :

create role hr_user
/

grant hr_user to mike
/

We now have some means of determining which database users are our application users :

select 1
from dba_role_privs
where granted_role = 'HR_USER'
and grantee = 'MIKE'
/
The function

Now all we need is a function that checks to see if the user attempting to login has this role granted to them.
It’s worth bearing in mind here that, for a function based on the above statement, select privileges on DBA_ROLE_PRIVS is required.

To start with I’m going to grant the privilege to HR :

grant select on sys.dba_role_privs to hr
/

and then I’m going to create the function in the HR schema :

create or replace function is_hr_user_fn
    return boolean
is
--
-- Is this user a database user with privileges to access the APEX Application ?
-- NOTE - the owner of this function requires SELECT privilege on DBA_ROLE_PRIVS
--
    l_dummy pls_integer;
begin
    select 1 
    into l_dummy 
    from sys.dba_role_privs
    where granted_role = 'HR_USER'
    and grantee = apex_040200.v('APP_USER');
    
    return true;
exception
    when no_data_found then
        raise_application_error('-20000', 'You are not an application user');
end;
/ 

You’ll note that the references to both DBA_ROLE_PRIVS and the V function are done directly on the objects themselves rather than through their public synonyms.
In many cases, but especially where security is concerned, it’s usually a good idea to make sure that you’re referencing the object that you intend rather than relying on a synonym.

If you want to see an example of how public synonyms can be changed to point to objects other than those originally intended, then have a look here.

Now we need to tell our Authentication scheme to use this function as the Verify Function.
In the Application Builder, go back to the Shared Components screen then select Authentication Schemes.

Now click on the pencil icon next to HR_DB – Current :

If you want to be a bit more discerning...

If you want to be a bit more discerning…

In the Session Not Valid section, there is a field called Verify Function Name.
In here, simply enter the name of our function – i.e. is_hr_user_fn :

...add a Verify Function

…add a Verify Function

And save the changes.

So, we should now be able to connect as MIKE, but not any other database user.

Connecting as MIKE works as before. However, for SYSTEM, the results are slighty different :

Your name's not down, you're not coming in !

Your name’s not down, you’re not coming in !

As we can see, the Application Error raised by the function is displayed. If you hit the OK button, you’ll then be returned to the Login Page.

The Principle of Least Privilege

In case your not familiar with the term, it basically boils down to the principle that access to an application should be restricted to the minimum level required for a user, application or program to function.
Have a look here for a proper explanation.

It’s probably worth noting that, implementing this approach to Authentication means that, in order to create a new application user, all that’s required is the following :

create user plugger identified by pwd
/

grant hr_user to plugger
/

In case you’re wondering, Plugger is the nickname of a certain Tony Lockett who, apparently, was a pretty good Aussie Rules player in his time.

Anyway, as you can see, our new user requires no system privileges, not even CREATE SESSION. They simply need to be granted the role so that they can be identified as an application user.

Whilst were on the subject of least privilege, you might consider that it is by no means necessary for the parsing schema of an APEX application to have CREATE SESSION priviliges, or indeed, to even be the owner of the application’s database objects.
This applies irrespective of the Authentication Scheme being used.

We now have a robust and efficient Authentication Scheme. There is however, one rather thorny issue that we still need to consider.

Changing Passwords

Whilst we now have a mechanism for authenticating users through their database accounts, unless we give them the facility to change their passwords before they expire, we’ll be storing up a significant amount of admin for the poor, hard-pressed DBA.

The venerable Forms Application we’re migrating was written in the days prior to SSO becoming prevalent and authentication is still managed entirely within the database. Remember, the whole point of chosing Database Authentication is so that we minimise the amount of effort required to migrate this application onto APEX in terms of re-coding the Application’s Security Model.

This is where things get a bit tricky.
Whilst our users are authenticating as themselves, they are actually connecting to the database as ANONYMOUS or APEX_PUBLIC_USER.

Therefore, we need a procedure in a schema with ALTER USER privileges to change passwords from within the APEX application.

So, how do we provide this functionality in our application.

Danger ! Assumption Imminent !

As I’m all too aware ( often through bitter experience), Assumption is the Mother of all cock-ups.
Therefore, the assumption I’m about to make here requires careful explanation.

Here goes then…

I’m assuming that I can safely call a stored procedure from within APEX, passing a user password in clear text.

Clear text ! I hear you cry, Have you gone mad ?
Well, possibly. On the other hand a trawl through of the APEX documentation reveals that there are a few package members in the APEX packages themselves where this takes place.

These are :

Further research reveals that, certainly in the latest versions of APEX, there do not appear to be any exploits available to compromise these procedures. The most recent one I found was for APEX 3.1, an example of which can be seen on the Red Database Security site.

As well as giving the user the ability to change their password at any time, we also want to check immeadiately after the user connects and find out whether their password is near to expiry. If so, then we need to re-direct them to a password change page.
What was Jeff saying about scary code ?

Anyway, the steps to build this functionality are, in order :

  1. Create a Change Password Procedure to be called from the application
  2. Create a Change Password Page where the user can change their password ( and which will call the procedure)
  3. Create a branch in the Application to re-direct a user to the Change Password Page if their password is due to expire
Allowable characters in the password

As we’re going to have to change the password by executing an ALTER USER command from within a PL/SQL procedure, we’re going to have to use dynamic SQL. Critically, we’re not going to be able to use bind variables for this command because it’s a DDL statement.
In order to ensure that the resulting procedure is not vulnerable to SQL Injection, we’re going to have to make sure that passwords do not contain the single quote (‘) character.

To do this, we’re going to create a profile for our application users which includes a password verify function and assign it to them.

So, the Password Verify Function, which needs to be created in the SYS schema, looks like this :

create or replace function new_hr_verify_fn
(
    username varchar2,
    password varchar2,
    old_password varchar2
)
    return boolean 
is 
--
-- A very simple password verify function. In this case. all we're interested in
-- is that the password should not contain a quote (') character.
-- NOTE : this function is purely to illustrate this sole restriction.
-- A proper password verification function would be rather more extensive.
--
begin
    if instr(password, q'[']') > 0 then
        raise_application_error(-20000, q'[Password cannot contain a "'"]');
    end if;
    return true;
end;
/

A quick test of this function shows that it works as expected :

set serveroutput on size unlimited
declare
--
-- Script to test the password verify function
--
    type typ_passwords is table of varchar2(100)
        index by pls_integer;
        
    tbl_passwords typ_passwords;
    
    lc_old_password constant varchar2(50) := 'DUMMY';
    
    l_dummy boolean;
begin
    tbl_passwords(1) := q'[Hawthorn Top O' the heap!]';
    tbl_passwords(2) := 'Tony||chr(39)||or 1=1||chr(39)';
    tbl_passwords(3) := q'[Tony '; select * from dba_users --]';
    tbl_passwords(4) := 'beware men with funny shaped balls';
    for i in 1..tbl_passwords.count loop
        begin
            l_dummy := new_hr_verify_fn
                (
                    username => user,
                    password => tbl_passwords(i),
                    old_password => lc_old_password
                );
            dbms_output.put_line('Test '||i||' Password '||tbl_passwords(i)||' is allowed');
        exception when others then
            dbms_output.put_line('Test '||(i)||' ERROR : '||sqlerrm);
        end;
    end loop;
end;
/

Run this as we get…

SQL> @test_verify
Test 1 ERROR : ORA-20000: Password cannot contain a "'"
Test 2 Password Tony||chr(39)||or 1=1||chr(39) is allowed
Test 3 ERROR : ORA-20000: Password cannot contain a "'"
Test 4 Password beware men with funny shaped balls is allowed

PL/SQL procedure successfully completed.

Note that, although the string containing “chr(39)” is allowed, because there is no way to concatenate a quote into the entry string, this is treated as a collection of characters rather than a call to the CHR function.
Incidentally 39 is the ASCII code for a single quote.

Also note that this particular password verify function has been kept simple deliberately for the purposes of clarity.
Something rather more complex is likely to be in place in a real-life production scenario.

The profile then, looks like this :

create profile hr_default limit
    failed_login_attempts 10
    password_life_time 30
    password_reuse_time 1800
    password_reuse_max 60
    password_lock_time 1
    password_grace_time 7
    password_verify_function new_hr_verify_fn
/

Finally, we’re going to assign the profile to PLUGGER :

alter user plugger profile hr_default
/
The Change Password Procedure

Once again, this procedure is being created in the HR schema. It will be used to ultimately issue the ALTER USER command to change the passwords. Therefore, we need to grant the ALTER USER privilege to HR :

grant alter user to hr
/

As this procedure also needs to reference DBA_USERS, we’ll need to grant SELECT on that too.

grant select on sys.dba_users to hr
/

When writing this procedure, paranoia is the watchword. Objects need to be referenced directly, rather than via synonyms and any user input needs to be sanitised before we plug it into the dynamic SQL statement we need to run.
The result might look something like this :

create or replace procedure change_apex_user_pwd_pr
(
    i_old_pwd in varchar2,
    i_new_pwd in varchar2
)
is
--
-- Procedure to change the password for a user of the NEW_HR APEX application
-- The old password is required, as well as the new one because, if we're
-- using a verify function in the profile the user is assigned to, the
-- old password must be specified in the ALTER USER statement.
--
    l_user sys.dba_users.username%type;
    
    lc_apex_user constant sys.dba_users.username%type := 'ANONYMOUS';
    
    l_dummy pls_integer;
    cursor c_validate_user( cp_user sys.dba_users.username%type)
    is
        select 1
        from sys.dba_users usr
        inner join sys.dba_role_privs rol
            on rol.grantee = usr.username
        where usr.username = cp_user;
begin
    --
    -- Make sure that the parameter values have been specified
    --
    if i_new_pwd is null or i_old_pwd is null then
        raise_application_error(-20000, 'Both the Old Password and the New Password must be specified');
    end if;
    --
    -- Sanitize the user input parameters to prevent SQL Injection.
    -- This boils down to rejecting strings that contain a "'"
    --
    if instr(i_old_pwd, q'[']') > 0 
        or instr(i_new_pwd, q'[']') > 0
    then
        raise_application_error(-20001, 'Passwords must not contain the '||CHR(39)||' character.');
    end if;
    --
    -- Additionally, check that the password does not exceed the maximum length
    -- allowed ( 50 in 11g)
    --
    if length( i_old_pwd) > 50 
        or length( i_new_pwd) > 50
    then
        raise_application_error(-20002, 'Passwords must not exceed 50 characters in length.');
    end if;
    --
    -- Now validate that the user is indeed 
    --  (a) calling the function from APEX
    --  (b) exists in the database
    --  (c) is a user of this application
    -- ...also check that the username does not contain a quote character
    -- to guard against a Blind Injection.
    --
    l_user := apex_040200.v('APP_USER');
    if l_user is null 
        or user != lc_apex_user
    then
        raise_application_error(-20003, 'This function can only be called from APEX');
    end if;
    open c_validate_user( l_user);
    fetch c_validate_user into l_dummy;
    if c_validate_user%notfound then
        close c_validate_user;
        raise_application_error(-20004, 'This user is not a NEW_HR Application user');
    end if;
    close c_validate_user;
    --
    -- Now change the password. REPLACE clause is required in case the
    -- user's default profile has a password verify function specified
    --
    execute immediate 'alter user '||l_user||' identified by '||i_new_pwd||' replace '||i_old_pwd;
end;
/

In the procedure itself, we’re taking a number of precautions :

  • Values for both parameters must be supplied
  • The input parameter values must not exceed 50 characters – the maximum length of an 11g password
  • The input parameter values must not contain a single quote character
  • The user currently connected to the database is the Apex user ( in my case ANONYMOUS)
  • A call to the V function for the application user returns a value
  • The application user we’re changing is indeed a valid user of the NEW_HR Apex application – and a database user
  • references to any database objects are done directly and not via synonyms

Hopefully, that’s enough paranoia to prevent the procedure being misused.
Once again, we can use a test harness to check the parameter tests at least :

set serveroutput on size unlimited
declare
--
-- test for the change_apex_user_pwd_pr procedure.
-- Note all of these tests should fail as we're running from SQL*Plus and
-- are not connected as ANONYMOUS.
-- 
    type rec_params is record
    (
        old_pwd varchar2(100),
        new_pwd varchar2(100)
    );
    type typ_params is table of rec_params index by pls_integer;
    tbl_params typ_params;
begin
    -- populate the test parameter array
    
    -- Test 1 - missing old password value
    tbl_params(1).old_pwd := null;
    tbl_params(1).new_pwd := 'Boring';
    
    -- Test 2 - missing new password value
    tbl_params(2).old_pwd := 'Boring';
    tbl_params(2).new_pwd := null;
    
    -- Test 3 - old password contains a quote
    tbl_params(3).old_pwd := q'[I'm a silly password]';
    tbl_params(3).new_pwd := 'sensible';
    
    -- Test 4 - new password contains a quote
    tbl_params(4).old_pwd := 'sensible';
    tbl_params(4).new_pwd := q'[Who's sensible now ?]';
    
    -- Test 5 - old password > 50 characters
    tbl_params(5).old_pwd := 'just leaning on the keyboard until i have printed over 50 characters zzzzz';
    tbl_params(5).new_pwd := 'short_and_to_the_point';
    
    -- Test 6 - new password > 50 characters
    tbl_params(6).old_pwd := 'short_and_to_the_point';
    tbl_params(6).new_pwd := 'just leaning on the keyboard until i have printed over 50 characters zzzzz';
    
    -- Test 7 - parameters are valid but we're not connected through APEX...
    
    tbl_params(7).old_pwd := 'Valid_pwd';
    tbl_params(7).new_pwd := 'anotherboringpassword';
    
    --
    -- Execute the tests
    --
    for i in 1..tbl_params.count loop
        begin
            change_apex_user_pwd_pr
            (
                i_old_pwd => tbl_params(i).old_pwd,
                i_new_pwd => tbl_params(i).new_pwd
            );
            dbms_output.put_line('Test '||i||' - Somthing has gone wrong - no error !');
        exception
            when others then
                dbms_output.put_line('Test '||i||' Error : '||sqlerrm);
        end;
    end loop;
end;
/

Running this gives us :

SQL> @change_pwd_test
Test 1 Error : ORA-20000: Both the Old Password and the New Password must be specified
Test 2 Error : ORA-20000: Both the Old Password and the New Password must be specified
Test 3 Error : ORA-20001: Passwords must not contain the ' character.
Test 4 Error : ORA-20001: Passwords must not contain the ' character.
Test 5 Error : ORA-20002: Passwords must not exceed 50 characters in length.
Test 6 Error : ORA-20002: Passwords must not exceed 50 characters in length.
Test 7 Error : ORA-20003: This function can only be called from APEX

PL/SQL procedure successfully completed.

SQL> 

To test the rest of the function, we will of course, need to be connected via APEX.

The Change Password Page

Now we come to the page we will be using to call the procedure we’ve just created.
The page will have :

  • a password field for the application user to enter their current password
  • a password field for the application user to enter their new password
  • and another one for them to re-type it
  • some validation that the new password and confirm password matches
  • a button to call the change password procedure
  • a field to present a message to the user after the password change call

Sounds simple (dangerous) enough…

In Application Builder hit the Create Page button…

select Blank Page ….

In the Page Attributes…

Page Alias : change_db_pwd

change_pwd1

In the Page Name …

Name : Change My Password
HTML Region1 : change password

change_pwd2

In Tab Options…

Tab Options : Use an existing tab set and create a new tab within the existing tab set
New Tab Label : Change Password

change_pwd3

…and hit Finish.

Now Edit the Page.

Create a new field with an Item Type of Password :

old_pwd1

In the Display Position and Name screen,

Item Name : PX_OLD_PWD (where X is the number of the page you’re editing).

old_pwd2

In the Item Attributes Screen :

Label : Current Password
Field Width : 50

old_pwd3

In the Settings Screen –

Value Required : Yes
Submit when Enter pressed : No

old_pwd4

In the Source Screen :

Source Used : Always, replacing any existing session state

Hopefully, the APEX5 Graphical Page Designer will result in fewer screenshots being required in future !

Hopefully, the APEX5 Graphical Page Designer will result in fewer screenshots being required in future !

And hit Create Item.

Now create two further fields with the same properties except :

PX_NEW_PWD has a label of New Password
PX_CONFIRM_PWD has a label of Confirm New Password

Next, we create a Display Only field called PX_MESSAGE.
We’ll use this to provide feedback to the user.
We define this with no label so that it doesn’t show up on the screen, until it’s populated.

Now we’ve got all of the fields on the page the next step is to create the Change Password button :

Accept the defaults for Button Region and Button Position.

In the Button Attributes Page :

Button Name : change_pwd_btn
Label : Change Password

cpwd_btn1

Then just hit Create Button.

Finally, we need to add a Dynamic Action to validate that the values in PX_NEW_PWD and PX_CONFIRM_PWD are not null and identical, and then to call the Procedure.

NOTE – I daresay any APEX experts reading this may have a better way of doing this !

So, Create a Dynamic Action.

In the Identification Page :

Name : change_pwd_da

da1

In the When Page :
Action : Click
Selection Type : Button
Button : CHANGE_PWD_BTN

da2

In the True Action Page :

Action : Execute PL/SQL Code

The PL/SQL Code is as follows :

begin
    if nvl(:P6_NEW_PWD, 'x') != nvl(:P6_CONFIRM_PWD, 'y')
    then
       :P6_MESSAGE := 'Confirm Password does not match New Password.';
    else
        hr.change_apex_user_pwd_pr
        (
             i_old_pwd => :P6_OLD_PWD,
             i_new_pwd => :P6_NEW_PWD
        );
        :P6_MESSAGE := 'Your password has been changed';
    end if;
exception when others then
      :P6_MESSAGE := SQLERRM;
end;

Page Items to Submit : P6_OLD_PWD,P6_NEW_PWD,P6_CONFIRM_PWD,P6_MESSAGE
Page Items to Return : P6_MESSAGE

da3

Click Create Dynamic Action.

Now to test.
I’m connected as PLUGGER and I want to change my password.
So, I click on the Change Password Tab and I see :

cpwd_page1

If the new and confirm password fields don’t match, I get an error from the Dynamic Action itself, before it calls the procedure :

Someone's having a fat-finger moment

Someone’s having a fat-finger moment

If I try to enter a password that contains a single quote, I get :

We'll have none of those naughty quotes thank you very much.

We’ll have none of those naughty quotes thank you very much.

Finally, I manage to get it right and am rewarded with :

pwd_changed

Invoking the Change Password Programatically

All that remains now is for us to arrange for the user to be re-directed to the change password page when they connect and their password is near expiry.

The password expiry_date is available in the DBA_USERS view so we need to grant SELECT on this to HR :

grant select on sys.dba_users to hr
/

As I’m re-directing them to a page that belongs specifically to the current application, I’m going to put the re-direction in the application itself.
So, I’m going to add a Branch to the Home Page.
Once again we need to pause here for the APEX gurus to explain the proper way to do this !

Edit the Home Page and Create a Branch…

In Branch Attributes

Name : pwd_change_br
Branch Point : On Load : Before Header

br_cpw1

In Target

Page : the number of the Change Password Page ( 6 in my case)

In Branch Conditions

Condition Type : Exists( SQL query returns at least one row)
In Expression 1, enter the query :

select 1
from sys.dba_users
where username = apex_040200.v('APP_USER')
and expiry_date < trunc(sysdate) + 7

This will return 1 if the password is due to expire within the next 7 days.

br_cpw2

and hit Create Branch.

In order to test the branch, I’ve had a bit of a fiddle with the FIXED_DATE parameter [link to post] so that PLUGGER’s password is now due to expire in less than 7 days.

Now, when I login as plugger…

brtest1

…I go straight to the Change Password Page…

brtest2

Summary

What started off as a fairly short post about Database Authentication Schemes in APEX has grown quite a bit more than I intended.
I believe that the solution to password management, which I have outlined here, is secure.
Obviously, if anyone can spot any flaws in this, I (and anyone reading this), would find it immensly helpful if you could provide reasons/code as to why and how this approach could be exploited.
Whilst the Change Password functionality is something of an overhead in going down the Database Authentication route, the use of database roles, not to mention the RDBMS itself, does mean that this is an approach worth considering when porting older applications to APEX….or maybe it isn’t.
I wonder if there’s a passing Australian who’d like to share their opinion on this ?


Filed under: APEX, Oracle, PL/SQL, SQL Tagged: APEX Database Authentication Scheme, change password procedure, dba_role_privs, dba_users, password verify function, profile, verify function

APEX 503 – Service Unavailable – And you don’t know the APEX_PUBLIC_USER Password

Sun, 2014-12-07 12:46

It’s probably Monday morning. The caffeine from your first cup of coffee has not quite worked it’s way into your system.
The cold sweat running down the back of your neck provides an unpleasant contrast to the warm blast of panicked users as they call up to inform you that the Application is down.
APEX, which has been behaving impeccibly all this time, has suddenly decided to respond to all requests with :

503 – Service Unavailable.

The database is up. The APEX Listener is up. But something else is up. APEX just doesn’t want to play.
Better still, the person who set up the APEX in the first place has long-departed the company. You have no idea how the Apex Listener was configured.

Out of sympathy with your current predicament, what follows is :

  • How to confirm that this problem is related to the APEX_PUBLIC_USER (the most likely cause)
  • A quick and fairly dirty way of getting things back up and running again
  • How to stop this happening again

Note: These steps were tested Oracle Developer Day VM with a 12c database running on Oracle Linux 6.5. In this environment, APEX is configured to run with the APEX Listener.

Confirming the APEX User name

First of all, we want to make sure that APEX is connecting to the database as APEX_PUBLIC_USER. To do this, we need to check the default.xml file.
Assuming you’re on a Linux box :

cd /u01/oracle/apexListener/apex
cat default.xml

If you don’t see an entry for db.username then APEX_PUBLIC_USER is the one that’s being used.
If there is an entry for db.username then that is the name of the database user you need to check in the following steps.
For now, I’ll assume that it’s set to the default.

Incidentally, there will also be an entry for db.password. This will almost certainly be encrypted so is unlikely to be of use to you here.

Confirming the status of the APEX_PUBLIC_USER

The most likely reason for your current troubles is that the APEX_PUBLIC_USER’s database password has expired.
To verify this – and get the information we’ll need to fix it, connect to the database and run the query :

select account_status, profile
from dba_users
where username = 'APEX_PUBLIC_USER'
/

If the account_status is EXPIRED, then the issue you are facing is that the APEX_PUBLIC_USER is expired and therefore APEX can’t connect to the database.

The other item of interest here is the PROFILE assigned to the user.
We need to check this to make sure that there is no PASSWORD_VERIFY_FUNCTION assigned to the profile. If there is then you need to supply the existing password in order to change it, which is a bit of a problem if you don’t know what it is.
Whilst we’re at it, we need to check whether there is any restriction in place as to the length of time or number of password changes that must take place before a password can be reused.
In my case, APEX_PUBLIC_USER has been assigned the DEFAULT profile.

select resource_name, limit
from dba_profiles
where profile = 'DEFAULT'
and resource_name in 
(
  'PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX', 
  'PASSWORD_VERIFY_FUNCTION'
)
/

When I ran this, I was lucky and got :

RESOURCE_NAME                  LIMIT              
------------------------------ --------------------
PASSWORD_REUSE_TIME            UNLIMITED            
PASSWORD_REUSE_MAX             UNLIMITED            
PASSWORD_VERIFY_FUNCTION       NULL     

So, there are no restrictions on password reuse for this profile. Neither is there any verify function.

If your APEX_PUBLIC_USER is attached to a profile that has these restrictions, then you’ll want to change this before re-setting the password.
As we’re going to have to assign this user to another profile anyway, we may as well get it out of the way now.

The New Profile for the APEX_PUBLIC_USER

Oracle’s advice for the APEX_PUBLIC_USER is to set the PASSWORD_LIFE_TIME to UNLIMITED.

Whilst it’s only these four parameters we need to set in the profile for us to get out of our current predicament, it’s worth also including a limitation on the maxiumum number of failed login attempts, if only to provide some limited protection against brute-forcing.
In fact, I’ve just decided to use the settings from the DEFAULT profile for the attributes that I don’t need to change :

create profile apex_public limit
    failed_login_attempts 10
    password_life_time unlimited
    password_reuse_time unlimited
    password_reuse_max unlimited
    password_lock_time 1 
    composite_limit unlimited
    sessions_per_user unlimited
    cpu_per_session unlimited
    cpu_per_call unlimited
    logical_reads_per_session unlimited
    logical_reads_per_call unlimited
    idle_time unlimited
    connect_time unlimited
    private_sga unlimited
/

As we don’t specify a PASSWORD_VERIFY_FUNCTION, none is assigned to the new profile.

NOTE – it’s best to check the settings in your own default profile as they may well differ from those listed here.

Next, we assign this profile to APEX_PUBLIC_USER…

alter user apex_public_user profile apex_public
/

The next step is to reset the APEX_PUBLIC_USER password, which is the only way to unexpire the user.

No password, no problem

Remember, in this scenario, we don’t know the current password for APEX_PUBLIC_USER. We don’t want to reset the password to just anything because we’re not sure how to set the password in the DAD used by the Apex Listener.

First of all, we need to get the password hash for the current password. To do this :

select password
from sys.user$
where name = 'APEX_PUBLIC_USER'
/

You’ll get back a hex string – let’s say something like ‘DF37145AF23CCA4′.

Next step is to re-set the APEX_PUBLIC_USER password :

alter user apex_public_user identified by sometemporarypassword
/

We now immediately set it back to it’s original value using IDENTIFIED BY VALUES :

alter user apex_public_user identified by values 'DF37145AF23CCA4' 
/

At this point, APEX should be back up and running.

Once the dust settles…

Whilst your APEX installation may now be back up and running, you now have a database user for which the password never changes.
Although the APEX_PUBLIC_USER has only limited system and table privilges, it also has access to any database objects that are available to PUBLIC.
Whilst this is in-line with Oracle’s currently documented recommendations, you may consider that this is a situation that you want to address from a security perspective.
If there is a sensible way of changing the APEX_PUBLIC_USER password without breaking anything, then you may consider it preferable to simply setup some kind of reminder mechanism so that you know when the password is due to expire and can change it ahead of time.
You would then be able to set the password to expire as normal.
If you’re wondering why I’m being a bit vague here, it’s simply because I don’t currently know of a sensible way of doing this.
If you do, it would be really helpful if you could let me know :)


Filed under: APEX, Oracle, SQL Tagged: APEX 503 Unavailable, create profile, dba_profiles, dba_users.account_status, failed_login_attempts, identified by values, password_reuse_max, password_reuse_time, password_verify_function

Kilobytes, Kibibytes and DBMS_XPLAN undocumented functions

Mon, 2014-12-01 12:46

How many bytes in a Kilobyte ? The answer to this question is pretty obvious…and, apparently, wrong.
Yep, apparently we’ve had it wrong all these years for there are, officially, 1000 bytes in a Kilobyte, not 1024.
Never mind that 1000 is not a factor of 2 and that, unless some earth-shattering breakthrough has happened whilst I wasn’t paying attention, binary is still the fundemental basis of computing.
According to the IEEE, there are 1000 bytes in a kilobyte and we should all get used to talking about a collection of 1024 bytes as a Kibibyte

Can you imagine dropping that into a conversation ? People might look at you in a strange way the first time “Kibibyte” passes your lips. If you then move on and start talking about Yobibytes, they may well conclude that you’re just being silly.

Let’s face it, if you’re going to be like that about things then C++ is actually and object orientated language and the proof is not in the pudding – the proof of the pudding is in the eating.

All of which petulant pedantry brings me on to the point of this particular post – some rather helpful formatting functions that are hidden in, of all places, the DBMS_XPLAN pacakge…

Function Signatures

If we happened to be strolling through the Data Dictionary and issued the following query…

select text
from dba_source
where owner = 'SYS'
and type = 'PACKAGE'
and name = 'DBMS_XPLAN'
order by line
/

we might be surprised at what we find….

***snip***
  ----------------------------------------------------------------------------
  -- ---------------------------------------------------------------------- --
  --                                                                        --
  -- The folloing section of this package contains functions and procedures --
  -- which are for INTERNAL use ONLY. PLEASE DO NO DOCUMENT THEM.           --
  --                                                                        --
  -- ---------------------------------------------------------------------- --
  ----------------------------------------------------------------------------
  -- private procedure, used internally

*** snip ***

  FUNCTION format_size(num number)
  RETURN varchar2;

  FUNCTION format_number(num number)
  RETURN varchar2;

  FUNCTION format_size2(num number)
  RETURN varchar2;

  FUNCTION format_number2(num number)
  RETURN varchar2;

  --
  -- formats a number representing time in seconds using the format HH:MM:SS.
  -- This function is internal to this package
  --
  function format_time_s(num number)
  return varchar2;

***snip***
Formatting a time in seconds

Let’s start with DBMS_XPLAN.FORMAT_TIME_S because we pretty much know what it does from the header comments.
To save myself a bit of typing, I’m just going to use the following SQL to see how the function copes with various values :

with actual_time as
(
    select &1 as my_secs
    from dual
)
select my_secs,
    dbms_xplan.format_time_s(my_secs) as formatted_time
from actual_time
/

Plug in a variety of numbers ( representing a time in seconds) and …

SQL> @format_time.sql 60
old   3:     select &1 as my_secs
new   3:     select 60 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
               60.00 00:01:00

SQL> @format_time.sql 3600
old   3:     select &1 as my_secs
new   3:     select 3600 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
             3600.00 01:00:00

SQL> @format_time.sql 86400
old   3:     select &1 as my_secs
new   3:     select 86400 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
            86400.00 24:00:00

SQL> @format_time.sql 129784
old   3:     select &1 as my_secs
new   3:     select 129784 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
           129784.00 36:03:04

SQL> 

I wonder how it treats fractions of a second ….

SQL> @format_time.sql  5.4
old   3:     select &1 as my_secs
new   3:     select 5.4 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
                5.40 00:00:05

SQL> @format_time.sql  5.5
old   3:     select &1 as my_secs
new   3:     select 5.5 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
                5.50 00:00:06

SQL> 

So, the function appears to round to the nearest second. Not great if you’re trying to list the times of the Olympic Finalists of the 100 metres, but OK for longer durations where maybe rounding to the nearest second is appropriate.
One minor quirk to be aware of :

SQL> @format_time.sql 119.5
old   3:     select &1 as my_secs
new   3:     select 119.5 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
              119.50 00:01:60

SQL> 

SQL> @format_time.sql 3599.5
old   3:     select &1 as my_secs
new   3:     select 3599.5 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
             3599.50 00:59:60

SQL> 


If 59.5 seconds is rounded up, the function returns a value containing 60 seconds, rather than displaying the value as a minute.

Formatting Numbers

Next on our list of functions to explore are FORMAT_NUMBER and FORMAT_NUMBER2. At first glance, it may appear that these functions are designed to represent sizes using the IEEE standard definitions…

with myval as
(
    select &1 as the_value
    from dual
)
select the_value, 
    dbms_xplan.format_number(the_value) as format_size, 
    dbms_xplan.format_number2(the_value) as format_size2
from myval
/

Run this with a variety of inputs and we get :

SQL> @format_number.sql 999
old   3:     select &1 as the_value
new   3:     select 999 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
       999 999                             999

SQL> @format_number.sql 1000
old   3:     select &1 as the_value
new   3:     select 1000 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
      1000 1000                              1K

SQL> @format_number.sql 1024
old   3:     select &1 as the_value
new   3:     select 1024 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
      1024 1024                              1K

SQL> @format_number.sql 1000000
old   3:     select &1 as the_value
new   3:     select 1000000 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
   1000000 1000K                             1M

SQL> 

SQL> @format_number.sql 1500
old   3:     select &1 as the_value
new   3:     select 1500 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
      1500 1500                              2K

SQL> 

The FORMAT_NUMBER2 function reports 1000 as 1K.
Furthermore, for numbers above 1000, it appears to round to the nearest 1000.
FORMAT_NUMBER on the other hand, doesn’t start rounding until you hit 1000000.

From this it seems reasonable to infer that these functions are designed to present large decimal numbers in an easily readable format rather than being an attempt to conform to the new-fangled definition of a Kilobyte ( or Megabyte…etc).

Using the following script, I’ve created the BIG_EMPLOYEES table and populated it with 100,000 or so rows…

create table big_employees as
    select * from hr.employees
/

begin
    for i in 1..1000 loop
        insert into big_employees
        select * from hr.employees;
    end loop;
    commit;
end;
/

If we now apply these functions to count the rows in the table, we get the following :

select count(*),
    dbms_xplan.format_number(count(*)) as format_number,
    dbms_xplan.format_number2(count(*)) as format_number2
from big_employees
/

  COUNT(*) FORMAT_NUMBER        FORMAT_NUMBER2
---------- -------------------- --------------------
    107107 107K                  107K

You can see from this, how these functions might be useful when you’re looking at the number of rows in a very large table ( perhaps several million).

Counting the Kilobytes properly

We now come to the other two functions we’ve identified – FORMAT_SIZE and FORMAT_SIZE2.

with myval as
(
    select &1 as the_value
    from dual
)
select the_value, 
    dbms_xplan.format_size(the_value) as format_size, 
    dbms_xplan.format_size2(the_value) as format_size2
from myval
/

Running this the results are :

SQL> @format_size.sql 999
old   3:     select &1 as the_value
new   3:     select 999 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
       999 999                   999

SQL> @format_size.sql 1000
old   3:     select &1 as the_value
new   3:     select 1000 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
      1000 1000                 1000

SQL> @format_size.sql 1024
old   3:     select &1 as the_value
new   3:     select 1024 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
      1024 1024                    1k

SQL> @format_size.sql 1000000
old   3:     select &1 as the_value
new   3:     select 1000000 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
   1000000 976K                  977k

SQL> @format_size.sql 1048576
old   3:     select &1 as the_value
new   3:     select 1048576 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
   1048576 1024K                   1m

SQL> @format_size.sql 2047.4
old   3:     select &1 as the_value
new   3:     select 2047.4 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
    2047.4 2047                    2k

SQL> @format_size.sql 2047.5
old   3:     select &1 as the_value
new   3:     select 2047.5 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
    2047.5 2047                    2k

SQL> 

Things to notice here include the fact that format_size appears to FLOOR the value (1000000 bytes = 976.56 K), wheras FORMAT_SIZE2 rounds it up.
Additionally, once you pass in a value of over 1024, FORMAT_SIZE2 returns values in Kilobytes.

So, if we want to know the size of the BIG_EMPLOYEES table we’ve just created :

select bytes, 
    dbms_xplan.format_size(bytes) as format_size,
    dbms_xplan.format_size2(bytes) as format_size2
from user_segments
where segment_name = 'BIG_EMPLOYEES'
/

     BYTES FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
   9437184 9216K                   9m

If all you need is an approximate value, then FORMAT_SIZE2 could be considered a reasonable alternative to :

select bytes/1024/1024 as MB
from user_segments
where segment_name = 'BIG_EMPLOYEES'
/

As well as it’s primary purpose, DBMS_XPLAN does offer some fairly useful functions if you need a quick approximation of timings, or counts or even sizes.
Fortunately, it adheres to the traditional definition of a Kilobyte as 1024 bytes rather than “Litebytes”.


Filed under: Oracle, PL/SQL, SQL Tagged: dbms_xplan, format_number, format_number2, format_size, format_size2, format_time_s

APEX and Privileges Granted through Roles

Sat, 2014-11-15 15:33

The mystery has finally been solved. England’s surrendering of the Ashes last winter was nothing to do with Australia being a much better cricket team. Thanks to Kevin Pietersen’s recently published Autobiography, we now know that the problem was that there were rather too many silly points in the England dressing room.
Moving swiftly on from that weak pun, the subject at hand can also be rather mystifying at first glance.

In a “traditional” Oracle Forms application, you would have one database user per application users.
Connections via the Application to the database would be done as the individual users.
It’s quite likely that database roles would be used to grant the appropriate privileges.

For applications using other web technologies, the application may interact with the database via a single account, often that of the Application Owner. Whether or not this is a good idea is probably a discussion for another time.

For now though, the question we’re asking is, how an APEX application connect to the database ?
On the face of it, it would seem that it’s pretty similar to the second of the two approaches above. APEX connects as the Parsing Schema (usually the application owner).
As Kevin will tell you, appearances can be deceiving…

The Environment

For the purposes of this post, I’ll be using a simple APEX application that’s been created in it’s own workspace.
The application is called NEW_HR and uses the default APEX Authentication Scheme.
The parsing schema is defined as HR.
At this point the application consists of a login screen and a blank Home Page.
I’ve also created a Workspace Admin user called…well…let’s call it Kevin.
The database version is Oracle 11g Express Edition and the APEX version is 4.2.
This environment uses the embedded PL/SQL Gateway to manage database connections from APEX. This is the default setup on Oracle 11g XE.

Who am I ? No, really

Now, I know that there is no user called KEVIN in my database….

select count(*) 
from dba_users 
where username = 'KEVIN'
/
  COUNT(*)
----------
         0

SQL> 

…so I’d like to know who the database thinks I am when I login through my APEX app. I’d also like to check who the APEX itself thinks I am.

The first step then, is to add a couple of fields to the application Home Page…

First of all, I’ve add an HTML Region called whoami. Apart from the name I’ve just accepted the defaults.

Now to add a field to display the Application User – i.e. who APEX thinks I am.

This is a Display Only Item called P1_APEX_USER in the whoami region.
The source settings for this item are the defaults except for :

Source Used : Always, replacing any existing value in session state
Source value or expression : APP_USER

apex_user_source

Next up is to add a field to display the database user.

The field is defined in the same way as P1_APEX_USER, except for :

Source Type : SQL Query (return single value)

and the source itself which is the following query :

select user from dual

db_user_source

Now, if we connect as Kevin….

login

…we can start to resolve our identity crisis….

whoami

So, as expected, APEX knows that Kevin is the Application user. However, the database user is not HR, rather it’s something called ANONYMOUS.

NOTE – If you’re using the Embedded PL/SQL Gateway ( the default setup for Express Edition) then you’ll be connected as ANONYMOUS. If you have the APEX Listener setup then, unless you’ve changed the default, you’ll be connected as APEX_PUBLIC_USER.
For our current purposes we can treat these accounts as synonymous from a database standpoint.
I’ll continue to refer to ANONYMOUS from here on because (a) I’m running this on XE and (b) the name has slightly more comedic potential.

Let’s find out a bit more about this user whilst trying not to worry that our application has been visited by hacktivists.
Hmmm, maybe not so much comedic potential.

The ANONYMOUS User

Looking in the database, we can confirm that ANONYMOUS is indeed a database user :

select account_status, profile, authentication_type
from dba_users
where username = 'ANONYMOUS'
/

ACCOUNT_STATUS                   PROFILE                        AUTHENTI
-------------------------------- ------------------------------ --------
OPEN                             DEFAULT                        PASSWORD

Doesn’t seem to be anything out of the ordinary there.
Now let’s see what ANONYMOUS has granted to it. For good measure, we can see what objects it owns ( if any).
The query looks like this :

select 'SYSTEM PRIVILEGE' as priv_type,
    null as db_object,
    privilege
from dba_sys_privs
where grantee = 'ANONYMOUS'
union
select 'ROLE GRANTED' as priv_type,
    granted_role as db_object,
    null as privilege
from dba_role_privs
where grantee = 'ANONYMOUS'
union
select 'OBJECT PRIVILEGE' as priv_type,
    owner||'.'||table_name as db_object,
    privilege
from dba_tab_privs
where grantee = 'ANONYMOUS'
union
select 'OWNED OBJECT' as priv_type,
    object_name as db_object,
    null as privilege
from dba_objects
where owner = 'ANONYMOUS'
order by 1,2
/ 

When we run it we get variations on the theme of :

PRIV_TYPE            DB_OBJECT                                                    PRIVILEGE
-------------------- ------------------------------------------------------------ ------------------------------
OBJECT PRIVILEGE     APEX_040000.WWV_FLOW_EPG_INCLUDE_MODULES                     EXECUTE
OBJECT PRIVILEGE     APEX_040200.WWV_FLOW_EPG_INCLUDE_MODULES                     EXECUTE
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           ALTER
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           DELETE
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           FLASHBACK
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           INDEX
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           INSERT
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           ON COMMIT REFRESH
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           QUERY REWRITE
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           REFERENCES
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           SELECT
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           UPDATE
SYSTEM PRIVILEGE                                                                  CREATE SESSION

Now, the Object Privileges listed here are probable the result of some of the sample APEX applications I’ve installed.
By default, the only thing granted to ANONYMOUS is the CREATE SESSION privilege.

More pertinent here though is that it has no permissions at all on any objects owned by HR. This begs the question as to how our APEX application will work. Remember, our parsing schema ‎( essentially the Application Owner) is HR. Therefore, it’s reasonable to assume that we’ll want to interact with the tables in that schema.

NOTE – at this point I should add that, of course, ANONYMOUS does have additional privileges – i.e. everything granted to PUBLIC in the database. Whilst th‎is is not strictly relevant to the matter at hand, it’s probably worth bearing in mind when you look at how you implement security around this user.

Anyway, let’s put it to the test…

The Regions Report

In our application we’re going to create a new page – a Report on the HR.REGIONS table so…

In the Application Builder, click on Create Page :

create_page

Select Report and click Next

Select Interactive Report and click Next

Accept the defaults for Page Region Attribute and click Next

In Tab Options choose Use an existing tab set and create a new tab within the existing set
New Tab Label is Regions :

tab_options

Click Next

For the SQL Query :

select region_id, region_name
from regions

Note – we’re not specifying the table owner in this query, even though ANONYMOUS does not have a synonym on the HR.REGIONS table ( let alone any privileges)

query

Click Next

…and click Create

create

When we now connect to the application as Kevin and click on the Regions tab….

regions

So, the report has worked without error, despite the lack of privileges and synonyms. So what’s happening ?

Session Privileges in APEX

To answer this, we’ll need to tweak our earlier privileges query. This time, we’ll use the USER_ version of the views.
We can then it to the Application Home Page in a new reports region to see what ANONYMOUS can actually do when connected via APEX.

First, the new query, using USER_ versions of the views and without the order by clause.

select 'SYSTEM PRIVILEGE' as priv_type,
    null as db_object,
    privilege
from user_sys_privs
union
select 'ROLE GRANTED' as priv_type,
    granted_role as db_object,
    null as privilege
from user_role_privs
union
select 'OBJECT PRIVILEGE' as priv_type,
    owner||'.'||table_name as db_object,
    privilege
from user_tab_privs
union
select 'OWNED OBJECT' as priv_type,
    object_name as db_object,
    null as privilege
from user_objects
where object_type != 'INDEX'
/

Spoiler Alert – the reason I’m not using the SESSION_PRIVS view here is because it will list privileges granted via roles. The distinction between these and directly granted privileges will shortly become apparent.

We now simply create a new interactive reports region called User Privileges on the Home Page, using the above query.
If we now filter on PRIV_TYPE = ‘OWNED OBJECT’, we can see that we’ve magically acquired ownership of all the HR objects…

owned_objects

If we filter on PRIV_TYPE = ‘SYSTEM PRIVILEGE’, we can see that we also seem to have inherited HR’s System Privileges…

sys_privs

So, we can infer from this that, although the database connection from APEX is as the ANONYMOUS user, the session will inherit all of the objects and privileges of the parsing schema.
A reasonable assumption, given the evidence, and a correct one…mostly.

Objects not owned by the parsing schema

I’ve created a simple function in my own schema :

create or replace function name_scandal_fn( i_basename varchar2)
    return varchar2
as
begin
    return i_basename||'gate';
end;
/

Next we’re going to create a role and then grant execute on this function to that role. Finally, we’re going to grant the role to hr :

create role hr_role
/

grant execute on name_scandal_fn to hr_role
/

grant hr_role to hr
/

First off, we’ll test this in SQL*Plus. Connect as HR and …

select mike.name_scandal_fn('Twitter') from dual
/

MIKE.NAME_SCANDAL_FN('TWITTER')
--------------------------------------------------------------------------------
Twittergate

SQL> 

So, we should have no problem invoking this function from our application then.

Let’s create a page with a Display Only field that is populated by a call to this function :

Blank Page :

fn_page1

Called Scandal

fn_page2

…With an HTML Region…

fn_page3

…on a new tab…

fn_page4

…and confirm…

fn_page5

Now, add the Item…

fn_item1

…called P3_SCANDAL_NAME…

fn_item2

Accept the defaults for the Item Attributes settings, and Settings…

… and change the Source settings to :

Source Used : Always, replacing any existing value in session state
Source Type : SQL Query (return single value)
Item Source Value – here we put in our call to the function :

select mike.name_scandal_fn('Twitter') from dual

fn_item3

Finally, hit the create button.

No problems so far. Now, let’s try running the page…

fn_err

Hmmm, not quite what we were expecting.

Looking at the error stack, a possible source of the problem emerges.
In the background, it looks as if APEX is calling a package called WWV_FLOW_FORMS, which in turn calls WWV_FLOW_DYNAMIC_EXEC.
Whilst the source for both of these packages is wrapped, there are some notes availble on the next package in the call stack, WWV_DBMS_SQL here.

Putting all together and looking at the package headers, it would seem reasonable to assume that, rather than running the SQL statement directly, APEX does this via a series of package calls which then run the statement as dynamic SQL.
The effect of calling a (presumably) Definer’s Rights package is that any privileges granted via roles are ignored.

In order to test this theory, we can revoke the role from HR and instead, grant execute on the function directly.
So, connected to SQL*Plus as the function owner ( in my case MIKE) :

revoke hr_role from hr
/

grant execute on name_scandal_fn to hr
/

Now a quick sanity check to make sure that HR can see the function.
Connect as HR and :

SQL> select mike.name_scandal_fn('Twitter') from dual
  2  /

MIKE.NAME_SCANDAL_FN('TWITTER')
--------------------------------------------------------------------------------
Twittergate

SQL> 

Now let’s see what APEX makes of this.
Re-run the page and we can see…

itworks

There you have it. APEX, like Kevin, is just a little bit different.


Filed under: APEX, Oracle, SQL Tagged: apex anonymous user, apex_public_user, granting privileges via roles, parsing schema

Going dotty – Generating a Filename containing a parameter value in SQL*Plus

Sat, 2014-10-18 12:01

As I have alluded to previously, I was not born in the UK.
Nope, my parents decided to up-sticks and move from London all the way to the
other side of the world, namely Auckland.
Then they had me. Then they came back.
To this day, they refuse to comment on whether these two events were related.

I went back to New Zealand a few years ago.
As I wandered around places that I hadn’t seen since I was five, it was strange how memories that I had forgotten came flooding back.
That last sentence doesn’t make much sense. It’s probably more accurate to say that memories I hadn’t thought about for years came flooding back.

I recently remembered something else I once knew, and then forgot – namely how to generate a SQL*Plus file name which includes a parameter value.

The scenario

I’ve got a script that lists all of the employees in a given department :

accept deptno prompt 'Enter Department ID : '
spool department.lis

select first_name, last_name
from hr.employees
where department_id = &deptno
order by employee_id
/

spool off

Now, rather than it just creating a file called department.lis, I want to create a file that includes the department number I’m querying.

Obvious…but wrong

You might think the following is a reasonable attempt to do this :

accept deptno prompt 'Enter Department ID : '
spool department_&deptno.lis

select first_name, last_name
from hr.employees
where department_id = &&deptno
order by employee_id
/

spool off

Unfortunately, SQL*Plus insists on being obtuse and outputting the following file :

ls
department_10lis.lst

It is at this point that a colleague came to the rescue ( thanks William)…

Going dotty

This will do the job…

accept deptno prompt 'Enter Department ID : '

spool department_&deptno..lis

select first_name, last_name
from hr.employees
where department_id = &deptno
order by employee_id
/

spool off

Run this and we not only get :

Enter Department ID : 10
old   3: where department_id = &deptno
new   3: where department_id = 10

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jennifer             Whalen

SQL> 

…we get a file, appropriately named :

ls
department_10.lis

The magic here is that the “.” character delimits the variable substitution.
Just to prove the point, we can do the same with a positional parameter :

set verify off

spool department_&1..lis

select first_name, last_name
from hr.employees
where department_id = &1
order by employee_id
/

spool off

…run this and we get :

SQL> @position_param.sql 10

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jennifer             Whalen

SQL> 

…and the appropriate file…

ls
department_10.lis

On that note, I’m off to the pub. Now, where did I leave my keys ?


Filed under: Oracle, SQL Tagged: spool; filename including a variable value, SQL*Plus

Sayonara to Sequences and Trouble for Triggers – Fun and Games in Oracle 12c

Sat, 2014-09-13 08:57

Ah, Nostalgia.
Not only can I remember the Good Old Days, I also remember them being far more fun than they probably were at the time.
Oh yes, and I was much younger….and had hair.
Yes, the Good Old Days, when Oracle introduced PL/SQL database packages, partitioning, and when the sequence became extinct.
Hang on, I don’t remember that last one…

The Good Old Ways

Say we have a requirement for a table to hold details of gadgets through the ages.
This table has been modelled with a synthetic key, and also a couple of audit columns so we can track when a row was created and by whom.
Traditionally, the code to fulfill this requirement would follow a familiar pattern.

The table might look something like this :

create table gadgets
(
    id number constraint dino_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default user,
    creation_date date default sysdate
)
/

NOTE – you’d normally expect to see NOT NULL constraints on the CREATED_BY and CREATION_DATE columns. I’ve left these off for for the purposes of the examples that follow.

We’ll also want to have a sequence to generate a value for the id…

create sequence gad_id_seq
/

As it stands, this implementation has one or two issues…

-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Dial-Up Modem', user, sysdate)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( gad_id_seq.nextval, 'Tablet Computer')
/

-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/

The first problem becomes apparent when we query the table after these inserts…

SQL> select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	       CREATION_DATE
---- -------------------- -------------------- --------------------
   1 Dial-Up Modem	  MIKE		       31-AUG-14
   2 Tablet Computer	  MIKE		       31-AUG-14
   3 Netbook

Yes, although the insert was successful for the Netbook row, the explicit specification of CREATED_BY and CREATION_DATE values as NULL has overidden the default values defined on the table.

What’s more, there’s nothing enforcing the use of the sequence to generate the ID value. This becomes a problem when we go to do the next insert…


-- Next insert using sequence...
insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

insert into gadgets(id, gadget_name, created_by, creation_date)
*
ERROR at line 1:
ORA-00001: unique constraint (MIKE.DINO_PK) violated

Because we didn’t use the sequence for the previous insert, it’s still set to the value it had after it was last invoked…


SQL> select gad_id_seq.currval from dual;

   CURRVAL
----------
	 3

The traditional solution to these problems is, of course, a trigger…

create or replace trigger gad_bir_trg
    before insert on gadgets
    for each row
    --
    -- Make sure that :
    --  - id is ALWAYS taken from the sequence
    --  - created_by and creation date are always populated
begin
	:new.id := gad_id_seq.nextval;
    :new.created_by := nvl(:new.created_by, user);
    :new.creation_date := nvl(:new.creation_date, sysdate);
end;
/

Now, if we re-run our insert…


insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

1 row inserted


SQL> select * from gadgets where gadget_name = 'Smart Phone';

  ID GADGET_NAME	  CREATED_BY	       CREATION_DATE
---- -------------------- -------------------- --------------------
   5 Smart Phone	  MIKE		       31-AUG-14

Yes, even though we’ve invoked the sequence in the INSERT statement, the trigger invokes it again and assigns that value to the ID column ( in this case 5, instead of 4).
Reassuringly thought, the CREATED_BY and CREATION_DATE columns are now populated.

So, in order to fulfill our requirements, we need to create three database objects :

  • A table
  • a sequence
  • a DML trigger on the table

Or at least, we did….

12c – the Brave New World

Oracle Database 12c introduces a couple of enhancements which will enable us to do away with our trigger completely.
First of all…

Changes to Default Values Specification

You can now specify a default value for a column that will be used, even if NULL is explicitly specified on Insert.
Furthermore, you can now also use a sequence number as a default value for a column.

If we were writing this application in 12c, then the code would look a bit different….


create sequence gad_id_seq
/

create table gadgets
(
    id number default gad_id_seq.nextval 
        constraint dino_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user,
    creation_date date default on null sysdate
)
/

We’ve dispensed with the trigger altogether.
The ID column now uses the sequence as a default.
The CREATED_BY and CREATION_DATE columns will now be populated, even if NULL is explicitly specified as a value in the INSERT statement….


-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Dial-Up Modem', user, sysdate)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( gad_id_seq.nextval, 'Tablet Computer')
/

-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/




  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Tablet Computer	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14

Whilst we can now guarantee that the CREATED_BY and CREATION_DATE columns are populated, we are still left with one issue, or so you might think…

-- Next insert using sequence...
insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

1 row inserted

That’s odd. You’d think that the sequence NEXTVAL would be 3, thus causing the same error as before. However…

 select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Tablet Computer	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14
  21 Smart Phone	  MIKE		 31-AUG-14

Hmmm. Let’s take a closer look at the sequence…

select min_value, increment_by, cache_size, last_number
from user_sequences
where sequence_name = 'GAD_ID_SEQ'  
/

 MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
	 1	      1 	20	    41

Yes, it looks like, in 12c at least, the default for sequences is a cache size of 20.
If we wanted to create the sequence in the same way as for 11g ( i.e. with no caching), we’d need to do this :

create sequence gad_id_seq
    nocache
/

We can now see that the sequence values will not be cached :

PDB1@ORCL> select cache_size
  2  from user_sequences
  3  where sequence_name = 'GAD_ID_SEQ'
  4  /

CACHE_SIZE
----------
	 0

All of this is a bit of an aside however. The fact is that, as it stands, it’s still quite possible to by-pass the sequence altogether during an insert into the table.
So, we still need to have a trigger to enforce the use of the sequence, right ?
Well, funny you should say that….

Identity Column in 12c

Time for another version of our table. This time however, we’re dispensing with our sequence, as well as the trigger…

create table gadgets
(
    id number generated as identity 
		constraint gad_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user not null,
    creation_date date default on null sysdate not null
)
/

Let’s see what happens when we try to insert into this table. Note that we’ve modified the insert statements from before as the sequences does not exist ….

-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Dial-Up Modem', user, sysdate)
/


-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( null, 'Tablet Computer')
/

The first statement succeeds with no problem. However, the second and third both fail with :

ORA-32795: cannot insert into a generated always identity column

We’ll come back to this in a bit.

In the meantime, if we check the table, we can see the ID column is automagically populated….

 select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14

Oh, it’s just like being on SQL Server.

How is this achieved ? Well, there are a couple of clues.
First of all, executing the create table statement for this particular version of the table requires that you have the additional privilege of CREATE SEQUENCE.
A further clue can be found by looking once again at USER_SEQUENCES…

select sequence_name, min_value, increment_by, cache_size, last_number
from user_sequences
/

SEQUENCE_NAME	      MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------------- ---------- ------------ ---------- -----------
ISEQ$$_95898		      1 	   1	     20 	 21

If we have a look at the column details for the table, we get confirmation that this sequence is used as the default value for the ID column :

  1  select data_default
  2  from user_tab_cols
  3  where table_name = 'GADGETS'
  4* and column_name = 'ID'
PDB1@ORCL> /

DATA_DEFAULT
--------------------------------------------------------------------------------
"MIKE"."ISEQ$$_95898".nextval


It’s worth noting that this sequence will hang around, even if you drop the table, until or unless you purge the table from the RECYCLEBIN.

If you prefer your sequences to be, well, sequential, the good news is that you can use the Sequence Creation syntax when specifying an identity column.
The change in the default number of values cached for sequences created in 12c, compared with 11g and previously, may lead you to consider being a bit more specific in how you create your sequence, just in case things change again in future releases.

Here we go then, the final version of our table creation script….

 create table gadgets
(
    id number generated always as identity
    (
        start with 1
        increment by 1
        nocache
        nocycle
    )
    constraint gad_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user not null,
    creation_date date default on null sysdate not null
)
/

As we saw earlier, the INSERT statements for this table, now need to change. We can either specify “DEFAULT” for the ID column :

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Dial-Up Modem', user, sysdate)
/

…or simply omit it altogether…

insert into gadgets(gadget_name, created_by, creation_date)
values('Smart Phone', user, sysdate)
/

And, of course, we can also omit the values for the other defaulted columns should we choose….

insert into gadgets(gadget_name)
values('Netbook')
/

If we check the table after these statements, we can see that all is as expected :


select * from gadgets
/

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Smart Phone	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14

As with a “traditional” table/sequence/trigger setup, an erroneous INSERT will cause a gap in the sequence…

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Psion Series 5', 'Aridiculouslylongusernamethatwontfitnomatterwhat', sysdate)
/

values( default, 'Psion Series 5', 'Aridiculouslylongusernamethatwontfitnomatterwhat', sysdate)
                                   *
ERROR at line 2:
ORA-12899: value too large for column "MIKE"."GADGETS"."CREATED_BY" (actual:
48, maximum: 30)

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Psion Series 5', default, default)
/

select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Smart Phone	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14
   5 Psion Series 5	  MIKE		 31-AUG-14
Conclusion

While we can see that 12c hasn’t done away with sequences altogether, it is fair to say that they are now a lot more unobtrusive.
As for the good old DML trigger ? Well, they’ll still be with us, but they may well be a little lighter on the mundane default handling stuff we’ve been through in this post.


Filed under: Oracle, PL/SQL, SQL Tagged: column default value, create sequence, default always, default cache value of sequence, default on null, generated as identity, identity column, insert value into identity column, ORA-32795 : cannot insert into a generated always identity column