Link to home
Start Free TrialLog in
Avatar of junaid_baig1
junaid_baig1

asked on

How to grant select on v$session to any user !


Hello Experts

I've just imported the production database into another database. After importing my some procedure become invalid. I checked then I saw it was the problem due to some system views like v$session. As there was no grants. Now I've 2 queries.

1. Is grants on system views not imported with other objects, if we are doing full database import with grant=y.

2. How to grant select on system views like v$session to any user. Because I'm facing this problem in granting.
SVRMGR> connect internal
Connected.

SVRMGR> grant select on v$session to prod;
grant select on v$session to temp
*
ORA-02030: can only select from fixed tables/views
For temporally, for compiling the procedures, I've given this grant.
SVRMGR> grant select any table to prod;
Statement processed.



Thanks & regards
 Jdbaig
Avatar of Shaju Kumbalath
Shaju Kumbalath
Flag of India image

1) if u r using full import grants should import
2) grant select any dictionary to your_user;
Additionaly, if you want to see which permission is missing in your temp schema, use the following
select * from user_tab_privs where table_name='table_name';
select * from user_sys_privs;
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

v$session is a view.  

The underlying table has an underscore:

grant select on sys.v_$session to prod;
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
From SYS:
create view v_session as select * from v$session;
grant select on v_session to public;
create public synonym v_session for sys.v_Session;

Now any user should be easily able to query v_session, so they get v$session info
>>create view v_session as select * from v$session;

I would not recommend EVER creating personal objects in the SYS or SYSTEM schemas.

That said:  As I already posted:  v$session is a view.  What is the point of creating a view from a view?  I also believe there is a public synonym to v$session.

Create a new user and only grant them create session.  Connect as them and try: desc v$session and see what happens.
Avatar of junaid_baig1

ASKER

Thanks!!