Solved

Automatically deploy Object Security Setup after changing/deploying a view


Userlevel 3
Badge +4

Hi community,

A customer is using a specific role that is allowed to select data from specific views in SQL. When one of these views are deployed, the Object Security Setup  isn't deployed. The Database Role/Security Settings needs to be redeployed manually.

Is it possible to automatically deploy the Object Security Setup, if the a view/table is deployed that is using security settings? 

 

icon

Best answer by Christian Hauggaard 1 May 2023, 12:59

View original

10 replies

Userlevel 6
Badge +7

@bas.hopstaken ,

 

as far as I know you cannot, but it would be very useful in combination with Semantic Models as well.

Userlevel 6
Badge +5

@bas.hopstaken does the issue still occur if you do a differential deployment of the whole data warehouse? can you please share a recording of the issue? If you do not wish to share publicly, can you please send the recording in a private message to me or to support@timextender.com

I have the same issue - security has been applied to a view which then loses that security when using the migration tool from our DevTest environment to Prod.

 

I do a differential deployment so only the changes are promoted and the security still drops off so has to be manually deployed in Production.

Userlevel 6
Badge +5

HI @bas.hopstaken 

When I do differential deployment on the Data Warehouse after changing a custom view and I review tasks, I see that TimeXtender recognizes that the view and the security role related to the view needs to be redeployed. Using the review tasks functionality you can exclude any deployment tasks that you are not ready to deploy yet. Does this solve the issue? 

@Matt Thomas when you do the differential deployment after the promote do you do it for the whole project?

 

 

@Christian Hauggaard I do a transfer using the ‘Multiple Environment Transfer’ wizard:

Then I do a Deploy using the wizard:
 

 

Then I choose ‘Managed Deployment’ and ‘Differential’:
 

 

Which are then deployed (this is the last step in the Wizard):

 

And in doing these screen snips, the security was successfully deployed into Prod.  ie; it did not get dropped.  I tested by not changing the view with role security on it and then promoting to Prod.  I then tested by changing the view and promoting to Prod.  In both cases, the security was applied automatically so I didn't have to redeploy the security in Prod.

 

Sorry about this - I don’t know what was happening when security wasn’t applied in Prod when promoting from DevTest.  The only thing I can think of was the security not being applied in DevTest which was then promoted to Prod meaning the view in Prod didn’t have security applied.

Userlevel 3
Badge +4

Hi @Christian Hauggaard ,

I've send you a short screen capture video (private message). This video shows that the Object Level Security isn't in the list of deployment tasks after changing a customer view that has Object Level Security applied.

 

 

Userlevel 6
Badge +5

Hi @bas.hopstaken 

Thanks for the recording. As stated above, if you change and deploy a view the object security will not automatically be deployed as well. However, if you do a differential deployment of the data warehouse it will recognize that the object security must be redeployed due to the changed view. Does this answer your question?

 

Userlevel 3
Badge +4

Hi @Christian Hauggaard ,

 

Deploying only the modified object for the Data Warehouse did the trick.
I will mark the correct answer. 

Badge

Hi @Christian Hauggaard 

The suggested solution is to use 'Review Tasks’ to deploy the changed table/View together with security. But than you have to deselect ALL other tables and views you do not want to deploy/execute.

This is not a workable solution when you have hundreds of tables you need to deselect each time you need to deploy a table with security. It is also not directly clear for developers which tables are used in security rules. This would mean that every time you change a table or a view you have to do a differential deployment on data warehouse level.

Please make sure that table/view security is automatically deployed when a table and/or view is changed. Make sure that users don't have to manually add security to the production environment each time after a environment transfer.

Userlevel 6
Badge +5

Hi @coen.donders 

Since you do a differential deployment on the data warehouse level, only the tables that have been modified since the last deploy will be deployed. So it should not be necessary to deselect hundreds of tables, unless you have made changes to hundreds of tables since the last deployment. When you transfer the project to production, typically a differential deployment is done for the whole project, which will mean the security role is also redeployed. In this case, security roles in production should be maintained correctly without manual intervention. For proposed changes to the current functionality, please submit an idea here

Reply