Grant Problems.

[pascal01]

I exported an 8.1.7 database and imported into a 10.2.0.3 database. I had no problem with the import except there were many invalid functions, packages, procedures...etc. Many of these objects were invalid because they were referencing objects owned by SYS.. (ie OBJ$, COL$ SYN....). I logged in as SYS and granted "SELECT ANY TABLE" to the schema owner. This did not resolve the problem. I had to manually grant all the SYS objects (i.e "GRANT select on SYS.OBJ$ to "SMSYSADMIN" to make this work. Why ?

[vnktummala]

grant SELECT_CATALOG_ROLE to the schema owner.

Thanks,

[gandolf989]

I had to manually grant all the SYS objects (i.e "GRANT select on SYS.OBJ$ to "SMSYSADMIN" to make this work. Why ?

In 9i access to the sys schema was locked down. To make a system secure you should operate under the principle of least privileges. i.e. giving schemas the minimum number of privileges to do what they need to do. So if you really need to look at system tables then do direct grants fro what you need. But be aware that for every direct grant you do the is a chance that something else will break the next time you upgrade to a newer version of Oracle. It would be better to use Oracle supplied packages rather than to write your own code against the data dictionary, and you should not manually change the data dictionary.

Also if you user user_tables, user_tab_columns, etc.. you should not need the direct grants that you would need if you went to the underlying sys tables.