Quản Lý SQL Server Security – Lab SQL 2014 P8

A. Quản lý server-level security:

1. Set authentication mode: Tại SSMS, ở cửa sổ Object explorer, phải chuột tên server SQL (trong bài lab này là Ser100) chọn properties. Tại cửa sổ server properties chọn Security, trong phần server authentication bạn có thể chọn Windows Authentication mode hay đổi thành SQL and Windows Authentication mode Ok, Nếu bạn thay đổi server authentication thì khởi động lại SQL server, bằng cách tại cửa sổ phải Object explorer, phải chuột tên server SQL chọn restart.

2. Tạo Login: Tại cửa sổ Object Explorer chọn security, phải chuột vào login chọn new login. Tại cửa sổ Login-new , trong phần login name ấn Search, tại cửa sổ select user service account or group ấn location chọn tên domain (trong bài lab này là labs.ctl.vn), nhập vào tên user: HumanResource_user ấn check names(user này là domain user đã được tạo trước trong domain controller labs.ctl.vn) OK

Kiểm tra đã có tên user trong login mane, chọn option Windows authentication, trong phần Default Database chọn AdventureWorks OK, Tạo xong login windows authentication

Tương tự, tại cửa sổ Object Explorer chọn security, phải chuột vào login chọn new login. Tại cửa sổ Login-new , trong phần login name nhập vào Payroll_Application, chọn option SQL server authentication nhập password 2 lần, bỏ checkbox Enforce password policy, trong phần Default database chọn AdventureWorks OK, tạo xong login SQL server authentication.

Bạn của có thể tạo login bằng lệnh T-SQL, mở file Createlogins.sql trong folder module 9:

USE master;

GO

–Create a login for a Windows user

CREATE LOGIN [CTL\AnthonyFrizzell]

FROM WINDOWS

WITH DEFAULT_DATABASE = AdventureWorks;

GO

— Create login for Windows group

CREATE LOGIN [CTL\Database_Managers]

FROM WINDOWS

WITH DEFAULT_DATABASE = AdventureWorks;

GO

— Create SQL Server login

CREATE LOGIN Web_Application

WITH PASSWORD = ‘Pa$$w0rd’,

CHECK_POLICY = ON, CHECK_EXPIRATION = OFF,

DEFAULT_DATABASE = AdventureWorks;

GO

Ấn F5 thực thi, sau đó phải chuột vào login chọn refresh kiểm tar lại các user vừa tạo

3. Tạo server-level role: Tại cửa sổ Object Explorer, chọn security, chọn server roles, phải chuột vào serveradmin chọn properties. Tại cửa sổ server role properties – serveradmin ấn add. Tại cửa sổ select server login or role ấn Browse chọn CTL\Database_Managers OK OK

Trở lại cửa sổ Server role properties – serveradmin kiểm tra trong phần members of this role đã có CTL\Database_Managers OK

Bạn cũng có thể tạo server role và add member vào role bằng T-SQL, mở file serverRoles.sql trong folder module 9:

USE master;

GO

— Create a user-defined server role

CREATE SERVER ROLE AW_securitymanager;

GO

— Add a login to the role

ALTER SERVER ROLE AW_securitymanager

ADD MEMBER [CTL\AnthonyFrizzell];

GO

ấn F5 để thực thi, kiểm tra trong server role có thêm server role tên AW_securitymanager mới vừa tạo. Phải chuột vào server role này chọn properties, kiểm tra xem đã có login CTL\AnthonyFrizzell trong phần members of this role chưa Ok. Bạn vừa thực hiện tạo server role và add thành viên vào role, ngoài ra bạn cũng có thể tạo server role bằng cách phải chuột vào server roles bạn sẽ tự biết cách tạo.

4. Cấp quyền cho server role (server role permissions): Mở file serverPermissions.sql trong folder module 9:

USE master;

GO

— Grant permission to alter login

GRANT ALTER ANY LOGIN TO AW_securitymanager;

Ấn F5 thực thi, cấp quyền ALTER ANY LOGIN cho server role AW_securitymanager (gán quyền modify login cho AW_securitymanager)

Phải chuột vào AW_securitymanager server role chọn Properties, tại cửa sổ server role properties chọn General trong phần securables kiểm tra các quyền vừa được cấp Ok.

Tại Logins, phải chuột vào CTL\AnthonyFrizzell chọn properties, tại cửa sổ Login properties chọn Securables ấn search… chọn option All objects of the types.. Ok, chọn Logins OK

Trở lại cửa sổ login properties, trong phần Securables tìm và chọn Payroll_Application, trong phần Permissions for payroll_Apllication chọn tab Explicit không thấy quyền nào được cấp cho CTL\AnthonyFrizzell, như qua tab Effective sẽ thấy quyền ALTER được thừa hưởng từ AW_securitymanager server role Ok.

B. Quản lý Database user và Database Role:

1. Tạo database user: Tại SSMS, ở cửa sổ Object explorer, chọn Databases, chọn AdventureWorks, chọn Security, phải chuột vào User chọn New User… Tại cửa sổ Database user, trong phần User type chọn SQL user with login, User name: nhập vào Web_Application, login name: ấn … chọn login name là Web_Application, Default schema: ấn … chọn Sales OK

Bạn cũng có thể tạo database user bằng lệnh T-SQL, Mở file CreateUser.sql trong folder module 9:

USE AdventureWorks;

GO

— Create users

CREATE USER Payroll_Application

FOR LOGIN Payroll_Application

WITH DEFAULT_SCHEMA = HumanResources;

CREATE USER HumanResources_Users

FOR LOGIN [CTL\HumanResources_Users]

WITH DEFAULT_SCHEMA = HumanResources;

CREATE USER AnthonyFrizzell

FOR LOGIN [CTL\AnthonyFrizzell];

Ấn F5 để tạo database user Payroll_Application, HumanResources_Users, AnthonyFrizzell, sau đó phải chuột vào users chọn refresh để kiểm tra kết quả.

2. Tạo database Role: Tại SSMS, ở cửa sổ Object explorer, chọn Databases, chọn AdventureWorks, chọn Security, chọn Roles, chọn Database Roles, phải chuột vào db_datareader chọn properties, tại cửa sổ Database Role Properties ấn nút Add ấn Browse chọn user AnthonyFrizzellOk Ok, trở lại cửa sổ Database Role Properties kiểm tra xem trong phần member of this role đã có user AnthonyFrizzell OK, bạn đã cho user AnthonyFrizzell là thành viên của Database role db_datareader.

Để tạo Database role mới, bạn phải chuột vào Database Roles chọn New Database Role…Tại cửa sổ Datbase role-new, trong phần Role name: nhập vào hr_Reader, ấn Add Ấn Browse chọn 2 user HumanResource_User và Payroll_Application OK OK

Trở lại cửa sổ Database Role-new, trong phần Members of this role đạ có 2 user OK

Bạn cũng có thể tạo database role bằng T-SQL, mở file DatabaseRoles.sql trong folder module 9:

USE AdventureWorks;

GO

— Create roles

CREATE ROLE hr_writer;

CREATE ROLE web_customer;

— Add members

ALTER ROLE hr_writer

ADD MEMBER HumanResources_Users;

ALTER ROLE web_customer

ADD MEMBER Web_Application;

Ấn F5 thực thi và chọn database Roles ấn Refresh kiểm tra lại các database role đã tạo.

C. Sử dụng Application Role:

1. Tạo Application Role: Tại SSMS, ở cửa sổ Object explorer, chọn Databases, chọn AdventureWorks, chọn Security, Chọn Role, phải chuột vào Application role chọn New Application Role…Tại cửa sổ Application Role-new,trong phần Role name:Pay_admin,

Default schema: chọn HumanResources, nhập 2 lần Password: Pa$$w0rd OK

2. Sử dụng Application Role: Mở file ApplicationRole.sql trong folder module 9:

— user security contenxt

SELECT ‘Default user context’ As Context, user_name() AS [DB Identity], SUSER_NAME() AS [System Identity]

— Use application role

DECLARE @cookie varbinary(8000);

EXEC sp_setapprole ‘pay_admin’, ‘Pa$$w0rd’, @fCreateCookie = true, @cookie = @cookie OUTPUT;

SELECT ‘Application role active’ As Context, user_name() AS [DB Identity], SUSER_NAME() AS [System Identity]

EXEC sp_unsetapprole @cookie;

— user security contenxt

SELECT ‘Reverted to user context’ As Context, user_name() AS [DB Identity], SUSER_NAME() AS [System Identity]

đoạn code này sẽ hiện thị tên user hiện tại, và tên của pay_admin application role sau khi được active, để thực hiện bạn phải chuột vào bất kỳ chỗ nào trong cửa sồ Query chọn ConnectionChange Connection…

Xuất hiện cửa sổ Connect to Database Engine, trong phần Authentication: chọn SQL Server Authentication, login:Payroll_Application, nhập password của login này Connect, sau đó ấn F5 để thực thi, bạn xem kết quả trong phần System Identity không thay đổi luôn luôn là: Payroll_Application, nhưng DB Identity có thay đổi sang Pay_admin khi application role được active.

D. Sử dụng Contained Database:

1. Tạo Contained Database: Mở file ContainedDatabase.sql trong folder module 9:

Chọn các lệnh trong phần Enable contained database ấn F5, thực thi lệnh này để có thể tạo Contained Database. (Bạn cũng có thể vào SSMS phải chuột vào server SQL chọn properties chọn Advanced, trong phần Enable Contained Database chọn True Ok)

–Enable contained databases

sp_configure ‘show advanced options’, 1 ;

GO

RECONFIGURE ;

GO

sp_configure ‘contained database authentication’, 1;

GO

RECONFIGURE ;

GO

sp_configure ‘show advanced options’, 0 ;

GO

RECONFIGURE ;

GO

Tiếp tục chọn các lệnh trong phần Create a contained database ấn F5, thực thi lệnh này để tạo Contained database tên ContainedDB (Bạn cũng có thể phải chuột vào Database chọn New Database trong phần Database name đặt tên là ContainedDB, chọn option, trong phần containment type chọn Partial Ok)

–Create a contained database

CREATE DATABASE [ContainedDB]

CONTAINMENT = PARTIAL;

GO

Phải chuột vào Database chọn Refresh, Sau đó phải chuột vào database ContainedDB chọn properties, tại cửa sổ Database Properties chọn option, trong phần containment type là Partial OK. Tạo Contained Database xong.

2. Tạo Contained User: Tiếp tục chọn các lệnh trong phần Create contained users ấn F5 để tạo database user (các user này được lưu trữ trong ContainedDB, không có login ở cấp server)

–Create contained users

USE [ContainedDB]

CREATE USER [SalesApp] WITH PASSWORD = ‘Pa$$w0rd’

CREATE USER [CTL\RosieReeves]

Phải chuột vào bất kỳ vị trí nào trong cửa sổ query chọn Connection change Connection, tại cửa sổ Connect to database Engine, trong phần Authentication chọn SQL server Authentication, login: SalesApp, Password: Pa$$w0rd, ấn option

Trong phần Connect to database chọn default ấn Connect

Báo lỗi không thể kết nối vào server OK

Trong phần Connect to database nhập vào ContainedDB ấn connect, kết nối thành công.

E. Quản lý Permissions:

1. Cấp quyền(set permission): Tại SSMS mở file DatabasePermissions.sql trong folder module 9. Chọn các lệnh trong phần Grant schema permissions ấn F5, thực thi các lệnh này đề cấp quyền SELECT trên HumanResources schema cho hr_reader databse role và cấp quyền INSERT, UPDATE, EXECUTE trên HumanResources schema cho hr_writer databse role

— Grant schema permissions

USE AdventureWorks;

GRANT SELECT ON SCHEMA::HumanResources TO hr_reader;

GRANT INSERT,UPDATE, EXECUTE ON SCHEMA::HumanResources TO hr_writer;

Chọn các lệnh trong phần Grant individual object permissions ấn F5, thực thi các lệnh này để cấp quyền EXECUTE trên dbo.uspGetEmployeeManagers stored procedure cho hr_reader database role, cấp quyền INSERT trên 2 table Sales.SalesOrderHeader và Sales.SalesOrderDetail cho Web_customer database role, và cấp quyền SELECT trên Production.vProductAndDescription view cho Web_customer database role

— Grant individual object permissions

GRANT EXECUTE ON dbo.uspGetEmployeeManagers TO hr_reader;

GRANT INSERT ON Sales.SalesOrderHeader TO web_customer;

GRANT INSERT ON Sales.SalesOrderDetail TO web_customer;

GRANT SELECT ON Production.vProductAndDescription TO web_customer;

Chọn các lệnh trong phần Override inherited permissions ấn F5, thực thi các lệnh này đề cấp quyền INSERT và UPDATE trên Sale schema cho AnthonyFrizzell user, cấp quyền UPDATE trên HumanResources.EmployeePayHistory table cho Payroll_Application user, , cấp quyền UPDATE trên SalariedFlag column của HumanResources.Employee table cho Payroll_Application user, DENY quyền SELECT trên HumanResources.EmployeePayHistory table Cho AnthonyFrizzell user.

–Override inherited permissions

GRANT INSERT, UPDATE ON SCHEMA::Sales TO AnthonyFrizzell;

GRANT UPDATE ON HumanResources.EmployeePayHistory TO Payroll_Application

GRANT UPDATE ON HumanResources.Employee(SalariedFlag) TO Payroll_Application;

DENY SELECT ON HumanResources.EmployeePayHistory TO AnthonyFrizzell;

2. xem quyền đã cấp(Effective permissions):

Trong cửa sồ Object Explorer, chọn Databases AdventureWorks database Tables phải chuột vào HumanResources.Employee chọn Properties. Tại cửa sổ Table properties – Employee chọn Permissions, kiểm tra Payroll_Application user đã được cấp quyền UPDATE. Chọn tab Effective sẽ thấy user có quyền SELECT trên table và quyền UPDATE trên SalariedFlag column.