Security management of SQL Server databases (logins, roles, permissions)

Security Management of---database

-- login :
Authentication mode for SQL Server database server logins:
1)Windows authentication .
2)hybrid authentication for Windows and SQL Server

--role :
category：
1)Server Role。 Server role isfixed server functionality, users cannot create and modify server roles.You can add the server's login account to the server role so that it has the permissions of the server role。
2)Database Roles。A database role is the object of a database，used to add a user who logs on to a database to a database role, with the ability to have a database role。
--Database roles includefixed database role、Standard Database RolesAndApplication Roles。
♦ Fixed database role: Yessystem-Given, cannot be deleted, can add members；
♦ Standard Database role:You can create, delete, add members；
♦ Application Role: Yesa special role that requires a custom password, can be deleted and cannot be added to a member。

-- Permissions :
Overview : In a SQL Server database management system, permissions are the actions that a user performs on a database or data table .

Classification : Depending on the permissions set method, the permissions can be divided into three types of implied permissions , object Permissions , and statement permissions .

1) implied Permissions
Note: Refers to the permissions that are defined by the system and do not require authorization . Does not need to be explicitly granted, it is a built-in permission for specific server objects and database objects. The database's server, the owner of the database, and the owner of the database object have implicit permissions.

2) Object Permissions
Note: Refers to the grant, prohibit, or revoke permissions granted to database objects by the owner of the database. Includes whether to allow users to read data tables or attempt to perform select, update, delete, or insert operations, whether to allow users to create databases, create tables, execute stored procedures, back up databases, and so on.

3) Statement Permissions
Note: You are instructed to grant, disallow, or revoke permissions to database objects in the database definition language in the T-SQL language. Statement permissions can generally be used only by the database owner (dbo) and the SA user.
　　the grant, deny, and REVOKE commands represent grants, prohibitions, and revocation permissions, respectively .

　　♦ granting Permissions :
Grant Operation command [on] object name to user name

Example 1: (the permission to add data to the Product information table is granted to the database login user "Db_user01")
Grant insert on commodity information sheet to Db_user01
Example 2: (Grant user "Db_user01" permission to modify the "Contact Phone" field and "Mailbox" field in the Customer information table)
Grant Update (contact number, email) on customer information sheet to Db_user01

♦ Disable Permissions :
Note: Disabling permissions not only prohibits certain permissions for users or roles , but also prevents those users or roles from inheriting prohibited permissions from other roles .
Deny operation command [on] object name to user name

Example: (Prohibit user "Db_user01" to "Customer information table" to perform delete operation)
Deny Delete table on customer information table to Db_user01

♦ Revoke Permissions :
Note: Revoking permissions removes only certain permissions that a user or role has , and does not prevent users or roles from inheriting revoked permissions in other ways .
Revoke operation command [on] object name from user name

Example: (Revoke user "Db_user01" to create the database permission)
Revoke CREATE DATABASE from Db_user01

Note: "--" can be seen as a description or comment text

Security management of SQL Server databases (logins, roles, permissions)