NetNews Usenet Archive 1993 #3/NN_1993_3.iso/spool/comp/database/sybase/707

Internet Message Format | 1993-01-21 | 3.8 KB

Path: sparky!uunet!olivea!pagesat!spssig.spss.com!brent
From: [e-mail] (Brent Lambert)
Newsgroups: comp.databases.sybase
Subject: Re: Accessible Databases & Objects
Message-ID: <[e-mail]>
Date: 21 Jan 93 17:31:33 GMT
References: <[e-mail]> <[e-mail]>
Sender: [e-mail] (Net News Admin)
Organization: SPSS, Inc. - portable code group
Lines: 68

In article <[e-mail]>, [e-mail] (Benjamin E. Von Ullrich) writes:
> In article <[e-mail]>, [e-mail] (Michael J. Harper) writes:
> |> I would like to issue a SELECT statment that will tell me what databases I
> |> can USE.  Getting the complete list of databases is a cinch; how do I list
> |> only those to which I have access?
>
> you must have access to a database (your suid or 'guest' must be listed
> in the sysusers or sysalternates tables) to see if you have access.
> (yes, that's a .. recursive sentence, but it is TRUE!).  the only way
> to know if you are a user is to be one in the first place, as you won't
> be able to open a database without being a user of it.
>
> the only way around this is to have guest access added to all database, or
> have the sa create a system stored procedure (a procedure beginning
> with "sp_" residing in the master database) that takes a username as a
> parameter, and is coded to search all databases for that user.

As a side note, Ben's suggestions are fine for interactive use or internal
applications, but if you're working on an application for external use, eg.
commercial software, the only way you can rely on these workarounds is to
make them part of your application's installation procedure.  If your app.
is closely integrated with the database anyway, this is fine, but if your
app. is only incidently related to the database, then it's a real pain.

Hey Sybase:  How about providing a system stored procedure (or better yet,
             a simple view in the master database) to do this?


> |> Once I know which databases I can access, how can I list the tables and
> |> views which I can access within a database?
>
> declare @login sysname (30)
> select @login=user_name()
> exec sp_helprotect @login
> exec sp_helprotect 'public'
>
> this prints out access for all objects.  if you want to get only tables and
> views, get the last two queries out of the text for sp_helprotect and adapt
> them to look for only these object types.

Again, this is fine for interactive use, but sifting through the output in an
Open-Client application can be a pain.  For this, I wrote a couple of BIG
queries which join sysobjects, syscolumns, and sysprotects and select into a
temporary table, then got the results from the temporary table.  A 'union'
would have helped, but the version of Sybase I had to support lacked UNION.

In retrospect, sifting the output of sp_helprotect might have been easier.
But if I had known how to "... get the last two queries out of the text for
sp_helprotect ...", writing my own queries would have been easier, too.  Sounds
like if I had talked to Ben when I called Tech Support many months ago, my whole
task would have been easier.

Hey Sybase:  How about providing a system stored procedure (or better yet,
             a simple view in each database) to do this?  (Oracle does :->)

If you want some more info on how I solved the problem, Email me ([e-mail])
and we can discuss it off-line.


> ------
> Benjamin von Ullrich         all words are those of the author, not Sybase.
> [e-mail]                   {pyramid,pacbell,sun,lll-tis}!sybase!ben

--
The above statements are not the opinions or policies of SPSS Inc.
The above statements may not be the opinions of Brent Lambert.
The first disclaimer is a policy of SPSS Inc.
Subsequent disclaimers are probably the opinion of Brent Lambert.