Re: I only want to see five tables with an Oracle user ID?

I assume that, to produce the list of tables, ODBC logs on to the database under account X and then gets the tables by querying the DBA_TABLES view?

You could solve this by creating a view DBA_TABLES (yech!, I admit) under schema X that produces the desired tables. This view would have to contain a join on SYS.DBA_TABLES and DBA_TAB_PRIVS, limiting the rows from SYS.DBA_TABLES to those which the user has access to.

Jaap.

On Wed, 24 Apr 2002 14:35:42 +1000, "Howard J. Rogers" <dba_at_hjrdba.com> wrote:

>Er, no
>
>What I hoped was obvious from my post was that *regardless* of what
>privileges a user has been granted, the ODBC dialogs always display every
>table that exists. So, pursuing my Access example (where Scott has
>privileges on 7 tables) I can successfully link a table in the CTXSYS
>schema. Only when I actually try to do anything with that table do I get the
>ODBC message "insufficient privileges".
>
>So the problem is (again, I'm only assuming the original poster meant this)
>that the list of tables etc. is collated *before* any privileges or
>user-specific issues are checked on.
>
>So I don't see that creating additional users is actually going to help
>resolve the issue. It has to be an ODBC call that you can make, but how
>*that* would help anyone using things like Access's linked tables dialog, I
>again have no idea.
>
>But your thoughts were certainly more helpful than the earlier ones. So
>thank you.
>
>Regards
>HJR
>
>
>
>"Stan" <stan0074_at_yahoo.com> wrote in message
>news:3CC63453.6060707_at_yahoo.com...
>> Don't know whether this could help you or not, but just a thought. how
>> about creating a user separate from actual appuser and then grant
>> respective privileges(select/insert/update/delete) for those required
>> tables that needs to be seen by the new user and have synonym created to
>> hide its identity.
>>
>> Now, if the user attempts an ODBC connection to new user instead of
>> appuser, then he'd only see the required tables.
>>
>> -Stan
>>
>> Howard J. Rogers wrote:
>>
>> > Yes, I see the usual suspects have been as forthcoming and as helpful as
>> > ever.
>> >
>> > What I suspect the original poster was after was a way of preventing the
>> > display of countless pointless tables whenever a user attempts an ODBC
>> > connection to a database. For example, when I, as Scott, attempt to
>create
>> > a linked table in Access, the dialog window I use to accomplish that
>task
>> > includes a listing of some 2000 tables -practically every table that
>exists
>> > in my database, in fact. Yet this particular Scott was granted only
>'create
>> > table' and 'create session' privileges, so he should have seen (in my
>> > particular case) 7 tables listed at most.
>> >
>> > So I think our original poster wanted to know if there was a way to
>limit
>> > the list of tables either to what the user actually has rights to or
>(even
>> > better) to a defined list of tables.
>> >
>> > Of course, that might not be what the original poster was after at all.
>But
>> > I still think he deserved better than what he got.
>> >
>> > As to an answer.... well, I don't know. It's bugged me for years, and I
>know
>> > of no easy way to stop it happening. There's certainly nothing obvious
>in
>> > the ODBC driver dialogs to suppress tables to which you don't have
>access.
>> > But perhaps there is someone out there who can give a useful answer to
>the
>> > original question, instead of a smug one, and then we can all learn
>> > something.
>> >
>> > Regards
>> > HJR
>> >
>> >
>> >
>> >
>> >
>> >
>> > "stinky" <stankonia_at_stunkitystunk.org> wrote in message
>> > news:3CC60ABB.205_at_stunkitystunk.org...
>> >
>> >>Daniel Morgan wrote:
>> >>
>> >>
>> >>>The Pig wrote:
>> >>>
>> >>>
>> >>>>Guys,
>> >>>>
>> >>>>Need some help. When connecting via ODBC to an oracle server, I want
>> >>>>to only see let's say five tables. That's it. I don't want to see
>> >>>>the system tables associated with a default user, only certain tables.
>> >>>>How do I do this. I have a Oracle DBA telling me that it can't be
>> >>>>done but I know that it can. Any help would be appreciated. A
>> >>>>detailed answer would be appreciated. Thanks.
>> >>>>
>> >>>>The Pig
>> >>>>
>> >>>>
>> >>>I am very tempted to tell you that if you know it can be done even
>though
>> >>>your DBA tells you it can't that you should not come whining to us in
>an
>> >>>attempt to make your DBA look bad. You know it can be done ... go do
>it.
>> >>>
>> >>>In fact, though, you may be right. But not knowing from where you are
>> >>>selecting your list of tables I can't be sure. But one thing I can tell
>> >>>you is that the following query will produce no data dictionary objects
>> >>>and only five tables. Which five is indeterminate.
>> >>>
>> >>>SELECT table_name
>> >>>
>> >>>FROM user_tables
>> >>
>> >>>WHERE rownum < 6;
>> >>>
>> >>>If you mean the complete contents of five tables ... I can do it ...
>but
>> >>>I'm going to side with your DBA and not tell you how.
>> >>>
>> >>>Perhaps rephrasing your question with more clarity might help.
>> >>>
>> >>>
>> >>Nice. I think you made Sy proud with that post.
>> >>
>> >>
>> >>>
>> >>>Daniel Morgan
>> >>>
>> >>>
>> >>
>> >
>> >
>> >
>> >
>>
>
>
Received on Wed Apr 24 2002 - 03:28:01 CDT