Hello Tom,

I agree that the application logic and security logic should be at the server side rather than the client. But would you suggest to put it inside the database or in the middle tier? Fine grained security is a great model for restricting data access to "database users". But it may not be that flexible to restrict the "application users". On ERP and E-commerce systems we tend to have large number of application users and the middle tier with only data and index on the database. Could you also kindly give your views on moving the business logic out of the database.

Thank you.

Tom:

I have a web application that has a table for menu items shown to the user. Based on which group user belongs the main menu is built dynamically using stored procedure.

I also restrict data access by puttin filter in SQL queries based on userids.

Is this considered to be database secure application or application secure.

Tom:

AS a followup, in my web application I have a main page for userid and password where after user enters it I validate it against a user table to check if it is valid.

My question, I pass userid from one page to another. I do not pass password. Do you think if a user bookmarked a page, he can get to it later without going through the main login page. Would this be a bad unsecure design.

How would you handle this?

Thank you,

Hi Tom,

We do have the same architecture for application security. One table for application users and home page to accept userid and password and keeping the userid in the session and using it for transactions. Will we face any security issues with this, if yes, please let me know so that I can tell development team to stop working on this. Is there any other approach to keep users in a table and put some security checks in the database/application so that on one can 'become any user' as you said.

Regards,
Tony.

Tom:

1. DOes not making a hidden password field on every page solve the problem. There is no way a user can load a page without knowing the password. If I use post method too the user can not see the parameters nor he can bookmark it?

2. Do you mean encrypt the userid or password stored in the database?

3. I did not quite understand what you mean by expiring user and your method. Is there an example on your website or reference in oracle documentation?

Thank you,

Tom:

1. I can not use cookies in this application due to management decisions. I am confused on how you actually do this. Are you saying to encrypt/decrypt userid when passing this parameter from one page to another after you select it from the oracle table? How would this protect the application (just for the view source)?

2. I am thinking of creating another column in addition to the username and password called session_id. Every web page will require this session_id for the user. User will never know what his session_id is. I will make this a hidden field and encrypt it in the browser and when I submit the page I will decrypt it. If the value of this parameter was null when a page is called, I tell user to log in first. If there is a value then i run the page.

What do you think of this approach?

3. How do you handle the session expiration without using a cookie?

4. If you use POST method, and every procedure requires unknown number of parameters for the user, how would the user know how to run the procedure with a different userid? Also, if password is passed to every page and encrypted he would not be able to pass the otehr person password?
Thank you,

TOm:

AS a followup to your quote:
<quote>
If you want to go down this path you should consider:

dbms_obfuscation_toolkit, to encrypt the identity

adding an "expire time" to the identity so the encrypted string changes with
each and every page. (eg: the identity is the string "TKYTE 27-may-2002
15:04:03". You encrypt that. When you recieve the encrypted string, you
decrypt it, figure out "it is tkyte", check the expiration date and send back a
newly encrypted string with an updated expiration time)

using ever changing keys -- so the key cannot be guessed over time.
</quote>

1. I have about 50 procedures. Each one has a "p_user_id" as an input parameter and I pass that as a hidden field from one to the other. I do not currently encrypt it. I want o implement your suggestion. Let me confirm what to be done.
I do not use cookie now.

User "Tom" clicks on a URL that show a page to prompt him for userid/password. I take these two parameters, hash the password and query the table for record with userid="Tom" and check if the hashed password is identical to the hashed password in the table. If it is, let him in otherwise not.

If authentication is OK, the user sees a main menu based on what he is allowed to see. Now here, in order to run the main_menu procedure I am passing the user_id as "Tom".

What you are saying is that I should take "Tom" and encrypt it as soon as user is authenticated then pass it in encrypted mode from one procedure to another. This way if a user does a "View Source" or even looks at the URL (get mode) he sees a string that does not mean anything. DO he cannot take "Tom" and replace it with "Mike" and run the procedure as "Mike".

2. If I encode the user_id per step 1, and I have a lot of SQL statements in each procedure that are based on that user_id, do I have to replace all with
select * from table where user_id=decrypt(user_id);

3. If I want to add an expiration time for my system, I can write system time to a cookie and run a function i nevery procedure that compares current time to the time in the cookie and if it is for example larger than 60 minutes force him to log in.

4. I can still do this without a cookie by adding system time to every page as a parameter and comapre it to the intial login time. OR encode it with a userid and decrypt it and pull time string out?

5. If one user wants to skip the login everytime and instead want to go to the main menu. How do you implement this? DO you create a cookie on an initial login and then when the runs the authentication procedure you check if a cookie is there and the userid/password values. If it is valid you let him go to the main menu.

However, if I want to do this only for one user do I have to hard code it in my PL/SQL code for one userid. Create the cookie an check its values?

Thank you,

TOm:

As a follow up to (5):

1. I still need a login routine because I am bascially here automating the entry of the userid/password. This user apparently wants something like Microsoft IE feature where "It remembers userid/password" and windows will paste for you. Here it is the same thing. I think I need t ocreate a permanenet cookie and write the userid/password into that cookie and then when user calls in the login routine it checks for the values in the cookie. if it is a valid entry let him in. Can I do this in reality or not?
Would it jeproadize other things?

2. AS a followup to your quote:
<Quote>
1) no, i just "view source" and your scheme is right there.

Besides, what stops me from opening notepad and typing in:

<form METHOD=POST action=
</code> http://yourserver/url/i/want/to/break/into > <code>

<input type=hidden name=SECRET_HIDDEN_USERNAME_FIELD value=CEO_OF_YOUR_COMPANY>
<input type=text...... other fields here>
<input type=submit>
</form>


saving that file, and loading it into my browser. Wah-lah -- 5 seconds later
"I'm in".
</Quote>

In order to run a procedure, you have to know all input parameters to it. How would you know by looking at the html file. this show you the output of the procedure but how would you break it by guessing how many parameters the procedure needs?

You also say that encrypted("Mike") would be much safer than "Mike: Corect?

3. Can you change the key in your encryption every session?would you recommend doing this?

4. Can you use PL/SQL RAND random number generator for assigning keys? How would this be different than using a regular sequence or sysguid().



Thank you,

Tom:

1. This a special user and you know that you have to do what the client wants sometimes without arguing him. Anyway, the "AutoComplete" feature does not skip the main menu . It only remembers the userid/password and types it in for you.

Would creating a cookie with userid/password on his PC, and adding some code in the login screen to read a cookie if it exists, and run the main_menu procedure if userid="manager" work.

2. As a followup to 2, are you bascially saying that it is very easy for the user to look at the html file and figure out how many parameters you are submitting to the following procedure and then bascially replacing a userid with something else.

3. When you talk about a "Key" IS it like a door key for encryption, and you have to change the lock and get a new key once in a while? When yo usay pseudo random number generator you mean it is predictable while random random is not predictable?

4. Since I use the parameter "p_user_id" in my SQL statments there is no way really to leave code as is unless I can define a default :
p_user_id IN varchar2 default decrypt(p_user_id);

and just use the parameter instead of using a variable. Can i do that?

Thank you,

as a followup to #5:

why do i have to change the name of parameter defined:

here is how I will do it:

In login page I will do:

htp.p('i_user_id',decrypt(i_user_id);

and in main_menu page I will do:

create or replace procedure main_menu as
(i_user_id IN VARCHAR2 default decrypt(i_user_id));
select * from table where col1=i_user_id;
htp.formHidden('i_user_id',encrypt(i_user_id));

and so on from one page to the next.

Thank you,

Tom:

Is there a way to hide a port or server name in mod_plsql.

What i have is an index page on a main web server. then there is a search form which runs a pl/sql procedure and shows the results. However the result page shows the hyperlink for the
</code> http://xx50.was.yyy:7776/pls/xyz/search <code>

This may open it up for hackers. Any hints?

Tom:

Are you saying knowing the server name and port address (7776) does not in any way help a hacker to hack in? Is not that like giving a private phone number out for all to see?

I do not know anything about hacking but this is what a manager is worried about?

Thank you,

Our applications depand on database security. Ie we use databse logins , roles and object privilages.
How ever we would like to prevent users connecting to the databse from odbc based tools like EXEL , ACCESS, VB progams. We would like to allow only oracle forms, reports.
How do we achive this.

Tom:

A DBA said this and I doubt its reliability. Is it true?

<In Oracle Release 9, we have an option of installing the Internet Application Server on a Windows computer, while leaving the Oracle database on an Unix computer. This means that you could use almost any URL for your application. If it works, you could definitely get rid of the numeric portion of an URL.

E.g, A Dell Windows computer could have an address, </code> http://dell24.xxx.xxx

 In the company nameserver, this address could be translated to anything:  

http://www.myfavoritename.com <code>for example.
On this Windows computer, we would install Oracle 9iAS. This software from Oracle would redirect requests to your Oracle database on database server AB20>

1. Was not this always true with 8i too?
2. Does being able to run app server on windows has any effect on hiding URL or re-routing it?

Am in the process of developing an application, which will be rolled out to 5 users at present. The application will reside on the user's machine. The application needs to access 10 tables in the database. The users will have the privilege to Insert, Delete and Update these tables. These tables are also updated by a nightly batch cycle too. A user that is different from the application user runs the batch cycle. From a Security point I need to stop the application users from directly connecting to the database using an ODBC connection (say Access) and Inserting, Deleting or Updating the 10 tables that they have access to. These users have Oracle ID to connect to the database. How can I make sure that the application users do not Insert, Delete and Update these 10 tables that are connected to the application without first connecting or going through the application. What in your opinion is the best way.

Hi Tom,
Talking about the database security, I intend to use FGAC and app contexts ability. But I have a doubt.....our application is on the inetrnet(web-reporting stuff)in order to use the FAGC feature, I'll have to log in each application user as a database user. Suppose, I have millions of users....is it a viable and advisable option to create million database users.
Thanks

Thanks Tom for the followup.....However, I am just getting curious to know how this works in sites like hotmail /yahoo where they have immeasurable users ... do they really have a database user created for each account???????

Hi sir could u plz tell me a way to secure user name and password passing to report in the url
im using forms 9i and open the report using builtin web.show_document

thnx sir for ur attention

Hello Tom,
The information provided in this thread has been very helpful in raising security concerns.

We are currently using the dbms_obfuscation_toolkit server side using our own wrapped procedure and it is working perfectly, but now we have some data which is stored encrypted on the database and we need to display it to the user in a form (Oracle Forms 6i client-server). Additionally we need to take input from the user and store it encrypted in the database. If we call the toolkit from within the forms then the data will be passed via sqlnet unencrypted over the network and will be hackable thru anyone of the means you indicate above.

Is there a pll or builtin that we can use within Oracle Forms to encrypt the data client side before sending to the database?

Thanks!

Hello Tom Sir, 
I have a table like this:

SQL> SELECT * FROM T;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 !ªpT<ùÞ¼   CLERK           7902 17/12/1980        800                    20
      7499 bóÑÁ&#8226;Î&#8211;   SALESMAN        7698 20/02/1981       1600        300         30
      7521 &#8250;&#8221;ô&#352;§ªc   SALESMAN        7698 22/02/1981       1250        500         30
      7566 Æík&#8240;   MANAGER         7839 02/04/1981       2975                    20
      7654 ÂÉàh)rD   SALESMAN        7698 28/09/1981       1250       1400         30
      7698 ©Ü&LïðYH   MANAGER         7839 01/05/1981       2850                    30

I have encrypted the ENAME column by using your encryption function:

function crypt( p_str in varchar2 ) return varchar2 is
        l_data  varchar2(255);
    begin
        l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
        dbms_obfuscation_toolkit.DESEncrypt
            ( input_string => l_data,
             key_string   => 'MagicKey',
             encrypted_string=> l_data );
       return l_data;
end;

Then I developed a form in Forms6i (Please don't stop reading...) based on table T. Created the above function within forms (not as a stored procedure). Then I created another forms function as:

function DCrypt (p_str VARCHAR2)return VARCHAR2 is
        l_data  varchar2(255);
    begin
        dbms_obfuscation_toolkit.DESDecrypt
            ( input_string => p_str,
          key_string   => 'MagicKey',
          decrypted_string=> l_data );
    
        return(rtrim( l_data, chr(0) ));
   end;

Created a control item NAME & a block level POST-QUERY trigger entered this line:

:name := dcrypt(:ename);

Now when I hit F8, I can see the decrypted ENAMEs in NAME control item. Can you please comment on this approach. I have two concerns (may be I'm missing something here):
    1.    The ENAME column is viewable only through my application.
    2.    My KEY_STRING is not accessable.
Am I right or wrong?

Thank you 

Sir,
From the above scenario,
1. I wanted to to access the data only through my application. and
2. as the key is hardcoded in the application, it is not visible anywhere I mean not through sql*net, not anywhere in the database so there no chance of getting/tracing/hacking the key
I think my requirements are fulfilled by the above approach.

<quote>
it would take me 5 seconds to get your key (with no extra software, just a setting in a
file on my pc)
</qoute>
Just wanted you to spit this statement ;). I was challenged by someone on this and I said that the key could be traced without using any special software. Would you PLEASE tell me the setting you would make in the file on your pc?
I dont know the clear line on which to work this out. But I have an idea of using sqlnet* trace option but I dont know how & what setiings/options would be made there
thanks for taking the time.

Hi Tom,

I have a situation like this:

SUPER_USER
|
|- USER-1
|
|- USER-2
|
|- USER-3
|

User -1/2/3 should not be aware of SUPER_USER password.
But, they all will be accessing its objects and
their access to SUPER_USER's objects will be
decided by the grants.

How can I implement this without creating n schema's for
n users?

Please give your valuable opinion.

Thanks.

Hi Tom,

Our application has no security in the database, it's all in the Java and poorly done at that. We are planning on re-doing it. I'm looking on how to get started basically. There seems to be tons of options in the documentation I was overwhelmed by it all. I was hoping you could tell me what the right approach is and how most Oracle applications do security. Have you ever worked for a client where you had to implement new security to an already existing application? That is what we are faced with.

At this time I don't really know enough to ask some more specific questions. I'm hoping you can provide some advice and point me in the right direction. Then maybe I can be more direct and less vague. Thank you.

Tom:

You said:
"adding an "expire time" to the identity so the encrypted string changes with each and every page. (eg: the identity is the string "TKYTE 27-may-2002 15:04:03". You encrypt that. When you recieve the encrypted string, you
decrypt it, figure out "it is tkyte", check the expiration date and send back a newly encrypted string with an updated expiration time)"

1. Let say user A logs in to the main menu. On main menu you create a hidden parameter for the value of
encrypt(user name||sysdate). Then user A clicks on a link to open a form. The encrypted string gets passed to the next page.

Now on the second page you decrypt the string. But what do you base your validation against? Do you just basically read the userid part and see if it exists in the users table to see if it is valid? If that is true, then any userid would be allowed to get into the page.

2. Would your method be better than passing the userid and the password (hashed). Then on next page you compare both values against the users table to see if they match.

3. If you have pages with "URL calls" and not a post page method, would you have to pass the encrypted string in each URL too?

THank you,

Tom:

1. Does not that mean you pass the key from one page to the next and a user can see it from "View source", otherwise how would you know the key on the next page.

2. trying to see how this works in real life:

PROCEDURE 1

PROCEDURE P1
(i_user_id VARCHAR2 DEFAULT NULL)

(User "mike" logs in)
dbms_obfuscation_toolkit.desencrypt(
input_string ==> i_user_id
key_string ==> '1'
encrypted_string ==> v_encrypted );
htp.formHidden('i_user_id',i_user_id)
htp.formHidden('i_enc_string',v_encrypted);
htp.formHidden('i_key',1);
---------------
PROCEDURE 2

PROCEDURE P2
(i_user_id VARCHAR2 DEFAULT NULL.
i_enc_string VARCHAR2 DEFAULT NULL,
i_key VARCHAR2 DEFAULT NULL)

dbms_obfuscation_toolkit.desdecrypt(
input_string ==> i_enc_string
key_string ==> i_key
encrypted_string ==> v_decrypted );
--Now you check if the decrption works
IF (v_decrypted is not null) THEN
LET USER IN
ELSE
TAKE HIM TO LOG IN PAGE
END IF;

IS THIS CORRECT OR AM I MISSING SOMETHING?


THanks,

Tom:

1. What do you mean "the key is in the database". It is a random number. How do you know that random number when you go to the next page if you did not pass it.

2. Can you write briefly the logic (not code) for your methodology that would be implemented in the two procedures? I still do not understand how you read the same key from one page to the next and how when you decrypt using that key you decide it is valid user.

Thanks

Tom:

1. You say to keep key in database. Does that mean you add another field to the users table

Userid password key

And then on every page you decrypt the string using the key associated with that user and then decide if the userid exists in the table?

2. If you decrypt the string using a different key, you would still get a result but that would not be a valid user name and you reject the page, Correct?

3. Do you assign a different key for every userid? Do you set a job to update this key daily?

4. How do you get the userid and date from the string? Do you use instr/substr for that?

thank you

Tom:

It seems the “key” is like an alarm code. You can’t get in to the house without it and you change it every few months.

You said:
<if you were able to decrypt the string, that means you knew the key - no one else knows the key - hence the very fact you were able to decrypt validates the
data.>

1. I tried decrypting a string with different keys and I did get a value even though it is scrambled. However, the decryption returned a value. My question, is that do you extract the user name from the decrypted string and then do a count against the users table to see if it is a valid user id and then build the page. Correct?

2. On another thread you said to use random key generator or algorithm for key. Does not that mean you have a different key with each page. How would you store the key in a database? Do you just create one table/one column for it?

3. Your method does not prevent me from bookmarking the URL and opening a new browser and pasting it (as long as the time you set did not expire”. Does it?

Thank you,

Tom:

A few questions on web security issues:

1. Do you see a small loophole in the above method of passing encrypted (userid+sysdate) string. If you do "View source" and print the page, then someone else can rebuild the page with the same parameters and it will work as long as the expiration time has not passed. So if the expiration time was 10 minutes, he can rebuild the page in 10 minutes and get a successful execution? Correct

2. Is not the best way to get 100% security is to create a Session cookie on the user cache memory after he logs in that stores the encrypoted userid and system time. When going to the each page we validate the values in that cookie?

3. Actually as an alternative to #2 above would not be better to use a unique session id in the cookie and a session id in the user/password/session table. User is allowed to access page if the session id in his users table match the session id value in the cookie in memory. This way even if someone steals the URL he would not be able to open another session for that user because he would not have the cookie with same session id.

4. If we keep the paramter "p_userid" passed from one page to page in addition to enc(userid+sysdate) would the application still be insecure?

5. In one oracle web book they used a varchar2(2000) for encrypted password field. I thought that has to be binary in multiples of 8?

6. I find that saving encrypted password is more helphul than hashed passwords because when a user has a problem you can unecrypt his password and log in as him to see the problem. When it is hashed you cant do that. Wht do you think of this?

7. You say the fact the you can decrypt the encrypt string means you can access the page. However how do you validate this. Decrytion will always return something (good text or scrambled text). Would you capture the first x number of characters of decrypted string and do a count against the users table to see if you get a count or not?

thank you,

As a follow up. let me clarify some of the issues;

1. SSL is for someone spoofing the network. What I meant if someone in the same room left his browser on and went outside for a minute then another person came over and did "View source" and print the code and see what is the "encrypted_string". Then he can rebuild the URL and submit the page from any other machine before expiration time kicks in. Do you think this is a weak application security? Would not having the string in a cookie in cache eliminate this possibility?

2. How can you get the encrypted identity value from the cookie on my machine? I can't see it being possible?

3. How can you set a cookie with telnet on your machine or my machine?

4. Are you saying the oracle 8i web development book is wrong in storing encrypted password value in varchar2(2000) field. Check out the profile table on page 614 of Oracle 8i Web Development.

5. A system admin can see all information in database tables. I do not see where the problem here. He does not need to see it from the application. What I meant is that it is a good troubleshooting tool to log in as the user and see what he sees. Sometimes users are wrong. The only way to validate what they claim is to log in as them. This is a customized app not a COTS app. What you say is like me hiding my phone number from the phone company. They already know everything.

6. It seems to me if you use session cookies, we make the application:
a. Cookie dependent
b. Will create problems for users with firewalls or proxy servers that block cookies
c. Will create problems for users with browsers set to "high" security
d. There might also be some issues between Firefox, Netscape and IE browsers. Each one may handle cookies differently.

Am I correct with the above issues?

Do you think "passing parameters" even though is more time consuming it is much safer and reliable?

7. When you use a cookie, I assume you set the value once after authentication and keep it throughout the session? Is this correct or you change the encrypted value in the cookie with every page just like when you are passing parameters from page to page.

8. Would you be able to use a database table for "encrypted identity string" instead of a cookie?

We add another unique field to the users table called "ip_address"
User logs in and we update the field "ip address+expiration time" in the users table with the machine ip. When he tries to access another page we check the ip address for that user in the users table against what retreived value for client ip address. If they match we allow him access within expiration time. If another user tried to submit a page using another userid, the ip address for his machine will not match the ip address for the faked user id in the users table.

I am not full sure about this technique because they may use proxy server and the database will get the same ip for everyone in that case.

Do you see any problems with this solution?


9. Since encryption generates binay string, how is the encrypted identity going to pass through the URL. My understanding is that only ASCII data can pass.

Tom:

1. How do you explain this:

When I test encryption/decrption on he same page it works:

Expiration Date: 14-apr-2006 17:49:41
User ID: TOM
Encryption Key: MICHAEL3

Encrypted String: K/>|o1p?3|l`#w[Dc?QBW\Vicn 
Decrypted String: TOM 14-apr-2006 17:49:41

When I submit the encrypted string from page to page it does not work. I get this.

Encrypted String: uW} 5%T`Gmj~w).`?apC70Lq]}#
Key: MICHAEL3
Decrypted String: 2(i:WP*4\=Wq(eqVd
;i o]?

The code for this was:

dbms_obfuscation_toolkit.DESDecrypt
( input_string => i_encrypted_string,
key_string => v_key_String,
decrypted_string=> V_DECRYPT1 );

When I submit the encrypted string using (strfmt.url_encode) or (htf.escape_sc)
I get another error:

Fri, 14 Apr 2006 21:44:03 GMT

ORA-28232: invalid input length for obfuscation toolkit
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 0
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 75
ORA-06512: at "IMSADMIN.TEST_SECURITY_2", line 35
ORA-06512: at line 7



2. Are you aware of this?

</code> http://www.computerworld.com/securitytopics/security/holes/story/0,10801,103473,00.html <code>

Tom:

1. Are you suggesting to chage the type of the input parameter to "RAW" instead of VARCHAR2?

2. I thought you can't pass binary data in the URL. How do you work around this when you wanna pass the string in a URL.

3. You said to save the key in a database table. Where else would you keep so that your code have acces to it?

Thank you,

Tom:

Thanks for the hint. I think what you meant is to change the binary to hex before submitting it and then back to binary before decryption. Is this correct?

I used the type "varchar2" for the passed HEX string and "raw" and they both work. Is not "HEX" of type varchar2?


PAGE 1

v_input_string := rpad(i_user_id||' '||to_char(sysdate+10/1440,'dd-mon-yyyy HH24:MI:SS'),32);

dbms_obfuscation_toolkit.desencrypt(
input_string => v_input_string,
key_string => v_key_string,
encrypted_string => v_encrypted );

htp.formHidden('i_encrypted_string',utl_raw.cast_to_raw(v_encrypted));


PAGE 2

PROCEDURE TEST_SECURITY_2 (
i_user_id IN VARCHAR2 DEFAULT NULL,
button IN VARCHAR2 DEFAULT NULL,
i_input_string IN VARCHAR2 DEFAULT NULL,
i_encrypted_string IN VARCHAR2 DEFAULT NULL )

BEGIN

v_encrypted_string := utl_raw.cast_to_varchar2(hextoraw(i_encrypted_string) );

dbms_obfuscation_toolkit.DESDecrypt
( input_string => v_encrypted_string,
key_string => v_key_String,
decrypted_string=> V_DECRYPT1 );

2. Oracle book suggests to save the key in a pl/sql package body and wrap it and accessign it from there. Do you think this is a good way?

3. Is there an oracle package the implements Advanced Data Encryption (AES) since DES was cracked by RSA using a big computer and 56 hours of work.


</code> http://en.wikipedia.org/wiki/EFF_DES_cracker <code>

I beleive DES3 is different than AES.

4. Is there an oracle package that does "binary to ASCII" conversion?

Thank you,

Tom:

1. I understood your comment but did not understand what I should do. Should the P_encrypted HEX string passed from page to page be set to "RAW" or "VARCHAR2"?

2. I am confused. Before you told me to put the key in a table in the database, now you say do not keep it in the database! If it is not in the database, how all those procedures that decrypt the key are going to get the key. I can write it on a piece of paper and save it in a locked drawer but how would the system get it? How would you do it?

3. Which current release implements AES? Which function does it?



4. If i am using a session cookie to pass encrypted identity, do I really need to edit all my existing web pages to take out the "p_user_id" I pass from page to page. My queries sometimes depend on this parameter. I do not see any need to change that since no one can break the system with the encrypted identity. Do you see it same way or there are drawbacks?

5. My web app generates several email alerts that let user view an html order confirmation or pdft order confirmation. Now with adding security features do you let users log in before viweing that or you just add an encrypted identity to that URL too.

Thank you,

Tom:

When I create a session cookie and write text to it, it retrieves the value on the next page fine. However it is not working for encrypted string. The decrypted string comes out as some scrambled data and not the same as the original text string (user id+expiration time).

I tried all conversions and still would not work. I keep getting

Session Cookie Created with Value: b?_4_n2!Wz\Dk_x~___SJ__h_+_a!J6

Decrypted Cookie Value: @f%NIPlyD426m~@a.J{ [CR


Here is the code:

v_ret_val:= get_user_cookie;
v_enc_string := v_ret_val;

dbms_obfuscation_toolkit.DESDecrypt
( input_string => v_enc_string,
key_string => v_key_String,
decrypted_string=> V_DECRYPT1 );


Then I tried adding combinations and all of these two statements before decryption and it is still does not work.

v_enc_string := utl_raw.cast_to_raw(v_ret_val);
v_enc_string := utl_raw.cast_to_varchar2(hextoraw(v_enc_string));

Tom:
I did it in RAW. I tried all kinds of things and this error keeps coming up when it tries to DECRYPT. Do you see anything wrong? I also tried returning a raw value from the cookie but changed it to VARCHAR2 because oracle returns a cookie value in their GET function.

Fri, 21 Apr 2006 20:08:26 GMT

ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at "ADMIN.TEST_AUTHEN", line 244
ORA-06512: at line 7


Here is the code:
---------------------------------------------------------------------------
FUNCTION get_user_cookie
-- RETURN RAW
RETURN VARCHAR2
IS
cookie OWA_COOKIE.cookie;
ret_val VARCHAR2(2000) DEFAULT NULL;
-- ret_val RAW(2048) DEFAULT NULL;
BEGIN
cookie := OWA_COOKIE.get('nls_user');
IF (cookie.num_vals != 0) THEN
ret_val := cookie.vals(1);
--ret_val := UTL_RAW.CAST_TO_RAW(cookie.vals(1));
elsif (cookie.num_vals = 0) THEN
HTP.print('<h1>Cookie not found!</h1>');
END IF;

RETURN ret_val;
END;
-------------------------------------------------------------------------
PROCEDURE TEST_PAGE1 (
i_user_id IN VARCHAR2 DEFAULT NULL)

IS
v_ret_val VARCHAR2(2000);
v_decrypt1 RAW(2048) ;
v_key_string varchar2(100);
v_encrypted_raw RAW(2048);
v_raw_key RAW(128);

BEGIN

v_key_string := 'MICHAEL3';
v_raw_key := UTL_RAW.CAST_TO_RAW(v_key_string);

v_ret_val:=test_authen.get_user_cookie;
v_encrypted_raw:=UTL_RAW.CAST_TO_RAW(v_ret_val);

-- v_encrypted_raw:=hextoraw(v_ret_val);
-- v_encrypted_raw:=UTL_RAW.CAST_TO_VARCHAR2(v_ret_val);
-- v_encrypted_raw:=v_ret_val;
-- v_encrypted_raw:=rawtohex(v_encrypted_raw);

--This is where error occurs-->

dbms_obfuscation_toolkit.DESDecrypt
( input_string => v_encrypted_raw,
key_string => v_raw_key,
decrypted_string=> V_DECRYPT1 );

END;
--------------------------------------------------------------------------

TOm:

I did not paste the whole thing but that is where Line 244 starts. I took out the htp print statements.

What do you mean that it did not come out from decryption package? Do you see anything wrong in the code.

Tom:

1. I did check the user_source and it was that line 244 (DESdecrypt) causing the error. It is easy to check that the code was not causing it by commenting the package out.

I found that the paramweter names for RAW is different. It is input, key, and decrypted_data. That was probably causing it!

2. On every page:
a. I decrypt the value in cookie (which is always hex)
b. Check if decrypted string has a valid user id and either contunue or stop
c. Update value in cookie with a new value before any <HTML> statments on page.

Step c does not seem to work properly. I keep getting old values of time stamp. Do you see anything wrong with the function?


PROCEDURE set_cookie (i_user_id IN VARCHAR2)
IS

v_key_string varchar2(100);
v_input_string varchar2(100);
v_encrypted_String varchar2(200);

v_raw_input RAW(2048);
v_raw_key RAW(128);
v_encrypted_raw RAW(2048);
v_decrypted_raw RAW(2048);


BEGIN

OWA_UTIL.mime_header('text/html',FALSE);
v_key_string := 'MICHAEL3';

v_input_string := rpad(i_user_id||' '||to_char(sysdate+20/1440,'dd-mon-yyyy HH24:MI:SS'),32);

v_raw_input := UTL_RAW.CAST_TO_RAW(v_input_string);
v_raw_key := UTL_RAW.CAST_TO_RAW(v_key_string);

dbms_obfuscation_toolkit.DESEncrypt(
input => v_raw_input,
key => v_raw_key,
encrypted_data => v_encrypted_raw );

OWA_COOKIE.send('ls_user',v_encrypted_raw);
OWA_UTIL.http_header_close;

END;


3. Based on the above, I have to pass the "i_user_id" from page to page which is not a good idea. Would you change the above formula so you always get the value for "i_user_id" from the cookie and not pass anything from page to page in the whole application?

Thank you,

Tom:

here is your comment about keeping the key in database;
<quote> January 31, 2006
you have a key, key is in database. keep key in database. access key from
database. change key from time to time over time.
</quote>

But later you said it is not safe because DBA can find it. IMO, in order for encryption/decryption to work you have to put the key somewhere inside the database. Can you tell me if you were designing a system where you would store it at.

2. You do not have any comment on the above why updating the value of the cookie with new timestamp does not work.

Tom:

I want to use your method for authentication by a perl client program to an oracle stored procedure over http.

Basically the perl program calls a url to the oracle database. Perl will encrypt the string "joe 09-25-2007 10:13:00' and I assume convert that to hex and post it to oracle. ORacle will decrypt it and find it valid or not.

WOuld this work? Would encryption to DES be standard among all sofwware vendors. any better ideas for authenticating batch jobs in perl running over http to oracle database.

thanks,

TOm:

<quote>
If you want to go down this path you should consider:
dbms_obfuscation_toolkit, to encrypt the identity
adding an "expire time" to the identity so the encrypted string changes with each and every page. (eg: the identity is the string "TKYTE 27-may-2002 15:04:03". You encrypt that. When you recieve the encrypted string, you
decrypt it, figure out "it is tkyte", check the expiration date and send back a newly encrypted string with an updated expiration time)
</quote>

This is what I mean. Even though your description was for a web application authentication I want to use your method for remote batch job (perl) authentication to run a stored procedure.

SSL is not set up right now. beyond our control. We want a mechanism where we id the batch job that will try to run the SP. We can either include the key in the code or put it in a text file somewhere on the file system.

DO you think the an encrypted string in perl using AES and a key "magic" can be decrypted using oracle package.

any better ideas.

let me first say that this is batch job and not an application where user logs in. The batch job uses LWP to make an http call to the oracle SP.

In a.) you say the login info needs to be encrypted.

OK this means that PERL has to encrypt the "userid/password" or in my case the string "tom 09-01-07 10:10:00".

So this is the first step, is perl encryption of a string.

Now this is posted to an oracle SP.

The second step is that oracle MUST decrypt this hex string using same key used to figure out if this user is valid or not.

This is my second step, is oracle decrypting the string to validate it.

Oracle will send an http response back only.

When perl makes another request it will repeat procedure above.


So basically we are doing perl batch job authentication with every call to oracle sp. String will be build and passed by perl and oracle only verifies that decrypted string had a valid username and valid expiration time.


2. SSL encrypts the whole message and we are encrypting part of it. Why a sniffer can't steal the whole message SSL sent and try to run it to same way he steals the URL that contains encrypted uername and password.

Thanks,

Tom:

disregard my previous post. I read the thread again and refreshed my memory.

You are right that the database is the one who should be issuing the encrypted string to perl.

I think this authenitcation should work as follows.

a. Perl submits an encrypted userid/password (db account info) to oracle. Oracle validate credentials and allow or deny access.
b. ORacle send a response with a token which is encrypted(userid + expiration time).

c. Perl will use that for with every request to an SP.

d. If token epxires, perl will have to re-login.


1. Is this correct?

2. My question is how the client perl will encrypt userid/pasword. I assume the only way to secure this is using SSL but we are not now. If perl to encypt it that means oracle will decrypt it. I am not sure i PERL encryption and oracle decryption will yield same result.

3. Is there any way for oracle SP to capture the ip address of the machine where perl is running and not the ip address of the web server. If we can, then we can only allow that ip and you do not really need encryption or ssl.

4. There will be about 10 perl progams running on same machine continuosly. Does this mean each perl has to login or you can authenticate the first one and the others use the token passed to the first.

thanks,

Tom:

1. I assume you can use several options for key management:

a. hard code it in the program as a variable.
b. Store it in the server in a text file on the perl server
c. Store it in a table (hidden) in the database/
d. Create a formula that computes the key.

The key has to be somewhere on the server otherwise how would the program gets it.


2. in summary, are you saying that it is impossible to have web security without SSL?

3. Why can't apache web server pass the ip address of the client machine to mod_plsql? that would solve the problem in case someone steals the userid/password.

Tom:

Do you usually require the client to send the session id and userid or just SESSION ID in every call?

If I have a table, and I create a session id for a user. I think i would require the page to send
both parameters and check those against the table. correct?


users
-------
userid
password
session_id
expiration_time


User A logs in with userid/password. Oracle issues a session idusing SYS_GUIID() and send it back to user.

Now when user send back the session id, do you require him to send the userid too to validate the sessionid against that user.


***This will tell me if the session id is valid but it does not tie it to that specific user

select count(*) from users where session_id=p_session_id and sysdate < expiration_time


In the above note, you say to only pass session id and not username?

Tom:

so you say you query the user table for the passed session id to find out who the user is and other session variables?

I was thinking that if you have 100 users, one user may steal a session id and impersonate the session id of another and log in as his. But SSL should prevent this.
anyway passing the username does not solve this problem.

Tom:

When you receive input from a web user do you code your pl/sql to verify this or you leave it for database:

1. Validate length of string submitted against column name.

If yes, how do you do it and do you raise any specific exception.

2. Validate value passed against column allowed values

For example, gender has to be null,'M', 'F'

If user passes anything else reject the transaction

3. Validate Required fields. If the parameter is null for a required field reject transaction

TOm:

This is a claient program that will be calling those stored procedure. BUt it is doing similar thing to a browser.
However, When I receive a message from the http client I have to validate the above and send an XML message back.

can you show me a small example (insert into EMP table) of how you code this. I think you most likey use an exception for each condition and print the message there.

Tom:

The client is PERL using LWP for http calls.

I am not writing the client. So i want to validate in the DB side. would you let the DB error propagate like if they sent a string longer than column, or you would check like

If length(p_name) > 40
raise name_too_long;
end ifl

exception
WHEN name_too_long
htp.p('Error Code = 001');
htp.p('Error MEssage = Name is too long');
raise;

end;

Tom,

I have a question regarding application level security.
(I know - it sucks - don't do it).
Unfortunately, we have an existing system that is massive and we are looking for a way of restricting/identifying different (in house) applications.

So, simliar to what's been asked here, "can we restrict access to Forms, but not SQL or Toad", we are trying to identify an application (say "abc.exe" or "def.exe" - our own apps, vs, say "toad.exe").

We are looking at signing the .exe itself, and then loading the related certificates into the database. Basically - a way of "iding" an application. Then we can set roles based on app (again, I know, it sucks, but we don't have many options at the moment).

Is there anyway of Oracle being able to id a certificate-signed app? Is there a "better way" of doing this - aside from redesigning the whole thing?

I undertan the "best way" is redesign, I'm hoping to find a compromise .. something we use to .. well, "patch" things up for a bit ... *sigh*

Client Server ... (Power Builder front end, Oracle 10g backend). I've been doing some reading on certificates in PowerBuilder and on Sybase, and will probably head over there next, but wanted an opinion that made sense, first ;)

I do not believe it does.

Users login to the database, via the PB app ...

I was thinking we could sign the app
(ie via a tool such as: http://msdn2.microsoft.com/en-us/library/8s9b9yaz(VS.80 ).aspx or verisign, or whatever ...)

Then if (big if) we could validate ... "something" on the Oracle database side ... that's kinda where I hit a roadblock ... I can't think of anything in Oracle that does that, or anyway of doing it ... I wanted to check with somebody who would know if it's doable or not ..

I was afraid you'd say that ... *sigh*

Thanks for your time!!

Does the Windows Oracle 10 client encrypt data passing across? Is there an option to encrypt?

Yep, that's what I meant ... I thought it did, just wanted to verify .. ;)
(ok, now more mundane questions .. sorry .. )

Thanks!! ;)

Tom:

can you provide a small example of how you do what you said here below? Let us say i have the emp table

<<But the validation should be metadata driven so that when the rules change, the page changes and the new rules are reflected immediately. 
I would not hard code this stuff in the plsql routine, just let the database do its job - if you wanted, you would create a table of constraint name to "more meaningful message to be displayed to end user" for all of your constraints and the main thing that "paints the page" would display that text for the end user. >>

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)


My requirement is to validate the inputs provided by an http client to make sure i only insert clean data.

1. validate input type (strings against strings, numbers against numbers, dates against date)
2. validate input length.
3. Remove embedded HTML tags or fake sql statements

would you write small pl/sql functions (i.e. validate_date, validte_string,validate_number) to do this before you hit the table? If not how would you do it.
I want to raise an exception "INVALID_INPUT_DATA" whenever the inputs do not match.


validate that p_empno is a valid number else reject
validate that p_ename is a valid string else reject
validate p_hiredate is a valid date in 'DD_MON-YYY' format else reject.

Also,

validate that p_empno less than 5 bytes else reject
validate that p_ename is less than 11 bytes else reject
validate that p_deptno is less than 4 bytes else reject

Tom:

1. What do you tell that camp that says the data validation should be done before you hit the database? I know you have pretty convincing arguments. Is there any benefit to what they are saying. Maybe that applies when you have the code on an application server which is a separate machine from the DB server.

2.  Is this what oracle will for for "Data Type Validation": validating numbers and dates?

Do you just add "INVALID_NUMBER" exception handler and "INVALID_MONTH" and those will be raised automatically by DB.

SQL> insert into emp (empno) values ('abc');
insert into emp (empno) values ('abc')
                                *
ERROR at line 1:
ORA-01722: invalid number


SQL> insert into emp (empno,hiredate) values (123,'01/01/2008');
insert into emp (empno,hiredate) values (123,'01/01/2008')
                                             *
ERROR at line 1:
ORA-01843: not a valid month

3. I do not know how you validate a string against a string? do you have any idea. a number could be a string unless the string is defined to be alphabet only and no number or special characters.

4.  is this what you would get for validating "Parameter Lengths"? Do i create an exception to report a user fiendly message.

SQL> /
 insert into emp (empno,ename) values (1123456,'abcdefg')
                                       *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


  1*  insert into emp (empno,ename) values (156,'abcdefghijklmn')
SQL> /
 insert into emp (empno,ename) values (156,'abcdefghijklmn')
                                           *
ERROR at line 1:
ORA-01401: inserted value too large for column

5. By "fake sql" I meant "bogus" sql statements that users sometimes stick in a field to do sql injection.

  Do you filter the html tags or special characters or anything or do you jsut use bind variable and you
  leave the data as is.

2> create table t ( x varchar2(10) constraint make_sure_x_is_ABCDEF check ( replace( translate( x, 'ABCDEF', 'A' ), 'A', '' ) is null ) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 'ABCDEF' );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 'ABCDEFG' );
insert into t values ( 'ABCDEFG' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.MAKE_SURE_X_IS_ABCDEF) violated

Tom:

1. So you are saying data validation MUSt be always done in DB but it can also be added to the application.
OK, does that mean I will have to write a plsq/l routine for this validation on top of the DB. Or you are refering to doing javascript validation if it is html form.

2. my #2 question was related to how the DB will validate the data type. I assume when data does not match field type you will get these exceptions: ORA-01722 or ORA-01843.  

I want the procedure to raise my own "invalid_data" exception to the user. Do I create a program or do you use a WHEN OTHERS or how you do it efficiently when oracle is raising the error implicitly.

SQL> insert into emp (empno) values ('abc');
insert into emp (empno) values ('abc')
                                *
ERROR at line 1:
ORA-01722: invalid number


SQL> insert into emp (empno,hiredate) values (123,'01/01/2008');
insert into emp (empno,hiredate) values (123,'01/01/2008')
                                             *
ERROR at line 1:
ORA-01843: not a valid month

3. what is that replace/transalte do? Are not you here just validating the values of data that can be stored in that column. for example, if it is (Y/N), you are only accepting a Y/N values.

4. so you do not do any stripping or checking of characters for user input?

if user typed in "select * from emp;" OR "<HTML><HEAD>this is a test</HEAD></HTML>

you do not change anything. right.

all i have to do is 

INSERT into TABLE_T values (p_col1,p_col2,...)

thank you,

Tom:

Here is the question for you.

How can I bring all those implicit oracle exceptions that are raised for validating field size and length (on INSERT or UPDATE) to raise my user defined exception "invalid data input" which returns a user friendly message to the client.



<?xml version="1.0" encoding="ISO-8859-1" ?>
- <ROWSET>
- <ROW>
<ERROR_CODE>ORA-21000</ERROR_CODE>
<ERROR_MSG>Invalid Data data input</ERROR_MSG>
</ROW>
</ROWSET>


I think all of those exceptions are going to be handled under WHEN OTHERS which is designed now to report the oracle error code and message (SQERRCODE and SQLERRM) for unexpected errors.
.
The client is not coded to handle all those errors. It is just logs into a text file for further review by a human for possible action.

When I tell him you have provided invalid input using ORA-21000 he knows to check the data he provided based on the data exchange format we agreed on.


thank you,

Tom:

This is what I was afraid you were going to say.

There are many many expcetions that can come out. How can i list those. it sounds like a big job.

there are two things i am thinking of. you can advise me on which one is better:

1. report the oracle error number as it is under "WHEN OTHERS".

2. create a variable and assign it a value befor INSERT or UPDATE statement and reset it afterwords immedately.

If INSERT results in an error, I hit the WHEN OTHERS and put some code to check if the variable is set then report that error ORA_21000 else report the oracle error code as usual.

v_error:='invalid_data';
INSERT INTO table.....
v_error:=null;

WHEN OTHERS THEN
IF (v_error := 'invalid_data') THEN
report the ORA_21000;
ELSE
report sqlcode ||SQLerrm;
END IF;

begin
    ... code ...
    ... code ...
    begin
        insert into t ....
    exception
        when ....
    end;
    ... code....
end;

Tom:

It is a different kind of "big" job.

There are hundreds of oracle errors. The task now is to identify any error that can happen ONLY due to data validation (field length and type). THere might be only a few. I do not know.

I am not sure how you can even identify this to start with. You have a long list of errors:

1. Identify which one result from INVALID insert.
2. Identify which expceptions occur as result of data validation issue and handle those differently.

I am just wondering like doing a cost/benefit analysis it is not worth it. Just report the error as it is reported by oracle "invalid date" or "invalid number", etc.

Reporting all of these under "invalid data" does not seem to have a lot of benefit.

Do you agree.