Thread: MS Access and field-level permissions?
Ok, now I have another issue... We're using field level permissions to restrict what data is accessible over ODBC - we're enabling SELECT only and on a restrictive set of fields. But, when we try to link up with MS Access, none of the fields are populated. You can see the fields, but never the content of the fields. Similarly, with this configuration, via psql when I run "select * from table" where I don't have permissions to all fields in the table, I get a "Permission denied" error rather than any content. This may be a PG issue and not an ODBC issue, but Filemaker Pro works fine with the restrictive permissions, MS Access is where things break. ideas? -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
* Benjamin Smith (lists@benjamindsmith.com) wrote:
> This may be a PG issue and not an ODBC issue, but Filemaker Pro works fine with
> the restrictive permissions, MS Access is where things break.
It's actually a SQL spec requirement..
> ideas?
You should be able to create queries in access against just the fields
the user(s) have access to against the linked table (I'd hope anyway,
but I'm not an Access expert). An alternative would be to create views
that only pull through the columns you want visible to your Access
users.
Thanks,
Stephen
Attachment
Access SQL Passthrough queries should accomplish this. >-----Original Message----- >From: pgsql-odbc-owner@postgresql.org >[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Stephen Frost >Sent: Tuesday, August 17, 2010 4:04 PM >To: Benjamin Smith >Cc: pgsql-odbc@postgresql.org >Subject: Re: [ODBC] MS Access and field-level permissions? > > >* Benjamin Smith (lists@benjamindsmith.com) wrote: >> This may be a PG issue and not an ODBC issue, but Filemaker >Pro works >> fine with >> the restrictive permissions, MS Access is where things break. > >It's actually a SQL spec requirement.. > >> ideas? > >You should be able to create queries in access against just >the fields the user(s) have access to against the linked table >(I'd hope anyway, but I'm not an Access expert). An >alternative would be to create views that only pull through >the columns you want visible to your Access users. > > Thanks, > > Stephen >
On Tue, Aug 17, 2010 at 3:50 PM, Benjamin Smith <lists@benjamindsmith.com> wrote: > We're using field level permissions to restrict what data is accessible over > ODBC - we're enabling SELECT only and on a restrictive set of fields. But, when > we try to link up with MS Access, none of the fields are populated. You can see > the fields, but never the content of the fields. I've never tried to setup field level permissions on access linked tables before, so this is news to me. However, I wonder if this behavior is a result of the way MS-Access implements optimistic locking. I'll have to do more digging on this subject. > Similarly, with this configuration, via psql when I run "select * from table" > where I don't have permissions to all fields in the table, I get a "Permission > denied" error rather than any content. This is correct. This is the way it is documented to work since '*' expands to list every field of the set. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Tue, Aug 17, 2010 at 3:50 PM, Benjamin Smith <lists@benjamindsmith.com> wrote: > We're using field level permissions to restrict what data is accessible over > ODBC - we're enabling SELECT only and on a restrictive set of fields. But, when > we try to link up with MS Access, none of the fields are populated. You can see > the fields, but never the content of the fields. If you have select rights on every field in the table, it should display provided you also grant select on xmin which the odbc driver is also calling if you've configured it that way. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Wednesday, August 18, 2010 03:23:42 pm you wrote: > > We're using field level permissions to restrict what data is accessible > > over ODBC - we're enabling SELECT only and on a restrictive set of > > fields. But, when we try to link up with MS Access, none of the fields > > are populated. You can see the fields, but never the content of the > > fields. > > If you have select rights on every field in the table, it should > display provided you also grant select on xmin which the odbc driver > is also calling if you've configured it that way. > How would I go about configuring it that way? I don't see "xmin" or anything resembling that anywhere in the ODBC config options... Thanks, Ben -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
On Wed, Aug 18, 2010 at 4:19 PM, Benjamin Smith <lists@benjamindsmith.com> wrote: > How would I go about configuring it that way? I don't see "xmin" or anything > resembling that anywhere in the ODBC config options... this xmin column which is described here: http://www.postgresql.org/docs/9.0/static/datatype-oid.html is used by the odbc driver when you select the config option Row Versioning. When row versioning is set in the driver, the driver also queries each rows xmin value to determine versioning information. If you don't have permission to it, PostgreSQL will throw an access violation error. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug