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). Can use a join on the PK to obtain the calc'd value (defined in the view).
• 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'.

 

[tvanstiphout]

Replace the word "SSMS" with "SQL Server". The former is an editing tool; the latter is the database engine.

You could put complicated calculations in a .NET assembly, and call it from your sproc or function. See this about assembly security. See my Simil blog page for an example of calling an assembly.

 

[CJ_London]

one of the things I have done in the past is use a python script. The script is encrypted and hidden away in the access front end. It is then decrypted and executed as an when required.. All executed in memory, so no files on disk, even for a second or two. You can use ADO and DAO to communicate with whatever data sources are required - one of my scripts extracts data from an external datasource, does a bit of manipulation and populates an access table for example.

Similar to Tom's suggestion, you could just encrypt the calculation, store it somewhere in your access FE and inject the decrypted calc into your querydef before executing it.

Clearly you need to supply your client with a .accde so they cannot determine the encryption key

Edit: If you want to prevent repeated reruns of the calc on the same data, use a query to append or update the result to a temporary table - include an FK so it can be linked back to the main query

 

[The_Doc_Man]

Unless you are doing some kind of Nobel-prize class of computations, I'm not sure what you are protecting. Surely the formulas you use were derived or obtained directly from a textbook on finance or accounting or whatever? If so, what is there to protect about a formula?

 

[dalski]

put complicated calculations in a .NET assembly, and call it from your sproc or function.

Thanks Tom, oh so an 'Assembly' is a unit of compiled code ready to be executed & can refer to external .dll's which SSMS can execute. I'm researching them now. Sounding more secure.
This addresses security concerns, but I'm failing to see how I could refer to a calc'd column from that without having to execute the function wherever it is required. Maybe my assumption of referring to a calc'd column does not actually bring bring much benefit whatsoever. It will certainly be easier to refer to a single calc'd field; opposed to executing the function each time with several params... then to do the calc required off the back of that each time if that is clear.

Replace the word "SSMS" with "SQL Server". The former is an editing tool; the latter is the database engine.

Thanks, I thought SSMS was relevant due to variations of the SQL language & I'm probably wrong here as I've only just leaned what an Assembly is from you here but pg1 of Google shows only results from SSMS; not SQL.

Clearly you need to supply your client with a .accde so they cannot determine the encryption key

Thanks CJ, I think .accde's won't be as secure soon; see deleted user's thread where a clever guy built early stages of an .accde decompiler for Access.

use a python script... script is encrypted and hidden away in the access front end. It is then decrypted and executed as an when required..

Interesting, presumably some sort of encryption benefit over VBA. But I think this still leaves the problem of a child calculation having to run an unnecessary parent calculation again.

Edit: If you want to prevent repeated reruns of the calc on the same data, use a query to append or update the result to a temporary table - include an FK so it can be linked back to the main query

Thank you for grasping an additional concern. Yes this is looking like the typical scenario of not storing the value here is flat-wrong in my context of having many dependencies on that value. It would be far easier to store the value in the table (well relating to my first var anyways). Still a big problem for several nested calc's futher down the line. It is such a shame SSMS does not have a way to encrypt a view's code.

what is there to protect about a formula?

Certainly the math is not genius level but no they are not in textbooks... I had to figure the logic out myself. You'd probably grasp it in a few hours. Only 1 application out of hundreds provides these kind of features; so I certainly am not looking to expose it.

 

[tvanstiphout]

> without having to execute the function wherever it is required

Perhaps this is an issue of not using precise enough language, but the way I am thinking about it, yes, you have to execute a function if you want its return value.
If it is a deterministic function, then conceivably you can cache the input parameters and the return value, so you can do a lookup rather than a calculation next time you need the same value again.

Keep in mind that modern computers can easily do more than 1 billion (very small) computations per second, so don't get too hung up on performance at the outset. Perhaps you implement a simple Proof of Concept to see what performance will be like.

 

[CJ_London]

From the user/ people whom use my .accde application

won't be as secure soon; see deleted user's thread where a clever guy built early stages of an .accde decompiler for Access

You think they will go to that extent?

 

[tvanstiphout]

You think they will go to that extent?

That was my thought as well. The OP better very clearly define what threads he wants to defend against. Keep an excellent Access developer, putting in an effort of N hours, from accessing the algorithms / encryption keys, or the NSA?

 

[dalski]

You think they will go to that extent?

A user won't but a competitor will (not building a decompiler; but using one).

Perhaps this is an issue of not using precise enough language

Oh definitely, apologies I'm not a developer, just an enthusiastic donkey with a goal.

If it is a deterministic function, then conceivably you can cache the input parameters and the return value, so you can do a lookup rather than a calculation next time you need the same value again.

Thanks Tom, yes aware - but correct me if wrong but a function's contents cannot be encrypted & hidden from a user.
So that suggests to use an 'Assembly' performing the action of the function (would provide better obfuscation)?
Or maybe a Stored Procedure used within a View. Which would get the benefit of being able to refer to a field in the view for it's value but still allow obfuscation. But I'm starting to think that maybe Encrypted Stored Procedures do not provide much encryption at all.

Forgive me, just trying to get an idea of the better route. I will focus less on performance atm & more on security & proof of concept.