0

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.

1 Answer 1

1

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.

grant read on friends to owner_of_friends_v4 ; 
grant read on clients to owner_of_friends_v4 ; 

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.