I recently tried to add ACS permissioning to the folders in my Photo Archive application. Unfortunately, all SQL queries that I have tried so far to retrieve the sub-folders that the user has read permission on have been heineously slow. Here is an example of a query that I tried:
select path as full_path from par_folders pf1, acs_objects ao1 where ao1.object_id = pf1.folder_id and ao1.context_id = '13667' and exists (select 1 from all_object_party_privilege_map where object_id = ao1.object_id and party_id = '2426' and privilege = 'read');
Here is the size of some of the tables and views involved:
marklundweb=# select count(*) from acs_objects; count ------- 9021 (1 row) marklundweb=# select count(*) from parties; count ------- 6 (1 row) marklundweb=# select count(*) from acs_privileges; count ------- 82 (1 row) marklundweb=# select count(*) from all_object_party_privilege_map; count --------- 1455495 (1 row)
Is the ACS permissioning system still unscalable? Can my query somehow be optimized?