Hello,
My application will have more than 100 stored procedures and I want to use the "WITH ENCRYPTION" clause so that they are not visible to the my application's customer using EM. I will be writing the stored procedures in VS.Net server explorer to write the
se stored procedures. The problem is that if I add "WITH ENCRYP.." right there while coding the stored procedures then after saving the sto. proc. even I cannot access it, as it is encrypted. I will be using the "Create Script" utility of SQL Server to
create scripts for tables, stored procedures etc and then will execute this script on client's machines. Is there any way I can continue seeing the stored procedure but not the client.
Thanks
On Thu, 20 May 2004 12:56:03 -0700, dev wrote:
>Hello,
>My application will have more than 100 stored procedures and I want to use the "WITH ENCRYPTION" clause so that they are not visible to the my application's customer using EM. I will be writing the stored procedures in VS.Net server explorer to write th
ese stored procedures. The problem is that if I add "WITH ENCRYP.." right there while coding the stored procedures then after saving the sto. proc. even I cannot access it, as it is encrypted. I will be using the "Create Script" utility of SQL Server to
create scripts for tables, stored procedures etc and then will execute this script on client's machines. Is there any way I can continue seeing the stored procedure but not the client.
>Thanks
Hi Dev,
Two options:
1) Store your stored procedures as text files on your computers. Copy and
paste the code into and out of your development tool, unless it provides
load and save facilities (like Query Analyzer does).
2) Don't use encryption on your development database. Reexecute all
procedures with encryption on a seperate database, then ship that database
to your customers.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo,
In the 1st idea, do you mean save in separate .sql files ? The 2nd idea wont work for me I think because I won't be shipping the database, instead I will include the .sql files generated after using "Create Scripts".
dev
Showing posts with label visible. Show all posts
Showing posts with label visible. Show all posts
Thursday, March 22, 2012
Friday, March 9, 2012
Encrpyt
Hi
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
Mex
Hello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>
|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegr oups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||Hi
"Redivivus" wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sql.html#good_practices
HTH
John
|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).
> sry my english is not very good
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegr oups.com...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
Mex
Hello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>
|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegr oups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||Hi
"Redivivus" wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sql.html#good_practices
HTH
John
|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).
> sry my english is not very good
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegr oups.com...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Encrpyt
Hi
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
MexHello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/it.../sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>|||On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
*
> from names
>
Do you really want to return "crap" to some users? I suspect not. I
think your real requirement is to deny user access to data certain
data. You can do that using GRANT / DENY and no encryption is
required.
Encryption is not a substitute for data access control.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi
"Redivivus" wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad co
de
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive...encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sq...#good_practices
HTH
John|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad co
de
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).
> sry my english is not very good
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
MexHello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/it.../sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>|||On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
*
> from names
>
Do you really want to return "crap" to some users? I suspect not. I
think your real requirement is to deny user access to data certain
data. You can do that using GRANT / DENY and no encryption is
required.
Encryption is not a substitute for data access control.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi
"Redivivus" wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad co
de
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive...encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sq...#good_practices
HTH
John|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad co
de
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).
> sry my english is not very good
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Encrpyt
Hi
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
MexHello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>|||On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select *
> from names
>
Do you really want to return "crap" to some users? I suspect not. I
think your real requirement is to deny user access to data certain
data. You can do that using GRANT / DENY and no encryption is
required.
Encryption is not a substitute for data access control.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good :)
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
>> Hi
>> What is the best method to encrypt data in tables?
>> Lets say if special program or user calls select * from names
>> then all data is visible and normal, but all athers get "crap" with
>> select *
>> from names
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi
"Redivivus" wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good :)
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sql.html#good_practices
HTH
John|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).
> sry my english is not very good :)
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
>> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
>> Hi
>> What is the best method to encrypt data in tables?
>> Lets say if special program or user calls select * from names
>> then all data is visible and normal, but all athers get "crap" with
>> select *
>> from names
>>
>> Do you really want to return "crap" to some users? I suspect not. I
>> think your real requirement is to deny user access to data certain
>> data. You can do that using GRANT / DENY and no encryption is
>> required.
>> Encryption is not a substitute for data access control.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
What is the best method to encrypt data in tables?
Lets say if special program or user calls select * from names
then all data is visible and normal, but all athers get "crap" with select *
from names
;)
Best Regards;
MexHello,
Take a look into Symmetric or Asymmetric encryptions in SQL 2005.The below
URL details various encryption methods and algorithems
which is available in SQL Server.
http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx
Thanks
Hari
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uNYS7XSdHHA.4344@.TK2MSFTNGP02.phx.gbl...
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select
> * from names
>
> ;)
> Best Regards;
> Mex
>|||On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
> Hi
> What is the best method to encrypt data in tables?
> Lets say if special program or user calls select * from names
> then all data is visible and normal, but all athers get "crap" with select *
> from names
>
Do you really want to return "crap" to some users? I suspect not. I
think your real requirement is to deny user access to data certain
data. You can do that using GRANT / DENY and no encryption is
required.
Encryption is not a substitute for data access control.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||let me explain what i want
this "encrpyt" function is needed for web application.
lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
and hackers can use sql injection attack
to get data from database.
when data is encrypted even with sqlinjection hackers can't get sensitive
data.
sry my english is not very good :)
Mex
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
>> Hi
>> What is the best method to encrypt data in tables?
>> Lets say if special program or user calls select * from names
>> then all data is visible and normal, but all athers get "crap" with
>> select *
>> from names
> Do you really want to return "crap" to some users? I suspect not. I
> think your real requirement is to deny user access to data certain
> data. You can do that using GRANT / DENY and no encryption is
> required.
> Encryption is not a substitute for data access control.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi
"Redivivus" wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
> sry my english is not very good :)
> Mex
>
Hari has suggested using SQL 2005 encryption features, but you have not said
if you are using SQL 2005!
If you have left yourself vulnerable to SQL Injection attacks then the
hacker may be able to find out what encryption keys are being used or in a
worse case if the victim procedure has opened the keys he may still be able
to see the data through the injection anyhow. This could apply to any method
of server based encryption, you may want to read
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
Part of your solution should be to make sure that all code is reviewed and a
coding standards implemented and maybe justification should be required when
it is necessary to use dynamic SQL. You may want to read the following
http://www.sommarskog.se/dynamic_sql.html#good_practices
HTH
John|||On 2 Apr, 17:01, "Redivivus" <meelis.lil...@.deltmar.ee> wrote:
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad code
> and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
>
You are mistaken. If you allow arbitrary code execution through SQL
injection then anything could be possible within the security context
of the connection. If the authentication layer for your encryption
(password, certificate or some other method) is compromised as a
result then your encryption is worthless. Encryption is no substitute
for controlling data access.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:ew3rnAUdHHA.1080@.TK2MSFTNGP02.phx.gbl...
> let me explain what i want
> this "encrpyt" function is needed for web application.
> lets say, we have a bad/lazy programmer in our comapny;). he writes bad
> code and hackers can use sql injection attack
> to get data from database.
> when data is encrypted even with sqlinjection hackers can't get sensitive
> data.
Sure they can. Because if you have programmers that bad, you'll also do
encryption that poorly.
Ok, granted, I do agree that some encryption is a good idea, but I'd spend
more time with better hiring. :-)
Note, regardless, you still do NOT want to encrypt every column. You really
can't get performance that way. (as in you'll get NONE).
> sry my english is not very good :)
> Mex
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1175523112.383268.101970@.b75g2000hsg.googlegroups.com...
>> On 2 Apr, 13:53, "Meelis Lilbok" <meelis.lil...@.deltmar.ee> wrote:
>> Hi
>> What is the best method to encrypt data in tables?
>> Lets say if special program or user calls select * from names
>> then all data is visible and normal, but all athers get "crap" with
>> select *
>> from names
>>
>> Do you really want to return "crap" to some users? I suspect not. I
>> think your real requirement is to deny user access to data certain
>> data. You can do that using GRANT / DENY and no encryption is
>> required.
>> Encryption is not a substitute for data access control.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Subscribe to:
Posts (Atom)