grant select on sysman view – Laurent Schneider

Quite often you select from the sysman schema, let’s say

select target_name from sysman.mgmt$target;

To do so, you better be sys.. because SYS can see everything !

Let’s see what’s missing with a standard user

connect  sys/***@oem01 as sysdba;
create user user1 identified by ***;
grant create session to user1;
conn user1/***@oem01;
select count(*) from sysman.mgmt$target;
SQL Error: ORA-00942: table or view does not exist

Fair enough, we don’t have select

connect  sys/***@oem01 as sysdba;
grant create session to user1;
grant select on sysman.mgmt$target to user1;
conn user1/***@oem01;
select count(*) from sysman.mgmt$target;
  COUNT(*)
----------
         0

This is more than enough to confuse the innocent ! the query returns 0 row! The witchcraft behind the scene is called virtual private database (VPD).

One lazy dba would grant you exempt access policy

connect  sys/***@oem01 as sysdba;
drop user user1 cascade;
create user user1 identified by ***;
grant create session, exempt access policy to user1;
conn user1/***@oem01;
select count(*) from sysman.mgmt$target;
  COUNT(*)
----------
        72

But this is hurting the need to know principle. I really want to give only select on that view.

Creating a view on the view doesn’t do the trick

connect  sys/***@oem01 as sysdba;
create or replace view sysman.v as 
  select * from sysman.mgmt$target;
grant create session to user1;
grant select on sysman.v to user1;
conn user1/***@oem01;
select count(*) from sysman.v ;
  COUNT(*)
----------
         0

Let’s try to add PL/SQL in the view :

connect  sys/***@oem01 as sysdba;
create or replace function sysman.f 
  return sysman.MGMT_TARGET_ARRAY 
is 
  r MGMT_TARGET_ARRAY; 
begin 
  select sysman.MGMT_TARGET_OBJ(TARGET_NAME,TARGET_TYPE) 
  bulk collect into r 
  from sysman.mgmt$target; 
  return r; 
end;
/
create or replace view sysman.v as select * from table(f);
grant select on sysman.v to user1;
conn user1/yF_5lOoxNp_g03Z#NMFM@oems1d;
select count(*) from sysman.v;
  COUNT(*)
----------
         0

Well, it doesn’t help yet, but now one more magic trick.

create or replace function sysman.f 
return sysman.MGMT_TARGET_ARRAY is 
  pragma autonomous_transaction;
  r MGMT_TARGET_ARRAY; 
begin 
  sysman.setEMUserContext(null,null,disable_vpd_policies=>1);
  select sysman.MGMT_TARGET_OBJ(TARGET_NAME,TARGET_TYPE) 
  bulk collect into r 
  from sysman.mgmt$target; 
  return r; 
end;
/
create or replace view sysman.v as select * from table(f);
grant select on sysman.v to user1;
conn user1/***@oem01;
select count(*) from sysman.v;
  COUNT(*)
----------
        72