RE: Permissions in PL/SQL
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <jkstill@xxxxxxxxx>, <HANDM@xxxxxxxxxxxx>
- Date: Thu, 9 Apr 2009 15:10:40 -0400
But you can grant select on the 'table' and then it should work. All X
tables are X_$ with synonyms X$.
SYS AS SYSDBA @ 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 @ 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 @ pegprod> grant select on x_$ktfbue to dbmon;
Grant succeeded.
DBMON @ pegprod> select count(*) from x$ktfbue;
COUNT(*)
----------
75711
Joel Patterson
Database Administrator
904 727-2546
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Thursday, April 09, 2009 1:55 PM
To: HANDM@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: 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:
