Re: Permissions in PL/SQL

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 9 Apr 2009 10:55:22 -0700
Message-ID: <bf46380904091055o2382c3bagee82e49699217d12_at_mail.gmail.com>

 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:

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 09 2009 - 12:55:22 CDT