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

SQL Server: SQL Server

SQL Server is different from other servers in that it usually holds important data such as operations, development, marketing, etc., and a large amount of the data is also confidential. Therefore, mastering SQL Server permission settings is a key skill for DBAs.

1. SQL Server security mechanism

SQL Server security is based on authentication and access permissions. Authentication is used to determine whether the login account and password of the user logging in to SQL Server are correct, so as to verify whether he has the permission to connect to SQL Server.

Access permissions are used to grant users or groups the ability to perform certain operations in the database, such as creating tables, querying data, and so on.

SQL Server's security mechanism mainly includes the following four aspects:

Client security mechanisms;

Security mechanism of server;

Security mechanisms for databases;

security mechanism of data object;

Users must be able to log on to the client before they can access the database using SQL Server applications or administrative tools. For customers who use Windows, it mainly involves operating system security. This is mainly about Windows account security.

When users log in to the server, they must use an account (also known as a 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 uses the concept of "database user" for database authority management.

Users can access the data objects in the database only after passing through the first three lines of defense. What kind of access can be made to the data objects is called access rights. Common access rights include querying, updating, inserting, and deleting data. 1) SQL Server authentication

SQL Server has two authentication modes:

Windows authentication mode;

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

①Windows authentication mode

When Windows authentication is used, SQL Server uses the Windows user password from the operating system. That is, user identity is confirmed by Windows. SQL Server no longer provides passwords and does not perform authentication. Windows authentication uses the Kerberos security protocol. More secure protocol than SQL Server authentication, more secure than SQL Server authentication, protects SQL Server from most illegal operations from the network.

Windows authentication mode applies to accessing databases within a local area network.

SQL Server and Windows authentication modes

SQL Server and Windows authentication mode is also known as mixed authentication mode. Allows users to connect using Windows authentication or SQL Server authentication. This mode is typically used when users running non-Windows systems, Internet users, or miscellaneous workgroup users access SQL Server. For example, when an outside user accesses a database or cannot log on to a Windows domain.

When using SQL Server authentication, logins created in SQL Server are not based on Windows user accounts. User names and passwords are created using SQL Server and stored in SQL Server. If SQL Server authentication must be used, password policies such as mandatory password expiration, password complexity, and so on must be enabled for all SQL Server accounts.

Set the server authentication mode in SSMS as follows:

Open SSMS, right-click SSMS instance, and select Properties from the shortcut menu that pops up.

Select the Security option in the Server Properties window that opens to set the authentication mode

2) Login account settings

By using SSMS you can create and manage login accounts. By default, SQL Server uses Windows authentication mode and SQL Server login accounts cannot log in. If you need to log in with 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 SQL Server login accounts:

Enforce password policies;

Force password expiration;

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

When creating a new SQL Server login account, it is recommended that you check the Enforce password policy checkbox to improve its security.

sa is the default administrator account for SQL Server. Because this account is widely known and easy to be targeted by malicious users, it is recommended to disable this account unless some programs require it to be used. Never set the sa account as a blank password or weak password. It is recommended to create a super user with the same permissions as the sa account to manage the database. However, it is not allowed to flood accounts with administrator privileges.

SQL Server Access Permission Settings

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

1. Server-level permission settings

Built into SQL Server are a set of server roles (also known as fixed server roles) that perform server-level administration, such as creating databases, managing and auditing login accounts, and aborting long-running processes in SQL Server instances. They are scoped to an 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 operating privileges.

A user can have multiple fixed server role permissions, but fixed server roles cannot be modified, added, or deleted. Specific server roles and the actions they can perform are as follows:

SQL Server also has a special server role, 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 settings 1) Create a new database user

After the SQL Server login account is established, the user needs to be granted user rights before the user can query and update the database. Login account users access SQL Server instances and database users access databases. If you need to access a database, you need to establish the corresponding database user in the database.

In effect, database users are mapped to login accounts. The process is as follows:

2) Built-in database users

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 database's dbo user, who in turn is a member of the db_owner database role, which has full permissions on the database. Thus, it can be said that the login account created the database, and this login account has all administrative rights for the database. In addition, the login account sa corresponds to dbo users for all system databases.

Guest is a guest user and cannot be deleted. Guest users do not map to any login accounts. All unmapped logins will access the database as guest users. Guest users do not have any permissions by default and are disabled, so be careful when enabling and granting permissions. 3) Database role

Server roles mainly control server login access within the scope of the server, but in setting specific database management and operation permissions appear to be too large, so you need to rely on database roles to database level objects for more detailed permission division. SQL Server provides a fixed set of database roles by default, which are used to give users permissions to manipulate databases, such as querying database tables, backing up and restoring databases, etc. Fixed database roles and their permissions are shown below:

Fixed database also contains a role named public, which is used to capture all default permissions of database users. All users belong to public role, and this role cannot be deleted either.

Data control languages include GRANT and REVOKE;

The steps to set database level permissions for users are as follows:

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 granted read permission to the database, the user can read all tables or views under the database.

1) Authorize the table

SQL Server also provides more granular permissions, such as the right to query, insert, or update data, that can be applied to individual tables. Common permissions and their descriptions are shown below:

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

If both Grant and Deny permissions are granted, Deny takes precedence over Grant.

2) Authorize the database

Database itself is an object, database authorization is, you 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 granting user permissions on a single database are as follows:

SQL Server Backup and Restore

Database administrators are most worried about the situation is the database crash, resulting in data loss. As a copy of data, backup can effectively protect and restore data.

1. Why backup

For production data, data security is critical, and any loss of data can have serious consequences. Generally, the main causes of data loss include the following categories:

Program error; human error (e.g. administrator error); computer failure (system crash); disk failure; disaster and theft;

Therefore, one of the main responsibilities of a database administrator is to implement and plan a proper backup and restore strategy to protect the database from loss due to various failures and to recover the database as soon as possible after a 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 backups of the entire database, partial transaction logs, database structure, and file structure. A complete backup represents the database at the time of backup completion.

A full backup is the foundation for backup and provides a baseline for any other backup. Other backups, such as differential backups, cannot be performed until the backup is complete.

2) Differential backup

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

Differential backups have the advantage of being fast and taking very little time to back up the database, but differential backups require a complete backup to be performed 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 the transaction log:

The transaction log cannot be backed up until at least one complete backup has been performed;

Transaction logs cannot be backed up in simple recovery mode;

Transaction logs cannot be recovered without a consistent database backup;

During database transaction log backups, SQL Server does the following:

Backup transaction log: backup from the last successful transaction log 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;

Also, transaction log backups are primarily SQL statements, not the entire database structure, file structure, or data.

3. Recovery mode

Both backup and restore are performed in a certain recovery mode. A recovery mode is a characteristic of a database that controls the basic behavior of database backups and restores. The library administrator should select the appropriate recovery model based on the operational requirements of the enterprise system and the requirements for data availability and recovery.

SQL Server provides three recovery models: Simple, Full, and Bulk.

1) Simple recovery mode

Under simple recovery, inactive logs are deleted, so transaction log backups are not supported, as shown in the figure below, where some database backups are made. Some time after the most recent backup t5, there was a data loss in this database. Database administrators will use t5 backups to restore the database to the point in time when the backup is complete, after which changes to the database will be lost.

Since 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 the point of failure or to a specific point in time. Therefore, the simple recovery model is primarily used for small databases and databases that do not change frequently.

2) Full recovery mode

For critical production databases, a failure may require recovery to a point in history. Once a failure occurs, it must be guaranteed that the data is not lost. Once a failure occurs, it must be guaranteed that the data is not lost and that the data can be restored to the state at the time of the failure. This requires a full recovery model.

The full recovery model provides maximum protection against data loss in the event of a failure, includes database backups and transaction log backups, and provides comprehensive protection against media failures. This pattern restores the database using the backup area of the database and all logs. If the logs are not corrupted, SQL Server can restore all data except transactions at the moment of failure.

Because transaction log backups are supported, the full recovery model restores 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, thereby recovering all data. The recovery operation restores all committed transactions, and ongoing transactions roll back.

3) Bulk Log Recovery Mode

DBAs need to perform large-scale data insertion, update, or deletion operations on databases in some cases, such as importing millions of pieces of data at once. Under full recovery mode, these operations generate a large number of log records, resulting in poor database performance. In this environment, bulk-logged recovery models can be employed to improve performance.

Although the bulk-logged recovery model logs other transactions completely, it logs only minimal bulk operations. 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 for these bulk-copy operations because the minimally logged bulk-copy operations do not log bookstores individually.

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

4) Change recovery mode

4. Backup equipment

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

SQL Server identifies backup devices using both physical and logical backup devices.

A physical backup device is the name the operating system uses to represent a backup device; for example: C: \Backups\Accounting\Full.bak.

A logical backup device is a user-defined alias that identifies a physical backup device; the 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 it than the physical device name.

4. Backup and restore 1. Database backup

2. Database restoration

--------This article ends here, thanks 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.