1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

compile package - select V$MVREFRESH without user's rights

Discussion in 'SQL PL/SQL' started by ecivgamer, Nov 17, 2014.

  1. ecivgamer

    ecivgamer Active Member

    Likes Received:
    Trophy Points:
    Hi all,

    My need is to compile package user1.pkg_1

    package body should have

    select column1 from V$MVREFRESH

    But user1 doesn't have rights to select from V$MVREFRESH (only via user role).

    I asked our dba

    grant select on V$MVREFRESH to FLATBED_DW;

    but he said that it is not good practice.

    What other ways we can use in order to compile package?

    Thanks ahead.

    Oracle Database 10g Enterprise Edition Release - 64bi
    PL/SQL Release - Production
    "CORE Production"
    TNS for Solaris: Version - Production
    NLSRTL Version - Production
  2. ocprep

    ocprep Forum Advisor

    Likes Received:
    Trophy Points:
    Orlando, Florida
    In order for a stored procedure to access a database table, it must have all require privileges (SELECT/DELETE/etc) granted to the schems that the procedure will be using the privileges of. There isn't a way around that. Anonymous PL/SQL blocks can make use of privileges granted via roles, but stored PL/SQL cannot.

    Theoretically you could write a function that performed the SELECT you want from that table and returned the results. The DBA could compile that function in a schema with SELECT rights granted directly to V$MVREFESH. The DBA would then grant EXECUTE privileges on the function to FLATBED_DW. I don't know if what you are trying will lend itself well to that workaround, though.
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Likes Received:
    Trophy Points:
    Russian Federation