Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Permission setting of SQL Server and data backup and restore

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

Brief introduction

SQL Server is a relational database management system developed by Microsoft Company. It has the advantages of easy to use, good scalability and high integration of related software. it can be used across a variety of platforms, from laptops running Microsoft Windows 98 to large multiprocessor servers running Microsoft Windows 2012.

Microsoft SQL Server is a comprehensive database platform that uses integrated business intelligence (BI) tools to provide enterprise-level data management. The Microsoft SQL Server database engine provides more secure and reliable storage for relational and structured data, allowing you to build and manage highly available and high-performance data applications for your business.

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.

1. The security mechanism of SQL Server

The security of SQL Server is based on two security mechanisms: authentication and access permission. Among them, authentication is used to determine whether the login account and password of the user who logs in to SQL Server is correct, so as to verify whether he has the right to connect to SQL Server.

Access permissions are used to grant users or groups what actions they can perform in the database, such as creating tables, querying data, and so on.

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

Security mechanism of client

Security mechanism of server

The security mechanism of database

Security mechanism of data object

Users must be able to log in to the client before they can use SQL Server applications or administrative tools to access the database. For customers using Windows systems, it is mainly related to the security of the operating system. It's mainly about the security of Windows account.

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

Any account and password that can log on to 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 the first three lines of defense, and what kind of access to the data objects is called access rights. Common access rights include querying, updating, inserting, and deleting data. 2. Login permission setting 1) SQL Server authentication method

SQL Server has the following two authentication modes:

Windows authentication mode

SQL Server and Windows authentication mode (mixed authentication mode)

① 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.

The Windows authentication mode is suitable for accessing the database within the local area network.

② 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:

Open SSMS, right-click the SSMS instance, and select Properties from the pop-up shortcut menu

You can set the authentication mode by selecting the Security option in the Server Properties window that opens

2) Login account settings

Login accounts can be created and managed by using SSMS. By default, SQL Server uses Windows authentication mode, and SQL Server's login account cannot be logged in. If you need to log in using the SQL Server account you created, you need to change the authentication mode to SQL Server and Windows authentication mode

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

Enforce password policy

Force password expiration

The user must change the password the next time he logs in

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.

II. SQL Server access permission setting

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

1. Permission settings at the 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 and the actions they can perform are as follows:

There is also a special server role in SQL Server, public, to which all logins belong. This role only has permission to view the database.

1) the specific steps for assigning server-level permissions to users are as follows:

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:

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 dbo users 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. The fixed database roles and their permissions are shown below:

The fixed database also contains a role called public, which is used to capture all default permissions of database users. All users belong to the public role, which also cannot be deleted.

Data control languages include GRANT and REVOKE

The specific steps for setting database-level permissions for users are as follows:

You can configure your own options according to your needs.

3. Object-level permission settings

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. Commonly used permissions and their descriptions are shown in the following figure:

The specific steps for granting user permissions on a single table are as follows:

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 for authorizing user rights on a single database are as follows:

III. SQL Server backup and restore

The situation that database administrators worry most is that the database is paralyzed, resulting in data loss. As a copy of the data, backup 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 main causes of data loss include the following:

Program errors; human errors (such as misoperation by administrators); computer failures (system crashes); disk failures; disasters 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. 2. Backup type

Three common backup types are provided in SQL Server: full backup, differential backup, and transaction log backup.

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

Transaction logs 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: discard the inactive part at the beginning of the active part of the transaction log

In addition, 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 a simple recovery, inactive logs are deleted, so transaction log backups are not supported, as shown in the following figure, where some database backups are made. 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

4. Backup equipment

When making a backup, you first need to create a file to store the backup data. this backup file 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:\ Backups\ Accounting\ Full.bak.

A logical backup device is a user-defined alias used to identify a physical backup device; a logical backup device is permanently stored in a system table within SQL Server, and the advantage of using a logical backup device is that it is easier to reference than a physical device name.

Backup and restore 1. Database backup

2. Database restore

-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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report