By Edhy Rijo - 8/5/2010
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!
|
By Greg McGuffey - 8/5/2010
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! 
|
By Edhy Rijo - 8/5/2010
Hi Greg,
Thanks for the detail explanation, but still here is where I am stock:
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 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?
|
By Greg McGuffey - 8/5/2010
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.
|
By Edhy Rijo - 8/6/2010
Greg McGuffey (08/05/2010)
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!
|
By Greg McGuffey - 8/6/2010
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.
|
By Edhy Rijo - 8/12/2010
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?
|
By Greg McGuffey - 8/12/2010
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...
|
By Edhy Rijo - 8/12/2010
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!
|
By Edhy Rijo - 8/12/2010
Greg McGuffey (08/12/2010)
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.
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 
|
By Dustin Taylor - 8/13/2010
Whoo, long thread  . I'll read it in detail when I get a few free minutes, but just to throw a quick clarification out there, Greg is correct that we don't get heavy handed with the permission deploy since there are quite a few questions as to why the two (DDT definitions and actual records in the database) don't line up. In short, we leave this up to the developer to decide how he wants to handle it.In our medical app, we choose to wipe out the permissions via a post-deployment script before deploying them in the DDT to prevent the issues Edhy has mentioned. Our Post-Deployment script looks like this: ALTER TABLE dbo.SFSRolesXPermissions NOCHECK CONSTRAINT FK_SFSPermissions_SFSRolesXPermissionsALTER TABLE dbo.SFSUsersXPermissions NOCHECK CONSTRAINT FK_SFSPermissions_SFSUsersXPermissionsDELETE FROM SFSPermissionsALTER TABLE dbo.SFSRolesXPermissions CHECK CONSTRAINT FK_SFSPermissions_SFSRolesXPermissionsALTER TABLE dbo.SFSUsersXPermissions CHECK CONSTRAINT FK_SFSPermissions_SFSUsersXPermissionsThis may be something we examine deeper in the future, but as it stands it's fairly straight forward to handle the different scenarios with the available merge types and/or a deployment script. As such we don't want to make any hasty changes that could break the deployments of existing developers
|
By Edhy Rijo - 8/13/2010
Hi Dustin,
Glad to see you back
In deed, what I am looking for is for how SF developers are dealing with these realities. To make story short I am releasing an application and once customers start buying I will not have remote access to some of them after installation and I need to automate the data update process as much as possible without user intervention.
Thanks for the script, I will test it right away, and when you have couple of minutes free, I would appreciate if you can read the whole thread. 
|
By Edhy Rijo - 8/13/2010
Hey Dustin,
One quick clarification if I may ask...
If your Post Script is always resetting the Permissions records then I should assume that the StartDataDeploymentPhase() should always be run to ensure the Deployment Data packages are always deployed not matter what the Deployment Options (Standard, SP-Views-UDF Only) is used, right? Of course this would include all Data Deployment packages defined in the DDT, not only RBS.
Again, in my case all the update is done automatically, so I want to put together all the pieces to make sure this will work.
Thanks!
|
By Edhy Rijo - 8/13/2010
Sorry, this thread keeps getting bigger, but here is another request...
While testing Dustin suggestion, I noticed that the Pre/Post Deploy Script will only run for the Deployment Options of Standard and SP-Views-UDFs Only, when deploy using "Deploy Data Only" the Pre/Post will not run.
Can anybody confirm this? and if that is the case, I guess we may need 2 more hooks Pre/Post Deploy Data Scripts so we can have the hooks needed to deal with this.
Keep in mind that in my case since the DDT does not handle INCLUDE indexes (see post), I have to use a Post-Deploy Script to create those indexes in a table with millions of records which takes from 5 to 25 minutes to complete with each update, by me controlling the process to of how the deployment will be done, I can save customers time, waiting for the update to rebuild those INCLUDE indexes when changes will not affect those big tables using a deployment other than "Standard".
|
By Dustin Taylor - 8/16/2010
Yep, using the post deployment script I posted earlier would obligate you to deploy the permissions every time, otherwise you'll end up with customers that have empty permission tables.As far as adding additional hooks to DDT deployment options, I'll talk to Trent and see if we can add it to the enhancement list. Since workarounds exist here (handling the table clear outside of the DDT, doing full/stored procedure deployment so that the scripts get fired, etc.) this isn't mission-critical, so it may not make it in until the next time we are going through the DDT.
|
By Edhy Rijo - 8/16/2010
Dustin Taylor (08/16/2010)
... As far as adding additional hooks to DDT deployment options, I'll talk to Trent and see if we can add it to the enhancement list. Since workarounds exist here (handling the table clear outside of the DDT, doing full/stored procedure deployment so that the scripts get fired, etc.) this isn't mission-critical, so it may not make it in until the next time we are going through the DDT.
Thanks for the explanation Dustin. Even though this may not look as mission-critical, the whole idea is to use the DDT to take care of this kind of things instead of having many stored procedures all over. Hooks can be added to allow us to control this kind of situation. I hope you guys can take the time to review this and other DDT enhancement request to make the DDT more powerful and flexible for us  .
In the mean time as suggested, I will create a SP to try to handle this gracefully for the end user.
|
By Dustin Taylor - 8/26/2010
Edhy, You may want to take a look at the 1.7.3 version. We added a few hooks and whistles to the data deployment options that could make some of the above work more smoothly for you .
|
By Edhy Rijo - 8/26/2010
Hi Dustin,
Thanks for the information. I am already looking at those to incorporate then in my automatic logic, will post results later today or tomorrow morning after testing those.
|
By Alex Luyando - 11/1/2010
Long, but GOOD thread!
So as Edhy stated, you can end up with orphaned settings. Just today I found I could not access a business object due to the permission key value on the BO having been deprecated in SF security a while back. Question: What is the quickest way to find all of these situations (i.e., without going object-by-object to inspect the assigned permission key).
TIA
|
By Ivan George Borges - 11/1/2010
Hi Alex.
If you knew the keys, you could do a "find" on the solution on those keys and check where they were used, but I guess this is not the case, right?
Maybe doing the same to find the "SecurityKey" string would probably take you to most places where you have set one.
|
By Alex Luyando - 11/3/2010
Hi Ivan.
I was actually hoping that there was a utility or similar that would show assigned permission keys that do not actually exist in the security database, as any such forms, BOs, etc. would definitely not be accessible in these cases. Guess we'll just find those out when users get access denied! lol
Thanks.
|
By Ivan George Borges - 11/3/2010
Yeah, that is another way to find it out!
|
|