I have an application script to run against a database as the schema owner. The script creates a number of views against the data dictionary tables. This has worked on our production database but when I set up a test database it does not work. The script reports an ORA-942 against SYS.COL$ (and a number of the other SYS tables). When I try to access that system table as the schema owner I get data back. Why can't I do a CREATE VIEW against it?
I've looked in the documentation and can't find what privilege I need to have, I have also checked the owner's privileges on the production database and I have all the same roles. I've even run CATALOG.SQL as the owner. Still no luck. Has anyone run into this?
That did the trick. There was no PL/SQL in the script but it was trying to create views on SYS.COL$ and other similar tables. When I explicitly granted SELECT on those tables with GRANT OPTION, I could create the views and GRANT public access to them.