Setting Application Context in Oracle for RLS

Dear All,
I have a requirement.
I have web Based Oracle Application (Oracle Forms 10gAs and Oracle 10g DB).
There are approx. 800 application userid but only one DB User .
All application userid is maintained in a table (say T_USER_MASTER).
Each application Userids is associated with one user role or more (stored in T_USER_ROLE).
I want to restrict one column(Col1) of a table (say T1) for all application userid other than one who is doctor (Role).

I want to do this through RLS but I do not know where ,when & how to set system context for this.
Pls.let me know the steps to be followed.
surendra_oracleAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
contexts are set using a procedure or package.  you define that object when you create the context

for instance...


CREATE OR REPLACE CONTEXT my_context USING context_test_pkg;  

CREATE OR REPLACE PACKAGE context_test_pkg
AS
    PROCEDURE set_context;
END;

CREATE OR REPLACE PACKAGE BODY context_test_pkg
AS
    PROCEDURE set_context
    IS
    BEGIN
            DBMS_SESSION.set_context('my_context',
                                    'some_name',
                                    'some_value'
                                    );

             DBMS_SESSION.set_context('my_context',
                                    'some_other_name',
                                    'some_other_value'
                                    );
    END;
END;
0
 
surendra_oracleAuthor Commented:
Thanks.
I do not understand "you define that object when you create the context for instance...".
When and where to set context in D2K Application.
Pls. give me some real example (as I have mentioned in my question)
0
 
sdstuberCommented:
usually you set a context at the beginning of a session.

in a web application where you might have a connection pool, you might want to set the context per page.
I can't be more specific than that because the answer depends on your specific application operation
basic rule:  before you access an object you want context controlled- set the context, if you set it more than once, it's inefficient, but not dangerous


>> I do not understand "you define that object when you create the context for instance...".

CREATE OR REPLACE CONTEXT my_context USING context_test_pkg;  

the object "context_test_pkg"  is defined for the context "my_context"

this means you can only set "my_context" by using "context_test_pkg"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.