Re: Permissions in PL/SQL

 On Thu, Apr 9, 2009 at 7:02 AM, Hand, Michael T <HANDM@xxxxxxxxxxxx> 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:

As sysdba:

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: [16206], [4294950952], [],
[], [], [], [], []
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.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Other related posts: