Different result set of view in different sessions: is it reliable?

From: Zoe <ZoeTMa_at_gmail.com>
Date: 17 Jan 2006 02:48:56 -0800
Message-ID: <1137494936.264050.262920@g44g2000cwa.googlegroups.com>

Hello,

I have a problem concerning views.

Problem Definition: In an application, each application user should have access to specific departments. All application users connect to the database as scott; having a database user per actual application user is not an option due to specific limitations. The application already exists, and references dept.

Example Description: The example quoted below is a small test. Table dept is renamed to dept_main and the new view will be named dept, as we don't want to change all application references to dept. In table user_depts we link departments to users (this is the application user id, not the database user id) The table user_session will be updated by the application each time a users logs in the applcation. The view dept contains only the departments which should be accessible to each user according to their application user id.

Question: In the test it seems to be working, but is it actually reliable to use a view with contents which can be different in different sessions of the same database user?

Thanks in advance,

Zoe

EXAMPLE WITH USER SCOTT

---

SQL> create table user_depts (
  2 user_id varchar2(15) not null,
  3 deptno number(2) not null )
  4 /

Table created.

SQL> create table user_session (
  2 user_id varchar2(15) not null,
  3 session_id number not null )
  4 /

Table created.

SQL> rename dept to dept_main;

Table renamed.

SQL> create view dept as
  2 select dept_main.deptno, dept_main.dname, dept_main.loc   3 from dept_main, user_depts, user_session   4 where user_session.session_id = userenv('sessionid')   5 and user_session.user_id = user_depts.user_id   6 and user_depts.deptno = dept_main.deptno;

View created.

USER1, SESSION 12024

---

SQL> select userenv('sessionid') from dual;

USERENV('SESSIONID')

---

               12024

SQL> insert into user_session values (
  2 'user1', userenv('sessionid') );

1 row created.

SQL> commit;

Commit complete.

USER2, SESSION 12031

---

SQL> select userenv('sessionid') from dual;

USERENV('SESSIONID')

---

               12031

SQL> insert into user_session values (
  2 'user2', userenv('sessionid') );

1 row created.

SQL> commit;

Commit complete.

SQL> insert into user_depts values ( 'user1', 10 );

1 row created.

SQL> insert into user_depts values ( 'user1', 20 );

1 row created.

SQL> insert into user_depts values ( 'user2', 30 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept;

    DEPTNO DNAME LOC
---------- -------------- -------------

        30 SALES CHICAGO USER1, SESSION 12024

---

SQL> select * from dept;

    DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

Received on Tue Jan 17 2006 - 04:48:56 CST