User Custom Permission

Table

Database	Table	Entity	On Key 5 Conversions	On Key 5 Tables	Other Entities stored in same Table
`Transaction`	`uam.user_custom_permission`	User Custom Permission	ConvertUserCustomPermission	`usrUserCustomSites`

Columns

Property	Db Table	Db Column	Db DataType	Nullable	Primary Key	Joins
`CreatedByUserId`	`uam.user_custom_permission`	`created_by_user_id`	bigint	False
`CreatedByUserCode`	`uam.user`	`code`	character varying(50)	False		`uam.user_custom_permission.created_by_user_id = uam.user.user_id`
`CreatedByUserFullName`	`uam.user`	`full_name`	character varying(200)	False		`uam.user_custom_permission.created_by_user_id = uam.user.user_id`
`CreatedOn`	`uam.user_custom_permission`	`created_on`	timestamp without time zone	False
`ModifiedByUserId`	`uam.user_custom_permission`	`modified_by_user_id`	bigint	False
`ModifiedByUserCode`	`uam.user`	`code`	character varying(50)	False		`uam.user_custom_permission.modified_by_user_id = uam.user.user_id`
`ModifiedByUserFullName`	`uam.user`	`full_name`	character varying(200)	False		`uam.user_custom_permission.modified_by_user_id = uam.user.user_id`
`ModifiedOn`	`uam.user_custom_permission`	`modified_on`	timestamp without time zone	False
`Version`	`uam.user_custom_permission`	`version`	integer	False
`PermissionTreeId`	`uam.user_custom_permission`	`permission_tree_id`	bigint	False
`PermissionTreeCode`	`uam.permission_tree`	`code`	text	False		`uam.user_custom_permission.permission_tree_id = uam.permission_tree.permission_tree_id`
`PermissionTreeDescription`	`uam.permission_tree`	`description`	text	True		`uam.user_custom_permission.permission_tree_id = uam.permission_tree.permission_tree_id`
`Id`	`uam.user_custom_permission`	`user_custom_permission_id`	bigint	False	True
`ApplyDownPermissionTree`	`uam.user_custom_permission`	`apply_down_permission_tree`	boolean	False
`ExcludedProperties`	`uam.user_custom_permission`	`exclude_properties`	text[]	True
`Notes`	`uam.user_custom_permission`	`notes`	text	True
`PermissionId`	`uam.user_custom_permission`	`permission_id`	integer	False
`PermissionsChanged`	`uam.user_custom_permission`	`permissions_changed`	boolean	True
`PermissionTrees`	`uam.user_custom_permission`	`custom_permission_tree_ids`	bigint[]	True
`UserId`	`uam.user_custom_permission`	`user_id`	bigint	False
`UserCode`	`uam.user`	`code`	character varying(50)	False		`uam.user_custom_permission.user_id = uam.user.user_id`
`UserFullName`	`uam.user`	`full_name`	character varying(200)	False		`uam.user_custom_permission.user_id = uam.user.user_id`

Queries

The list of example User Custom Permission queries can be interpreted using the following legend:

Legend	Description
Primary Table Alias	The alias for the `uam.user_custom_permission` table in the query
Include References	Include (`true`) or exclude (`false`) all lookup table joins in the query
Include Permissions	Include (`true`) or exclude (`false`) the permission table join in the query to enforce or skip row-level security based on the permissions for the user

Query 1

Primary Table Alias	Include References	Include Permissions
`_ucp`	False	False

SELECT
    _ucp.created_by_user_id AS "CreatedByUserId",
    _ucp.created_on AS "CreatedOn",
    _ucp.modified_by_user_id AS "ModifiedByUserId",
    _ucp.modified_on AS "ModifiedOn",
    _ucp.version AS "Version",
    _ucp.permission_tree_id AS "PermissionTreeId",
    _ucp.user_custom_permission_id AS "Id",
    _ucp.apply_down_permission_tree AS "ApplyDownPermissionTree",
    _ucp.exclude_properties AS "ExcludedProperties",
    _ucp.notes AS "Notes",
    _ucp.permission_id AS "PermissionId",
    _ucp.permissions_changed AS "PermissionsChanged",
    _ucp.custom_permission_tree_ids AS "PermissionTrees",
    _ucp.user_id AS "UserId"
FROM
    uam.user_custom_permission _ucp

Query 2

Primary Table Alias	Include References	Include Permissions
`_ucp`	True	False

SELECT
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _ucp.created_on AS "CreatedOn",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _ucp.modified_on AS "ModifiedOn",
    _ucp.version AS "Version",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _ucp.user_custom_permission_id AS "Id",
    _ucp.apply_down_permission_tree AS "ApplyDownPermissionTree",
    _ucp.exclude_properties AS "ExcludedProperties",
    _ucp.notes AS "Notes",
    _ucp.permission_id AS "PermissionId",
    _ucp.permissions_changed AS "PermissionsChanged",
    _ucp.custom_permission_tree_ids AS "PermissionTrees",
    _u2.user_id AS "UserId",
    _u2.code AS "UserCode",
    _u2.full_name AS "UserFullName"
FROM
    uam.user_custom_permission _ucp
    JOIN uam.user _u ON _ucp.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _ucp.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _ucp.permission_tree_id = _pt.permission_tree_id
    JOIN uam.user _u2 ON _ucp.user_id = _u2.user_id

Query 3

Primary Table Alias	Include References	Include Permissions
`_ucp`	False	True

Parameter Name	Data Type	Value
`@permission_user_id`	Bigint	`500000000000`
`@permission_id`	Bigint	`10012600`

WITH permission_tree_ids AS
(
    SELECT DISTINCT unnest(psd.permission_tree_ids) AS permission_tree_id FROM uam.user_permission up JOIN uam.permission_set_data psd ON up.permission_set_id = psd.permission_set_id WHERE up.user_id = @permission_user_id AND up.permission_id = @permission_id
)
SELECT
    _ucp.created_by_user_id AS "CreatedByUserId",
    _ucp.created_on AS "CreatedOn",
    _ucp.modified_by_user_id AS "ModifiedByUserId",
    _ucp.modified_on AS "ModifiedOn",
    _ucp.version AS "Version",
    _ucp.permission_tree_id AS "PermissionTreeId",
    _ucp.user_custom_permission_id AS "Id",
    _ucp.apply_down_permission_tree AS "ApplyDownPermissionTree",
    _ucp.exclude_properties AS "ExcludedProperties",
    _ucp.notes AS "Notes",
    _ucp.permission_id AS "PermissionId",
    _ucp.permissions_changed AS "PermissionsChanged",
    _ucp.custom_permission_tree_ids AS "PermissionTrees",
    _ucp.user_id AS "UserId",
    _ucp.permission_tree_id AS "_ucp_permission_tree_id"
FROM
    uam.user_custom_permission _ucp
    JOIN permission_tree_ids permission_join ON _ucp.permission_tree_id = permission_join.permission_tree_id

Query 4

Primary Table Alias	Include References	Include Permissions
`_ucp`	True	True

Parameter Name	Data Type	Value
`@permission_user_id`	Bigint	`500000000000`
`@permission_id`	Bigint	`10012600`

WITH permission_tree_ids AS
(
    SELECT DISTINCT unnest(psd.permission_tree_ids) AS permission_tree_id FROM uam.user_permission up JOIN uam.permission_set_data psd ON up.permission_set_id = psd.permission_set_id WHERE up.user_id = @permission_user_id AND up.permission_id = @permission_id
)
SELECT
    _u.user_id AS "CreatedByUserId",
    _u.code AS "CreatedByUserCode",
    _u.full_name AS "CreatedByUserFullName",
    _ucp.created_on AS "CreatedOn",
    _u1.user_id AS "ModifiedByUserId",
    _u1.code AS "ModifiedByUserCode",
    _u1.full_name AS "ModifiedByUserFullName",
    _ucp.modified_on AS "ModifiedOn",
    _ucp.version AS "Version",
    _pt.permission_tree_id AS "PermissionTreeId",
    _pt.code AS "PermissionTreeCode",
    _pt.description AS "PermissionTreeDescription",
    _ucp.user_custom_permission_id AS "Id",
    _ucp.apply_down_permission_tree AS "ApplyDownPermissionTree",
    _ucp.exclude_properties AS "ExcludedProperties",
    _ucp.notes AS "Notes",
    _ucp.permission_id AS "PermissionId",
    _ucp.permissions_changed AS "PermissionsChanged",
    _ucp.custom_permission_tree_ids AS "PermissionTrees",
    _u2.user_id AS "UserId",
    _u2.code AS "UserCode",
    _u2.full_name AS "UserFullName",
    _ucp.permission_tree_id AS "_ucp_permission_tree_id",
    _u.permission_tree_id AS "_u_permission_tree_id",
    _u1.permission_tree_id AS "_u1_permission_tree_id",
    _u2.permission_tree_id AS "_u2_permission_tree_id"
FROM
    uam.user_custom_permission _ucp
    JOIN permission_tree_ids permission_join ON _ucp.permission_tree_id = permission_join.permission_tree_id
    JOIN uam.user _u ON _ucp.created_by_user_id = _u.user_id
    JOIN uam.user _u1 ON _ucp.modified_by_user_id = _u1.user_id
    JOIN uam.permission_tree _pt ON _ucp.permission_tree_id = _pt.permission_tree_id
    JOIN uam.user _u2 ON _ucp.user_id = _u2.user_id

Table of Contents

User Custom Permission

Table

Columns

Queries

Query 1

Query 2

Query 3

Query 4