Thread: 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@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/
--- "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/
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. > > >
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. > > >