Cơ sở dữ liệu - Chapter 2: Managing security

Chapter 2 Managing Security 3/29/20201UsersUsers are database-level principals and are created to access resources within a database.User and Log-in names should match. Users can be added to any one or more of the available database roles.3/29/20202Users vs. LoginLogins:Be created at the instance level Can be mapped to a Windows user account, a domain account, a Windows group, a domain group, ..Provide a user access to the SQL Server instance.access to one or more databases. Do not provide access to the objects contained within the database. Permissions to access database objects are at the database user level.3/29/20203SQL Server securityA user passes through 2 stages of security in SQL ServerAuthentication – validates that a user can connect to a SQL Server instance (Login)Authorization – permissions validation; controls the activities the user is allowed to perform in the SQL Server database (User)3/29/20204SQL Server authenticationWindows Authentication Windows performs the authentication SQL Server trusts that authentication and provides access to the Windows accounts as configured. Windows user and group accounts can be mapped to SQL Server3/29/20205SQL Server authenticationSQL Server specific logins:Windows user account is not requiredPassword is passed across the network for authentication Password is encrypted automaticallyThe primary advantage of this authentication scheme: SQL Server can authenticate any login no matter how they may have authenticated to the Windows network. This option is typically less secure because it gives access to any-one who has the SQL Server password, without regard to his or her Windows identity.3/29/20206SQL Server authenticationAdding a new loginCreate new user in Windows. Once the users exist in the Windows user list or the Windows domain, SQL Server can recognize them.Add a new login to SQL Server.Use SSMSUse T-SQL command3/29/20207SQL Server authenticationAdding a New Windows LoginUse SSMS3/29/20208SQL Server authenticationAdding a New LoginUse T-SQL command:CREATE LOGIN [name] {WITH | FROM }Options: contain many options. The most important one is the PASSWORD option. (The other possible options are DEFAULT_DATABASE, DEFAULT_LANGUAGE, and CHECK_EXPIRATION.)Source: WINDOWS: the login will be mapped to an existing Windows user account CERTIFICATE: the name of the certificate to be associated with this login.ASYMMETRIC KEY: the name of the asymmetric key to be associated with this login. 3/29/20209SQL Server authenticationEx 1: Create a new SQL Server login for “Mary”USE sample; CREATE LOGIN mary WITH PASSWORD = ‘password';Ex 2: Creates a new login for “Bob” on the serverUSE sample;CREATE LOGIN Bob from Windows;Remove an existing login: use the DROP LOGIN statementEx: DROP LOGIN [AughtEight\Bob];3/29/202010SchemaSchemas are collections of database objects such as tables, views, and procedures.Permissions can be granted to individual schemas within a database, providing a powerful way to manage permissions.It is not necessary to grant access to each object within a schema when granting permission to the schema.3/29/202011PrincipalPrincipal are logins allow you to connect to SQL Server. There are effectively three types of logins or server principalsWindows domain loginWindows local loginSQL Server login3/29/202012PrincipalWindows-level principalsWindows Domain LoginWindows local loginWindows group3/29/202013PrincipalSQL Server-level principalsSQL Server loginSQL Server login mapped to a Windows loginSQL Server login mapped to a certificateSQL Server login mapped to an asymmetric key3/29/202014PrincipalDatabase-level principalsDatabase userDatabase user mapped to SQL Server login Database user mapped to a Windows loginDatabase user mapped to a certificateDatabase user mapped to an asymmetric keyDatabase roleApplication rolePublic role3/29/202015Securable objects in SQL ServerServerDatabaseSchema3/29/202016RolesSQL server provides two rolesFixed server-level: have a serverwide scopeUsed for administration tasks Database-level roles:have a database-level scopecustom database-level roles can be createdUsed for admin and security Include the public 3/29/202017The fixed server-level rolesysadmin – Perform any activity in the server. The BUILTIN\Administrators group and the local administrator’s are sysadminserveradmin – Change server-wide configuration options and shut down the server.securityadmin – Manage logins and their properties. They will be able to reset passwords for SQL Server logins and GRANT, DENY, and Revoke permissions.processadmin – End processes running in an instance of SQL Server.setupadmin – Add and remove linked servers.bulkadmin – Run the BULK INSERT statement.diskadmin – Manage disk files.dbcreator – CREATE, ALTER, DROP, and restore any database.3/29/202018The fixed server-level roleA user is assigned to a server role by means of a system sp:sp_addsrvrolemember [ @loginame = ] ‘login’,[ @rolename = ] ‘role’Ex: EXEC sp_addsrvrolemember ‘XPS\Lauren’, ‘sysadmin’sp_dropsrvrolemember to remove a login from a fixed server roleEx: EXEC sp_dropsrvrolemember ‘XPS\Lauren’, ‘sysadmin’sp_helpsrvrole: Get a list of the fixed server rolessp_srvrolepermission: get the specific permissions for each role3/29/202019The fixed database-level rolesdb_owner – Can drop the database as well as permission to perform all configuration and maintenance tasks.db_security_admin – Can modify role membership and manage permissions. Please be careful when adding principals to this role; an unintended privilege escalation could result.db_accessadmin – Can add or remove database access for Windows logins, Windows groups, and SQL Server logins.db_backupoperator – Can back up the database.db_ddladmin – Can run any Data Definition Language command.db_datawriter – Can add, delete, or change data in all user tables.db_datareader – Can read all data from all user tables.db_denydatawriter – Will deny permission in the database to add, modify, or delete any data in the user tables.db_denydatareader – Will deny permission in the database to read any data in the user tables.3/29/202020The fixed database-level rolesSetting Up Database User Accounts:CREATE USER [LoginName] FOR LOGIN [LoginNameEx: USE master;CREATE LOGIN [AughtEight\Bob] FROM WINDOWS;USE AdventureWorks2008;CREATE USER BillyBob FOR LOGIN [AughtEight\Bob]WITH DEFAULT_SCHEMA = sales;3/29/202021The fixed database-level roleA user is assigned to a server role by means of a system sp:sp_addrolemember [ @rolename = ] ‘role’,[ @loginame = ] ‘login’,Ex: sp_addrolemember ‘db_datareader’, ‘Carol’;sp_dropsrvrolemember to remove a login from a fixed server roleEx: sp_droprolemember ‘db_datareader’, ‘Carol’;sp_helprolemember: Get a list of the fixed database roles3/29/202022Principal of least privilegeDo not grant more permissions than necessary. Be familiar with what each specific permission enables a user to accomplish.Inadvertently elevated permissions can pose a significant security risk. ˛3/29/202023AuthorizationOnly authorized users are able to execute statements or perform operations on an entityThere are three Transact-SQL statements related to authorization:Grant gives a right right to perform an action Deny explicitly denies a rightRevoke removes an existing grant or denyPermissions are applied to the objects (tables, views, stored procs, etc.) in the database3/29/202024PermissionGrant statement GRANT action ON object TO principal WITH {options}Ex 1:USE masterGRANT CREATE ANY DATABASE TO Ted;GOEx2:USE MasterGRANT CREATE FUNCTION TO mary;3/29/202025PermissionDENY statement: DENY action ON object TO principal WITH {options}Ex 1:USE masterDENY CREATE TABLE, CREATE PROCEDURE TO PeterGO3/29/202026PermissionRevoke statement:REVOKE action ON object TO principal WITH {options}Ex 1:USE masterREVOKE ALTER ANY LOGIN TO Ted CASCADE;Ex 2:REVOKE SELECT ON project From PUBLIC3/29/202027 Permission – action on serverSELECTVIEW CHANGE TRACKINGUPDATEREFERENCESINSERTDELETEEXECUTERECEIVEVIEW DEFINITIONALTERTAKE OWNERSHIP CONTROL3/29/202028SQL Server EncryptionA well-designed encryption method: encrypts data using symmetric keys, and encrypts the symmetric keys using asymmetric keys. A certificate is technically an asymmetric key, but there is a standard, X.509, that defines the format for a certificate 3/29/202029SQL Server EncryptionSetting Up an Encryption Methodology1. First, create a fresh database called EncryptionExample. CREATE DATABASE [EncryptionExample]2. Create a login named LowPrivLogin with a password “pw” CREATE LOGIN LowPrivLogin WITH PASSWORD = ‘pw’3. Next, grant the login access to the EncryptionExample database. USE EncryptionExample CREATE USER LowPrivLogin FOR LOGIN LowPrivLogin3/29/202030SQL Server EncryptionSetting Up an Encryption Methodology4. Create a table in the dbo schema that you’ll use throughout this example. The table will hold fake credit card information. Note: the credit card number is stored as a variable binary column because this column is used to store encrypted data.CREATE TABLE dbo.CustomerCreditCards(CustomerID INT PRIMARY KEY,CardNumber varbinary(256))3/29/202031SQL Server EncryptionSetting Up an Encryption Methodology5. Create a master key for the database:CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘EncryptionExampleMasterKey08$’6. Next, protect other keys with a certificate. CREATE CERTIFICATE [CertSymmetricKey]WITH SUBJECT = ‘User defined subject. This key will protect the secret data.’3/29/202032SQL Server EncryptionSetting Up an Encryption Methodology7. With the certificate now created, create a symmetric keyCREATE SYMMETRIC KEY [SecretSymmetricKey]WITH ALGORITHM = TRIPLE_DES --AES_128 Fine tooENCRYPTION BY CERTIFICATE [CertSymmetricKey]3/29/202033SQL Server EncryptionSetting Up an Encryption Methodology7. With the certificate now created, create a symmetric keyCREATE SYMMETRIC KEY [SecretSymmetricKey]WITH ALGORITHM = TRIPLE_DES --AES_128 Fine tooENCRYPTION BY CERTIFICATE [CertSymmetricKey]3/29/202034SQL Server EncryptionEncrypting the Data1. First, use the symmetric key that was created earlier by issuing the OPEN SYMMETRIC KEY syntax. This key will remain open until your session expires or you issue the CLOSE statement:OPEN SYMMETRIC KEY [SecretSymmetricKey]DECRYPTION BY CERTIFICATE [CertSymmetricKey]3/29/202035SQL Server EncryptionEncrypting the Data2. Encrypt dataDECLARE @Key_Guid AS UNIQUEIDENTIFIERSET @Key_Guid = key_guid( ‘SecretSymmetricKey’)IF( @Key_Guid is not null )BEGININSERT INTO dbo.CustomerCreditCardsVALUES ( 1, encryptbykey( @Key_Guid, N‘4111-1234-1234-5678’))INSERT INTO dbo.CustomerCreditCardsVALUES ( 2, encryptbykey( @Key_Guid, N‘4111-9876-7543-2100’)) ENDELSE BEGIN PRINT ‘Error retrieving key GUID’ END3/29/202036SQL Server EncryptionEncrypting the Data2. SELECT * FROM dbo.CustomerCreditCardsSELECT CustomerId,convert( NVARCHAR(100), decryptbykey( CardNumber )) as ‘CardNumber’FROM dbo.CustomerCreditCardsGOTo close the key, use the CLOSE syntax, naming the key that you wish to close:CLOSE SYMMETRIC KEY SecretSymmetricKey3/29/202037SQL Server AuditServer Audit can track and log events that occur at the server level or the database level.An Audit object is a collection of one more individual actions or a group of actions to be tracked. For instance, you can configure an Audit object to track all failed logins. An Audit object can be created via either Management Studio or T-SQL.3/29/202038SQL Server AuditAfter creating Audit, the next step is to create the appropriate Audit Specifications. An Audit Specification tells an Audit object what to track.3/29/202039