Home » SQL & PL/SQL » SQL & PL/SQL » Finding all tables I have permission to read from...
Finding all tables I have permission to read from... [message #271207] Sun, 30 September 2007 18:52 Go to next message
jw08
Messages: 3
Registered: September 2007
Junior Member
Hello,

I need help. We're having an argument with IT. Basically, the want to cut off my access to our Oracle database because there is "sensitive" data that they've decided that, even though I've had access to it for over 10 years, it's now a security risk.

Really, I only need SELECT access to about a dozen of the 5000 tables. But they're claiming the GRANT command does not exist, and that an account must have full access to everything.

There is another account that a co-worker has that, over the years, we've found that there are in fact tables that he can't access that I can. Unfortunately, we can't remember the names of any of these tables to prove our point.

So I'm looking for a query that will dump out a list of all tables that I have SELECT access. I'm then want to have my co-worker log in to dump out a list of all tables that he can access. I'll then write a simple C program or something that will list all the tables that appear for only one of us.

So is there a way I can write a query to do that? Or are the two of us just going to have to sit down and guess table names for the next three weeks until we find a match?

Neither of us are the 'owner' of any of the tables, nor are any of us DBA's to access the dba_ tables which might list the info.

Thanks for your help.
Re: Finding all tables I have permission to read from... [message #271209 is a reply to message #271207] Sun, 30 September 2007 19:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT TABLE_NAME FROM ALL_TABLES;
Re: Finding all tables I have permission to read from... [message #271211 is a reply to message #271207] Sun, 30 September 2007 19:52 Go to previous messageGo to next message
jw08
Messages: 3
Registered: September 2007
Junior Member
I thought that's what it was, but in another Oracle database I have access to, table names come up with "SELECT table_name from all_tables" that I do not have permission to access.
Re: Finding all tables I have permission to read from... [message #271227 is a reply to message #271211] Sun, 30 September 2007 23:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not possible.
By definition, all_tables contains all tables you have any access.
In your other database, you think you have no access but you have at least one privilege on them.

see all_tab_privs_recd

Regards
Michel
Re: Finding all tables I have permission to read from... [message #271384 is a reply to message #271211] Mon, 01 October 2007 08:48 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
jw08 wrote on Sun, 30 September 2007 19:52
I thought that's what it was, but in another Oracle database I have access to, table names come up with "SELECT table_name from all_tables" that I do not have permission to access.


Are you logged in with the same user for whom you are concerned about ?

Re: Finding all tables I have permission to read from... [message #271396 is a reply to message #271207] Mon, 01 October 2007 09:30 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I would really worry about an IT staff that knows so little about oracle that they don't understand the rules of oracle privileges. Very, very scary that they are administrating your database.
Re: Finding all tables I have permission to read from... [message #271408 is a reply to message #271396] Mon, 01 October 2007 10:31 Go to previous message
jw08
Messages: 3
Registered: September 2007
Junior Member
Bill B wrote on Mon, 01 October 2007 09:30
I would really worry about an IT staff that knows so little about oracle that they don't understand the rules of oracle privileges. Very, very scary that they are administrating your database.


Don't worry, I have no faith in them whatsoever, either.
Previous Topic: deleting a column
Next Topic: Problem with inserting a DATE value (merged & renamed by LF)
Goto Forum:
  


Current Time: Fri Dec 09 15:41:34 CST 2016

Total time taken to generate the page: 0.11323 seconds