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

Microsoft's SQL Server is a widely used database in enterprises, and now many e-commerce websites and enterprise informatization are built and deployed based on SQL Server. With the continuous development of the network and the growth of illegal user technology, there are more and more illegal operations against SQL Server database, and the confidential data of many enterprises are threatened. As a qualified DBA, you must know how to strengthen the security of SQL Server in many ways.

Blog outline:

I. SQL Server permission setting

Security Mechanism of 1.SQL Server

Login permissions settin

(1) SQL Server authentication method

1) Windows authentication mode

2) SQL Server and Windows authentication mode

3. Access permission settin

(1) permission settings at server level

(2) Database level permission setting

1) create a new database user

2) built-in database user

3) Database role

(3) object-level permission setting

1) authorize the table

2) authorize the database

II. SQL Server backup and restore

1. Why back up?

two。 Backup Typ

(1) full backup

(2) differential backup

(3) transaction log backup

3. Recovery model

(1) simple recovery model

(2) complete recovery model

(3) bulk log recovery model

(4) change the recovery model

4. Backup equipment

(1) SQL Server backup target

(2) backup equipment

5. Backup and restore

(1) Database backup

(2) Database restore; I. SQL Server permission setting

Different from other servers, SQL Server usually stores operation, development, marketing and other important data, in which a large number of data is also confidential. Therefore, mastering the permission setting of SQL Server is a necessary key skill for DBA.

Security Mechanism of 1.SQL Server

The security of SQL Server is based on two security mechanisms: authentication and access permission.

Authentication is used to determine whether the login account and password of the user who logs in to SQL Server is correct, and in turn to verify whether he or she has the right to connect to SQL Server. The access permission is used to grant the user or group what operations can be performed in the database.

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

Security mechanism of client, security mechanism of server, security mechanism of database, security mechanism of data object

(1) users must be able to log in to the client before they can access the database using SQL Server applications or administrative tools. For customers using Windows systems, it is mainly related to the security of the operating system. Mainly because of the security of Windows account.

(2) when users log in to the server, they must use an account (also known as login account) and password. The server will judge the correctness of this account and password according to different authentication methods.

(3) any account and password that can log into the server corresponds to a default database. SQL Server adopts the concept of "database user" to manage the rights of the database.

Users can only access the data objects in the database through this line of defense, and what kind of access to the data objects can be called access rights. Common access rights include querying, updating, inserting, and deleting data.

two。 Login permission setting (1) authentication method of SQL Server

SQL Server has the following two authentication modes:

Windows authentication mode; SQL Server and Windows authentication mode (mixed authentication mode); 1) Windows authentication mode

When using Windows authentication, SQL Server uses the Windows user password in the operating system. In other words, the identity of the user is confirmed by Windows. SQL Server no longer provides passwords and does not perform authentication. Windows authentication uses the Kerberos security protocol. It is a more secure protocol than SQL Server authentication, more secure than SQL Server authentication, and protects SQL Server from most illegal operations from the network.

This authentication mode is applicable to the local area network when accessing the database. This method is safe and fast to log in.

2) SQL Server and Windows authentication mode

SQL Server and Windows authentication modes are also known as mixed authentication modes. Allows users to connect using Windows authentication or SQL Server authentication. This mode is typically used when users running non-Windows system environments, Internet users, or mixed workgroup users access SQL Server. For example, when an external user accesses the database or cannot log on to the Windows domain.

When using SQL Server authentication, the login created in SQL Server is not based on the Windows user account. The user name and password are created using SQL Server and stored in SQL Server. If you must use SQL Server authentication, you must enable password policies for all SQL Server accounts, such as forced password expiration, password complexity, and so on.

Set the server authentication mode in SSMS as follows:

In SQL Server, the following security policies are provided for the SQL Server login account:

Enforce password policies; force passwords to expire; users must change passwords the next time they log in

As shown in the figure:

When creating a new SQL Server login account, in order to improve its security, it is recommended that you select the mandatory password Policy check box.

Sa is the default administrator account for SQL Server. Because this account is well known and can easily become an illegal target for malicious users, it is recommended to disable this account unless some programs require it to be used. Never set the sa account to an empty password or a weak password. It is recommended to create a super user with the same permissions as the sa account to manage the database. However, accounts with administrator privileges should not be allowed to flood.

3. Access permission settin

Access permissions in SQL Server can be set at three levels: server, database, and object.

(1) permission settings at server level

A set of server roles (also known as fixed server roles) are built into SQL Server to perform server-level administration, such as creating databases, managing and auditing login accounts, and aborting long-running processes in SQL Server instances. They scope the entire server, not a single database.

Roles in SQL Server2016 are similar to groups in Windows. By assigning different roles to different users, users can have different operation permissions.

A user can have multiple fixed server role permissions, but fixed server roles cannot be modified, added, or deleted. The specific server roles are shown in the figure:

Note: there is also a special server role in SQL Server, public, to which all logins belong. This role is the least privileged role in the server and can only be viewed.

Specific steps for assigning server-level permissions to users:

(2) Database level permission setting 1) create a new database user

After the SQL Server login account is established, the user needs to be given permission before the user can query and update the database. The login account user accesses the SQL Server instance and the database user accesses the database. If you need to access a database, you need to establish the corresponding database user in the database.

In fact, the database user is mapped to the login account. The establishment process is as follows:

2) built-in database user

Any database created contains two special users, dbo and guest by default, and cannot be modified or deleted, as shown in the figure:

Details for dbo and guest users:

Dbo is the owner of the database and has all permissions on the database. By default, the login account that creates the database automatically corresponds to the dbo user of the database, the dbo user is a member of the db_owner database role, and the db_owner database role has full permissions on the database. Therefore, it can be said that the login account created the database, and the login account has all the administrative privileges of the database. In addition, the login account sa corresponds to the dbo user of all system databases; guest is a guest user and cannot be deleted. Guest users do not map any login accounts. All unmapped login accounts will access the database as guest. Guest users do not have any permissions by default and are disabled, so be careful when enabling and granting permissions. 3) Database role

The server role mainly controls the access of the server login name within the server scope, but it appears to have too much authority in setting the management and operation permissions of the specific database. therefore, we need to rely on the database role to divide the database-level objects into more detailed permissions. SQL Server provides a set of fixed database roles by default to give users permission to operate the database, such as querying data tables, backing up and restoring databases, and so on.

Fixed database roles and their permissions are shown in the figure:

Note: the default database also contains a role called public, which is used to capture all default permissions of database users, all users belong to public, and this role cannot be deleted.

To set database-level permissions for a user:

(3) object-level permission setting

A database usually contains multiple data tables, views, stored procedures and other objects. if a user is given read permission to the database, the user can read all the tables or views under the database.

1) authorize the table

SQL Server also provides more detailed permissions, such as permissions to query, insert, or update data, which can be applied to a single table. As shown in the figure:

The specific steps are as follows:

Note: if both grant and deny permissions are given, the deny permission takes precedence over the grant permission.

2) authorize the database

The database itself is also an object, and authorization for the database can grant it permission to create various other objects in the database, such as changing users, creating tables, querying data, and so on.

The specific steps are as follows:

II. SQL Server backup and restore

The situation that database administrators worry most is that the database is paralyzed, resulting in data loss. Backup, as a copy of the data, can effectively protect and restore the data.

1. Why back up?

For production data, the security of data is very important, and any loss of data may have serious consequences.

In general, the reasons for data loss are:

Program error; human error; computer failure (system crash); disk failure; disaster and theft

Therefore, one of the main responsibilities of the database administrator is to implement and plan a proper backup and restore strategy to protect the database and avoid the loss of data caused by various failures, so as to restore the database as soon as possible after system failure.

two。 Backup Typ

Three common backup types are available in SQL Server:

(1) full backup

A full backup includes a backup of the entire database, partial transaction logs, database structure, and file structure. Completing a backup represents the database at the time of completion of the backup.

A full backup is the basis of a backup and provides a baseline for any other backup. Other backups, such as differential backups, can only be performed after the backup is completed.

(2) differential backup

Differential backup refers to the backup of all the data that has changed since the last full backup. The backup process can identify which part of the database has been modified and only back up this part.

The advantage of differential backup is that it is fast and takes little time to back up the database, but differential backup requires a complete backup in advance.

(3) transaction log backup

Transaction log backups record all changes to the database. The following factors need to be considered when backing up transaction logs:

The transaction log cannot be backed up until at least one backup has been performed; the transaction log cannot be backed up under the simple recovery model; if there is no consistent database backup, the transaction log cannot be restored

During a database transaction log backup, SQL Server does the following:

Backup transaction log: from the last successful transaction log backup to the tail of the current transaction log; truncate the transaction log: to the beginning of the active part of the transaction log, discard the inactive part

Note: transaction log backups are mainly SQL statements, not the entire database structure, file structure, or data.

3. Recovery model

Whether it is backup or restore, it is carried out in a certain recovery model. Recovery model is a characteristic of database and the basic behavior that controls the backup and restore of the database. The library administrator should choose the appropriate recovery model according to the actual operation requirements of the enterprise system and the requirements for data availability and recovery.

SQL Server provides three recovery models: simple model, full recovery model and bulk recovery model.

(1) simple recovery model

Under simple recovery, inactive logs will be deleted, so transaction log backups are not supported, as shown in the figure:

There are some database backups in the figure. Some time after the most recent backup of T5, data loss occurred in this database. The database administrator will use T5 backup to restore the database to the point in time when the backup is complete, after which changes to the database will be lost.

Because transaction log backups are not supported, the database can only be restored to the point in time of the backup, and the database cannot be restored to a point of failure or a specific point in time. Therefore, the simple recovery model is mainly used for small databases and databases that change infrequently.

(2) complete recovery model

For very important production databases, you may need to restore to some point in history in the event of a failure. In the event of a failure, we must ensure that the data is not lost, and that the data can be restored to the state when the failure occurs. In this way, the full recovery model must be adopted.

The full recovery model provides maximum protection against data loss in the event of a failure, including database and transaction log backups, and provides comprehensive protection against media failures. This model uses the database and the backup area of all logs to recover the database. If the log is not corrupted, SQL Server can recover all data except transactions at the moment of failure.

Because transaction log backups are supported, the full recovery model can restore the database to any specified point in time. As shown in the figure:

In the figure, the database can be restored to the point of failure to recover all data, the restore operation can restore all committed transactions, and ongoing transactions will be rolled back.

(3) bulk log recovery model

In some situations, DBA needs to insert, update or delete a large number of data to the database, such as importing millions of pieces of data at a time. If you are in the full recovery model, these operations will result in a large number of logging, resulting in poor database performance. In this environment, the bulk-logged recovery model can be used to improve performance.

Although the bulk-logged recovery model fully records other transactions, it keeps records of bulk operations to a minimum. The bulk-logged recovery model complements the full recovery model, provides good performance and takes up less log space d.anshi. The bulk-logged recovery model increases the risk of data loss in these bulk copy operations, because the minimum logging bulk operation does not record the bookstore one by one.

As long as the log backup contains bulk operations, the database can only be restored to the end of the log backup, not a transaction at a certain point in time, or a marked transaction in the log backup.

(4) change the recovery model

As shown in the figure:

4. Backup device (1) SQL Server backup destination

In SQL Server, to back up a database, you have to consider where to store the backup. Backup targets supported by SQL Server are disks, disks, or URL. SQL Server can back up a database, transaction log, or file to a specified location on disk, tape, or URL. Disk is the most commonly used storage medium, and tape backup is used in much the same way as disk devices.

(2) backup equipment

When making a backup, you first need to create a file to store the backup data, which is called the backup device.

SQL Server uses both physical and logical backup devices to identify backup devices.

The physical backup device is the name used by the operating system to represent the backup device; for example: C:\ backup logical backup device is a user-defined alias used to identify the physical backup device

Logical backup devices are permanently stored in system tables within SQL Server, and the advantage of using logical backup devices is that referencing it is easier than referencing physical device names.

5. Backup and restore (1) Database backup

The steps are as follows:

The same method for differential backup, transaction log backup!

You can also make a custom scheduled backup and choose your own!

(2) Database restore

Do the following:

Select the database to specify the restore

Choose the database to restore and back up the source files by yourself!

-this is the end of this article. Thank you for reading-

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.