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

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    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 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    "CORE 10.2.0.3.0 Production"
    TNS for Solaris: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    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

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation