RE: Permissions in PL/SQL

From: <>
Date: Thu, 9 Apr 2009 15:10:40 -0400
Message-ID: <>

But you can grant select on the 'table' and then it should work. All X tables are X_$ with synonyms X$.  

SYS AS SYSDBA _at_ pegprod> select * from dba_objects where object_name =

OWNER                          OBJECT_NAME

------------------------------ ------------------------------
------------------------------ ---------- -

PUBLIC                         X$KTFBUE


SYS AS SYSDBA _at_ pegprod> select * from dba_synonyms where synonym_name =

OWNER                          SYNONYM_NAME
TABLE_OWNER                    TABLE_NAME  

------------------------------ ------------------------------
------------------------------ ------------

PUBLIC                         X$KTFBUE                       SYS
X_$KTFBUE   SYS AS SYSDBA _at_ pegprod> grant select on x_$ktfbue to dbmon;  

Grant succeeded.  

DBMON _at_ pegprod> select count(*) from x$ktfbue;  



Joel Patterson
Database Administrator
904 727-2546

[] On Behalf Of Jared Still Sent: Thursday, April 09, 2009 1:55 PM
Subject: Re: Permissions in PL/SQL  

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

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

Received on Thu Apr 09 2009 - 14:10:40 CDT

Original text of this message