Advanced ways using the Authorization Schemes inside APEX

Von Tobias Arnhold 12.05.2011
The standard way to check the authorization is to select the correct scheme inside the security area:

What are authorization schemes and how do you create them?

There are a couple of hidden ways to check the authorization schemes differently to the standard way.

To check more then one authorization scheme with pl/sql use this hint from Denes Kubicek:

To check authorization schemes inside SQL follow this solution:

For those who do not understand German. Just create a simple function which let you use the authorization scheme function inside a sql statement:
create or replace function 
 my_check_auth(p_security_scheme in varchar2)
 return number is
  if apex_util.public_check_authorization(p_security_scheme) 
    return 1;
    return 0;
  end if;

  'VALID_USER' as return_val
from my_table
where col1 = 'NEW'
and (my_check_auth('AUTH_VALID_USER') = 1  
     or my_check_auth('AUTH_NEW_USER')

Post Tags:

3 Comments " Advanced ways using the Authorization Schemes inside APEX "

chrisonoracle 06 December, 2011 11:26

Hi Tobias!

One thing that might not be obvious when using our authorization checking function in SQL or PL/SQL is that caching still takes place. The authorization will only be evaluated at most once ("Once per page view") or not at all, if it's result is already stored in session state.

If you are using the function in SQL, a non-optimal execution plan might still cause many context switches to PL/SQL. In that case, you could use scalar subqueries, e.g.

'VALID_USER' as return_val
from my_table
where col1 = 'NEW'
and ( (select my_check_auth('AUTH_VALID_USER') from dual)=1
or (select my_check_auth('AUTH_NEW_USER') from dual)=1 );


Tobias Arnhold 07 December, 2011 20:31

Hi Christian,

thanks for the interesting information.



Hi Tobias

I had created a wrapper function for the apex_util.public_check_authorization to hopefully allow me to to run a sql query with a call to this as a condition, unfortunately I only ever get: 'ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML' errors. I came a cross your site when searching for a resolution. How did you get around this?

Cheers John