As the 'SYS' user, I can run the following command:

SYS> grant SELECT on V_$SESSION to "SOMEUSER" with GRANT OPTION;

Then, as that user, I can select from V$SESSION:

SOMEUSER> select * from V$SESSION;
 .....  Expected output .....

However, even though the user was given the GRANT option, I cannot GRANT that privilege to anyone else:

SOMEUSER> grant SELECT on V_$SESSION to "OTHERUSER" with GRANT OPTION;
grant select on V_$SESSION to "OTHERUSER"
                *
ERROR at line 1:
ORA-00942: table or view does not exist

Strangely though, I can do this:

SOMEUSER> grant select on V$SESSION to "OTHERUSER";

Grant succeeded.

Why do I have to specify V_$SESSION when running as the SYS user, but when running as a normal user, I have to specify V$SESSION?

CodePudding user response：

V_$SESSION is a view, V$SESSION is a public synonym for that view.

select object_type, object_name from all_objects where object_name in ('V$SESSION', 'V_$SESSION')

select * from all_synonyms where synonym_name = 'V$SESSION'

db<>fiddle

When you do

grant select on V$SESSION to "OTHERUSER";

the synonym is apparently being expanded.

When you do

grant SELECT on V_$SESSION to "OTHERUSER" with GRANT OPTION;

you are trying to grant the privilege directly on the view - but on an object with that name in your schema, and no such object exists under SOMEUSER.

It would work if you did:

grant SELECT on SYS.V_$SESSION to "OTHERUSER" with GRANT OPTION;

CodePudding user response：

The object is sys.v_$session. If you are logged in as someuser, v_$session would be resolved in the current schema, i.e. someuser.v_$session. That object doesn't exist, hence the error

grant select on sys.v_$session ...

should work.