Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SELECT (
-- Check access for objects
'container' = 'object' AND ( -- in_type
SELECT EXISTS (
-- Owner has all rights
SELECT 1
FROM object o
WHERE o.object_id = '82195a7e-86a2-4235-bcd6-126492273e64' -- in_element_id
AND o.owner_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
) OR EXISTS (
-- Check object permission table for user
SELECT 1
FROM object_account_permission oap
WHERE oap.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
AND oap.object_id = '82195a7e-86a2-4235-bcd6-126492273e64' -- in_element_id
AND oap.code = 'object_read' -- in_code
) OR EXISTS (
-- Check object permission table for user's groups
SELECT 1
FROM object_class_permission ocp
JOIN membership m ON ocp.class_id = m.class_id
WHERE m.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
AND ocp.object_id = '82195a7e-86a2-4235-bcd6-126492273e64' -- in_element_id
AND ocp.code = 'object_read' -- in_code
) OR (
'object_read' = 'object_read' AND ( -- in_code
SELECT (
-- Check if user has override permissions
user_has_permission('86d09109-64cc-4aa6-9336-b49b8ae71f2c', 'cmd_read_other') -- in_account_id
) OR EXISTS (
-- Check if user has access via institution/course membership
SELECT 1
FROM institution_role ir
JOIN course_element ce ON ir.course_id = ce.course_id
WHERE ce.object_id = '82195a7e-86a2-4235-bcd6-126492273e64' -- in_element_id
AND ir.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
) OR EXISTS (
-- Check if user is institution admin for institution which has course
SELECT 1
FROM course_element ce
JOIN institution_course ic ON ic.course_id = ce.course_id
JOIN institution i ON ic.institution_id = i.institution_id
WHERE ce.object_id = '82195a7e-86a2-4235-bcd6-126492273e64' -- in_element_id
AND i.owner_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
)
)
) OR (
'object_delete' = 'object_delete' AND ( -- in_code
SELECT (
-- Check if user has override permissions
user_has_permission('86d09109-64cc-4aa6-9336-b49b8ae71f2c', 'cmd_delete_other') -- in_account_id
)
)
)
)
) OR (
-- Check access for containers
'container' = 'container' AND ( -- in_type
SELECT EXISTS (
-- Owner has all rights
SELECT 1
FROM container c
WHERE c.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b' -- in_element_id
AND c.owner_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
) OR EXISTS (
-- Check container permission table for user
SELECT 1
FROM container_account_permission cap
WHERE cap.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
AND cap.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b' -- in_element_id
AND cap.code = 'container_read' -- in_code
) OR EXISTS (
-- Check container permission table for user's groups
SELECT 1
FROM container_class_permission ccp
JOIN membership m ON ccp.class_id = m.class_id
WHERE m.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
AND ccp.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b' -- in_element_id
AND ccp.code = 'container_read' -- in_code
) OR (
'container_read' = 'container_read' AND ( -- in_code
SELECT (
-- Check if user has override permissions
user_has_permission('86d09109-64cc-4aa6-9336-b49b8ae71f2c', 'cmd_read_container_other') -- in_account_id
) OR EXISTS (
-- Check if user has access via institution/course membership
SELECT 1
FROM institution_role ir
JOIN course_container cc ON ir.course_id = cc.course_id
WHERE cc.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b' -- in_element_id
AND ir.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
) OR EXISTS (
-- Check if user is institution admin for institution which has course
SELECT 1
FROM institution i
JOIN institution_course ic ON ic.institution_id = i.institution_id
JOIN course_container cc ON ic.course_id = cc.course_id
WHERE cc.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b' -- in_element_id
AND i.owner_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
)
)
) OR (
'container_read' = 'container_delete' AND ( -- in_code
SELECT (
-- Check if user has override permissions
user_has_permission('86d09109-64cc-4aa6-9336-b49b8ae71f2c', 'cmd_delete_container_other') -- in_account_id
)
)
) OR (
'container_read' = 'container_delete_child' AND ( -- in_code
SELECT (
-- Check if user has override permissions
user_has_permission('86d09109-64cc-4aa6-9336-b49b8ae71f2c', 'cmd_delete_container_other') -- in_account_id
)
)
)
)
) OR (
-- Don't know how to handle that type
false
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.