How to grant on v$ views


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.

Solutions:

  1. 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;

    Grant succeeded.

  2.  CREATE ROLE SELECT_VDOLLAR_ROLE;

BEGIN
FOR x IN ( SELECT object_name
FROM dba_objects
WHERE object_type = ‘VIEW’
AND object_name LIKE ‘V\_$%’ ESCAPE ‘\’ )
LOOP
EXECUTE IMMEDIATE ‘grant select on ‘ || x.object_name || ‘ to SELECT_VDOLLAR_ROLE’;
END LOOP;
END;
/

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.

References: https://community.oracle.com/thread/1083883?tstart=0

https://coskan.wordpress.com

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s