QL> grant select on v$session to hr;
grant select on v$session to hr
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
I wonder why i couldn’t give select privilage to a v$ view.
- the problem is caused because of trying to give select privilage on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.If you want to give permission to a V$ view you must give it like below
SQL> grant select on v_$session to hr;
- CREATE ROLE SELECT_VDOLLAR_ROLE;
FOR x IN ( SELECT object_name
WHERE object_type = ‘VIEW’
AND object_name LIKE ‘V\_$%’ ESCAPE ‘\’ )
EXECUTE IMMEDIATE ‘grant select on ‘ || x.object_name || ‘ to SELECT_VDOLLAR_ROLE’;
3. “select any dictionary” also allow for users getting information from dynamic perfomance views.
This mean is if user has “select any dictionary” then this user can query both datadictionary and dynamic perfomance views.Shortly “select any dictionary” , provides users with SELECT access to any object in the SYS schema without giving them DBA privileges.