Privilege errors when using execute immediate?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Privilege errors when using execute immediate?

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258

    Privilege errors when using execute immediate?

    Trying to create a materialsed view using execute immediate and comes up with the following error:
    QL> exec xxma_build_pace_views_pkg.build_pace_views;
    BEGIN xxma_build_pace_views_pkg.build_pace_views; END;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "PACE.XXMA_BUILD_PACE_VIEWS_PKG", line 30
    ORA-06512: at line 1

    I can create this matarialised view without using execute immediate, so I have all the privileges.
    I have create any table, create any view, drop any table, drop any view. I have even tried DBA privilege.
    The following is the procedure:

    PROCEDURE build_pace_views IS
    --
    v_sql VARCHAR2(10000);
    v_view_exist NUMBER;
    --
    BEGIN
    --
    --call procedure to drop all pace disco views
    drop_module_views('XXMA_DISCO_PACE%');
    drop_materialized_views('XXMA_DISCO_PACE%');
    --
    --create all pace disco views
    --
    --xxma_disco_pace_sctr_bgt_mv
    v_sql :=
    '
    CREATE MATERIALIZED VIEW xxma_disco_pace_sctr_bgt_mv
    REFRESH WITH ROWID
    AS
    SELECT TRUNC(pf.paceflgt_scheduled_date_time) scheduled_date,
    SUM(pf.paceflgt_no_of_passengers) pax_no
    FROM pace.paceflgt pf
    GROUP BY TRUNC(pf.paceflgt_scheduled_date_time)

    ';
    --
    EXECUTE IMMEDIATE v_sql;
    Thanks for any help.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    ... and you question is?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    My question is :

    Trying to create a materialsed view using execute immediate and comes up with the following error:
    QL> exec xxma_build_pace_views_pkg.build_pace_views;
    BEGIN xxma_build_pace_views_pkg.build_pace_views; END;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "PACE.XXMA_BUILD_PACE_VIEWS_PKG", line 30
    ORA-06512: at line 1
    What privileges do I need to create materialised view using 'execute immediate'?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    you have to grant the privs to the user that owns the package
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    The stored procedures are not able to use privileges granted via roles, so if that's the case, grant privileges directly to the user or define the procedure with invoker rights

  6. #6
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    I have granted privileges directly to the owner of the package, who is also the owner of the table, that I think it needs, but it still doesn't work. What privilege am I missing?
    the list of privileges are as follows:
    'create any procedure', 'create any sequence','create any table','create any view','create table','drop any table','drop any view','execute any procedure','select any sequence','select any table'.
    Thanks.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    ...and you are trying to create a Materialized View, aren't you?

    Where is the CREATE MATERIALIZED VIEW priv? can't see it in your list.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    I have fixed it. I finally resorted to using invoker rights.
    Thanks for all your help. Rgds. Sheryl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width