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

junaid_baig1 used Ask the Experts™

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

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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shaju KumbalathDeputy General Manager - IT

1) if u r using full import grants should import
2) grant select any dictionary to your_user;
Devinder Singh VirdiLead Oracle DBA Team

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;
Most Valuable Expert 2012
Distinguished Expert 2018

v$session is a view.  

The underlying table has an underscore:

grant select on sys.v_$session to prod;
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018
Forgot to comment on the import grants:

SYS needs to grant select on V$ views.  Exports don't capture what other users have granted to specific users. So, when you imported the user into the new database, the grants SYS had given to that user in the old database don't automatically transfer to the new user.

Think about it this way:
I'm a developer and you're a production DBA.  I want DBA access to production and you won't give it to me.

I develop a small app that needs the Fred user.
I grant SYSDBA to a user called Fred in my development database.  I export Fred and send you the file to import for my new app.  You create a production Fred with very limited rights and import my file.  Should I now have DBA access to production?

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
Most Valuable Expert 2012
Distinguished Expert 2018

>>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.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial