Databases Reference
In-Depth Information
•
Your code in your APEX application is executed as the parsing schema associated
with that application (not as
APEX PUBLIC USER
). This is possible since APEX uses
the
DBMS SYS SQL
package which has a procedure called
PARSE AS USER
, as shown
in Listing 1-3.
Listing 1-3.
PARSE_AS_USER Procedure in DBMS_SYS_SQL
PROCEDURE PARSE AS USER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
C NUMBER(38) IN
STATEMENT CLOB IN
LANGUAGE FLAG NUMBER(38) IN
USERID NUMBER(38) IN DEFAULT
USELOGONROLES BOOLEAN IN DEFAULT
EDITION VARCHAR2 IN DEFAULT
APPLY CROSSEDITION TRIGGER VARCHAR2 IN
FIRE APPLY TRIGGER BOOLEAN IN DEFAULT
The procedure shown in Listing 1-3 is actually an overloaded one, so there are many different
variants of the parameters you can pass in (for example, the statement can be a
VARCHAR2
instead of a
CLOB
). However, you can see that there is a parameter called
USERID
which allows the calling routine to
specify which user the code should be executed as. It is this feature that allows APEX to run different
applications in different primary parsing schemas with the correct privileges (while preventing someone
in Workspace A from accessing code/data from Workspace B).
You don't need to be too concerned with how this actually works, nor should you ever need to use
DBMS SYS SQL
yourself (in fact, it is perhaps the most powerful package in the database since it allows you
to run code as ANY user). I wanted to highlight it for one reason: you should make sure the password to
APEX PUBLIC USER
is not known to anyone else, since it is a privileged user.
This point is very important. If I had to do a rough “finger in the air” calculation, I would say that the
vast majority of people store the password in the
DADS.CONF
file in plaintext—after all, that's what the
example uses and what the documentation shows, too! But you can store the password in an obfuscated
format, by using the
dadTool.pl
command which is located in the
$ORACLE HOME/ohs/modplsql/conf
directory. In that same directory you will find a file called
dadTool.README
that details how to use the tool
in different environments (for example, Linux or Windows). Configuring
dadTool.pl
is not that difficult
(I won't detail the steps here since they're already well documented in the
dadTool.README
file). Listing 1-
4 shows an example in my environment:
Listing 1-4.
Running dadTool.pl to Obfuscate the DADS.CONF Password
[ohs@ae1 conf]$ export ORACLE HOME=/home/ohs/OraHome 1
[ohs@ae1 conf]$ export PATH=$ORACLE HOME/ohs/modplsql/conf:$PATH
[ohs@ae1 conf]$ export PATH=$ORACLE HOME/perl/bin:$PATH
[ohs@ae1 conf]$ export LD LIBRARY PATH=$ORACLE HOME/lib:$LD LIBRARY PATH
[ohs@ae1 conf]$export PERL5LIB=$ORACLE HOME/perl/lib
[ohs@ae1 conf]$ perl dadTool.pl -o
All passwords successfully obfuscated. New obfuscations : 1
