Skip to Main Content
  • Questions
  • do i need to hardcode password inside the program?

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, wallacel.

Asked: February 12, 2001 - 7:48 pm UTC

Last updated: December 13, 2003 - 10:09 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom:

oracle8i concepts page 1-41.
Typically,an application is designed so that when iti starts,it enables the proper role.
As a result, an application user does not need to know the password for an application's
role.

Does that mean i need to hard code password inside the application?
if that's case, how can i insure the security.
also, when sometime i need to change the password, does that mean i need to modify the
application?

and Tom said...

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!



Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very Good way of implementing the security at the appicatiion level

Senthil, June 13, 2001 - 9:54 am UTC

I am looking for this solution. If the password of the role
is stored in a table in an encrypted way and retrived ,it will be more secured.

Good one

Gururaj, June 14, 2001 - 10:59 am UTC


A reader, June 18, 2002 - 6:47 am UTC

</code> http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/c_wrap.htm#2884 <code>

Wrapping a procedure helps to hide the algorithm and prevent reverse-engineering, but it is not a way to hide passwords or table names that you want to be secret.


So the user can login and
select text from user_source
where name=' '

and find the password in the wrapped text!

Tom Kyte
June 18, 2002 - 7:14 am UTC

perhaps I wasn't 100% explicit, but my point was to hide the password in the database (say in a table).

Also, it is quite trivial to hide the password in wrapped code. Think of what you can do with some substrs, translates, replaces, use of multiple variables, concatenation, etc.

If you just code;

is
hey_look_at_this_password varchar2(20) default 'abcdefg';
begin

you are right, if you do it obscured, no.

Nice stuff there.

Godwin, June 18, 2002 - 10:53 am UTC

Tom u are doing great job there. In fact you are a role model to some of us which even ginger me to develop such love for Oracle.
But Tom may be i seem to be troublesome by my questions but i think it is because am naive in the oracle platform.
Please look at this issue for me. After i have developed an application to keep track on Accommodation for University staff i decided to restrict users to specific task to be performed.I am therefore deciding to implement these actions and i found answer to the already answered question as very useful. But besides that can i make use of the ff to acheive what am trying to arrive at?
eg 1. create synonym public syn_table1 on table1;
grant select,update on syn_table1 to user1;

2. grant insert,update,select on table1 to role1;
grant role1 to user1;

Now the problem is when i connect to sqlplus as user1 and issue the statement as "select * from syn_table1" it works alright but if i log on to my application through Forms 6 to retrieve data from that particular table i don't get any record. What is the cause of this?
Now with the example 2 can you please throw some light on how it is used and for what it is used for.


Tom Kyte
June 18, 2002 - 11:16 am UTC

I will hazzard a guess (I've been right on this guess 100% of the time so far, with almost exactly the same question)

you are using a different userid in forms then in sqlplus. that different userid sees a different synonym.

I'm not sure what you mean by your request for #2 there. You are giving some privs to a role. You grant the role to a user. The user now has those privs. If you grant/revoke privs to/from that role -- that user (and in fact, all users that have that role) will have those changes as well. It is a way to manage privs for thousands of users and thousands of objects with less typing/overhead.

Could you please elaborate a bit more?

gs, December 11, 2003 - 5:59 pm UTC

Tom,

You said
"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."

Now, to invoke that application, you have to connect to the database. To connect you need password.

Suppose, I have a web application.

How does one achive the same, without hardcoding (it could be some encrypted value wihc the web application decrypt before connecting) the password?

Could you please give the steps (algorithm) how you will achieve this?

What about if the web application (php lang) has many queries scattered throughout the application (not calls to server side procedures)

ex.
Create user x and ggrant only create session
Create role y and grant required object priv
...

Thanks,

Tom Kyte
December 11, 2003 - 6:31 pm UTC

depends on the tool you use.

with mod_plsql - just choose to not store the password with the DAD. then the browser uses basic authentication to ask you and sends it to the server.

Thanks Tom!

gs, December 11, 2003 - 6:40 pm UTC

I have Apache/PHP/Oracle. No mod_plsql. The application logs in with a fixed userid (say appuser) with privileges to do certain operation such as insert, update, irrespective of who the web user is.

The application reads the encrypted password from a file and decrypts before connecting to the db.

What is the best wasy to achieve the connection without compromising the security?


Tom Kyte
December 13, 2003 - 10:20 am UTC

If you have Oracle -- you got mod_plsql. But, anyway.


You've already pretty much compromised security by using a common userid ;) The database isn't part of your security infrastructure anymore, you've written us out of the equation.

Don't know exactly what you are looking for from me? What are you trying to secure against?

authenticating client

Sudhir, December 11, 2003 - 10:17 pm UTC

Hi Tom,

one suggestion on that subject,

lets say myapp.exe has a routine which calls
-os function call to get binary size(s) of the client(s)
-calls database to get connection time
-jumbles the size with time in some manner
-call a procedure on the database with jumbled number as
parameter

on the database side for every release we have provided
to customers, we keep track of various byte sizes. we
already know the algorithm used for creating jumble, thus
we unjumble it, check if the login time matches, check the
byte size matches to our repository. then we enable the role.

So even if you run level 16 trace on client, you can't do much because the jumbled number will be different each time you login and its not going to work from sqlplus or other clients as there is slim chance the sizes will match (you can add size of couple of delivered products to make this even more complicated and secure) and parameters will be messed up.

what do you think?

Thanks as usual

Tom Kyte
December 13, 2003 - 10:32 am UTC

seems plausible if you use secure application roles in 9i (roles that can only be enabled by running a stored procedure to enable them)

wrapped procedure

Sudhir, December 11, 2003 - 10:22 pm UTC

Tom,

" perhaps I wasn't 100% explicit, but my point was to hide the password in the
database (say in a table).

Also, it is quite trivial to hide the password in wrapped code. Think of what
you can do with some substrs, translates, replaces, use of multiple variables,
concatenation, etc.

If you just code;

is
hey_look_at_this_password varchar2(20) default 'abcdefg';
begin

you are right, if you do it obscured, no.
"

I thought if you develop a pl/sql package. then wrap it. then drop original package and load wrapped package. Then it should not be readable to even dba, correct? you pretty much need the code or sccs to get access to password, correct? what am i missing here? I thought this make wrap pretty good solution ..

Thanks again,

Tom Kyte
December 13, 2003 - 10:35 am UTC

see the comment below....

in 10g, it does work differently:


[tkyte@megan tkyte]$ cat > test.sql
create or replace procedure test_warp is
key_str1 varchar2(30) default 'Visibleeeiii' ;
key_str2 varchar2(30) default
chr(86)||chr(105)||chr(115)||chr(105)||chr(98)||chr(108)||chr(101)||chr(101)||chr
(101)||chr(105)||chr(105)||chr(105);
begin
null;
end test_warp ;
/
[tkyte@megan tkyte]$ wrap iname=test.sql

PL/SQL Wrapper: Release 10.1.0.1.0- Beta on Sat Dec 13 10:43:56 2003

Copyright (c) 1993, 2003, Oracle. All rights reserved.

Processing test.sql to test.plb
[tkyte@megan tkyte]$ cat test.plb
create or replace procedure test_warp wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
110 df
EUPWGsLmPwrYk9/hF/AOeo0q9BMwg5nnm7+fMr2ywFznUrLL9Ci/dJ5ppXSLCaYdILGFzo/I
yoTKgkd/hTFqf4VaarRnXq+VPdd/d0miNQBWA1eCPkkqKiqCgl5Mr6tcE/PReXH/HDWrb8ns
KirKQGs3Pl8BERioyd77H+ZLdLOTcRas6FSTPHHc4rlBP2l4xRbOApLOVMe+kr7KOmjs+6Yw
5elI

/


Followup to Sudhir's comments

gs, December 12, 2003 - 1:23 pm UTC

When you wrap the code the variables and the values still are visible unless you do something similar to tom has suggested. It is better, the key string to have repetitive charactes.

It looks like warp is kind of compress + some kind of crypting. Tom, can you please verify this?

When the key has repettitive characters they are displayed once.

ex.

create or replace procedure test_warp is
key_str1 varchar2(30) default 'Visibleeeiii' ;
key_str2 varchar2(30) default chr(86)||chr(105)||chr(115)||chr(105)||chr(98)||chr(108)||chr(101)||chr(101)||chr(101)||chr(105)||chr(105)||chr(105);
begin
null;
end test_warp ;
/

part of wraped code:
====================

1TEST_WARP:
1KEY_STR1:
1VARCHAR2:
130:
1Visibleeeiii:
1KEY_STR2:
1CHR:
186:
1||:
1105:
1115:
198:
1108:
1101:
0

0
0
70


Thnaks to you and a reader for the clarification. EOM

Sudhir, December 13, 2003 - 10:09 pm UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library