Inherited Access Rights on single Objects

JB
Contributor

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

1 ACCEPTED SOLUTION

ADoerr
SQL-Fighter

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.

 

 

 

View solution in original post

4 REPLIES 4

PeterK
Xpert

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
SQL-Fighter

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
SQL-Fighter

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.

 

 

 

View solution in original post

JB
Contributor

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 🙂