Toad World® Forums

Use of Session Brower Disabled after Permissions Changes

I am having problems using the session browser after the DBA has made all these permissions changes.

Accessing Database --> Monitor --> Session Browser

Getting pop up message that says:

“Access to the following views required:
V$SESSION, V$PROCESS, V$SESS_IO, V$SESSION_WAIT, V$SESSION_EVENT, V$ACCESS, V$SESSSTAT, V$STATNAME, V$OPEN_CURSOR, V$SQL, V$LOCK, V$SESSION_LONGOPS, SYS.V_$TRANSACTION, SYS.V_$ROLLNAME, V$SQLTEXT_WITH_NEWLINES”

So we went through the motions to give access:

DBA suggested granting the following to a specific role:

GRANT SELECT ON V_$SESSION TO DEVELOPER_ROLE;
GRANT SELECT ON V_$PROCESS TO DEVELOPER_ROLE;
GRANT SELECT ON V_$SESS_IO TO DEVELOPER_ROLE;
GRANT SELECT ON V_$SESSION_WAIT TO DEVELOPER_ROLE;
GRANT SELECT ON V_$SESSION_EVENT TO DEVELOPER_ROLE;
GRANT SELECT ON V_$ACCESS TO DEVELOPER_ROLE;
GRANT SELECT ON V_$SESSTAT TO DEVELOPER_ROLE;
GRANT SELECT ON V_$STATNAME TO DEVELOPER_ROLE;
GRANT SELECT ON V_$OPEN_CURSOR TO DEVELOPER_ROLE;
GRANT SELECT ON V_$SQL TO DEVELOPER_ROLE;
GRANT SELECT ON V_$LOCK TO DEVELOPER_ROLE;
GRANT SELECT ON V_$SESSION_LONGOPS TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$TRANSACTION TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$ROLLNAME TO DEVELOPER_ROLE;
GRANT SELECT ON V_$SQLTEXT_WITH_NEWLINES TO DEVELOPER_ROLE;

My user was given this role; I verified this and now can see the data in the views. For Example, putting: V$SESSION in the editor window and hitting F4 on it shows all the information, so I have access to view it.

But when I try to go to the session browser, I still get the popup message saying I need access to those views. Why is this and how can I fix it? I can’t ask our DBA group about this because they don’t support Toad and actually don’t like it. So they will not provide me any help on this matter. I really use the session browser a lot and it worries me that I won’t be able to use it after these changes go into our Production environment.

Thanks in advance!

Database > Spool SQL to Screen

Open the window

Grab the SQL being sent and figure out what query/view is causing the issue.
It’s possible we’re missing one.

Grant select any dictionary, select any table, select_catalog_role to user
….

You don’t need all three of these – but usually any one of the three
(depending on oracle version) is what you need ……

I doubt a paranoid DBA would grant any of those to a developer, but that sure
would make it easier

Agreed – DBA should create a role called TOAD_DBA and grant just what they
are comfortable with and that Toad needs – and then manage users via that
role.

If you are connected to an 11g database, you also need to grant select on
v$session_connect_info.

Also, DBA_ROLLBACK_SEGS should be in that list but it is not.

The list has been updated in Toad 10.5.

If those two still don’t get it, please follow Jeff’s suggestion. If
you find one that is not in the message, let me know and I will update it.

-John

One warning in general about “ putting: V$SESSION in the editor window and
hitting F4 on it shows all the information ”

If you have SELECT_CATALOG_ROLE or something equivalent, you can see everything
about a table/view (colum n definitions, statistics, comments, etc.) but it
doesn ’ t give you the right to see the data in the table/view. If you
’ re in this situation, F4 will show you everything you could ask for,
until you get to the Data tab at which point you will be told you don ’ t
have access. So: when you ’ re using this sort of test, make sure you
check the Data tab.

And a gotcha about roles that we ’ ve run into: it ’ s possible to
grant Select on an object to a role, grant the role to a user, log on as that
user, and still not be able to Select from the object. Old hands, can you figure
it out from these clues?

.

.

.

Sometimes the role is not granted as a Default role to the user! See the column
user_roles.default_role If th is column says NO, the user must explicitly
activate the role after logging on before that session has the access the role
provides. Much easier in our situation is to make sure the role is a default
role: execute “ Alter user default role all ” whenever a
role is granted.

Nate Schroeder

US Seed & Trait Commercial IT - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167

314-694-2592

I asked the DBA to add these items but I haven’t heard back from him on if he will do it yet or not.

GRANT SELECT ANY DICTIONARY TO DEVELOPER_ROLE;
GRANT SELECT ANY TABLE TO DEVELOPER_ROLE;
GRANT SELECT_CATALOG_ROLE TO DEVELOPER_ROLE;

In the meantime, I did the spool. I got these results below. BTW this is a Oracle 10g db running on LINUX. My user cannot view data in GV$SESSION.

The DBA just complied with my request and it is working now. I really appreciate the help everyone! Thanks so much.

Julia

If you’re querying a RAC database, you need the GV version of all those
views, OR you can uncheck the “Use RAC Views” checkbox. You might
need to re-open the session browser after unchecking.

Ahhhh, the DEFAULT ROLE thing. That’s bitten me quite a few times.

And there’s a limit to the number of roles you can have setup for a user
at the DEFAULT level.

Hi Jeff,

I doubt a paranoid DBA would grant any of those to a
developer, but that sure would make it easier
Put me down as “paranoid” then. Nobody gets select any table on my
databases!

Consider the ability to look in, lets see, sys.link$, and finding out
passwords etc on other databases. I suspect 11g may encrypt/hash these
now, but …

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

A paranoid DBA wouldn’t allow for DB_LINKS with clear passwords either
though :wink:

Hi Jeff,

A paranoid DBA wouldn’t allow for DB_LINKS with clear
passwords either though :wink:

Not sure I follow Jeff. If I create a DB Link I have the option to leave
username and password blank which means that I connect to the “other”
database as the user owning the db link and with the same password. If I
change my password, I have to change the “other” database password as
well.

If I specify a username and password, Oracle stores it in plain text in
sys.link$, not me!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Right, I wouldn’t use DB_LINKS at ALL…or worst case scenario, leave
the user/pwd blank.

A better way to say this is that unless absolutely necessary (and only after
serous battle), many DBA’s simply avoid db links like the plague since it
can cause performance issues for statements (due to optimizer and which side
work gets done on depending on the code) and unforeseen or planned performance
hits from some other system. Yes there are times when you have no choice. But to
date I’ve been able to keep that to 2% or less – and damn happy I
did J

And restrict access to those links to a few select users / roles – and
audit their use to boot J

I just tried it - I have SELECT ANY TABLE on this instance (verified in
SESSION_PRIVS) [I didn’t ask for it, it came with the job - and I’m not a DBA on this database], but an attempt to SELECT * FROM SYS.LINK$ gave
me ORA-01031: insufficient privileges. So I guess Oracle (10.2.0.4.0)
gives that table a bit of extra protection.

Nate Schroeder
US Seed & Trait Commercial IT - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592

Yeah SELECT ANY TABLE is for all the Non-SYS tables. You don’t need it for
the session browser. SELECT ANY DICTIONARY is for the SYS tables.

Hi Jeff,

Right, I wouldn’t use DB_LINKS at ALL…or worst case
scenario, leave the user/pwd blank.

Got you now!

I agree, I’m not fond of DB Links. Especially when some careless person
does a refresh from production and decides to drop the existing DB
Links. Once refreshed, the test database is now pointing straight into a
production one. Nightmare!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk