grant SELECT access to v$session to other users

user_7661910 0
Viewed 22747

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"
oracle11gr2 oracle11g oracle
2 Answers

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;
user_35157090

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
user_19878460
Related