Bảo vệ an toàn dữ liệu trong các ứng dụng của doanh nghiệp luôn là vấn đề quan trọng nhất. Làm thế nào để thực hiện an toàn dữ liệu ở cấp server và ở cấp database, là yêu cầu quan trọng của DBA. Trong bài lab này chúng ta sẽ tìm hiểu các khái niệm cốt lõi trong SQL server security.
Thực hiện xong bài lab này, bạn có thể biết cách chuyển đổi authentication mode, tạo login, quản lý và cấp quyền server level role, tạo và quản lý database user, tạo và sử dụng Application role, tạo contained database, contained user, cấp quyền và xem lại các quyền đã cấp.
Thực Hiện Quản Lý SQL Server Security
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.
Mr CTL – labs.ctl.vn