I have deployed the RBS to one of my customer and everything is good. Now I am creating more forms and new Permissions and want to update customer with those new permissions and the DDT cannot find the correct permission records at the customer database to be updated.



The problem is that the pm_pk field value at the customer does not match with the pm_pk field value in my StrataFrame database. I believe what happened was that at some point during test, the customer database was TRUNCATEd and then new pm_pk values where auto-generated when security was deployed causing the customer and mine security databases to be out of synch.



So my question is: what is the DDT deployment is using to properly identify the record and do its merge?

In this particular case, for the SFPermissions table, the pm_Key should be the primary field or at least the key field used by the DDT to deploy the data and avoid the problem I am having now. If that is not the case, can you please explain the following:

1.- How is the DDT Data Deployment logic works?

2.- How can I fix my current problem without having to have to recreate all my customer's security configurations for each user?



Thanks!

Edhy Rijo

Hi Greg,

Yes it make sense. I guess I will have to write down all the customer's permission settings assigned, truncate the SFSPermissions, SFSRolesXPermissions and SFSUsersXPermissions, re-deploy those and manually re-assign all permissions. I wish we have a Security Report for this



But before I do that, could you please check with the SF developers to see if they have had this issue and/or have a better suggestion on how I can fix this mess with the pm_pk field value?

if no ideas, then this weekend would be the time for me to do it, since I will have late Saturday and Sunday to manually accomplish this in the proper way to guarantee future updates to the security are properly applied.



Thanks!

Edhy Rijo

Edhy, I guess I'm not understanding the problem. First, do you have some custom security tables and functionality you've built? Do you have a copy of one or more of you customers databases that you've tried this on and it's failed? If so how?



Also, I just double checked how the security tables are setup and deleting a permission does not effect the role-x-permissions or user-x-permission table. The FK to the permission takes no action when you delete the permission in either of these tables (a good thing). Thus here is what happens when you deploy permissions, using the merge type of overwrite:



1. check for any existing permissions with same PK as those being deployed

2. Delete the matches: permissions mapped to roles and/or users are "orphaned" for these matches, i.e. the mapping tables SFSRolesXPermissions and SFSUsersXPermissions now have records that point to permissions that no longer exists (but this is a good thing)

3. Add all the permissions from deployment, using the Set Identity ON SQL command so the permissions are added using the same PK they have in deployment. At this point, the "orphaned" records in the mapping tables are "unorphaned" (er..."adopted") because the only ones deleted were those that were added back in this step. Since they have the same PK as before, all is good in the world.



So, as a result, any changes to existing permissions have been deployed, along with new permissions. Note that if you remove permissions, they aren't touched, as they wouldn't be in the deployed permissions, thus they wouldn't be deleted. You'd likely have to use a custom script to clean those up. Note that leaving them in would do nothing because they (theoretically) wouldn't be used for anything, other than potentially confuse the users.

Following this thread, I noticed that Permission's records in SFSPermissions and SFSRolesXPermissions can be left orphan if the the permission record is deleted from the StrataFrame Security Editor (SE). This is what I did:



1.- Create a new Permission named "Test" in the SF Security Editor.

2.- Create a new DDT package with the RBS Deployment Data using the Overwrite Merge Type.

3.- Deploy the Data Packages.

4.- In my application SE, I can now see the "Test" permission.

4a.- In my application SE I created a new Role "Test Role" and assigned the "Test" permission.

5.- In SF SE I now deleted the "Test" permission.



In the step 5 I expect that when I deploy the DDT package, in my application the "Test" permission will be also deleted, but that is not what is happening. I assume that since the "Test" record was deleted in SF SE, then the DDT will not pick up that record and simply will not do anything at the customer database, if this is the case, how the Permission table at the customer will be keep in sync with mine in the SF table?

Edhy Rijo

At this time is situation is not handled. You are correct that when deployed (via the DDT data package) and using a Merge Type of Overwrite (actually any merge type), any permissions in the application database that aren't in the deployed data package will simply be ignored.



The reason is that currently the DDT really has no way to know why the permission is in the application but not in the deployed package. It could as it is in your case, that the permission was deleted in the SF database but that is not the only case. It could also be that you are simply distributing only new permissions (you can set a filter when creating a package). In this case, we want the permissions to be left alone in the application.



It would be possible to add a switch (a checkbox) that indicated that you are deploying all permissions and would like to clean up any permissions in the application database that aren't in the deployed data, including removing those permissions from existing roles and/or users. However, we'd have to think about this pretty hard. First, I'm not sure this would be a very frequent thing that would happen. Messing with permissions usually involves major changes in the application, as permissions are the link between the RBS and the application, so it likely is pretty infrequent that permissions are deleted, especially from a production app. Second, other than potentially confusing the user, they do no harm being left in. Third, it is certainly possible for the developer to use a post deployment script to clean them up, which would mean that this is a very deliberate act. But the biggest issue is that if the switch was incorrectly used (turned on when it should be off) and the developer was only deploying new/changed permissions, it would royally fubar any deployed applications. I.e. the ramifications of choosing the wrong setting would be really, really bad.



With that said I'll look into this a bit more to see if I'm missing something...

Greg, I agree with you, but as you know while creating an application, a lot of changes can occur, and the fact that the DDT depends on the developer PK value also could cause a mess when choosing the wrong merge type as I did and new PK values where generated. I also understand why the SF team when this route since it does make sense once the whole process is understood, but since the only way to Add/Change/Delete a Permission records is from the developer computer with the SF database, then there should be an option to avoid leaving orphan records which the End User will have access to, and probably assign them incorrectly if the Security Editor allows then to use those orphan records.



Using a Pre/Post script will always work, but the more we can get out the DDT the better, specially if you are a consultant working on new projects all the time

Edhy Rijo

Also, just for clarification, I am looking for the correct Merge Type to use when deploying the RBS data to my customers. When using the DDT wizard, it will set all MergeType to "Overwrite" which may not be the desired case when taking into consideration that the only information the End User (the Customer) cannot change is the "Security Permissions" data, so "Overwrite" will simply wipe out all customization done by the End User.



I am testing with the following Merge Types (see attached image)

Security Roles - Ignore (So I can add some new Roles and be able to deploy those)

Security Permissions - Overwrite (This is a must to keep in sync with development)

Security Users - DeployIfTableEmpty (Should always be done by End User)

Security Users XPermissions - DeployIfTableEmpty (Should always be done by End User)

Security Preferences - DeployIfTableEmpty (Should always be done by End User)

Security Context Restrictions - DeployIfTableEmpty (Should always be done by End User)



I would appreciate if somebody with experience deploying RBS in production database can confirm/correct my assumtions.



Thanks!

Edhy Rijo