Mailing List Archive: Possible to get alle Collections a user can access via SQL Query?

On Tue, 2010-04-20 at 17:47 +0200, Sebastian Gau? wrote:
> Hi,
>
> i am currently working on exporting all the collections a user can
> access (read or read+write). I want to get a list that shows the
> dav_name of all collectios a given user_no/pricipal_id can access, and
> if they have just read or read+write access.
>
> Any ideas how to get this done via SQL Query? If any better solution
> exists I would also bei open to it ;)

Something like this should do what you want:

SELECT dav_name,
bits_to_privilege(path_privs($PRINCIPAL_ID, dav_name, 2))
FROM collection
WHERE ( ( path_privs($PRINCIPAL_ID, dav_name, 2)
& privilege_to_bits( ARRAY[ 'DAV::read' ] ) )
= privilege_to_bits( ARRAY[ 'DAV::read' ] ) );

Explanation:

Insert your pre-loved principal_id into this in both places in place of
$PRINCIPAL_ID :-)

privilege_to_bits() converts an array of privilege strings into a
bitmask. Maybe specify the array as ARRAY[ 'DAV::read', 'DAV::write' ]
if you think you have some with write access but not read(!) Note that
privilege_to_bits( ARRAY[ 'DAV::read' ] ) returns a set of three
privileges, so we have to test that "(A & B) = B" in order to confirm
they really have read privileges, and not just read-free-busy.

bits_to_privilege() converts a bitmask of privileges to a human-readable
comma-separated list of privilege names.

path_privs( principal_id, pathname, depth ) returns a bitmask of the
expanded privileges for the identified principal in respect of
'pathname'. The third 'depth' parameter is a shortcut for how deeply it
searches through layers of groups which are members of groups, which are
members of groups, and '2' is more than enough for almost everyone (it's
also the system-wide default).

Regards,
Andrew.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
You have a strong appeal for members of your own sex.
------------------------------------------------------------------------

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL: <http://lists.morphoss.com/pipermail/davical-users/attachments/20100421/0fc1971e/attachment.pgp>
-------------- next part --------------

Hi,

this works great, thank you!

If someone needs to look up what a specific user_no can access try this
(it also outputs the old rt_id instead of the binary [.requires that the
rights are set exactly like the old read/read+write of course]):

SELECT dav_name, dav_displayname, (SELECT rt_id FROM relationship_type
WHERE bit_confers=path_privs((SELECT principal_id FROM principal WHERE
user_no='215'), dav_name, 2))
FROM collection
WHERE ( ( path_privs((SELECT principal_id FROM principal WHERE
user_no='215'), dav_name, 2)
& privilege_to_bits( ARRAY[ 'DAV::read' ] ) )
= privilege_to_bits( ARRAY[ 'DAV::read' ] ) );

Regards
Sebastian

On 21.04.2010 07:33, Andrew McMillan wrote:
> On Tue, 2010-04-20 at 17:47 +0200, Sebastian Gau? wrote:
>> Hi,
>>
>> i am currently working on exporting all the collections a user can
>> access (read or read+write). I want to get a list that shows the
>> dav_name of all collectios a given user_no/pricipal_id can access, and
>> if they have just read or read+write access.
>>
>> Any ideas how to get this done via SQL Query? If any better solution
>> exists I would also bei open to it ;)
>
> Something like this should do what you want:
>
> SELECT dav_name,
> bits_to_privilege(path_privs($PRINCIPAL_ID, dav_name, 2))
> FROM collection
> WHERE ( ( path_privs($PRINCIPAL_ID, dav_name, 2)
> & privilege_to_bits( ARRAY[ 'DAV::read' ] ) )
> = privilege_to_bits( ARRAY[ 'DAV::read' ] ) );
>
>
> Explanation:
>
> Insert your pre-loved principal_id into this in both places in place of
> $PRINCIPAL_ID :-)
>
> privilege_to_bits() converts an array of privilege strings into a
> bitmask. Maybe specify the array as ARRAY[ 'DAV::read', 'DAV::write' ]
> if you think you have some with write access but not read(!) Note that
> privilege_to_bits( ARRAY[ 'DAV::read' ] ) returns a set of three
> privileges, so we have to test that "(A & B) = B" in order to confirm
> they really have read privileges, and not just read-free-busy.
>
> bits_to_privilege() converts a bitmask of privileges to a human-readable
> comma-separated list of privilege names.
>
> path_privs( principal_id, pathname, depth ) returns a bitmask of the
> expanded privileges for the identified principal in respect of
> 'pathname'. The third 'depth' parameter is a shortcut for how deeply it
> searches through layers of groups which are members of groups, which are
> members of groups, and '2' is more than enough for almost everyone (it's
> also the system-wide default).
>
> Regards,
> Andrew.
>
> ------------------------------------------------------------------------
> andrew (AT) morphoss (DOT) com +64(272)DEBIAN
> You have a strong appeal for members of your own sex.
> ------------------------------------------------------------------------
>

--
Sebastian Gau?
Systemadministration, Internal Systems
Abteilung Technik

E-Mail: sga at hosteurope.de
Fax: +49 180 5 66 3233 (*)

-----------------------------------------------------------------------
Host Europe GmbH - http://www.hosteurope.de
Welserstra?e 14 - 51149 K?ln - Germany
Telefon: 0800 467 8387 - Fax: +49 180 5 66 3233 (*)
HRB 28495 Amtsgericht K?ln - USt-IdNr.: DE187370678
Gesch?ftsf?hrer:
Uwe Braun - Alex Collins - Mark Joseph - Patrick Pulverm?ller

(*) 0,14 EUR/Min. aus dem dt. Festnetz; maximal 0,42 EUR/Min. aus
den dt. Mobilfunknetzen

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 260 bytes
Desc: OpenPGP digital signature
URL: <http://lists.morphoss.com/pipermail/davical-users/attachments/20100422/bc4e3765/attachment.pgp>
-------------- next part --------------