Use of Session Brower Disabled after Permissions Changes

Hi Bert,

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 :slight_smile:

Very true. But, did you know that there is a way that you can select
stuff from a "db link" that doesn't actually exist yet? Provided that
the @whatever bit corresponds to an entry in tnsnames (onames as well
perhaps?) it works.

I've not got the full details so I'm not sure if the global names bit
needs to be true or false to make it work etc.

This could allow people "in the know" to execute poorly perfroming code
on other databases even without DB Links.

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

Hi Nate,

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.

Yes, I'm seeing that as well. Thanks for the correction.

Cheers,
Norm. [TeamStandsCorrected!]

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

Hi John,

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.

I knew that! Honest!

Hangs head in shame!

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

Greetings;

From a production data perspective, “SELECT ANY TABLE” is still a
security issue and should never be granted.

Especially if you have such sensitive information in the system you just granted
that to such as an HR database and all those lovely personal employee details.
Or your database consists of data sensitive to a third party such as the
Government and your business contractual obligations is that people only get the
access they need to do their jobs.

So… even if the passwords stored in SYS tables is protected, there’s
still the high potential of other equally sensitive data.

Roger S.

For that matter, any priv that has the word ‘ANY’ in it should be
granted very carefully. ANY is my favorite 3-letter 4-letter word.

Right up there with WVU J

Norm!

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!

Interesting! Isn't that one of the scenarios that the GLOBAL_NAMES init.ora
parameter is supposed to prevent? I had thought that it was mandatory to
set it to "true" for some circumstance, but that bit of info is out to lunch
right about now...

Rich -- [TeamT]

Disclaimer: A sandwich walks into a bar and orders a drink. Bartender
shouts "We don't serve food here!"

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.

I tried unconvincingly to show a vendor why granting SELECT on SYS.USER$ was
a very bad security hole. It gave me the "obstructionist" label.

Very glad I don't have to deal with them any more! :slight_smile:

Rich -- [TeamT]

Disclaimer: "DBA" is not usually "Dumb Bunnies Association"

It gave me the "obstructionist" label.

Yea… I get that from time to time too :slight_smile:

It always ends pretty much the same way though. I mention that sooner or later
that security issue will come to light to an auditor who red-flags it and then
it’ll have to be fixed. Then later it’s red-flagged and a fixing
process begins.

In my case though, it’s not so much a vendor as it’s usually upper
management. Ah well… perhaps some day they’ll eventually learn
it’s cheaper to do something that is foreseeable properly the first time
then to fix it later … then again… perhaps not :wink:

RAS

It always ends pretty much the same way though. I mention that sooner or
later that security issue will come to light to an auditor who red-flags it
and then it'll have to be fixed. Then later it's red-flagged and a fixing
process begins.

Hmmm...the company in question was private when I worked there, but is now
public after a merger. I wonder if a Sox audit would check access to USER$?

Then again, from an IT perspective, that's the least of their worries. All
of IT having full unaudited access to Production data would probably be
closer to the top... :slight_smile:

Sorry for getting waaaaay off-Toad here. Mea culpa!

Rich -- [TeamT]

Disclaimer: It wasn't me! It was the one-armed man!

Old thread I know but struggled with this today and thought worth posting the updated list I eventually got to work.

This works with SQL Navigator 7.2 and imagine similar to TOAD.

GRANT SELECT ON SYS.DBA_ROLLBACK_SEGS TO DEVELOPER_ROLE;

GRANT SELECT ON SYS.V_$SESSION TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$DATABASE TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$PROCESS TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$SESS_IO TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$SESSION_WAIT TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$SESSION_EVENT TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$ACCESS TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$SESSTAT TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$STATNAME TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$OPEN_CURSOR TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$SQL TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$LOCK TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$LOCKED_OBJECT TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.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 SYS.V_$SQLTEXT_WITH_NEWLINES TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$OPEN_CURSOR TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$PX_SESSION TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$PX_PROCESS TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$PX_SESSTAT TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$STATNAME TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.V_$MYSTAT TO DEVELOPER_ROLE;
– Oracle 11g only
–GRANT SELECT ON SYS.V$SESSION_CONNECT_INFO TO DEVELOPER_ROLE;

GRANT SELECT ON SYS.GV_$SESSION TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$DATABASE TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$PROCESS TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$SESS_IO TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$SESSION_WAIT TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$SESSION_EVENT TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$ACCESS TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$SESSTAT TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$STATNAME TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$OPEN_CURSOR TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$SQL TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$LOCK TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$LOCKED_OBJECT TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$SESSION_LONGOPS TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$TRANSACTION TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$TRANSACTION TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$SQLTEXT_WITH_NEWLINES TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$OPEN_CURSOR TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$PX_SESSION TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$PX_PROCESS TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$PX_SESSTAT TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$STATNAME TO DEVELOPER_ROLE;
GRANT SELECT ON SYS.GV_$MYSTAT TO DEVELOPER_ROLE;
– Oracle 11g only
–GRANT SELECT ON SYS.GV$SESSION_CONNECT_INFO TO DEVELOPER_ROLE;