You don't have to hard code it into the application, there are many creative ways you can do it.

I myself prefer to put the application into the database (write all of the logic in PLSQL) in that way -- I never need to worry about it because my application is the ONLY way to get access to the data. I don't care if you call my application from sqplus or from the pretty GUI i created. In that way, the roles are enabled by default.


I find applications that rely on password protected roles to "protect" themselves to be security holes in disguise. The concept (the theory) is:

o we'll grant INSERT/UPDATE/DELETE on our tables to a role
o we'll password protect the role and make it non-default
o our application will be the only thing to enable the role
so it will be the only way the people can insert/update/delete
out tables.

The hole is point #3 above. The problem is you have to give your application SOME way to enable the role. If your application can enable the role -- so can I from SQLPLUS. Lets say you create a procedure to enable the role -- so you hide the password for it in the database (perhaps as a wrapped PLSQL routine so no one can read the code). It would look like this:

ops$tkyte@DEV816> l
1 create or replace procedure turn_on_role
2 authid current_user
3 as
4 begin
5 execute immediate
'set role new_role identified by password';
6* end;

ops$tkyte@DEV816> create role new_role identified by password;
Role created.

ops$tkyte@DEV816> set role none;
Role set.

ops$tkyte@DEV816> select * from session_roles;
no rows selected

ops$tkyte@DEV816> set role new_role;
set role new_role
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'NEW_ROLE'


ops$tkyte@DEV816> exec turn_on_role;
PL/SQL procedure successfully completed.

ops$tkyte@DEV816> select * from session_roles;

ROLE
------------------------------
NEW_ROLE

ops$tkyte@DEV816>


So, I can hide the password in the database (doesn't have to be hard coded into the application). But the fact remains that i still have to give the end users the privilege to execute that procedure "turn_on_role". As shown above, if I can execute it in the application -- I sure can execute it in sqlplus.

Well, we'll just have our procedure query v$session for the program -- if the program is not myapp.exe, we'll not turn it on. Ok, I'll just copy sqlplus.exe to myapp.exe -- now I'll apear to be your application.

Ok, we'll hard code into the application some magic piece of data that it'll send into turn_on_role -- Ok, I'll just set trace_level_client = 16 in my sqlnet.ora and see what it is you are sending.

If you were to use ASO (advanced security) and encrypted the data stream between client and server and had the application send over some magic bit of data and could be assured this magic bit of data could never be comprimised -- then you might have something.


followup to comment

I don't agree that that would be more secure.

At the end of the day -- the application will retreive and decode the password and then send it back to the database. We are back to the same case as when you just hard code it into the application itself. Also -- you now have key managment problems -- where do you store the key used to decrypt the data? the key is as immportant as the data itself!