T-SQL問題解決まとめ——データの復号化
出典:CSDNフォーラムDBA_Huangzj
問題1:データの暗号化と復号化をどのように行い、使用者が機密データを盗むことを避けるか.パスワード、カード番号などの機密データについては、通常の数値では格納できません.安全上の危険があります.従来の暗号解読には、完了を支援するフロントエンドアプリケーションがあります.データベースは一般的に暗号化のみで復号できません.
2005年からデータベースレベルのデータ暗号化と復号化が提供されている.その実現方式は主に以下の通りである.
1、CONVERTを利用して符号化方式を変更する:
この関数で文字やデータをVARBINARYに変換します.しかし、この方式はデータを保護する能力を備えておらず、データを閲覧する過程で敏感なデータを直接見ることができる役割を避けるだけだ.
2、対称鍵を利用する:
EncryptByKeyと組み合わせてデータ暗号化を行います.DecryptByKey関数を使用して復号化します.この方式はビッグデータ量に適している.対称鍵のプロセスはリソースが少ないためです.
3、非対称鍵の利用:
EncryptByAsymKeyと組み合わせてデータ暗号化を行います.DecryptByAsymKey関数を使用して復号化します.より高いセキュリティ・レベルの復号化データに使用されます.リソースの消費量が多いからです.
4、証憑の利用方法:
EncryptByCertと組み合わせて暗号化し、DecryptByCert関数で復号化します.非対称鍵と比較します.
5、パスワードフレーズ方式を利用する:
EncryptBypassPhraseと組み合わせて暗号化し、DecryptByPassPhrase関数を使用して復号します.意味のあるフレーズや他のデータ行を使用して、暗号化、復号化のキーワードとして使用することができ、一般的なデータの復号化に適しています.
ケース:
SQL code
問題2:移行による機密情報の露出を避けるために、データベース・オブジェクト定義をどのように保護するか.
一般的な保護策は、オブジェクトの作成時にWITH ENCRYPTIONを使用してオブジェクトを暗号化することで、定義を表示できません.しかし、問題はメンテナンスに問題があり、バックアップのリストア時にこのオブジェクトが失われることです.
解決策の1つは、暗号化前に定義文をオブジェクトの「拡張属性」に保存することで、上記の問題を解決できます.
次に例を示します.
SQL code
問題1:データの暗号化と復号化をどのように行い、使用者が機密データを盗むことを避けるか.パスワード、カード番号などの機密データについては、通常の数値では格納できません.安全上の危険があります.従来の暗号解読には、完了を支援するフロントエンドアプリケーションがあります.データベースは一般的に暗号化のみで復号できません.
2005年からデータベースレベルのデータ暗号化と復号化が提供されている.その実現方式は主に以下の通りである.
1、CONVERTを利用して符号化方式を変更する:
この関数で文字やデータをVARBINARYに変換します.しかし、この方式はデータを保護する能力を備えておらず、データを閲覧する過程で敏感なデータを直接見ることができる役割を避けるだけだ.
2、対称鍵を利用する:
EncryptByKeyと組み合わせてデータ暗号化を行います.DecryptByKey関数を使用して復号化します.この方式はビッグデータ量に適している.対称鍵のプロセスはリソースが少ないためです.
3、非対称鍵の利用:
EncryptByAsymKeyと組み合わせてデータ暗号化を行います.DecryptByAsymKey関数を使用して復号化します.より高いセキュリティ・レベルの復号化データに使用されます.リソースの消費量が多いからです.
4、証憑の利用方法:
EncryptByCertと組み合わせて暗号化し、DecryptByCert関数で復号化します.非対称鍵と比較します.
5、パスワードフレーズ方式を利用する:
EncryptBypassPhraseと組み合わせて暗号化し、DecryptByPassPhrase関数を使用して復号します.意味のあるフレーズや他のデータ行を使用して、暗号化、復号化のキーワードとして使用することができ、一般的なデータの復号化に適しています.
ケース:
SQL code
1
、 Convert ： a)
USE
tempdb b)
GO
c)
CREATE
TABLE
test d) ( e) userID
INT
IDENTITY
(
1
,
1
) , f) userName
VARCHAR
(
10
) , g) userSalary
FLOAT
, h) cyberalary
NVARCHAR
(
MAX
) i) ) ; j) k)
INSERT
INTO
TEST l) ( userName, userSalary ) m)
VALUES
(
'
taici
'
,
1234
), n) (
'
hailong
'
,
3214
), o) (
'
meiyuan
'
,
1111
) p)
--
ALTER TABLE test
q)
--
ADD userNewSalary VARBINARY(512)
r)
--
varbinary， 。
s)
SELECT
*
, t)
CONVERT
(
VARBINARY
(
512
), userSalary) u)
FROM
test v)
--
int，
w)
SELECT
*
, x)
CONVERT
(
INT
, userSalary) y)
FROM
test
2
、 : a)
--
b)
USE
AdventureWorks c)
GO
d)
CREATE
SYMMETRIC
KEY
SymKey123 e)
WITH
ALGORITHM
=
TRIPLE_DES ENCRYPTION
BY
PASSWORD
=
'
P@ssw0rd
'
f)
GO
g)
--
: , OPEN SYMMETRIC KEY ， null 。 Key_GUID
h)
--
i)
OPEN
SYMMETRIC
KEY
SymKey123 DECRYPTION
BY
PASSWORD
=
'
P@ssw0rd
'
; j)
--
k)
SELECT
*
,ENCRYPTBYKEY(KEY_GUID(
'
SymKey123
'
),
CONVERT
(
VARCHAR
(
max
),AddressLine1)) l)
FROM
Person.Address m) r)
--
s)
UPDATE
Person.Address t)
SET
AddressLine2
=
ENCRYPTBYKEY(KEY_GUID(
'
SymKey123
'
),
CONVERT
(
VARCHAR
(
max
),AddressLine1)) u)
--
: OPEN SYMMETRIC KEY , DECRYPTBYKEY CONVERT
v)
OPEN
SYMMETRIC
KEY
SymKey123 DECRYPTION
BY
PASSWORD
=
'
P@ssw0rd
'
; w) x)
SELECT
AddressID,
CONVERT
(
VARCHAR
(
MAX
) ,
CONVERT
(
VARCHAR
(
MAX
),DECRYPTBYKEY(AddressLine2))) y)
FROM
Person.Address
3
、 ： a)
--
， ，
b)
USE
AdventureWorks c)
GO
d)
CREATE
ASYMMETRIC
KEY
AsymKey123
WITH
ALGORITHM
=
RSA_2048 ENCRYPTION
BY
PASSWORD
=
'
P@ssw0rd
'
; e)
GO
f) g)
--
h)
ALTER
TABLE
Person.Address
ADD
AddressLine3
nvarchar
(
MAX
) i)
GO
j)
--
k)
SELECT
*
,ENCRYPTBYASYMKEY(ASYMKEY_ID (
'
AsymKey123
'
),
CONVERT
(
VARCHAR
(
MAX
),AddressLine1)) l)
FROM
Person.Address m)
GO
n) o)
--
p)
UPDATE
Person.Address q)
SET
AddressLine3
=
ENCRYPTBYASYMKEY(ASYMKEY_ID (
'
AsymKey123
'
),
CONVERT
(
VARCHAR
(
MAX
),AddressLine1)) r) s) t)
SELECT
*
--
addressline3
u)
FROM
Person.Address v) w)
--
： ， ， varchar, nvarchar 。
x)
SELECT
TOP
10
AddressID,
CONVERT
(
VARCHAR
(
MAX
),
CONVERT
(
VARCHAR
(
MAX
),DECRYPTBYASYMKEY(ASYMKEY_ID(
'
AsymKey123
'
),AddressLine3,N
'
P@ssw0rd
'
)))
AS
Decryptedata y)
FROM
Person.Address
4
、 ： a)
--
： （certificate）
b)
CREATE
CERTIFICATE certKey123
--
c) ENCRYPTION
BY
PASSWORD
=
'
P@ssw0rd
'
--
d)
WITH
SUBJECT
=
'
Address Certificate
'
,
--
e) START_DATE
=
'
2012/06/18
'
,
--
f) EXPIRY_DATE
=
'
2013/06/18
'
;
--
g)
GO
h)
--
i)
SELECT
*
,ENCRYPTBYCERT(CERT_ID(
'
certKey123
'
),
CONVERT
(
VARCHAR
(
MAX
),AddressLine1)) cyberAddress j)
FROM
Person.Address k) l)
--
m)
ALTER
TABLE
Person.Address
ADD
AddressLine4
Nvarchar
(
MAX
) n) o)
--
p)
UPDATE
Person.Address q)
SET
AddressLine4
=
ENCRYPTBYCERT(CERT_ID(
'
certKey123
'
),
CONVERT
(
VARCHAR
(
MAX
),AddressLine1)) r) s)
--
t)
SELECT
AddressID,
CONVERT
(
VARCHAR
(
MAX
),
CONVERT
(
VARCHAR
(
MAX
),DECRYPTBYCERT(CERT_ID(
'
certKey123
'
),AddressLine4,N
'
P@ssw0rd
'
))) DecryAddress u)
FROM
Person.Address
5
、 ： a)
--
： ， EncryptByPassPhrase ， ， ， 。
b)
SELECT
*
,AddressLine5
=
ENCRYPTBYPASSPHRASE(
'
P@ssw0rd
'
,
CONVERT
(
varbinary
,AddressLine1),AddressID) c)
FROM
Person.Address d) e)
--
， ，ENCRYPTBYPASSPHRASE VARBINARY
f)
ALTER
TABLE
Person.Address
ADD
AddressLine5
VARBINARY
(
256
) g) h)
--
， P@ssw0rd AddressID
i) j)
UPDATE
Person.Address k)
SET
AddressLine5
=
ENCRYPTBYPASSPHRASE(
'
P@ssw0rd
'
,
CONVERT
(
varbinary
,AddressLine1),AddressID) l) m)
SELECT
*
FROM
Person.Address
問題2:移行による機密情報の露出を避けるために、データベース・オブジェクト定義をどのように保護するか.
一般的な保護策は、オブジェクトの作成時にWITH ENCRYPTIONを使用してオブジェクトを暗号化することで、定義を表示できません.しかし、問題はメンテナンスに問題があり、バックアップのリストア時にこのオブジェクトが失われることです.
解決策の1つは、暗号化前に定義文をオブジェクトの「拡張属性」に保存することで、上記の問題を解決できます.
次に例を示します.
SQL code
--
1、
USE
AdventureWorks
GO
CREATE
PROC
test
WITH
ENCRYPTION
AS
SELECT
SUSER_SNAME
() ,
USER_NAME
()
GO
--
2、 ， EncryptByPassPhrase ， sys.sp_addextendedproperty ， 。
USE
AdventureWorks
GO
DECLARE
@sql
VARCHAR
(
MAX
)
SET
@sql
=
'
CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO
'
--
3、 sql_variant
DECLARE
@bsql
SQL_VARIANT
SET
@bsql
=
(
SELECT
CONVERT
(SQL_VARIANT, ENCRYPTBYPASSPHRASE(
'
P@ssw0rd
'
,
CONVERT
(
VARCHAR
(
MAX
),
@sql
))) )
--
4、 ：
EXEC
sys.sp_addextendedproperty
@name
=
N
'
test
'
,
@value
=
N
'
System.Byte[]
'
,
@level0type
=
N
'
SCHEMA
'
,
@level0name
=
N
'
dbo
'
,
@level1type
=
N
'
PROCEDURE
'
,
@level1name
=
N
'
test
'
GO
EXEC
sys.sp_addextendedproperty
@name
=
N
'
'
,
@value
=
N
'
CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO
'
,
@level0type
=
N
'
SCHEMA
'
,
@level0name
=
N
'
dbo
'
,
@level1type
=
N
'
PROCEDURE
'
,
@level1name
=
N
'
test
'
GO
--
5、
DECLARE
@pwd
VARCHAR
(
100
)
=
'
P@ssw0rd
'
--
DECLARE
@proc
VARCHAR
(
100
)
=
'
test
'
--
DECLARE
@exName
NVARCHAR
(
100
)
=
'
'
--
--
SELECT
value
FROM
sys.all_objects
AS
sp
INNER
JOIN
sys.extended_properties
AS
P
ON
P.major_id
=
sp.
object_id
AND
P.minor_id
=
0
AND
P.class
=
1
WHERE
( P.name
=
@exName
)
AND
( ( sp.type
=
N
'
p
'
OR
sp.type
=
N
'
rf
'
OR
sp.type
=
'
pc
'
)
AND
( sp.name
=
@proc
AND
SCHEMA_NAME(sp.schema_id)
=
N
'
dbo
'
) )