I am involved in the development of a complex application that accesses Oracle 9i through a middle tier. End users of the application all login to Oracle through a single Oracle account but are uniquely identified within the application. End users have varying "roles" within the application, which in turn determine the database views and tables each user can access. In some cases, these "roles" only provide access to subsets of the columns in various base tables. Database views are used to enforce the necessary column level security. The application has no knowledge of these views. When the application queries a table, I would like to have the query to use the appropriate view, based on the privileges associated with the current user session. I understand the use of login triggers and Oracle 9i "secure application roles" to set user privileges at a session level. These application roles will be used to determine which views and tables a given user session is granted access to during session login.
QUESTION: Based on the explanation above, is there a way to have Oracle use the appropriate view when the application submits a query against a table? Conceptually, I would like to implement something like "session based synonyms."
The views and tables are not owned by the Oracle login account. All views will contain field types and ordering to match their base tables. Restricted columns will typically return something like NULL. The system will serve hundreds of users and potentially thousands of tables.
Thanks for any suggestions you can offer. Your book is exceptional!!
Ok, here is a method -- I'll warn you up front however that SYNONYMS and VIEWS in a very very very large system add overhead (everything does). You'll want to be aware of that. But....
We'll create two schemas A and B. They will have SYNONYMS and nothing else (they cannot even select from the tables they have syonyms on!)
We'll create a third schema, single_acct -- this is your application schema.
Using the "set current_schema", we can get "select * from t" to query totally different tables, consider:
ops$tkyte@ORA717DEV.US.ORACLE.COM> create user a identified by a;
User created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> create user b identified by b;
User created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> create user single_acct identified by single_acct;
User created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> grant create session, create synonym to a;
Grant succeeded.
ops$tkyte@ORA717DEV.US.ORACLE.COM> grant create session, create synonym to b;
Grant succeeded.
ops$tkyte@ORA717DEV.US.ORACLE.COM> grant create session to single_acct;
Grant succeeded.
ops$tkyte@ORA717DEV.US.ORACLE.COM> @connect scott/tiger
ops$tkyte@ORA717DEV.US.ORACLE.COM> set termout off
scott@ORA717DEV.US.ORACLE.COM> set termout on
scott@ORA717DEV.US.ORACLE.COM>
scott@ORA717DEV.US.ORACLE.COM> create or replace view v1
2 as
3 select ename, empno, decode(0,1,1,to_number(null)) sal
4 from emp
5 /
View created.
scott@ORA717DEV.US.ORACLE.COM> grant select on v1 to single_acct;
Grant succeeded.
V1 cannot see SAL, it'll see NULL everytimescott@ORA717DEV.US.ORACLE.COM> create or replace view v2
2 as
3 select ename, empno, sal
4 from emp
5 /
View created.
scott@ORA717DEV.US.ORACLE.COM> grant select on v2 to single_acct;
Grant succeeded.
V2 can see sal, everytimescott@ORA717DEV.US.ORACLE.COM> @connect a/a
scott@ORA717DEV.US.ORACLE.COM> set termout off
a@ORA717DEV.US.ORACLE.COM> set termout on
a@ORA717DEV.US.ORACLE.COM> create synonym emp for scott.v1;
Synonym created.
If you use a.emp, you get scott.v1 = NO SALARYa@ORA717DEV.US.ORACLE.COM> @connect b/b
a@ORA717DEV.US.ORACLE.COM> set termout off
b@ORA717DEV.US.ORACLE.COM> set termout on
b@ORA717DEV.US.ORACLE.COM> create synonym emp for scott.v2;
Synonym created.
if you use b.emp, you get scott.v2 = SALARYb@ORA717DEV.US.ORACLE.COM> @connect single_acct/single_acct;
b@ORA717DEV.US.ORACLE.COM> set termout off
single_acct@ORA717DEV.US.ORACLE.COM> set termout on
single_acct@ORA717DEV.US.ORACLE.COM>
single_acct@ORA717DEV.US.ORACLE.COM> alter session set current_schema=A;
Session altered.
single_acct@ORA717DEV.US.ORACLE.COM> select * from emp where rownum < 5;
ENAME EMPNO SAL
---------- ---------- ----------
A 7369
ALLEN 7499
WARD 7521
JONES 7566
Here EMP = A.EMP = SCOTT.V1 = no salary...
single_acct@ORA717DEV.US.ORACLE.COM> alter session set current_schema=B;
Session altered.
single_acct@ORA717DEV.US.ORACLE.COM> select * from emp where rownum < 5;
ENAME EMPNO SAL
---------- ---------- ----------
A 7369 800
ALLEN 7499 1600
WARD 7521 1250
JONES 7566 2975
#now, EMP = B.EMP = SCOTT.V2 = SALARY
I recommend you consider doing this in your middle tier however -- you are most likely using JDBC with Java - in which case, all SQL is dynamic anyway -- just "glue" the right view name in there in the app -- have the ROLE be part of the viewname itself:
create view CLERK_v1 as...
create view ADMIN_v1 as ...
create view USER_v1 as ...
and in your jdbc app:
query = "select * from " + their_role + "_v1 where ....";
depending on the scale of your application, this could make a difference down the road. See
</code>
http://www.ixora.com.au/newsletter/2001_05.htm#synonyms <code>
for a little background on that.