Thread: Ignoring the limited user-rights by using ODBC

Ignoring the limited user-rights by using ODBC

From
"Goeke, Tobias"
Date:
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




Re: Ignoring the limited user-rights by using ODBC

From
"Joshua D. Drake"
Date:
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@postgresql.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/


Re: Ignoring the limited user-rights by using ODBC

From
Jeff Eckermann
Date:
--- "Joshua D. Drake" <jd@commandprompt.com> wrote:
> 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.

This is a known issue, and I do not know of any
workaround.  IIRC, it was Dave Page who posted a
reason for this behaviour some months back, but I
forget the details.

>
> 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@postgresql.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/
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>



__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

Re: Ignoring the limited user-rights by using ODBC

From
Peter Eisentraut
Date:
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/

Re: Ignoring the limited user-rights by using ODBC

From
Marko Ristola
Date:

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


Re: Ignoring the limited user-rights by using ODBC

From
"Goeke, Tobias"
Date:
The crux is that the \d commands in psql does not necessarily define
the scope of a user's access privileges. (referring to Peter Eisentraut)

So there can't exist a solution for my "problem" (better wish!) because the
user must read out of the other tables in the views.
I should accept it that they are shown in the choice via odbc.

Thanks a lot for helping me!

Tobias Goeke

-----Ursprüngliche Nachricht-----
Von: Marko Ristola [mailto:marko.ristola@kolumbus.fi]
Gesendet: Mittwoch, 30. März 2005 20:48
An: Peter Eisentraut
Cc: Goeke, Tobias; pgsql-odbc@postgresql.org
Betreff: Re: [ODBC] Ignoring the limited user-rights by using ODBC




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