Views with Granted Select/Insert Permissions

I'm working on an Oracle9i server in college.

I have granted my friend select and insert permissions on a view 'v1'.

He is able to select and insert on the view,but apparently the view maintains a separate copy for his userid when he inserts a row.

ie. when I say:

select * from v1;

I'm not able to see the row he inserted into view 'v1'.Is this the default behaviour?Shouldn't I be able to see the row what he inserted because its still owned by me?

I'm a newbie so I think I'm not aware of some of the concepts with regards to views and granting permissions.

Solution

If the other user is actually inserting a row that meets the criteria of the view and the other user is committing his transaction, then you would be able to see the row in your session.

Are you certain that the other user is actually using the same object that you are? Is there any chance that the V1 he or she is referring to is different than the V1 that you are referring to? Are you both using fully qualified object names (i.e. SELECT * FROM Pavitar.v1)
Are you certain that the other user is actually committing his transaction after doing the INSERT?
Are you certain that the row that is being inserted meets the criteria of the view?

For example, if I create a view EMP_VIEW that returns all data from the EMP table where the DEPTNO is 10, I can insert a row into the EMP table via the view that does not have a DEPTNO of 10.

SQL> create or replace view emp_view
  2  as
  3  select *
  4    from emp
  5   where deptno=10;

View created.

SQL> insert into emp_view( empno, ename, job )
  2    values( 7623, 'PAV', 'Dev' );

1 row created.

So I won't see the row if I query the view

SQL> select empno, ename, job
  2    from emp_view;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7782 CLARK      MANAGER
      7839 KING       PRESIDENT
      7934 MILLER     CLERK

But I will see it in the underlying table

  1  select empno, ename, job
  2*   from emp
SQL> /

     EMPNO ENAME      JOB
---------- ---------- ---------
      7623 PAV        Dev
      7369 smith      CLERK
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN
      7566 JONES      MANAGER
      7654 MARTIN     SALESMAN
      7698 BLAKE      MANAGER
      7782 CLARK      MANAGER
      7788 SCOTT      ANALYST
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN
      7876 ADAMS      CLERK
      7900 SM0        CLERK
      7902 FORD       ANALYST
      7934 MILLER     CLERK
      1234 FOO

16 rows selected.