Thread: Client UI, ODBC, PG & permissions

Client UI, ODBC, PG & permissions

From
"Philippe Lang"
Date:
Hello,

I would be interested in knowing how you deal with permissions in your
ODBC applications. Mine is based on a thin Access client that accesses
Postgresql remote tables and stored procedures through ODBC.

I have already granted permissions to users based on their specific ODBC
login and password. Depending on the group they are member of, they
receive either SELECT, INSERT, UPDATE or DELETE rights on the table.
This works fine.

Now, the problem is when you have to adapt the UI in order to show the
user he won't be able to update a form, for example. At the moment, the
user only know when he tries to commit his changes that he shouldn't
have tried to. Another problem is when a certain field from a table
shouldn't be visible to certain users... How to deal with that?

I have thought of different "home-made" security systems, where I deal
with users in my database directly, instead of using ONLY the PG
security.

How do you manage permissions in your applications? Is it possible to
read the permissions a user has on a specific table from the client, and
adapt the UI accordingly?

Thanks

Philippe Lang


Re: Client UI, ODBC, PG & permissions

From
Steve Jorgensen
Date:
Generally, I use the back-end security and give each user their own copy of
the front-end, and all the links to tables and views are created with the
password saved, so the user won't be asked for a logon every time.

You can use any combination of Access and network/filesystem security to
ensure that each user is the only one with access to their own front-end.
 For deployment, you can send out the front-end linked with the logon of a
user with minimum permissions, and have a re-link routine that lets the
user enter the logon information to be saved with the links.  After
re-linking views from code, you need to execute DDL queries to create
virtual primary keys on views so Access can query them efficiently, and so
it will allow updates via recordsets (e.g. the recordset behind a form).
 The DDL is in exactly the same format as if you were simply creating a
normal primary key on a table.

If you need to block access to a particular field, deny the user access to
the table, and give them access via a view that does not include the field.

On Wednesday, March 24, 2004 11:59 PM, Philippe Lang
[SMTP:philippe.lang@attiksystem.ch] wrote:
> Hello,
>
> I would be interested in knowing how you deal with permissions in your
> ODBC applications. Mine is based on a thin Access client that accesses
> Postgresql remote tables and stored procedures through ODBC.
>
> I have already granted permissions to users based on their specific ODBC
> login and password. Depending on the group they are member of, they
> receive either SELECT, INSERT, UPDATE or DELETE rights on the table.
> This works fine.
>
> Now, the problem is when you have to adapt the UI in order to show the
> user he won't be able to update a form, for example. At the moment, the
> user only know when he tries to commit his changes that he shouldn't
> have tried to. Another problem is when a certain field from a table
> shouldn't be visible to certain users... How to deal with that?
>
> I have thought of different "home-made" security systems, where I deal
> with users in my database directly, instead of using ONLY the PG
> security.
>
> How do you manage permissions in your applications? Is it possible to
> read the permissions a user has on a specific table from the client, and
> adapt the UI accordingly?
>
> Thanks
>
> Philippe Lang
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

Re: Client UI, ODBC, PG & permissions

From
Tom Lane
Date:
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> How do you manage permissions in your applications? Is it possible to
> read the permissions a user has on a specific table from the client,

Sure.  See has_table_privilege() and related functions:
http://www.postgresql.org/docs/7.4/static/functions-misc.html

I think there may be privilege info available from the
information_schema as well, which would be a better bet if you want to
preserve some semblance of database independence.

We don't currently support any per-column privileges, sorry.

            regards, tom lane