MS Access and field-level permissions?

am 18.08.2010 00:50:03 von Benjamin Smith

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.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: MS Access and field-level permissions?

am 18.08.2010 01:04:07 von Stephen Frost

--0FWGRsEUTYx4i2U9
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Benjamin Smith (lists@benjamindsmith.com) wrote:
> This may be a PG issue and not an ODBC issue, but Filemaker Pro works fin=
e with=20
> the restrictive permissions, MS Access is where things break.=20

It's actually a SQL spec requirement..

> ideas?=20

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

--0FWGRsEUTYx4i2U9
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkxrFWcACgkQrzgMPqB3kiikSQCeIPse4MoX+SHzZLceOwQS 42LC
yuEAn3WiFqbDIGHNvrwpuiAMK8AefoJ9
=VBYH
-----END PGP SIGNATURE-----

--0FWGRsEUTYx4i2U9--

Re: MS Access and field-level permissions?

am 18.08.2010 01:19:51 von ssufficool

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
>

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: MS Access and field-level permissions?

am 18.08.2010 16:21:10 von Richard Broersma

On Tue, Aug 17, 2010 at 3:50 PM, Benjamin Smith
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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: MS Access and field-level permissions?

am 19.08.2010 00:23:42 von Richard Broersma

Re: MS Access and field-level permissions?

am 19.08.2010 01:19:21 von Benjamin Smith

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.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: MS Access and field-level permissions?

am 19.08.2010 02:09:52 von Richard Broersma

On Wed, Aug 18, 2010 at 4:19 PM, Benjamin Smith
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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc