Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Vikas.

Asked: April 06, 2002 - 9:58 pm UTC

Last updated: August 29, 2003 - 5:23 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,
After a long time I got chance to ask question. I have two questions.

Q1. How can I upload a file from client machine to server file system (not database) using mod_plsql dad configuration of oracle 8.1.7.0. I searched through your site but could not found I got the example for uploading to database but not to file system.

Q.2 Your book says (ch 21 pg 917 ) FGAC can be effectively use for web application developed for application service providers (ASP) who host the data of many clients. When using mod_plsql dad configuration of oracle 8.1.7.0 for a PSP application. My application has a login screen which validate user (not oracle user) from a table which stores the user account information. Once the login is successful ,it creates a session cookies by user name and I use that in my application for validations and authorizations.

Now the sys_context(userenv session_user) value will be same for each request. Or the other userenv parameter will be same for all request made using mod_plsql dad configuration of oracle 8.1.7.0.
My question is that If I create a after logon trigger to set the value of application context or create a procedure like set_app_role (ref. Page 927) and call it in after logon trigger. It will not benefit me. Am I right ? I think in 9ias where the user are the oracle user it works. Kindly let me know how can I use it using mod_plsql dad configuration of oracle 8.1.7.0 .0 database.

One thing more your book is very good, the way you have explain the things is great..
The java stored procedure running OS Command example is very helpful I have used it at many places in my application. GreatÂ… I shall be desperately waiting for Oracle 9i database version of it.

Regards,

Vikas Sharma


and Tom said...

q1) file system upload is not supported via mod_plsql.

You'll need to either:

a) write something in the middle tier, say in java, that accepts the file and writes it out

b) use a java stored procedure in the database to write the blob out after it's uploaded to the database.

q2) Here is how it would work for you.

All of your users login to the database using the SAME userid/password (say that name is "YOUR_APP"). You have done the authentication yourself. Your AUTHENTICATION procedure is executable by exactly one user in the database -- YOUR_APP (so its 'safe', no one else is going to run that).

Once that authentication procedure is satisfied the user on the other end is who they say they are -- you generate a cookie. When you generate that cookie -- you will ALSO set the application context (your OWN context).

On subsequent requests you *must* have some routine in that authentication package that validates the cookie -- here too you will set that application context to the appropriate values for that user.

Now, your fined grained access control policy will NOT look at session user, it will however look at YOUR context to get the appropriate values (hence the name "application context" -- your application sets up the context for the current environment -- your policy checks out that context and does the right thing)



Thanks for the feedback on the book!

Rating

  (4 ratings)

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

Comments

Any Effect for Session State

Vikas Sharma, April 09, 2002 - 1:21 pm UTC

Hi,
i would like to know that, will it depend on the session state. I mean It works for stateful and stateless session of mod_plsql.

i tried it with both it is same for both. Kindly let me know your review on weather i should use stateful or stateless session in this case and in general.

Tom Kyte
April 09, 2002 - 1:43 pm UTC

See the answer to q2 above. I address that there.

Me, I use only stateless connections in all of my applications. There is no way I want people consuming resources of any sort on my server whilst they are not actually using the server. Any state I need to store for them goes into database tables and you get a session id (thats how asktom works)

A clarification

Vikas Sharma, April 28, 2002 - 2:06 pm UTC

I am sorry for asking a delayed clarification. Actually i implemented it little late. The clarification required is:
I have create a procedure create_app_context which creates a context say orgid = 123. I have also created a policy function which return orgid as predicate from context. Policy function work for all DML and select statements.This procedure is called in all the pages who has DML or a select. The context takes value from the current cookie. procedure is
CREATE OR REPLACE PROCEDURE create_app_context IS
v_cookie owa_cookie.cookie;
v_username VARCHAR2(32) ;
v_orgid NUMBER;
BEGIN
v_cookie := owa_cookie.get('uname_orgid');
IF (v_cookie.num_vals > 0) THEN
v_username := SUBSTR(v_cookie.vals(1),0,(INSTR(v_cookie.vals(1),'_')-1));
v_orgid := SUBSTR(v_cookie.vals(1),(INSTR(v_cookie.vals(1),'_')+1));
dbms_session.set_context('hr_cm_app','username',v_username);
dbms_session.set_context('hr_cm_app','orgid',v_orgid);
ELSE
dbms_session.set_context('hr_cm_app','username',null);
dbms_session.set_context('hr_cm_app','orgid',null);
END IF;
END create_app_context;
/
The test procedure which calls a select statement.

CREATE OR REPLACE PROCEDURE testpolicy is
begin
create_app_context;
for i in (select user_id,name,organisation_id from hr_cm_users) loop
htp.prn(i.user_id||'--'||i.name||'--'||i.organisation_id||'<p>');
end loop;
exception
when others then
htp.prn(sqlerrm);
end;
/
This all works fine. I get the result of the query as per orgid. But I have doubt ie. suppose there are hundred of user who are using my application some one are executing a procedure which updates a table some are selecting some are inserting. At any point of time the value of the orgid in context is likely to conflict i mean result may not be as desired. For example if two user a and b from different who has different orgid execute two procedure user a does a update one row(can update only the rows which belongs to his orgid.) And user b execute a procedure which select some rows from the same table (can select only the row which belongs to his orgid.). both executes procedure at the same time. Then are there some chances of getting not the desired result. Because one policy function will be for update and select which uses a sys_context('hr_cm_app','orgid') in both cases. policy function is :

CREATE OR REPLACE PACKAGE BODY hr_cm_security_policyftn AS
gv_appctx VARCHAR2(30) default 'HR_CM_APP' ;
gv_insupddel_pred VARCHAR2(2000) default NULL;
FUNCTION policy_hr_cm_users(p_schema IN VARCHAR2, p_object IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF sys_context('HR_CM_APP','orgid') = 0 OR sys_context ('HR_CM_APP','orgid') IS NULL THEN
gv_insupddel_pred := '1 = 1 ' ;
ELSE
gv_insupddel_pred := ' organisation_id = sys_context('''||gv_appctx||''',''orgid'')';
END IF;
RETURN gv_insupddel_pred;
END policy_hr_cm_users;
.The table cloumns are:
hr_cm@ORCL.DEL1>select name,user_id,organisation_id from hr_cm_users;

NAME USER_ID ORGANISATION_ID
-------------------------------- ---------- ---------------
vsharma 1000 5000
asharma 1001 5001
administrator 0 0
hello1 1002 5000
hello2 1003 5001


Tom Kyte
April 28, 2002 - 3:38 pm UTC

Every session will have its OWN context as you have it defined. The context is local to a SESSION.

Application context's and FGAC wouldn't work otherwise.

You will get the right answer...

PSP Logoff

ht, August 29, 2003 - 5:08 pm UTC

Tom,
Can you point me in the right direction regarding writing a PSP logoff page? I'm testing user privileges in my app but it seems that I must relaunch my browser to login as a different user.
Thanks,
ht

Found the answer.

ht, August 29, 2003 - 5:23 pm UTC

I should have rtfm:

Another method of deauthentication is to add /logmeoff after the DAD in the URL, for example:

</code> http://myhost:2000/pls/myDAD/logmeoff <code>

Thanks and have a good weekend.
ht


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here