SQL Server 2008 provides several security catalog views and a dynamic management view, all of which can be used to retrieve information about encryption functionality. The following views are available in SQL Server 2008:
• sys.asymmetric_keys: This catalog view returns information about the asymmetric key pairs installed in the current database. The information returned by this view includes the name, asymmetric key ID, private key encryption type, encryption algorithm used, public key, and additional information about each installed asymmetric key pair.
• sys.certificates: This catalog view returns information about the certificates installed in the current database. The information returned by this view is similar to that returned by the sys.asymmetric_keys view. It includes the name, certificate ID, private key encryption type, name of the certificate’s issuer, certificate serial number, and additional certificate-specific information (such as subject, start date, and expiration date).
• sys.crypt_properties: This catalog view returns a row for each cryptographic property associated with a securable in the database. The information returned about each securable includes the class of the securable, ID of the securable, encryption type used, and SHA-1 hash of the certificate or asymmetric key used to encrypt the securable.
• sys.dm_database_encryption_keys: This dynamic management view returns information about the encryption state of a database and the encryption keys used in the database. Some of the values returned in the encryption_state column of this view are 0 if no encryption is present, 1 if the database is unencrypted, 3 when the database is encrypted, or another value indicating a database encryption or decryption action is currently in progress.
• sys.key_encryptions: This catalog view returns a row for every key encryption, as specified by the CREATE SYMMETRIC KEY statement’s ENCRYPTION BY clause. Information returned includes the ID of the encrypted key, encryption type, and thumbprint of the certificate or symmetric key used to encrypt the key. A thumbprint, in terms of SQL Server 2008 security catalog views, is an SHA-1 hash of a certificate or an asymmetric key, or a GUID for a symmetric key. Several of the security catalog views return a thumbprint of certificates, asymmetric keys, or symmetric keys.
• sys.master_key_passwords: This catalog view returns a row for each DMK password added with the sp_control_dbmasterkey_password stored procedure. Each row returns an ID of the credential to which the password belongs and a GUID of the original database at creation time. The GUID is used by SQL Server to identify credentials that may contain passwords that protect the DMK in the event that automatic decryption fails. Passwords used to protect the DMKs are stored in the credential store.
• sys.openkeys: This catalog view returns information about all open encryption keys in the current session. Information returned includes the ID and name of the database that contains the key; IDs, names, and GUIDs of each open key; and the date and time the key was opened.
• sys.symmetric_keys: This catalog view returns a row for each symmetric key in the database. Information returned includes the name, ID, GUID, length, and algorithm of the symmetric key. Also returned are the ID of the principal who owns the key and the dates that the symmetric key was first created and last modified.
Securables in SQL Server 2008 are resources and objects for which the SQL Server database engine regulates authorization and access. Securables are divided into three scopes for which SQL Server can regulate access: Server, Database, and Schema. The Server scope includes securables like endpoints, logins, and databases. The Database scope includes users, roles, certificates, asymmetric key pairs, symmetric keys, schemas, and other Database-scoped securables. The Schema scope contains tables, views, functions, procedures, constraints, and other objects. Not all securables have cryptographic properties, but the sys.crypt_properties security catalog view returns information for those that do.
Source of Information : Apress Accelerated SQL Server 2008
• sys.asymmetric_keys: This catalog view returns information about the asymmetric key pairs installed in the current database. The information returned by this view includes the name, asymmetric key ID, private key encryption type, encryption algorithm used, public key, and additional information about each installed asymmetric key pair.
• sys.certificates: This catalog view returns information about the certificates installed in the current database. The information returned by this view is similar to that returned by the sys.asymmetric_keys view. It includes the name, certificate ID, private key encryption type, name of the certificate’s issuer, certificate serial number, and additional certificate-specific information (such as subject, start date, and expiration date).
• sys.crypt_properties: This catalog view returns a row for each cryptographic property associated with a securable in the database. The information returned about each securable includes the class of the securable, ID of the securable, encryption type used, and SHA-1 hash of the certificate or asymmetric key used to encrypt the securable.
• sys.dm_database_encryption_keys: This dynamic management view returns information about the encryption state of a database and the encryption keys used in the database. Some of the values returned in the encryption_state column of this view are 0 if no encryption is present, 1 if the database is unencrypted, 3 when the database is encrypted, or another value indicating a database encryption or decryption action is currently in progress.
• sys.key_encryptions: This catalog view returns a row for every key encryption, as specified by the CREATE SYMMETRIC KEY statement’s ENCRYPTION BY clause. Information returned includes the ID of the encrypted key, encryption type, and thumbprint of the certificate or symmetric key used to encrypt the key. A thumbprint, in terms of SQL Server 2008 security catalog views, is an SHA-1 hash of a certificate or an asymmetric key, or a GUID for a symmetric key. Several of the security catalog views return a thumbprint of certificates, asymmetric keys, or symmetric keys.
• sys.master_key_passwords: This catalog view returns a row for each DMK password added with the sp_control_dbmasterkey_password stored procedure. Each row returns an ID of the credential to which the password belongs and a GUID of the original database at creation time. The GUID is used by SQL Server to identify credentials that may contain passwords that protect the DMK in the event that automatic decryption fails. Passwords used to protect the DMKs are stored in the credential store.
• sys.openkeys: This catalog view returns information about all open encryption keys in the current session. Information returned includes the ID and name of the database that contains the key; IDs, names, and GUIDs of each open key; and the date and time the key was opened.
• sys.symmetric_keys: This catalog view returns a row for each symmetric key in the database. Information returned includes the name, ID, GUID, length, and algorithm of the symmetric key. Also returned are the ID of the principal who owns the key and the dates that the symmetric key was first created and last modified.
Securables in SQL Server 2008 are resources and objects for which the SQL Server database engine regulates authorization and access. Securables are divided into three scopes for which SQL Server can regulate access: Server, Database, and Schema. The Server scope includes securables like endpoints, logins, and databases. The Database scope includes users, roles, certificates, asymmetric key pairs, symmetric keys, schemas, and other Database-scoped securables. The Schema scope contains tables, views, functions, procedures, constraints, and other objects. Not all securables have cryptographic properties, but the sys.crypt_properties security catalog view returns information for those that do.
Source of Information : Apress Accelerated SQL Server 2008
|
|
0 comments
Post a Comment