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