Shulou(Shulou.com)06/01 Report--

With the development of the network and the growth of the technology, there are more and more SQL Server databases, and the confidential data of many enterprises are threatened. How convenient is it to enhance the security of SQL Server?

I. the security mechanism of SQL Server

The security mechanism of SQL Server mainly includes the following four aspects:

(1) client security mechanism: users must be able to log in to the client and then use applications or management tools to access the database. For Windows customers, it is mainly related to the security of the operating system, mainly the security of Windows accounts.

(2) Security mechanism of the server: when users log in to the server, they must use an account and password, and the server will judge the correctness of this account and password according to different authentication methods.

(3) Database security mechanism: any account and password that can log in to the server corresponds to a default database. SQL Server adopts the concept of "database user" for database-level rights management.

(4) Security mechanism of data objects: users can only access database data objects (such as tables and views) through the first three lines of defense. Common access rights include query, update, insert and delete of data.

Second, login permission setting

SQL Server has two authentication modes:

Windows authentication mode: log in with the Windows user and password in the operating system, and SQL Server does not require a password and does not perform authentication. Windows authentication uses the Kerberos security protocol, which is more secure than SQL Server authentication. It is suitable for accessing database within the local area network (such as AD domain).

SQL Server and Windows authentication mode: also known as mixed authentication mode, which allows users to connect using Windows authentication and SQL Server authentication

Open SSMS, right-click "SQL Server instance", select properties, select security, and set the authentication mode, as shown in the figure:

You can use SSMS to create an administrative login account, expand SQL Server instance, security, right-click the login name, and create a new login name, as shown in the following figure:

III. Access permission setting

Access permission settings can be set at server, database and object levels respectively. SQL Server can be compared to a hotel, as shown in the following figure:

1. Server level permission settin

A set of server roles are built into SQL Server to perform server-level management, such as creating databases, managing and auditing login accounts, acting on the entire server rather than a single database.

The steps to assign server-level permissions to a user are as follows:

Expand "SQL Server instance" in SSMS, right-click a login, select properties, select server role, and set the permissions that need to be given to the user, as shown in the following figure:

two。 Permission settings at the database level

After the SQL Server login account is established, the user needs to be given permission to operate the database. If you need to access a database, you need to establish the corresponding database user in that database, and the database user is mapped to the login account.

For example, to create a user User2 on the MySchool database, the user corresponds to a login account User2, expand database MySchool, security, right-click the user, select New user, open the "Database-New" window, and make relevant settings, as shown below:

The specific steps for setting the database level for the user are as follows:

Expand "SQL Server instance" in SSMS, right-click a login, select attributes, select user mapping, and set the permissions to be granted to the user, as shown in the following figure:

You can also create a database role when the database role does not meet the requirements

Expand database MySchool, expand security, right-click the role, and create a new database role.

3. Object-level permission settin

A database usually contains multiple data tables, views, stored procedures, etc., which can grant permissions to users on a single table

(1) authorize for the table

Find the table that needs authorization on SSMS, right-click the table, select properties, permissions, click search, add users who need authorization, and select the permissions to be granted to users below

(2) authorize the database

The database itself is also an object, and the authorization method is similar to the table. Right-click the database, properties, permissions.

(3) authorize stored procedures

Expand server, database, select database, programmability, stored procedure, properties, permissions in turn

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.