Why SYSTEM cannot grant privs on tables owned by any schema

[egavish]

Why does my SYSTEM account does not have sufficient privileges to grant SELECT on "xxx.table1" for user to "UUU" ???


Thanks

egavish@snet.net

[jmodic]

Originally posted by egavish
Why does my SYSTEM account does not have sufficient privileges to grant SELECT on "xxx.table1" for user to "UUU" ???

Because your SYSTEM account is obviously not created in Oracle9i Release2, I would say. 9.2 is the first release in which DBA accounts are able to grant privileges on objects they don't own.

In any earlier release, you'll be able to grant privileges on objects you don't own only if the object owner has granted you that same privilege WITH GRANT OPTION.

[egavish]

Great. So user XXX needs to grant access (with grant or admin) on table1 to SYSTEM on each additional table? Or is there a way to do do it for all tables at once?


Thanks again

[raghud]

What version are you using. Try "alter any table" or "select any table" roles.

[raghud]

or try "EXP_FULL_DATABASE" role to system . Then any other user should get select any table privilege.

[pando]

raghud, no matter what you do a DBA Account cannot grant privileges on objects he does NOT own to others until Oracle 9.2 unless he has been previously granted privs with grant option as jmodic already pointed out

[egavish]

OK. But would 'select any' allow user UUU see all tables including the SYSTEM and SYS?

[raghud]

got it

[raghud]

Using dba account we can't grant access to others if the obejct is owned by other user. But if the dba account got "EXP_FULL_DATABASE" role dba account can select from any table. Correct me if I am wrong.

[raghud]

egavish, You question can any body see objects owned by sys and system. You mean catalog tables and views . You need to assign "select catalog role" to a user. Other wise owner should grant privileges to other account no matter it is sys or system or UUU.