Encrypted Column Formula In A View Would've Been Ideal (3 Viewers)

[dalski]

Think of an Orders Table where the Total is calc'd & value is not stored.

I think I'm right in saying "only a column's data can be encrypted in SSMS". So it's not possible to protect your math in calculated fields.

It also seems that an encrypted Stored Procedures weak-spot is the SP needs to be executed each time where you need to deduce values... Not getting the benefit of caching an already calc'd value & the inefficiency in executing the same SP/ variations for where the calc'd values are needed. So you're executing the same thing each time you want a value.

I don't think functions can be encrypted? But these are obviously less efficient than straight SQL calculations used within a Query/ View/ SP.

What to do when you're trying to hide your intellectual property? Are encrypted SP's the only option & the inefficiencies mentioned the only way forward?

 

[CJ_London]

Struggling to understand the point of your question - what has protecting intellectual property to do with calculating an order total? It’s hardly a difficult calculation

And if you are talking protecting stored procedures, protecting from who?

 

[Isaac]

What more specifically do you actually mean when you say calculated Fields? Do you mean columns with a default value? I assume every time you say field you actually mean column to begin with. And when you say calculated do you mean a column that has a default value?

Also have you considered throwing user defined functions into the mix of whatever mental exercise you're going through? Maybe I'm just very tired and need to go to bed. Happy New Year

 

[dalski]

Thank you both, happy new year to you both, sorry I did not explain well.

Struggling to understand the point of your question - what has protecting intellectual property to do with calculating an order total? It’s hardly a difficult calculation

Thanks CJ, agreed, my calc's are more complex but this simplifies the topic & the premise of a protecting the formula of a calc'd field in a query (maybe not as I haven't explained it well it all ).

And if you are talking protecting stored procedures, protecting from who?

From the user/ people whom use my .accde application with SSMS-22 for the BE. I know the vulnerabilities of Access & it's not possible to make it completely secure, but I'm certainly looking to make it as hard as possible.

What more specifically do you actually mean when you say calculated Fields? Do you mean columns with a default value? I assume every time you say field you actually mean column to begin with. And when you say calculated do you mean a column that has a default value?

Sorry Isaac, I was unknowingly using wrong term, a calculated column within a query; not in the table, in the query. As it's calc'd column I handle the default vaule within the calc... & default value is no concern.
If using Access my interpretation is to avoid a calc'd column within a table at all times. These calc's should be done within the query in Access or a user-defined-function (disadvantages with no caching or calc'd result). Moving to SSMS it seems that if you was in Access & wanted to refer to a calculated field in a saved query in the Object Browser in SSMS you create a View with that calc'd column.
View Benefits

• Calculations are initialized once in the view (calc-A) & it's result cached. The several child-calculated columns/ stored-procedures... (calc-B, calc-C...) do not have to deduce (calc-A) again in order for the child-calc's to obtain their value; which requires (calc-A's result)
• Straight-SQL performance benefit using calc in the SQL itself rather than passing a function
• Calculated Field updates automatically; calling SP's/ Triggers from all over the place in a convoluted fashion is not necessary

View Negatives

• Cannot encrypt the actual calculation of the calc'd column. Users can see the complex math behind the calc'd columns. I'm not worried about encrypting the data, I'm worried about encrypting/ hiding the complex math behind the calc'd columns in the view.

Stored Procedure Negatives

• Results are not cached, needs to be run each time you want to deduce a value. Seems you could refer to cached SP results by building another table but this seems silly; no benefit to auto calculation & worse than a calculated column in a table

Function Negatives

• As per SP; results are not cached, needs to be run each time you want to deduce a value; inefficient repeating the calc superfluously
• Calc execution plan itself less efficient jumping out of relevant SQL into a function/ several functions to process the compared to calc'd column within the query itself

Also have you considered throwing user defined functions into the mix of whatever mental exercise you're going through?

I don't think functions can be encrypted? But these are obviously less efficient than straight SQL calculations used within a Query/ View/ SP.

Thanks, Isaac, yes I did pretty much all variations in Access whilst learning the basics. But moving to SSMS to try to protect the application itself & enjoy larger storage capacities & increased accuracy with Decimal data-type & the new requirement to protect the calculations has me questioning what the best route is. I'm glad I moved to SSMS as soon as the control flow/ objects are quite different to Access'.