a month ago - last edited a month ago
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
Solved! Go to Solution.
a month ago - last edited a month ago
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.
a month ago
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
4 weeks ago
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;
a month ago - last edited a month ago
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.
4 weeks ago
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 🙂
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In