1

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.

  1. the roles assigned are same and privileges for that roles are also same.
  2. usr_tab_privs are same
  3. usr_sys_priv are same

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

  • What DB engine please? – gbn Dec 5 '14 at 7:28
  • @gbn sorry for that.. its oracle – Vishwanath gowda k Dec 5 '14 at 9:53
  • I assume that you already have compared the public synonyms and the synonyms of the CST02 user and the roles granted to roles (if there are such roles) and have not found any differences. You should try to create another user on the production system with the same privileges as CST2 and revoke successively the grants to find out what is responsible for accessing the view. How is the production replicated to dev? – miracle173 Dec 7 '14 at 5:42
  • @miracle I don not have permission on prod system to do as you suggested. – Vishwanath gowda k Dec 7 '14 at 5:50
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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