Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql,v diff -u -r1.39 -r1.39.2.1 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 27 Jul 2015 12:51:21 -0000 1.39 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 1 Jul 2016 04:57:03 -0000 1.39.2.1 @@ -1,7 +1,7 @@ -- -- acs-kernel/sql/acs-permissions-create.sql -- --- The ACS core permissioning system. The knowledge level of system +-- The ACS core permission system. The knowledge level of system -- allows you to define a hierarchichal system of privilages. The -- operational level allows you to grant to any party a privilege on -- any object. @@ -19,33 +19,33 @@ --------------------------------------------- create table acs_privileges ( - privilege varchar(100) not null constraint acs_privileges_privilege_pk - primary key, - pretty_name varchar(100), - pretty_plural varchar(100) + privilege varchar(100) not null constraint acs_privileges_privilege_pk + primary key, + pretty_name varchar(100), + pretty_plural varchar(100) ); create table acs_privilege_hierarchy ( - privilege varchar(100) not null - constraint acs_priv_hier_priv_fk - references acs_privileges (privilege), - child_privilege varchar(100) not null - constraint acs_priv_hier_child_priv_fk - references acs_privileges (privilege), - constraint acs_privilege_hierarchy_pk - primary key (privilege, child_privilege) + privilege varchar(100) not null + constraint acs_priv_hier_priv_fk + references acs_privileges (privilege), + child_privilege varchar(100) not null + constraint acs_priv_hier_child_priv_fk + references acs_privileges (privilege), + constraint acs_privilege_hierarchy_pk + primary key (privilege, child_privilege) ); create index acs_priv_hier_child_priv_idx on acs_privilege_hierarchy (child_privilege); create table acs_privilege_hierarchy_index ( - privilege varchar(100) not null - constraint acs_priv_hier_priv_fk - references acs_privileges (privilege), - child_privilege varchar(100) not null - constraint acs_priv_hier_child_priv_fk - references acs_privileges (privilege), - tree_sortkey varbit + privilege varchar(100) not null + constraint acs_priv_hier_priv_fk + references acs_privileges (privilege), + child_privilege varchar(100) not null + constraint acs_priv_hier_child_priv_fk + references acs_privileges (privilege), + tree_sortkey varbit ); create index priv_hier_sortkey_idx on @@ -57,13 +57,12 @@ -- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003 create table acs_privilege_descendant_map ( - privilege varchar(100) not null - constraint acs_priv_hier_priv_fk - references acs_privileges (privilege), - descendant varchar(100) not null - constraint acs_priv_hier_child_priv_fk - references acs_privileges (privilege) - + privilege varchar(100) not null + constraint acs_priv_hier_priv_fk + references acs_privileges (privilege), + descendant varchar(100) not null + constraint acs_priv_hier_child_priv_fk + references acs_privileges (privilege) ); -- DRB: Empirical testing showed that even with just 61 entries in the new table @@ -402,20 +401,20 @@ ------------------------------------ create table acs_permissions ( - object_id integer not null - constraint acs_permissions_object_id_fk - references acs_objects (object_id) - on delete cascade, - grantee_id integer not null - constraint acs_permissions_grantee_id_fk - references parties (party_id) - on delete cascade, - privilege varchar(100) not null - constraint acs_permissions_privilege_fk - references acs_privileges (privilege) - on delete cascade, - constraint acs_permissions_pk - primary key (object_id, grantee_id, privilege) + object_id integer not null + constraint acs_permissions_object_id_fk + references acs_objects (object_id) + on delete cascade, + grantee_id integer not null + constraint acs_permissions_grantee_id_fk + references parties (party_id) + on delete cascade, + privilege varchar(100) not null + constraint acs_permissions_privilege_fk + references acs_privileges (privilege) + on delete cascade, + constraint acs_permissions_pk + primary key (object_id, grantee_id, privilege) ); create index acs_permissions_grantee_idx on acs_permissions (grantee_id); @@ -507,7 +506,7 @@ BEGIN insert into acs_permissions (object_id, grantee_id, privilege) - values + values (grant_permission__object_id, grant_permission__grantee_id, grant_permission__privilege); @@ -544,148 +543,115 @@ END; $$ LANGUAGE plpgsql; --- Really speedy version of permission_p written by Don Baccus - - --- added select define_function_args('acs_permission__permission_p','object_id,party_id,privilege'); - -CREATE FUNCTION inline_0() -RETURNS integer AS $inline_0$ +-- +-- procedure acs_permission__permission_p/3 +-- +CREATE OR REPLACE FUNCTION acs_permission__permission_p( + permission_p__object_id integer, + permission_p__party_id integer, + permission_p__privilege varchar +) RETURNS boolean AS $$ +DECLARE + v_security_context_root integer; BEGIN + v_security_context_root := acs__magic_object_id('security_context_root'); - raise notice 'starting permissions recursive -- vguerra'; + RETURN EXISTS (WITH RECURSIVE + object_context(object_id, context_id) AS ( - IF cmp_pg_version('8.4') >= 0 THEN - -- recursive site_nodes recursive - START + SELECT permission_p__object_id, permission_p__object_id + FROM acs_objects + WHERE object_id = permission_p__object_id - -- - -- procedure acs_permission__permission_p/3 - -- - CREATE OR REPLACE FUNCTION acs_permission__permission_p( - permission_p__object_id integer, - permission_p__party_id integer, - permission_p__privilege varchar - ) RETURNS boolean AS $$ - DECLARE - exists_p boolean; - BEGIN - return exists (With RECURSIVE object_context(object_id, context_id) AS ( + UNION ALL - select permission_p__object_id, permission_p__object_id - from acs_objects - where object_id = permission_p__object_id + SELECT ao.object_id, + CASE WHEN (ao.security_inherit_p = 'f' OR ao.context_id IS NULL) + THEN v_security_context_root ELSE ao.context_id END + FROM object_context oc, acs_objects ao + WHERE ao.object_id = oc.context_id + AND ao.object_id != v_security_context_root - union all + ), privilege_ancestors(privilege, child_privilege) AS ( - select ao.object_id, - case when (ao.security_inherit_p = 'f' or ao.context_id is null) - then acs__magic_object_id('security_context_root') else ao.context_id end - from object_context oc, acs_objects ao - where ao.object_id = oc.context_id - and ao.object_id != acs__magic_object_id('security_context_root') + SELECT permission_p__privilege, permission_p__privilege + + UNION ALL - ), privilege_ancestors(privilege, child_privilege) AS ( + SELECT aph.privilege, aph.child_privilege + FROM privilege_ancestors pa + JOIN acs_privilege_hierarchy aph ON aph.child_privilege = pa.privilege - select permission_p__privilege, permission_p__privilege - - union all + ) + SELECT 1 FROM acs_permissions p + JOIN party_approved_member_map pap ON pap.party_id = p.grantee_id + JOIN privilege_ancestors pa ON pa.privilege = p.privilege + JOIN object_context oc ON p.object_id = oc.context_id + WHERE pap.member_id = permission_p__party_id + ); +END; +$$ LANGUAGE plpgsql stable; - select aph.privilege, aph.child_privilege - from privilege_ancestors pa join acs_privilege_hierarchy aph - on aph.child_privilege = pa.privilege - ) select - 1 - from - acs_permissions p - join party_approved_member_map pap on pap.party_id = p.grantee_id - join privilege_ancestors pa on pa.privilege = p.privilege - join object_context oc on p.object_id = oc.context_id - where pap.member_id = permission_p__party_id - ); - END; - $$ LANGUAGE plpgsql stable; +-- for tsearch +-- +-- procedure acs_permission__permission_p_recursive_array/3 +-- +perform define_function_args('acs_permission__permission_p_recursive_array','a_objects,a_party_id,a_privilege'); - -- for tsearch +CREATE OR REPLACE FUNCTION acs_permission__permission_p_recursive_array( + permission_p__objects integer[], + permission_p__party_id integer, + permission_p__privilege varchar +) RETURNS table (object_id integer, orig_object_id integer) AS $$ +DECLARE + v_security_context_root integer; +BEGIN + v_security_context_root := acs__magic_object_id('security_context_root'); - perform define_function_args('acs_permission__permission_p_recursive_array','a_objects,a_party_id,a_privilege'); + RETURN QUERY WITH RECURSIVE + object_context(obj_id, context_id, orig_obj_id) AS ( - CREATE OR REPLACE FUNCTION acs_permission__permission_p_recursive_array( - permission_p__objects integer[], - permission_p__party_id integer, - permission_p__privilege varchar - ) RETURNS table (object_id integer, orig_object_id integer) as $$ - BEGIN - return query With RECURSIVE object_context(obj_id, context_id, orig_obj_id) AS ( + SELECT unnest(permission_p__objects), unnest(permission_p__objects), unnest(permission_p__objects) - select unnest(permission_p__objects), unnest(permission_p__objects), unnest(permission_p__objects) + UNION ALL - union all + SELECT + ao.object_id, + CASE WHEN (ao.security_inherit_p = 'f' OR ao.context_id IS NULL) + THEN v_security_context_root ELSE ao.context_id END, + oc.orig_obj_id + FROM object_context oc, acs_objects ao + WHERE ao.object_id = oc.context_id + AND ao.object_id != v_security_context_root - select ao.object_id, - case when (ao.security_inherit_p = 'f' or ao.context_id is null) - then acs__magic_object_id('security_context_root') else ao.context_id END, - oc.orig_obj_id - from object_context oc, acs_objects ao - where ao.object_id = oc.context_id - and ao.object_id != acs__magic_object_id('security_context_root') + ), privilege_ancestors(privilege, child_privilege) AS ( - ), privilege_ancestors(privilege, child_privilege) AS ( + SELECT permission_p__privilege, permission_p__privilege - select permission_p__privilege, permission_p__privilege + UNION ALL - union all + SELECT aph.privilege, aph.child_privilege + FROM privilege_ancestors pa + JOIN acs_privilege_hierarchy aph ON aph.child_privilege = pa.privilege - select aph.privilege, aph.child_privilege - from privilege_ancestors pa join acs_privilege_hierarchy aph - on aph.child_privilege = pa.privilege - - ) select - p.object_id, oc.orig_obj_id - from - acs_permissions p - join party_approved_member_map pap on pap.party_id = p.grantee_id - join privilege_ancestors pa on pa.privilege = p.privilege - join object_context oc on p.object_id = oc.context_id - where pap.member_id = permission_p__party_id - ; - END; - $$ LANGUAGE plpgsql stable; - - -- recursive site_nodes END - - ELSE - - CREATE OR REPLACE FUNCTION acs_permission__permission_p( - permission_p__object_id integer, - permission_p__party_id integer, - permission_p__privilege varchar - ) RETURNS boolean AS $$ - DECLARE - exists_p boolean; - BEGIN - return exists (select 1 - from acs_permissions p, party_approved_member_map m, - acs_object_context_index c, acs_privilege_descendant_map h - where p.object_id = c.ancestor_id - and h.descendant = permission_p__privilege - and c.object_id = permission_p__object_id - and m.member_id = permission_p__party_id - and p.privilege = h.privilege - and p.grantee_id = m.party_id); - END; - $$ LANGUAGE plpgsql stable; - - END IF; - raise notice 'ending permissions recursive -- vguerra'; - - return null; + ) + SELECT p.object_id, oc.orig_obj_id + FROM acs_permissions p + JOIN party_approved_member_map pap ON pap.party_id = p.grantee_id + JOIN privilege_ancestors pa ON pa.privilege = p.privilege + JOIN object_context oc ON p.object_id = oc.context_id + WHERE pap.member_id = permission_p__party_id; END; -$inline_0$ LANGUAGE plpgsql; +$$ LANGUAGE plpgsql stable; -select inline_0(); -drop function inline_0(); + +-- +-- Local variables: +-- mode: sql +-- indent-tabs-mode: nil +-- End: