070-464 無料問題集「Microsoft Developing Microsoft SQL Server Databases」
You have a table named Table1 that contains one million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar(16) data type. You have a certificate named Cert1.
You must add a column named Column2 that contains an encrypted version of the data from Column1. You must use two-way encryption. You plan to remove Column1 after you create Column2.
Which five Transact-SQL statements should you run in sequence before you remove Column1? To answer, move the appropriate Transact-SQL statements from the list of Transact-SQL statements to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any correct orders you select.
You must add a column named Column2 that contains an encrypted version of the data from Column1. You must use two-way encryption. You plan to remove Column1 after you create Column2.
Which five Transact-SQL statements should you run in sequence before you remove Column1? To answer, move the appropriate Transact-SQL statements from the list of Transact-SQL statements to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any correct orders you select.
正解：
Explanation:
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/statements/close-symmetric-key-transact-sql?view=sql-server-2017
You plan to create a custom aggregation function named Function1.
You plan to deploy Function1 to SQL Server 2012.
You need to ensure that Function1 can access a web service. The solution must minimize the number of changes made to the database.
You create a Microsoft .NET Framework class that contains the definition of Function1.
You upload a certificate to SQL Server.
What three tasks should you perform next?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
You plan to deploy Function1 to SQL Server 2012.
You need to ensure that Function1 can access a web service. The solution must minimize the number of changes made to the database.
You create a Microsoft .NET Framework class that contains the definition of Function1.
You upload a certificate to SQL Server.
What three tasks should you perform next?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
正解：
1 - Modify the TRUSTWORTHY property of the database.
2 - Execute the CREATE ASSEMBLY statement.
3 - Execute the CREATE AGGREGATE statement.
Explanation:
Note:
* TRUSTWORTHY CREATE signature
The TRUSTWORTHY property indicates whether the instance of SQL Server trusts the database and the contents within it.
* CREATE AGGREGATE
Creates a user-defined aggregate function whose implementation is defined in a class of an assembly in the .NET Framework. For the Database Engine to bind the aggregate function to its implementation, the .NET Framework assembly that contains the implementation must first be uploaded into an instance of SQL Server by using a CREATE ASSEMBLY statement.
* Example:
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY [CLR.Utilities] FROM 'C:\Path\To\File\CLR.Utilities.dll' WITH PERMISSION_SET = UNSAFE GO CREATE AGGREGATE [dbo].[Concatenate] (@input nvarchar(max)) RETURNS nvarchar(max) EXTERNAL NAME [CLR.Utilities].[CLR.Utilities.Concatenate] GO
You have a table named Table1 that contains 1 million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar (16) data type.
You have a certificate named Cert1.
You need to replace Column1 with a new encrypted column named Column2 that uses one-way hashing.
Which code segment should you execute before you remove Column1?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
You have a certificate named Cert1.
You need to replace Column1 with a new encrypted column named Column2 that uses one-way hashing.
Which code segment should you execute before you remove Column1?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
正解：
Explanation:
Note:
* There are a few different hashing algorithms available in SQL Server 2005: MD2, MD4, MD5, SHA, SHA1, with each having pros and cons.
* In cryptography, SHA-1 is a cryptographic hash function designed by the United States National Security Agencyand published by the United StatesNISTas a USFederal Information Processing Standard.SHA stands for "secure hash algorithm".The four SHAalgorithmsare structured differently and are distinguished asSHA-0,SHA-1,SHA-2, andSHA-3.SHA-1 is very similar to SHA-0, but corrects an error in the original SHA hash specification that led to significant weaknesses.The SHA-0 algorithm was not adopted by many applications.SHA-2 on the other hand significantly differs from the SHA-1 hash function.
SHA-1 is the most widely used of the existing SHA hash functions, and is employed in several widely used applications and protocols.
* To encrypt a column of data using a simple symmetric encryption
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
USE AdventureWorks2012;
--If there is no master key, create one now.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj' GO CREATE CERTIFICATE Sales09 WITH SUBJECT = 'Customer Credit Card Numbers'; GO CREATE SYMMETRIC KEY CreditCards_Key11 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Sales09; GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encryptedvarbinary(128);
GO
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
, CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
, CreditCardID)));
GO
Reference:
Ref: http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/
