Re: Ignoring the limited user-rights by using ODBC

Hello,

I created a new user in a new schema.
This user is only authorized to select the views in this schema.
If i look at the tables and views for this user by using "\d", there are
only a few views shown.
That means that the rights are correctly set.

Now the real problem:

If i connect to the database via obdc with this user, all schemes are shown.
So i am able to select all the tables and views e.g. in excel, although the
user isn't autorized.

I should only be able to select a few views, like it is configured in psql.
How can i constrain this?

Can you help me?

Thanks
Tobias

On Thu, 2005-03-24 at 15:12 +0100, Goeke, Tobias wrote:
> Hello,
>
> I created a new user in a new schema.
> This user is only authorized to select the views in this schema.
> If i look at the tables and views for this user by using "\d", there are
> only a few views shown.
> That means that the rights are correctly set.
>
> Now the real problem:
>
> If i connect to the database via obdc with this user, all schemes are shown.
> So i am able to select all the tables and views e.g. in excel, although the
> user isn't autorized.

Have you verified that the user can actually select from the tables or
can the user just see that the tables exist?

Have you verified that you are actually connecting as the limited user?

Sincerely,

Joshua D. Drake

>
> I should only be able to select a few views, like it is configured in psql.
> How can i constrain this?
>
> Can you help me?
>
> Thanks
> Tobias
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
--
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/

Goeke, Tobias wrote:
> If i connect to the database via obdc with this user, all schemes are
> shown. So i am able to select all the tables and views e.g. in excel,
> although the user isn't autorized.

It is not possible that the ODBC driver can circumvent privileges that
would otherwise apply. Please provide a detailed way to reproduce your
problem.

Note that what the \d commands in psql show does not necessarily define
the scope of a user's access privileges. It merely shows what might be
of interest to the user.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

I remember from some other databases, that
the schema is not for security. It is for application
logic:

If you have marko.branch and users.branch
tables, you can link to both by

select * from marko.branch
union
select * from users.branch

You can revoke rights from the tables with the following commands:
revoke all from marko on marko.branch;
revoke all from marko on users.branch;
After these, "marko" user is not able to read, or write into the tables.

You can play with the schema like this with ODBC:

set search_path to marko,public; -- the new schema is "marko"
select * from branch; /* points into marko.branch */
set search_path to users,public;
select * from branch; /* points into users.branch */

Read or write rights (grant/revoke) for the table and
visibility (naming, search path, namespace, schema) of the table
name are a different thing.

Marko Ristola

Peter Eisentraut wrote:

>Goeke, Tobias wrote:
>
>
>>If i connect to the database via obdc with this user, all schemes are
>>shown. So i am able to select all the tables and views e.g. in excel,
>>although the user isn't autorized.
>>
>>
>
>It is not possible that the ODBC driver can circumvent privileges that
>would otherwise apply. Please provide a detailed way to reproduce your
>problem.
>
>Note that what the \d commands in psql show does not necessarily define
>the scope of a user's access privileges. It merely shows what might be
>of interest to the user.
>
>
>