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

Chia sẻ: Uhm Uhm | Ngày: | Loại File: PPTX | Số trang:39

Thêm vào BST

Báo xấu

92
lượt xem 9
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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,...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

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

4/5/17 1 Chapter 2 Managing Security
4/5/17 2 Users • 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 3 Users 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 4 SQL 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 5 SQL 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 Server
4/5/17 6 SQL 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 7 SQL 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 command
4/5/17 8 SQL Server authentication • Adding a New Windows Login • Use SSMS
4/5/17 9 SQL 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 10 SQL 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 server USE sample; CREATE LOGIN Bob from Windows; • Remove an existing login: use the DROP LOGIN
4/5/17 11 Schema • 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 12 Principal • 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 login
4/5/17 13 Principal • Windows-level principals • Windows Domain Login • Windows local login • Windows group
4/5/17 14 Principal • 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 key
4/5/17 15 Principal • 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 role
4/5/17 16 Securable objects in SQL Server • Server • Database • Schema
4/5/17 17 Roles • SQL server provides two roles • Fixed server-level: • have a serverwide scope • Used for administration tasks • Database-level roles: • have a database-level scope • custom database-level roles can be created • Used for admin and security • Include the public
4/5/17 18 The fixed server-level role • sysadmin – Perform any activity in the server. • The BUILTIN\Administrators group and the local administrator’s are sysadmin • serveradmin – 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
4/5/17 19 The fixed server-level role • A 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 ﬁxed server role Ex: EXEC sp_dropsrvrolemember ‘XPS\Lauren’, ‘sysadmin’ • sp_helpsrvrole: Get a list of the fixed server roles • sp_srvrolepermission: get the specific permissions for each role
4/5/17 20 The fixed database-level roles • db_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.