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 -N -r1.39.2.2 -r1.39.2.3 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 1 Jul 2016 14:04:39 -0000 1.39.2.2 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 5 Jul 2016 16:35:23 -0000 1.39.2.3 @@ -214,7 +214,6 @@ --- added select define_function_args('priv_recurse_subtree','nkey,child_priv'); -- @@ -290,7 +289,6 @@ --- added select define_function_args('acs_privilege__create_privilege','privilege,pretty_name;null,pretty_plural;null'); -- @@ -332,7 +330,6 @@ --- added select define_function_args('acs_privilege__drop_privilege','privilege'); -- @@ -352,7 +349,6 @@ --- added select define_function_args('acs_privilege__add_child','privilege,child_privilege'); -- @@ -375,7 +371,6 @@ --- added select define_function_args('acs_privilege__remove_child','privilege,child_privilege'); -- @@ -459,6 +454,7 @@ and pdm.privilege = p.privilege and pamm.party_id = p.grantee_id; + -- -- Obsolete and deprecated view. -- @@ -494,68 +490,29 @@ --- added -select define_function_args('acs_permission__grant_permission','object_id,grantee_id,privilege'); -- --- procedure acs_permission__grant_permission/3 +-- Create an SQL schema to allow the same dot notation as in +-- Oracle. The advantage of this notation is that the function can be +-- called identically for PostgreSQL and Oracle, so much duplicated +-- code can be removed. -- -CREATE OR REPLACE FUNCTION acs_permission__grant_permission( - grant_permission__object_id integer, - grant_permission__grantee_id integer, - grant_permission__privilege varchar -) RETURNS integer AS $$ -DECLARE -BEGIN - insert into acs_permissions - (object_id, grantee_id, privilege) - values - (grant_permission__object_id, grant_permission__grantee_id, - grant_permission__privilege); - - return 0; -EXCEPTION - when unique_violation then - return 0; -END; -$$ LANGUAGE plpgsql; - - --- procedure revoke_permission - - --- added -select define_function_args('acs_permission__revoke_permission','object_id,grantee_id,privilege'); - +-- Actually, at least all permission functions should be defined this +-- way, keeping the old "__" notation around for backwards +-- compatibility for custom packages. -- --- procedure acs_permission__revoke_permission/3 +-- TODO: handling of schema names in define_function_args -- -CREATE OR REPLACE FUNCTION acs_permission__revoke_permission( - revoke_permission__object_id integer, - revoke_permission__grantee_id integer, - revoke_permission__privilege varchar -) RETURNS integer AS $$ -DECLARE -BEGIN - delete from acs_permissions - where object_id = revoke_permission__object_id - and grantee_id = revoke_permission__grantee_id - and privilege = revoke_permission__privilege; +CREATE SCHEMA acs_permission; - return 0; -END; -$$ LANGUAGE plpgsql; - -select define_function_args('acs_permission__permission_p','object_id,party_id,privilege'); - -- --- procedure acs_permission__permission_p/3 +-- 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 +CREATE OR REPLACE FUNCTION acs_permission.permission_p( + p_object_id integer, + p_party_id integer, + p_privilege varchar ) RETURNS boolean AS $$ DECLARE v_security_context_root integer; @@ -565,9 +522,9 @@ RETURN EXISTS (WITH RECURSIVE object_context(object_id, context_id) AS ( - SELECT permission_p__object_id, permission_p__object_id + SELECT p_object_id, p_object_id FROM acs_objects - WHERE object_id = permission_p__object_id + WHERE object_id = p_object_id UNION ALL @@ -580,7 +537,7 @@ ), privilege_ancestors(privilege, child_privilege) AS ( - SELECT permission_p__privilege, permission_p__privilege + SELECT p_privilege, p_privilege UNION ALL @@ -593,23 +550,22 @@ 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 + WHERE pap.member_id = p_party_id ); END; $$ LANGUAGE plpgsql stable; --- for tsearch -- --- procedure acs_permission__permission_p_recursive_array/3 +-- procedure acs_permission.permission_p_recursive_array/3 -- +-- Return for a an array of objects a set of objects where the +-- specified user has the specified rights. -perform define_function_args('acs_permission__permission_p_recursive_array','a_objects,a_party_id,a_privilege'); - -CREATE OR REPLACE FUNCTION acs_permission__permission_p_recursive_array( - permission_p__objects integer[], - permission_p__party_id integer, - permission_p__privilege varchar +CREATE OR REPLACE FUNCTION acs_permission.permission_p_recursive_array( + p_objects integer[], + p_party_id integer, + p_privilege varchar ) RETURNS table (object_id integer, orig_object_id integer) AS $$ DECLARE v_security_context_root integer; @@ -619,41 +575,191 @@ 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) - - UNION ALL - + SELECT unnest(p_objects), unnest(p_objects), unnest(p_objects) + 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 + 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 ), privilege_ancestors(privilege, child_privilege) AS ( - SELECT permission_p__privilege, permission_p__privilege - + SELECT p_privilege, p_privilege UNION ALL - SELECT aph.privilege, aph.child_privilege - FROM privilege_ancestors pa - JOIN acs_privilege_hierarchy aph ON aph.child_privilege = pa.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; + WHERE pap.member_id = p_party_id; END; $$ LANGUAGE plpgsql stable; -- +-- procedure acs_permission.parties_with_object_privilege/2 +-- +-- Find all party_ids which have a given privilege on a given +-- object. The function is equivalent to an SQL query on the +-- deprecated acs_object_party_privilege_map such as e.g.: +-- +-- select p.party_id +-- from acs_object_party_privilege_map p +-- where p.object_id = :object_id +-- and p.privilege = 'admin'; +-- + +CREATE OR REPLACE FUNCTION acs_permission.parties_with_object_privilege( + p_object_id integer, + p_privilege varchar +) RETURNS table (party_id integer) AS $$ +DECLARE + v_security_context_root integer; +BEGIN + v_security_context_root := acs__magic_object_id('security_context_root'); + + RETURN QUERY WITH RECURSIVE + object_context(obj_id, context_id, orig_obj_id) AS ( + SELECT p_object_id, p_object_id, p_object_id + 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 + + ), privilege_ancestors(privilege, child_privilege) AS ( + SELECT p_privilege, p_privilege + UNION ALL + SELECT aph.privilege, aph.child_privilege + FROM privilege_ancestors pa + JOIN acs_privilege_hierarchy aph ON aph.child_privilege = pa.privilege + ) + SELECT pap.member_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; +END; +$$ LANGUAGE plpgsql stable; + + +-- +-- procedure acs_permission.grant_permission/3 +-- +CREATE OR REPLACE FUNCTION acs_permission.grant_permission( + p_object_id integer, + p_grantee_id integer, + p_privilege varchar +) RETURNS integer AS $$ +DECLARE +BEGIN + insert into acs_permissions + (object_id, grantee_id, privilege) + values + (p_object_id, p_grantee_id, p_privilege); + + return 0; +EXCEPTION + when unique_violation then + return 0; +END; +$$ LANGUAGE plpgsql; + + +-- +-- procedure acs_permission.revoke_permission/3 +-- +CREATE OR REPLACE FUNCTION acs_permission.revoke_permission( + p_object_id integer, + p_grantee_id integer, + p_privilege varchar +) RETURNS integer AS $$ +DECLARE +BEGIN + delete from acs_permissions + where object_id = p_object_id + and grantee_id = p_grantee_id + and privilege = p_privilege; + + return 0; +END; +$$ LANGUAGE plpgsql; + + + + +--- +--- Functions for backwards compatibility +--- +select define_function_args('acs_permission__permission_p','object_id,party_id,privilege'); +DROP FUNCTION IF EXISTS acs_permission__permission_p(integer, integer, varchar); +CREATE OR REPLACE FUNCTION acs_permission__permission_p( + p_object_id integer, + p_party_id integer, + p_privilege varchar +) RETURNS boolean AS $$ +BEGIN + RETURN acs_permission.permission_p(p_object_id, p_party_id, p_privilege); +END; +$$ LANGUAGE plpgsql stable; + + +select define_function_args('acs_permission__permission_p_recursive_array','objects,party_id,privilege'); +DROP FUNCTION IF EXISTS acs_permission__permission_p_recursive_array(integer[], integer, varchar); +CREATE OR REPLACE FUNCTION acs_permission__permission_p_recursive_array( + p_objects integer[], + p_party_id integer, + p_privilege varchar +) RETURNS table (object_id integer, orig_object_id integer) AS $$ +BEGIN + RETURN QUERY SELECT acs_permission.permission_p_recursive_array(p_objects, p_party_id, p_privilege); +END; +$$ LANGUAGE plpgsql stable; + + +select define_function_args('acs_permission__grant_permission','object_id,grantee_id,privilege'); +DROP FUNCTION IF EXISTS acs_permission__grant_permission(integer, integer, varchar); +CREATE OR REPLACE FUNCTION acs_permission__grant_permission( + p_object_id integer, + p_grantee_id integer, + p_privilege varchar +) RETURNS integer AS $$ +DECLARE +BEGIN + RETURN acs_permission.grant_permission(p_object_id, p_grantee_id, p_privilege); +END; +$$ LANGUAGE plpgsql; + + +select define_function_args('acs_permission__revoke_permission','object_id,grantee_id,privilege'); +DROP FUNCTION IF EXISTS acs_permission__revoke_permission(integer, integer, varchar); +CREATE OR REPLACE FUNCTION acs_permission__revoke_permission( + p_object_id integer, + p_grantee_id integer, + p_privilege varchar +) RETURNS integer AS $$ +DECLARE +BEGIN + RETURN acs_permission.revoke_permission(p_object_id, p_grantee_id, p_privilege); +END; +$$ LANGUAGE plpgsql; + + + +-- -- Local variables: -- mode: sql -- indent-tabs-mode: nil