sql数据库培训6安全管理

1、Module 9Managing SQL Server SecurityModule OverviewIntroduction to SQL Server SecurityManaging Server-Level SecurityManaging Database-Level PrincipalsManaging Database PermissionsLesson 1: Introduction to SQL Server SecuritySecurity ConceptsSQL Server SecurablesSQL Server PrincipalsSQL Server Permis

2、sionsSecurity ConceptsSecurablesObjects to which access must be securedPrincipalsSecurity identities that access securables and perform actionsPermissionsThe actions principals can perform on securableSecurity Hierarchies:Securables can contain other securablesPrincipals can contain other principals

3、Permissions are inherited unless overriddenPrincipalSecurablePermissionsSQL Server SecurablesSQL Server InstanceDatabaseSchemaObjectsSchemaObjectsDatabaseSchemaObjectsSchemaObjectsServer-level objectsDatabase-level objectsDatabase-level objectsSQL Server PrincipalsWindows UserWindows Local GroupData

4、base UserSQL Server LoginUser name&PasswordContained DatabaseDatabase UserAuthenticated by WindowsWindows LoginServer RoleDatabase RoleAuthenticated by SQL ServerAuthenticated by SQL ServerDatabase RoleWindows Global GroupSQL Server InstanceDatabaseApplication RoleApplication RoleSQL Server Permissi

5、onsGRANT assigns a permissionInherited permissions are cumulative unless deniedDENY explicitly denies a permissionUse to override inherited permissionsUse only for exceptionsREVOKE removes a previously assigned GRANT or DENYEffective PermissionsExplicit and inherited permissions that will actually b

6、e appliedLoginServer RoleDatabaseRoleUserSchemaObjectsApplicationRoleServer-LevelDatabase-LevelLesson 2: Managing Server-Level SecurityApplication Security ModelsSQL Server Authentication OptionsManaging LoginsManaging Server-Level RolesManaging Server-Level PermissionsDemonstration: Managing Server

7、-Level SecurityApplication Security ModelsTrusted Server Application Security ModelImpersonation / Delegation Security ModelSQL ServerUserApplicationSQL ServerUserApplicationUser accesses applicationApplication uses its own credentialsSQL Server access is based on application identityUser accesses a

8、pplicationApplication impersonates userSQL Server access is based on user identitySQL Server Authentication OptionsAuthentication is the process of verifying that an identity is valid:Windows authentication only users authenticated by Windows can connectMixed authentication users authenticated by Wi

9、ndows or SQL Server can connectManaging LoginsCreate logins:In SQL Server Management StudioBy using the CREATE LOGIN statementSet security policy for SQL Server LoginsRemove logins by using the DROP LOGIN statementCREATE LOGIN ADVENTUREWORKSSalesRepsFROM WINDOWSWITH DEFAULT_DATABASE =salesdb;CREATE

10、LOGIN DanDraytonWITH PASSWORD = Pa$w0rd, CHECK_POLICY = ON,DEFAULT_DATABASE = salesdb;DROP LOGIN DanDrayton;Managing Server-Level RolesAll server-level principals are members of the public server roleAssign logins to fixed server-level roles to delegate administrative tasksCreate user-defined server

11、 roles if fixed roles dont meet your needsCREATE SERVER ROLE app_admin;ALTER SERVER ROLE app_adminADD MEMBER ADVENTUREWORKSWebAdmins;ALTER SERVER ROLE dbcreatorADD MEMBER ADVENTUREWORKSJuniorDBAs;Managing Server-Level PermissionsServer-level permissions include:Server connectivityAdministrative task

12、sAssign permissions to logins and user-defined server rolesGRANT ALTER ANY LOGIN TO app_admin;Demonstration: Managing Server-Level SecurityIn this demonstration, you will see how to:Set the authentication modeCreate loginsManage server-level rolesManage server-level permissionsLesson 3: Managing Dat

13、abase-Level PrincipalsManaging Database UsersManaging dbo and guest AccessManaging Database-Level RolesDemonstration: Managing Database Users and RolesManaging Application RolesDemonstration: Using an Application RoleManaging Users for a Contained DatabaseDemonstration: Using a Contained DatabaseMan

14、aging Database UsersLogins cannot access a database to which they have not been granted accessGrant access to a login by creating a database user for itCREATE USER SalesRepsFOR LOGIN ADVENTUREWORKSSalesReps;WITH DEFAULT_SCHEMA = Sales;CREATE USER DanDraytonFOR LOGIN DanDrayton;CREATE USER WebUserFOR

15、 LOGIN ADVENTUREWORKSWebAppSvcAcct;Managing dbo and guest Accessdbo database user:sa login, members of sysadmin role, and owner of the database map to the dbo accountguest database user:Enables logins without user accounts to access a databaseDisabled by default in user databasesEnabled by using the

16、 GRANT CONNECT statementManaging Database-Level RolesAssign users to fixed database-level roles to grant common permissionsCreate user-defined database roles for finer-grained permissions managementCREATE ROLE product_reader;ALTER ROLE product_readerADD MEMBER WebApp;ALTER ROLE db_data_readerADD MEM

17、BER Sales;Demonstration: Managing Database Users and RolesIn this demonstration, you will see how to:Create databases usersManage database-level rolesManaging Application RolesUse an application role to switch security contextOften used for tasks that require elevated privilegesCreate an application

18、 role with a passwordActivate and deactivate an application roleCREATE APPLICATION ROLE sales_supervisorWITH PASSWORD = Pa$w0rd;EXEC sp_setapprole sales_supervisor, Pa$w0rd,fCreateCookie = true,cookie = cookie OUTPUT; .EXEC sp_unsetapprole cookie;Demonstration: Using an Application RoleIn this demon

19、stration, you will see how to:Create an application roleUse an application roleManaging Users for a Contained DatabaseContained databases do not have a hierarchical dependency on server loginsUse contained databases to:Move databases between different SQL Server instances without having to migrate s

20、erver-level dependenciesDevelop databases when the developer does not know which instance will ultimately host the database Enable failover in a high-availability scenario without having to synchronize server-level loginsUsers in a contained database include:Users mapped to Windows accounts (users o

21、r groups)Users with passwordsDemonstration: Using a Contained DatabaseIn this demonstration, you will see how to:Create a contained databaseCreate contained usersLesson 4: Managing Database PermissionsDatabase-Level PermissionsSchemasTable and View PermissionsExecutable Code PermissionsOwnership Cha

22、insDemonstration: Managing PermissionsDatabase-Level PermissionsStatement Permissions Govern DDLObject Permissions Govern DDL and DMLGRANT CREATE TABLE TO db_dev;GRANT ALTER ANY ROLE, ALTER ANY USER TO sales_admin;GRANT ALTER APPLICATION ROLE:sales_supervisorTO sales_admin;GRANT SELECT ON OBJECT:dbo

23、.ProductCategoryTO product_reader;GRANT SELECT ON dbo.ProductTO product_reader;SchemasNamespace and security boundary for database objectsSQL Server resolves names by:Firstly looking in the users default schemaThen looking in the dbo schemaAvoid any ambiguity by using two-part namesPermissions grant

24、ed on a schema are inherited for all relevant objects in the schemaGRANT INSERT ON SCHEMA:sales TO sales_writer;Table and View PermissionsSELECTINSERT UPDATEDELETEREFERENCESUse column-level permissions for finer-grained controlExecutable Code PermissionsStored ProceduresUsers require EXECUTE permiss

25、ionUser-Defined FunctionsScalar UDFs require EXECUTE permissionsTVFs require SELECT permissionsCHECK constraints, DEFAULT values, and computed columns require REFERENCES permissionsManaged CodeObject permissions are the same as Transact-SQL objectsCode execution is restricted by permission setsGRANT

26、 EXECUTE ON sales.insert_order TO web_customer;Ownership ChainsWhen dependent objects are owned by the same user, only permissions at the top-level object are requiredWhen there is a break in the ownership chain, dependent object permissions are requiredUser 1dbo.View1 (User2)dbo.Table1 (User2)dbo.View2 (User2)dbo.Table2 (User3)Demonstration: Managing PermissionsIn this demonstration, you will see how to:Set permissionsView effective permissionsLab