Home » RDBMS Server » Security » PRIVILEGES FOR TABLE FUNCTION (Oracle Database 11.2.0.3)
PRIVILEGES FOR TABLE FUNCTION [message #639880] Mon, 20 July 2015 00:35 Go to next message
kirankumarpv
Messages: 1
Registered: July 2015
Location: Australia
Junior Member
Hi,

In Oracle 11.2.0.3 I have created a user and given select permissions on V$TIMER, V$ACTIVE_SESSION_HISTORY.

But, when I execute the below query (This query is generated from DBMS_SQLTUNE) it throws an error saying ORA-00942 table or view does not exist. Only objects that are used in the below query are

1. V$TIMER
2. V$ACTIVE_SESSION_HISTORY
3. TABLE function
4. GV$ Undocumented function (Oracle executes the query inside GV$ function on all the instances of the object).

Below is the query.

SELECT * FROM TABLE(GV$(CURSOR((SELECT USERENV('INSTANCE') INST_ID FROM
(SELECT * FROM ( SELECT (CASE WHEN :B24 > 1 THEN 0 ELSE 1 END) BUCKET_NUM FROM
(SELECT 1 C1, NULL C2 FROM V$TIMER UNION ALL SELECT 1 C1,1 C2 FROM V$TIMER) DUP,
(SELECT /*+ no_merge */ 1 C1,ASH000.WAIT_CLASS, ASH000.QC_INSTANCE_ID INSTANCE_ID FROM (SELECT ASH0.* FROM
(SELECT * FROM V$ACTIVE_SESSION_HISTORY ASH ) ASH0 ) ASH000 ) ASH00 ) ASH1
GROUP BY USERENV('INSTANCE'),ASH1.BUCKET_NUM) ASH2)))) GVTF ;


Is there any privileges that I need to give?

The same sql gets executed without any issues if i give "SELECT ANY DICTIONARY" privilege to the user but can't give those permissions to that user.

Can you help me?

Kiran
Re: PRIVILEGES FOR TABLE FUNCTION [message #639881 is a reply to message #639880] Mon, 20 July 2015 00:52 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: PRIVILEGES FOR TABLE FUNCTION [message #639882 is a reply to message #639880] Mon, 20 July 2015 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65199
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post the complete code and session execution so we can reproduce what you say.
Before, Please read How to use [code] tags and make your code easier to read.

Re: PRIVILEGES FOR TABLE FUNCTION [message #639915 is a reply to message #639880] Mon, 20 July 2015 05:46 Go to previous message
gazzag
Messages: 905
Registered: November 2010
Location: Bristol, UK
Senior Member
V$TIMER and V$ACTIVE_SESSION_HISTORY are synonyms to V_$TIMER and V_$ACTIVE_SESSION_HISTORY respectively.
Previous Topic: How to prohibit execute select for upate
Next Topic: Oracle User - Authentcation via an LDAP Server
Goto Forum:
  


Current Time: Mon Nov 20 22:36:21 CST 2017

Total time taken to generate the page: 0.01710 seconds