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

Typically you only setup permissions in the development environment. When you deploy, you always deploy those permissions. So, when you deploy via DDT, you need to make sure you deploy the permission data with it. The trick is to setup the deployment so that it leaves existing data alone and only adds new permissions or updates existing permissions. When you setup a data deployment package, on the data tab you can setup what gets extracted and how it gets loaded into the target database.



It should include the correct project ID in the "Select * From SFPermissions Where" textbox. Note that the project ID is only used with the StrataFrame database at design time. This criterion is used to identify what data needs to be extracted from that database (StrataFrame) to be available to be loaded into the target database. Note you could add additional criteria here if needed.



The Merge Type is were you define what to do with the data if the data already exists in the target. For permissions I set it to Overwrite. There are four options:



- ignore: if the target database already has a record with the same PK, ignore the deployed data.

- merge: if the target database already has a record with the same PK, add the deployed data with a new PK. The original in the target plus the deployed record both exist.

- overwrite: if the target database already has a record with the same PK, delete the target database's record and add the deployed record with the same PK.

- only deploy if table is empty: no target data, so dump deployed data into table.



With permissions, I want to ensure that what I'm using to develop is what's available, so overwrite will update existing permissions with any changes (maybe a category change or a name change) plus add any new permissions I've added.



I also tend to deploy roles, as I may be deploying some role that was requested, but it is possible that they've added new roles, renamed existing roles. My app doesn't actually care about roles. When deploying roles I use a merge type of ignore. Any changes they've made to existing roles are left intact, but my new roles are added. Note that if they were creating roles, I might have to set it up to do a merge and then only select my new roles.



I'd use only deploy if table is empty for bootstrap data, like loading an initial user, so they can initially login (assuming the default admin user wasn't desired).



I hope that helps. If not, keep asking!

Hi Greg,



Thanks for the detail explanation, but still here is where I am stock:



I assume that the pm_pk is used here to do Merge, Override, etc. but if this field value is not the same as in my StrataFrame database, then the "Merge" will fail, right?



If I am correct, then the Permission Key field "pm_Key" should be used to properly identify the record and get it updated, right? Also then the data in the table SFSRolesXPermissions will have the wrong information if the rp_pm_pk is not the correct one as all Permissions will need to be re-assigned again.



Please, keep in mind that the pm_pk field value at the customer is not the same as in my StrataFrame database and that this is done by an automated update process I have to update customer's database where I have no access to their computers.



What is the logic used by the Data Deployment to properly identify the record to be deployed?

Edhy Rijo

Well, the normal way is that the pm_PK value does match. As I mentioned, you are the only one who should be making permissions. Permissions tie the security component to the application. I.e. the application must know about the permissions in order for them to any good. This means that when distributing permissions, you are always in control.



Overwrite means that the permissions in the source are deleted and re-added...with the same pm_pk. (Insert identity is turned on). Thus, the role/permission links are still valid.



Does this make sense? I deployed this way about 20 times and never had a problem with roles loosing permissions.

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...

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

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