CODEWITHSUNDEEP
oracle-databaseoracle11goracle11gr2
en Lopes
en Lopes

Reputation: 2123

grant SELECT access to v$session to other users

I want to grant SELECT access to v$session to other users in an Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

but when I run this query:

SELECT owner, object_type FROM dba_objects WHERE object_name = 'V$SESSION';

I got this error:

00942. 00000 -  "table or view does not exist"

Upvotes: 6

Views: 34956

Answers (2)

Steef
Steef

Reputation: 615

We also needed a regular user without access to v$session to cleanup sessions. A function will execute with the privileges of the owning schema, so if you create a function as the user having access to V$SESSION you can execute it from the user not having the required privilege.

For example, IFH_OWNER has access to v$session, user id854812 doesn't:

As id854812:

select count(*) from v$session
ORA-00942: table or view does not exist

As IFH_OWNER:

select count(*) from v$session
56

create or replace function getSessionCount return int
as
  vCnt int;
begin
  select count(*) into vCnt from v$session;
  return( vCnt);
end;

select getSessionCount from dual;
56

grant execute on getSessionCount to id854812;

As id854812:

select ifh_owner.getSessionCount from dual;
56

Upvotes: 1

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilege on a synonym. If you want to give permission to a V$ view you must give it like below 

SQL> grant select on v_$session to hr;

Upvotes: 11

Related Questions