Re: Permissions in PL/SQL
Date: Thu, 9 Apr 2009 10:55:22 -0700
On Thu, Apr 9, 2009 at 7:02 AM, Hand, Michael T <HANDM_at_polaroid.com> wrote:
> ... SYS-owned objects and X$KTFBUE. The original block was run from SYS,
Here's the problem with fixed tables - you can't grant privileges on them.
SQL> grant select on X$KTFBUE to scott;
grant select on X$KTFBUE to scott
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
Here's what this means:
> oerr ora 2030
02030, 00000, "can only select from fixed tables/views"
// *Cause: An attempt is being made to perform an operation other than // a retrieval from a fixed table/view. // *Action: You may only select rows from fixed tables/views.
A workaround that is sometimes used is something like this:
SQL> create view X_$KTFBUE as select * from X$KTFBUE; SQL> create public synonym X$KTFBUE for X_$KTFBUE; SQL> grant select on X_$KTFBUE to scott;
While that works, it does have drawbacks.
Most databases are eventually upgraded.
Say you upgrade the database to 10.2.0.4
If sufficient changes are made to the X$KTFBUE table, or if it is dropped altogether, subsequent attempts to drop or compile the X_$KTFBUE view will fail, resulting in:
SQL> alter view X_$KCBCBH compile;
alter view X_$KCBCBH compile
ERROR at line 1:
ORA-00600: internal error code, arguments: , , , , , , , 
ORA-00942: table or view does not exist
I just ran this on a database with one of those views, and now I am getting paged...
This object cannot be recompiled, or dropped by normal means.
Removing it would require opening an SR to get a Support sanctioned data dictionary hack.
To make a long story short, it's probably better to write your procedure using an existing view that you can grant access to.
Certifiable Oracle DBA and Part Time Perl Evangelist