Database Administrators Asked by Vishwanath gowda k on November 16, 2021
We are using oracle 11g
I am replicating production db for dev environment.
I Have a user named CST2 and CST0 where CST0 contains all tables and view and public synonyms for the same. I have provided same set of privileges for CST2 in dev env as in production.
There is a view named Product(based on product_info and product_ainfo) in CST0 which can be accessed by CST2 in production(Did select). but doing the same in replicated dev env is giving the error insufficient privilege.
I checked following means from which the user can get privileges.
I cant find out how the CST2 in prod is able to access view ‘product’ in CST0 as it got no privilege through roles or tab_privs.
Am I missing any other way by which CST2 in prod got privilege which I do grant in dev.?
An issue I found on a similar problem was that the ‘first’ grant on the object had to be done ‘with grant option’.
Eg for the issue above, this would therefore mean:
GRANT SELECT ON <underlying table owner>.<table_name> TO CST0 WITH GRANT OPTION;
Then running
GRANT SELECT ON <schema>.<view_name> TO CST0;
should work.
Note this this ‘foundation’ grant will need to be repeated for each of the underlying table owner>. which forms part of the view definition.
Answered by Stelios on November 16, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP