RE: Permissions in PL/SQL

From: <Joel.Patterson_at_crowley.com>
Date: Thu, 9 Apr 2009 15:10:40 -0400
Message-ID: <0684DA55864E404F8AD2E2EBDFD557DA029872CE_at_JAXMSG01.crowley.com>

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 =
'X$KTFBUE';
 

OWNER                          OBJECT_NAME
SUBOBJECT_NAME                  OBJECT_ID D

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

PUBLIC                         X$KTFBUE

49043  

SYS AS SYSDBA _at_ pegprod> select * from dba_synonyms where synonym_name =
'X$KTFBUE';
 

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;  

  COUNT(*)

---

     75711  

Joel Patterson
Database Administrator
904 727-2546

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Thursday, April 09, 2009 1:55 PM
To: HANDM_at_polaroid.com
Cc: oracle-l_at_freelists.org
Subject: Re: Permissions in PL/SQL  

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 - 14:10:40 CDT