Thread: Help with ODBC problem

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?

Thanks,

Chris

--- 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/

Jeff Eckermann wrote:

> 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.
>
>
Ok, I created a new schema and put my view into it giving my reporting
group access to this view only.  I also set the search_path in the odbc
driver and as an alter user, but I am still seeing a full db catalog
when I connect to my db via odbc. (link trying to link a table in Access).

Any further ideas?  I really need the end users to only see the specific
views/tables I grant them access to when they are an area where they are
able to choose tables/views to view.

Thanks,

Chris