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
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;
select * from user_tab_privs where table_name='table_name';
select * from user_sys_privs;
v$session is a view.
The underlying table has an underscore:
grant select on sys.v_$session to prod;
The underlying table has an underscore:
grant select on sys.v_$session to prod;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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.
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.
ASKER
Thanks!!
2) grant select any dictionary to your_user;