Apply Default Permissions (or Rights) to Existing Records

Dear Stephen,

To apply the set access rights to the existing records you need to run the following 2 scripts:

CREATE FUNCTION dbo.GetParentAdminUnits
(
  @ContactId UNIQUEIDENTIFIER
)
RETURNS @ResultTable TABLE(
	SysAdminUnitId UNIQUEIDENTIFIER
)
AS
BEGIN
	WITH ParentUnits (ParentId, Id)
	AS (
	SELECT ParentRoleId
			 , Id
	FROM
		dbo.SysAdminUnit sau
	WHERE
		ParentRoleId IS NOT NULL
	UNION ALL
	SELECT sau.ParentRoleId
			 , sau.Id
	FROM
		dbo.SysAdminUnit sau
		INNER JOIN
			ParentUnits pu
			ON (sau.Id = ParentId)
	)
	INSERT INTO @ResultTable
	SELECT DISTINCT ParentId
	FROM
		ParentUnits
	WHERE
		ParentUnits.Id IN (SELECT DISTINCT sau.Id
											 FROM
												 dbo.SysAdminUnit sau
												 INNER JOIN dbo.SysUserInRole suir
													 ON suir.SysRoleId = sau.Id
												 INNER JOIN dbo.SysAdminUnit sau1
													 ON sau1.Id = suir.SysUserId
													 AND sau1.ContactId = @ContactId)
	UNION
	SELECT DISTINCT sau.Id
	FROM
		dbo.SysAdminUnit sau
		INNER JOIN dbo.SysUserInRole suir
			ON suir.SysRoleId = sau.Id
		INNER JOIN dbo.SysAdminUnit sau1
			ON sau1.Id = suir.SysUserId AND sau1.ContactId = @ContactId

	INSERT INTO @ResultTable
	SELECT DISTINCT Id
	FROM
		dbo.SysAdminUnit sau
	WHERE
		sau.ContactId = @ContactId
	RETURN
END
GO

insert into SysUserInRole (SysUserId, SysRoleId) 
select SAU.Id, SAUR.Id 
from SysAdminUnit SAU, SysAdminUnit SAUR
where SAU.SysAdminUnitTypeValue = 4 and SAUR.Name = 'Все сотрудники компании'
and SAU.Id not in (select SysUserId from SysUserInRole where SysRoleId = (select Id from SysAdminUnit where Name = 'Все сотрудники компании'))

DECLARE @Entity VARCHAR(MAX) = 'Order';--Change the object here;
DECLARE @RightSchemaName nvarchar(max) = 'Sys' + @Entity + 'Right';
DECLARE @SQl nvarchar(MAX) = 
'DECLARE @Now DATETIME = getutcdate()
	DECLARE @SchemaUId UNIQUEIDENTIFIER = (SELECT DISTINCT UId FROM dbo.SysSchema ssis WHERE Name = ''' + @Entity + ''' and [ExtendParent] = ''0'');
DELETE ' + @RightSchemaName + ';
  Update ' +@Entity+'
  SET CreatedById = (SELECT id FROM Contact c WHERE c.Name = ''Supervisor'')
  where CreatedById not in (select Id from Contact);
WITH Rights
AS (SELECT DISTINCT
			c.Id RecordId
		, sesrdr.GranteeSysAdminUnitId SysAdminUnitId
		, sesrdr.Position + 1 position
		,' + '''f41e0268-e324-4228-9e9e-5cb7cc906398''' +
' AS source --по умолчанию 
		, sesrdr.Operation Operation
		, sesrdr.RightLevel RightLevel
		FROM
			dbo.' + @Entity +
' c
			CROSS APPLY GetParentAdminUnits(c.CreatedById) psau
			INNER JOIN dbo.SysEntitySchemaRecordDefRight sesrdr
				ON sesrdr.AuthorSysAdminUnitId = psau.SysAdminUnitId
				AND sesrdr.SubjectSchemaUId = @SchemaUId)
INSERT INTO dbo.' + @RightSchemaName +'
	(
	Id
, RecordId
, SysAdminUnitId
, Position
, SourceId
, CreatedOn
, CreatedById
, ModifiedOn
, ModifiedById
, Operation
, RightLevel
	)
SELECT newid()
		 , c.Id
		 , r.SysAdminUnitId
		 , r.position
		 , r.source
		 , @Now
		 , c.CreatedById
		 , @Now
		 , c.ModifiedById
		 , r.Operation
		 , r.RightLevel
FROM
	Rights r
	INNER JOIN dbo.' + @Entity +
' c
		ON c.Id = r.RecordId
--права создателя
INSERT INTO dbo.' + @RightSchemaName +'
		(
		Id
	, RecordId
	, SysAdminUnitId
	, Position
	, SourceId
	, CreatedOn
	, CreatedById
	, ModifiedOn
	, ModifiedById
	, Operation
	, RightLevel
		)
	SELECT newid() AS Id
			 , c.Id RecordId
			 , sau.Id
			 , 0
			 ,' + '''66ea17f7-df1d-4058-91ca-09a2057deae8''' + ' --владелец
			 , @Now
			 , c.CreatedById
			 , @Now
			 , c.ModifiedById
			 , operation.num
			 , 2
	FROM
		dbo.' + @Entity + ' c CROSS JOIN (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 ) AS operation
		INNER JOIN dbo.SysAdminUnit sau
			ON sau.ContactId = c.CreatedById;
'
  --PRINT @SQl
  EXEC (@SQl);

Lisa

Lisa,

Thank you for the response.  The script appears to be the same from the first posting in 2014.  The table "SysOfferingRight" is not in database instance.  Is the "Offering" just an example?  Also the table "SysSchemaInSolution" also does not exist.  Finally, what is the static GUID referencing? Should they be dynamic per the instance or user or other?  So I am not sure this is the valid script.

 Additionally, I would prefer not to guess on the objects.  Can the script go through all the necessary objects:  Accounts, Contacts, Leads etc. ?  I can write/modify the script myself, but I am not sure of the scope of objects.

Steve

Dear Stephen,

The 'Offering' is just an example.

Please try to use the following script substituting 'Activity' with the table name you need. 

DECLARE @TableName NVARCHAR(250)
SET @TableName = 'Activity' 
PRINT 'Start process ' + @TableName
EXEC ('
DELETE FROM Sys' + @TableName + 'Right;
INSERT INTO Sys' + @TableName + 'Right (CreatedOn, ModifiedOn, RecordId,
    SysAdminUnitId, Operation, RightLevel, Position, SourceId)
SELECT GETUTCDATE(), GETUTCDATE(), o.Id, au.Id, op.Id, 2, 0, ''{4220CFBA-0514-44CE-ADD0-109B54B52084}'' 
FROM ' + @TableName + ' o
JOIN SysAdminUnit au ON (au.ContactId = o.CreatedById)
CROSS JOIN (SELECT 0 Id UNION ALL SELECT 1 UNION ALL SELECT 2) op 
WHERE NOT EXISTS(SELECT * FROM Sys' + @TableName + 'Right WHERE RecordId = o.Id AND 
    SysAdminUnitId = au.Id AND Operation = op.Id);
INSERT INTO Sys' + @TableName + 'Right (CreatedOn, ModifiedOn, RecordId,
    SysAdminUnitId, Operation, RightLevel, Position, SourceId)
SELECT DISTINCT GETUTCDATE(), GETUTCDATE(), o.Id, dr.GranteeSysAdminUnitId, 
    dr.Operation, dr.RightLevel, 0, ''{F41E0268-E324-4228-9E9E-5CB7CC906398}''
FROM ' + @TableName + ' o
JOIN SysAdminUnit au ON (au.ContactId = o.CreatedById)
LEFT JOIN SysUserInRole uir ON (uir.SysUserId = au.Id)
JOIN SysEntitySchemaRecordDefRight dr ON (dr.AuthorSysAdminUnitId = uir.SysRoleId OR dr.AuthorSysAdminUnitId = au.Id OR
dr.AuthorSysAdminUnitId IN (SELECT Id FROM SysAdminUnit WHERE ParentRoleId IS NULL))
WHERE dr.SubjectSchemaUId IN 
       (SELECT UId FROM SysSchema WHERE [Name] = ''' + @TableName + ''') AND
    NOT EXISTS(SELECT * FROM Sys' + @TableName + 'Right WHERE RecordId = o.Id AND 
        SysAdminUnitId = dr.GranteeSysAdminUnitId AND Operation = dr.Operation);
')
PRINT @TableName + ' was processed'

For example, if you changed the access rights for the Accounts section, you need to put 'Account' instead of 'Activity'. If you want to do the same for the Leads section records, please put 'Lead'.

If this script doesn't work properly, please let us know.

Regards,

Lily

Regarding the two static GUIDs being used in the script, they appear to reference dbo.SysEntitySchemaRecRightSource for Author and Default sources recspectively corrrect?  No Owner references reapplied.

Regarding scope, would the following list of 117 tables all be eligible for this script?

select left(right(name,len(name)-3),len(name) - 8) as TargetObject_Table, name as TargetObject_RightTable

from sys.tables

where name like 'sys%right'

Hello Stephen,

The scripts apply the access rights that are set in the system at the moment you run the script. If you configure the system to provide the Owner the access rights, then after running the script, the owners of the exisitng records would receive the same rights as well. 

Regarding scope, you were looking for all the tables that have 'Sys' and 'Right' in their name. This way you receive the additional tables that you cannot distribute access rights to. You need to look for the tables that has 'Sys' + object name + 'right' name. There will be less that 117 and all of them can be used in the rights distribution scripts. 

Lisa,

If the script does apply the currently configured access rights to the existing records, that's great.  However, the script does not contain an Owner GUID to recreate/repopluate the rights into the "rights" table.  I don't follow how the Owner would receive the same rights with only the Author and Default rights being repopulated via the hard coded GUIDs shown in this script.  Perhaps I am confusing source of rights vs user's provide rights? Are you saying the Default sourced rights provide the Owner (of the object/record) their access rights?  

Back to the scope issue to identify eligible objects...I agree not all the 117 tables may be eligiblie and a true list of objects are a subset of these 117 tables.  So my question is how to identify the correct list of tables besides just using this pattern.  I was hoping bpm online documented these or someone has done this before to ensure all emcompassing objects.  If no one knows....I can cerntainly take an educated guess for all the objects.  Objects like Account, Contact, Lead are easy...but the others may not,,,,which is my inquiry.  I don't want omit any.

Dear Stephen,

To apply the access rights provided to Owner to the existing records, you need to modify the script above a little. You should replace 'CreatedById' by 'OwnerId' like this:

JOIN SysAdminUnit au ON (au.ContactId = o.OwnerId)

As for the list of all the objects you may apply the rights for, at the moment there is no document that contains them but I have already passed the suggestion to add one to our Academy/SDK team.

Lisa

Lisa & Lily,

Thanks for your help as it has given me something to go on.  The script posted earlier seems reversed regarding sources and did not replicate the same records (before & after).  I used SQL Trace to review the actual SQL statements being executed for adding say an Account record and then changing an owner.  From this I created my script to reproduce the same results.   Attached is my script that you can review, run and provide feedback.  It would helpful to confirm the logic, eligible tables and eligible table for owner rights.

Summary

• The script creates the rights records for Default, Author and Owner and focuses around sources of Default Permissions to provide rights to each target object table.
• The script does not remove Default or Author rights if the object record is orphaned (due no FKs, the contact that created the object record no longer exists) see Considerations.
• This a MSSQL TSQL script to be run on the backend of the database by a DBA or database developer.
• The script was created by reviewing and leveraging the SQL Trace of the bpm online platform v7.10 when making changes via the application.
• The script uses transitions and commits after each object.
• Additional testing recommended. Tested by comparing before and after of an Account sample record (rights created via the application vs rights created via this script).
• There are scripts to use for reviewing and creating backup of tables.  These two subscripts are scripted only and DO NOT run automatically with the actual DML scripts.

Options

• The script can target a single table object as well as a single record from that object.
• The script can either print out the "Rights" scripts, execute the "Rights" scripts or both.
• The script can remove all rights or all but the manual sourced rights.

Considerations for Changes

• Resolving orphaned records can be solved with additional INSERT script not implemented here.  The script does not remove rights from orphaned object records.
• It is possible to leverage use of batches to help will large sets of data, script not implemented here.
• The existence of multiple users using a single ContactId may exist in the database especially new install or demo databases (The database allows this data state as there is no unique key constraint) but appears not allowed via the application. For now, this script uses a best case to pick one user ContactId
• The eligible object tables are still unconfirmed, but the best guesses are listed in the script based on naming convention and relevance.  The script will not execute on objects outside of this list.
• Not all table objects may require the owner rights to be created even though the object has an OwnerId field.  e.g. Account has owner rights, but Lead may not or SocialSubscription.  Currently will create owner rights if OwnerId field exists.  There is a way to control this specifically for each object table if needed.
• This script could be converted to a stored procedure.

Steve

Sorry...the files did not attached like I thought

1. Script file:  bpmonline710_changerights_marketnet_v2.txt
2. Trace file: bpmonline710_righttrace.txt

UPDATE:  I found the object property that triggers the creation or setting of Owner default permissions.  The property can be changed to enable the system to create Owner default permissions (see thread https://community.bpmonline.com/discussion/10192 or https://community.bpmonline.com/discussion/10839).  For example, Accounts have owner permissions created when the owner is set or changed.  Leads do not have owner permissions set when setting an owner.  This appears to be the default setup for a new instance of bpm’online.  So the attached script in this thread (bpmonline710_changerights_marketnet_v2.txt) would require to have some object table owner rights suppressed within the script.  The logic is already in place, just not the specific objects names to target.  So depending on your configuration, specify the object tables to recreate owner permissions or not.

By default, these objects have the OwnerId field AND get set owner permissions by default:

Account, Activity, Contact, Document, Invoice, Opportunity, Order, Product

By default, these objects have the OwnerId field and DO NOT get set owner permissions by default:

BulkEmail, BulkEmailSplit, Campaign, CampaignPlanner, Case, ConfItem, Contract, Event, Lead, MktgActivity, Problem, Project, ServiceItem, ServicePact

Dear Stephen,

Thank you for sharing the information with us and the colleagues of yours. It's much appreciated.

Lisa

I have created and tested an updated version of my TSQL script to fix, create and apply rights to existing records.  This resolves this issue in that changes to security settings don’t apply to existing records.  This script fixes rights for one or all objects or one or all object records after you change security settings with existing data.  The script optionally leaves alone manual provided rights.  You can print out the dynamic scripts and/or execute them.

My script is recommended for new installations or instances with significant security changes.  Interestingly demos or new installations with preloaded data have inconsistences between right data and security configurations.  I thank Lisa and Lily from bpm online support for their help as their script was a good starting point.  However, I would not recommend using the script they posted as it deletes all rights, does not handle owner rights, does not look at the acatual secruity settings for correct RightLevels, does no target the correct schemas, and appears to have flipped sources for default and author rights.  For those reasons, I could not reproduce the application generated right data with their script.  You will find my attached script resolving all those issues to reproduce the applicaiton rights properly.  

I hope this script becomes of good use for everyone.  If you have questions, corrections, or improvements, feel  free to contact me.

The script file:  bpmonline710_applyrights_existingrecords_marketnet_v3.zip

Steve