Re: Help with ODBC problem

--- Chris Hoover <revoohc@sermonaudio.com> wrote:
> I am in need of some help.  We are looking at
> rolling out a reporting
> tool to our users and need to make sure that the
> users only see the
> objects we give them access to.
>
> To start testing, I have created a new user and
> group to test with.  I
> then created a view to use and gave the group select
> access to this view
> only and put the user into this group.  However,
> when we pull up the
> odbc connection from a client machine (windows), we
> see all tables
> listed and then the single view.  While this test
> user can not select
> from the tables, he can see them.  This is a problem
> for us since we do
> not want our end users to see any portion of our
> actual schema.
>
> How do I get the odbc/db configured so that the only
> odbc objects the
> end user sees are the views we give them access?
>

Create a separate schema for each user, or class of
users, that you want to have limited access.  Put all
of the objects that you want them to have access to
into the relevant schema(s).  Then, in the "connect
settings" box in the DSN, enter the command "set
search_path to myschema, public;" (or leave out public
if you want).  If you are connecting using code,
rather than a DSN, then just issue that command
directly after connecting.

That will restrict users to seeing only those objects
which you want them to see.  If you revoke their
access permissions to the public schema, and only
grant permissions on the one special schema, plus
tables etc., then you will lock the whole thing up
tight.

> Thanks,
>
> Chris
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map
settings





__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/