Bài giảng Cơ sở dữ liệu nâng cao - Chapter 3: Managing security

Bài giảng Cơ sở dữ liệu nâng cao - Chapter 3: Managing security. Những chủ đề được thảo luận trong chương này gồm có: Users, users vs. login, SQL server security, SQL server authentication, securable objects in SQL Server, The fixed server-level role,...
Nội dung trích xuất từ tài liệu:
Bài giảng Cơ sở dữ liệu nâng cao - Chapter 3: Managing security4/5/17 1 Chapter 2 Managing Security4/5/17 2Users• Users 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.4/5/17 3Users vs. Login• Logins: • 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.4/5/17 4SQL Server security• A user passes through 2 stages of security in SQL Server • Authentication – 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)4/5/17 5SQL Server authentication• Windows 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 Server4/5/17 6SQL Server authentication• SQL Server specific logins: • Windows user account is not required • Password is passed across the network for authentication • Password is encrypted automatically • The 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.4/5/17 7SQL Server authentication• Adding a new login • Create 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 SSMS • Use T-SQL command4/5/17 8SQL Server authentication• Adding a New Windows Login • Use SSMS4/5/17 9SQL Server authentication• Adding a New Login • Use 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:4/5/17 10SQL Server authentication• Ex 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 LOGIN4/5/17 11Schema• Schemas 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.4/5/17 12Principal• Principal are logins allow you to connect to SQL Server. There are effectively three types of logins or server principals • Windows domain login • Windows local login • SQL Server login4/5/17 13Principal• Windows-level principals • Windows Domain Login • Windows local login • Windows group4/5/17 14Principal• SQL Server-level principals • SQL Server login • SQL Server login mapped to a Windows login • SQL Server login mapped to a certificate • SQL Server login mapped to an asymmetric key4/5/17 15Principal• Database-level principals • Database user • Database user mapped to SQL Server login • Database user mapped to a Windows login • Database user mapped to a certificate • Database user mapped to an asymmetric key • Database role • Application role • Public role4/5/17 16Securable objects in SQL Server• Server• Database• Schema4/5/17 17Roles• SQL server provides two roles • Fixed server-level: ...