Managing User Privledges with Roles

[rcherch]

I am having issues with some of my roles and I am really hoping for some good suggestions.

I have an Oracle 9iR2 database.

I have a reporting_role that has been granted CONNECT and all table and view objects of prod_instance. When I create the report_user and grant reporting_role to report_user, it cannot create views referencing the prod_instance.

However, if you select the object it works.

My main goal is to manage my user with roles and not having to grant anything directly to the specific user_id.

Thanks for your help in advance.

[MMCP]

Have the reporting_role permissions to CREATE VIEW or ANY VIEW ?. As i can see, you only give it the CONNECT permission.
Logically,you can make selects to the tables and views belonging to prod_instance, because prod_instance gave those permissions to reporting_role.

Give the VIEW or ANY VIEW privilege to reporting role and see results.

HTH