Grant roles on ORACLE 9.2.0???

[smallpin]

Hi All,
I was wondering if there are changes made to granting roles on ORACLE 9.2.0? I imported a user and his schema into ORACLE 9.2.0 with the option grants = Y after having imported it from ORACLE 9.0.1 with the same option grants = Y.

However when I try to select from any of the tables under that schema I get the error "ORA-00942: table of view does not exist. I have granted select on all the tables in that user's schema and still can select from it unless I put the schema name then the table name.

Any Ideas? Thanks in advance.

Mee

[pando]

that's synonym problem not grants

[beppo]

I have run in to similar problems in 8i also and sometimes the solution has been to grant select on every single table, one by one. This has been an issue when installing third party Oracle tools where some of the objects are owned by sys.

I don't know if it is of any help?

/Beppo

[pando]

most probably is you did an user import, an user import only import GRANTS on owner´s tables not SYS

[jmodic]

Originally posted by smallpin
... select from it unless I put the schema name then the table name.

As pando said, it has nothing to do with grants and roles, its the SYNONYMS isue. In your 9.0.1 you probably have public synonyms for those tables, but public synonyms were not imported into 9.2 database because you did user mode import.

You must create synonyms for those tables if you don't want to use schema name prefixes.

[smallpin]

Thanks for you help guys. I have created the sysnonyms for the tables and everything works fine now. However I will like to know how I can get around the problem of exp/imp a user and still exp/imp the the synonyms? Do I have to exp/imp the sys user in order to get the synonyms? Thanks in advance.

Dee.

[pando]

synonyms has to be created under schema who is going to QUERY the tables

in your case you are not doing that, you are not importing anything to the user who is querying

imagine

userA is the one who is going to query userB's objects

you import into userB

if userA wants to query userB's tables he has to create private synonyms

unless you import into userA private synonym definitions from other database