How to I grant users access to v$ views on Oracle 10? (NETIQKB73329)

  • 7773329
  • 17-Jan-2012
  • 17-Jan-2012

Environment

Oracle Database

Oracle 10

NetIQ AppManager for UNIX

Situation

To monitor Oracle with the AppManager UNIX agent, I have to give a user access to the v$xxxx views. As having select all tables rights no longer gives access to views in Oracle 10, how do I accomplish this?

Resolution

DROP   ROLE SELECT_VDOLLAR_ROLE;
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;
/

now grant SELECT_VDOLLAR_ROLE to whomever you wish to be able to look at v$ views.

Cause

It seems the Oracle database has recently made changes to the scope of the select from all tables privledge that impacts giving users rights to v$ views.

Additional Information

Formerly known as NETIQKB73329

Noted at https://forums.oracle.com/forums/thread.jspa?threadID=1081883

Please keep in mind support for this KB is best effortas this falls under the scope of database administration.