Oracle Database Discussions

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Hello all.

I've got a problem inserting into a view that is based on a table residing in another schema.
Its a permission problem but I can't see what permission I need to grant - I don't want to give the user too much 'power'...

Here is my test case -

-- At this point I am connected to the Oracle instance as SYSDBA

create user a identified by a default tablespace users temporary tablespace temp;

-- give the user some basic permissions -
grant connect, resource, select any table to a;

create user b identified by b default tablespace users temporary tablespace temp;

grant connect, resource, select any table to b;

create user c identified by c default tablespace users temporary tablespace temp;

grant
connect,
resource,
select any table,
insert any table,
update any table,
delete any table
to c;

create table a.thetable (a_col1 nvarchar2(10), a_col2 nvarchar2(10));

create view b.theview (b_col1) as select a_col1 from a.thetable;

-- I now re-connect as user c
-- Test that we can see/select the table and view OK

select * from a.thetable

A_COL1 A_COL2
---------- ----------
0 rows selected

select * from b.theview

B_COL1
----------
0 rows selected

-- OK, so far so good.
-- Insert a row directly into the table -
insert into a.thetable (a_col1) values ('x');
1 rows inserted

-- insert a row via the view -
insert into b.theview (b_col1) values ('x');

Error starting at line 7 in command:
insert into b.theview (b_col1) values ('x')
Error at Command Line:7 Column:15
Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.
-- ???

If I try to insert into the view as SYSDBA I get no error.

So the question is, is why do I get the view insert error when connected as user c ?
As far as I can see, user c has all permissions against the underlying table and can insert directly?
What permission am I missing?

P.S. I don't personally like inserting via a view - but my hands are tied...

thanks for any help on this.

Andy

Edited by: hallam on 07-Jun-2011 03:41