An Oracle blog – Amardeep Sidhu

March 3, 2008

Missing grants

Filed under: PL/SQL,SQL,Troubleshooting — Sidhu @ 8:37 pm
Tags: , ,

Today one of my colleague was working on a simple PL/SQL procedure. Based on some logic it was returning count(*) from all_tab_columns for few tables. It gave count incorrectly for one table out of around fifty in total. He just hard coded the table name and ran it but again it showed count as zero.

Then he took the code out of procedure and wrote it in DECLARE, BEGIN, END and after running it showed the correct count. But ran as database procedure it always shows incorrectly.

Finally just as hit and trial, he gave SELECT on the TABLE to database user [Table was in different schema], used to run the procedure and everything was ok. Isn’t it bit stupid ๐Ÿ™‚

Update: Well, it happens for a reason. Nigel Thomas pointed out in the comment. The reason is that privileges granted to a role are not seen from PL/SQL stored procedures. You need to give direct grant to the user for this or another method is to define the procedure or package with invoker rights.

Thanks Nigel ๐Ÿ™‚


  1. Well known feature: privileges granted to a role are not normally seen from PL/SQL stored procs, which execute as if you had SET ROLE NONE – see Workarounds are:

    – grant privileges directly to the user (what a pain)

    – define the procedure/package with invoker’s rights – see eg

    Regards Nigel

    Comment by Nigel Thomas — March 4, 2008 @ 2:57 pm | Reply

  2. Thanks Nigel

    Interesting !!!

    I have read about invoker rights but never thought in this direction…something new ๐Ÿ™‚

    Also adding your blog to my list ๐Ÿ™‚


    Comment by Sidhu — March 4, 2008 @ 8:13 pm | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Theme: Rubric. Get a free blog at

%d bloggers like this: