V$TEMPFILE view in a procedure gives error even having DBA role
Posted by Kamran Agayev A. on February 1st, 2011
An interesting issue I’ve seen today in OTN forum, where OP was asking why it is not possible to query V$TEMPFILE view in a procedure when he was able to query it outside a procedure. I’ve made a little demonstration and would like to get your opinion as well
[code]
SQL> grant dba to us1 identified by us1;
Grant succeeded.
SQL> conn us1/us1
Connected.
SQL> desc v$tempfile;
Name Null? Type
—————————————– ——– —————————-
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
SQL> create or replace procedure test
2 is
3 v_id number;
4 begin
5 select file# into v_id from v$tempfile;
6 end;
7 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE TEST:
LINE/COL ERROR
——– —————————————————————–
5/1 PL/SQL: SQL Statement ignored
5/29 PL/SQL: ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL>
SQL> grant select on v$tempfile to us1;
grant select on v$tempfile to us1
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
SQL> grant select on v_$tempfile to us1;
Grant succeeded.
SQL> conn us1/us1
Connected.
SQL>
SQL> create or replace procedure test
2 is
3 v_id number;
4 begin
5 select file# into v_id from sys.v_$tempfile;
6 end;
7 /
Procedure created.
SQL> show error
No errors.
SQL>
[/code]
The reason why I got an error ORA-02030 and why I’ve granted SELECT privilege to not V$TEMPFILE, but V_$TEMPFILE, is because V$TEMPFILE is a synonym created on the base view V_$TEMPFILE. Here’s the proof:
[code]
SQL> select object_type from dba_objects where object_NAME=’V$TEMPFILE’;
OBJECT_TYPE
——————-
SYNONYM
SQL> select table_name from dba_synonyms where synonym_name=’V$TEMPFILE’;
TABLE_NAME
——————————
V_$TEMPFILE
SQL>
[/code]
For more information, check the following MOS:
February 2nd, 2011 at 9:03 am
Its because that from within the stored programs, the privs that have come via a role doesn’t work. This is what you have done as well by granting the user DBA role. So when the user tried to access the object within the stored programs, it didn’t work.
Aman….
February 2nd, 2011 at 3:02 pm
so u mean that outside procedure whatever privs u have does not work inside proc. So can we grant that dba role or privs to the user inside the proc and then use the select …. does that work?
February 2nd, 2011 at 3:05 pm
You can enable the DBA role implicitily in the procedure