| Author |
Topic |
|
AAAV
Posting Yak Master
152 Posts |
 Posted - 2010-03-08 : 10:59:20
|
| how efficient it is to encrypt stored procedures and functions and what is the best way to do it?any ideas on how to maintain the script if i encrypt? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-08 : 11:01:11
|
| we encrypt some of sensitive procedures and the code is maintained in version control system with access given only to priviledged users------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-08 : 11:02:11
|
| with encryption is what you use when you define the procedure or how do you do it?and how do you give access to specified clients? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-08 : 11:07:43
|
| yup..with encryption..access is given using GRANt statement.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-09 : 09:12:55
|
| Can you give samples?Further if we give the whole DB to few selected client will the encryption serve the purpose? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-09 : 09:42:05
|
quote:
Originally posted by AAAV
Can you give samples?
See SQL Documentation under CREATE PROCEDUREquote:
Further if we give the whole DB to few selected client will the encryption serve the purpose?
I doubt it. The encryption is very weak. It will prevent casual viewing of source code, but won't protect your Intellectual Property from someone intent on stealing it.For us its main use is to prevent naive people doing "Right Click : Edit : Save" with some well meaning correction but that a) usually breaks something and b) even if it works doesn't then get into the revision control etc. |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-09 : 10:50:18
|
| so what do you do if you want the user not to see your procedure ?How do you license the user to use the DB and not see/change it? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-09 : 11:04:50
|
| Host the server yourself and do not provide access for the client (well, you can provide restricted access, but not SysAdmin.Take care of it in the licence agreement with the client, and make them responsible for any theft / breach. |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-10 : 13:00:47
|
| ok...Now if i encrypt the procedure to stop the casual user to edit /view it.Is it possible for me to write one procedure/script which encrypts the 100s of other procedures?I will do it everytime i send it to the client. I dont want it to be decrypted on my dev machine since i dont have any version control.I dont want to encrypt in my db since there will be lot of editions going on. Just for caution... if i encrypt a procedure and need it back is there any way for me to recover it as sysadmin?Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-10 : 13:44:36
|
We use a comment and "uncomment" it in the release script.CREATE PROCEDURE MyProc@Param1 varchar(10)/** WITH ENCRYPTION **/AS... and then change the "/** WITH ENCRYPTION **/" globally in the script we use for Production/Client databases.We store ALL our sprocs in files on disk. We never use Right-Click Edit to change them. The Disk files are stored in a revision control system - so we have that benefit too.Given that encryption is not strong, yes you could de-encrypt them yourself. It is not something that is permitted discussion on SQL Team forums, but you'll find all the information you need in Google!However, my advice would be to avoid that scenario - by storing the Sproc source code in disk files, rather than only in the DB. |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-11 : 14:51:08
|
| I dont have a source control. I will store a copy of the script in the backup. Is it possible to write a script to encrypt the procedures/functions in the db in one shot. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-11 : 15:11:47
|
| Can;t you just globally Find & Replace in the script you use on your Client's DB?? |
|
|
|
|
encrypting stored procedures