I am learning Oracle and have to do roles. I created an c##admin
from a sys, granted him all grants. Then I created a c##manager_role
and c##manager
. I granted this role to c##manager
. After that I started to grant privileges to c##manager_role
. In addition I granted the following:
CREATE VIEW YES YES NO
CREATE ANY VIEW NO NO NO
DROP ANY VIEW YES YES NO
Above output from:
select * from role_sys_privs where ROLE = 'C##MANAGER_ROLE' order by 1;
Now, I can't understand, why CREATE ANY VIEW
has 3 "NO" and why I can't create any view from c##manager
.
From c##admin:
grant CREATE ANY VIEW TO c##manager_role with admin option CONTAINER=all;
From c##manager:
create view friends_v4 as select t1.nickname as "USER", t2.nickname as "FRIEND" from c##admin.clients t1, c##admin.clients t2, c##admin.friends where (t1.id = c##admin.friends.user_id and t2.id = c##admin.friends.friend_id);
Error:
Error report –
ORA-01031: привилегий недостаточно
01031. 00000 – "insufficient privileges"*Cause: An attempt was made to perform a database operation without
the necessary privileges.*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
All tables noted exist.
Best Answer
Being able to create a view is not [quite] enough.
The Owner [account] of the finished View must have read privileges on the Tables used by that view. These privileges cannot be conferred through any Role.
Also, why are you working with Common users here (starting "C##")?
I would only expect to see these in the Container database, not any of the Pluggable ones that live within it (which is where I would expect to see all Application Tables and Data).