Inherited Access Rights on single Objects

[JB]

Hey there,

maybe I don't see the wood for the trees, but I did not figure out how to solve this.

Background: There is a table in schema A and a view in schema B. The owner of schema B has been granted SELECT privs on the table in schema A. So far, so good.
The view in schema B shows a subset of the table in schema A.
Now I want to create another view in schema C. The view should show a subset of the view in schema B. But I got stuck at granting SELECT privs to owner of schema C:

[42500] insufficient privileges: SELECT on table TEST_TABLE must be grantable for PRIV_TEST

In this case PRIV_TEST is the owner of schema B. I need some equivalent to admin option for object privileges. Granting SELECT privileges for complete schemas would not be an option, but of course would work.

I hope I didn't explain it too complicated 😄

Thanks,
Jörg

[PeterK]

Hi Jörg,

I'm unable to reproduce the error so I'm probably misunderstanding the situation. Is this what you are trying to do?

create schema sA;
create table sA.tA (id int);
insert into sA.tA values 1,2,3;

create schema sB;
create role rB;
alter schema sB change owner rB;
grant select on sA.tA to rB;
create view sB.vB as select id from sA.tA where id > 1;

create schema sC;
create role rC;
alter schema sC change owner rC;
grant select on sB.vB to rC;
create view sC.vC as select id from sB.vB where id > 2;

Regards,

Peter

[ADoerr]

You have to create the views with the credentials of the different users.

So you are able to reproduce it.

create schema schema_a;
create schema schema_b;
create schema schema_c;


create user user_a identified by "user_a";
create user user_b identified by "user_b";
create user user_c identified by "user_c";

grant create session to user_a;
grant create session to user_b;
grant create session to user_c;

grant impersonation on sys to user_a;
grant impersonation on sys to user_b;
grant impersonation on sys to user_c;

grant create table to user_a;
grant create view to user_b;
grant create view to user_c;

alter schema schema_a change owner user_a; 
alter schema schema_b change owner user_b;
alter schema schema_c change owner user_c;


/*
 * schema a
 */

impersonate user_a;

select current_user;

create table schema_a.test_table
	(
	test varchar2(20)
	);
	
insert into schema_a.test_table values ('test A');
insert into schema_a.test_table values ('test B');
insert into schema_a.test_table values ('test C');

select * from schema_a.test_table;

impersonate sys;

/*
 * schema b
 */

impersonate user_b;

--does not work in 1st try as select grant is missing
create view schema_b.test_view_b as select * from schema_a.test_table;

impersonate sys;
impersonate user_a;

grant select on schema_a.test_table to user_b;

impersonate sys;
impersonate user_b;

--works as select was granted
create view schema_b.test_view_b as select * from schema_a.test_table;

select * from schema_b.test_view_b;

--error
grant select on schema_b.test_view_b to user_c;

impersonate sys;

[ADoerr · Accepted solution]

Hi 🙂 

For such a case, there I know just 2 options (which you will not really like):

1) PRIV_TEST has to be owner of the table in schema A

2) PRIV_TEST get the system privilege GRANT ANY OBJECT PRIVILEGE. But so it's also possible to grant e.g. Insert privilege on the table to any user.

So based on your use-case, it might be the better option to re-work you permission-concept, so that it's not needed grant

select privileges on the base table.

Another dirty workaround, if you are creating all views via SQL scripts:

1) Grant GRANT ANY OBJECT PRIVILEGE to the user PRIV Test

2) Grant select to the view for owner of schema C

3) Revoke  GRANT ANY OBJECT PRIVILEGE

The owner of schema C has still select privilege on the base table, as it was granted while PRIV TEST got the system privilege temporary.

[JB]

Hi Peter, hi ADoerr,

thanks for your answers. 
@PeterK Yes, that is exactly what I meant. But it is like @ADoerr said. The problem occurs when every schema / table / view is created and owned by different users. In fact, that is how it is implemented in our case.
It is right, the permission concept needs to be remodeled and I already started. But for now, the second workaround would work. Not pretty but should be a one (two tops) time thing, so I can create views for the business.

Thanks 🙂