SQL Learning Summary (10)--Security control technology

10.1 Manage login Account

Create login account Syntax format:

sp_addlogin login ID, login password, default database, default language, security code, encryption

In the whether encryption option, NULL indicates encryption of the password. Skipencryption indicates that the password is not encrypted. Skipencryptionold, used only for SQL Server upgrades, indicates that the old version has been encrypted with the password.

To modify the login account syntax format:

Change Password: sp_password old password, new password, specify login account

Modify default database: Sp_defaultdb Specify login account, default database

Modify default language: Sp_defaultlanguage Specify login account, default language

View login account: sp_helplogins Specify login account

Delete login account: Sp_droplogin Specify login account

10.2 Manage Database Users

Create DATABASE User: sp_grantdbaccess login account, database user name

View all databases user name: Sp_helpuser

To view the specified database user: Sp_helpuser database user Name

Delete database User: sp_revokedbaccess Specify the database name

Note: To create and delete database users, you must have a db_owner or db_access_admin database role to perform.

10.3 Management Server Role

Add Server Role member: sp_addsrvrolemember login account name, server role name

Remove server role member: sp_dropsrvrolemember login account name, server role name

Note: Server roles are scoped to the server and their permissions cannot be modified, there can be only two actions on the server role, and members are added to or removed from the server role. A total of 9 server roles:

1.Bulkadmin: Perform BULK insert operation

2.Dbcreater: Create and change databases

3.Disadmin: Managing Disk files

4.Processadmin: Managing processes running in SQL Server

5.Public: Provide default permissions for users in the database

6.Securityadmin: Login for Management Server

7.Serveradmin: Configure server-wide settings

8.Setupadmin: Managing Extended stored procedures

9.Sysadmin: Perform any operation in the SQL Server installation

10.4 Manage Database Roles

Create DATABASE role: Sp_addrole database role name, owner of database role

Delete database role: sp_droprole database role name

To add a member to a database role: sp_addrolemember database role name, database user name

To remove a member from a database role: Sp_droprolemember database role name, database user name

10.5 Managing Application Roles

Unlike a database role, the program by default does not contain any members, and is non-active.

To create an application role:

Create Applicatin role application role name with password= ' password '

To modify the application password:

Execute Sp_approlepassword application role name, password

To view application role information:

Execute sp_helprole Application role name

To remove an application role

Drop application Role Application role name

10.6 Use SQL Management of code implementation permissions

To authorize a database user or role:

Grant STATEMENT on table name or view name to user name or role name with Grant OPTION

Statement user permissions: SELECT, INSERT, UPDATE, delete, CREATE TABLE, create rule, and so on.

With GRANT option indicates that the permission grant can grant other users access to the database object.

To deprive a database user or role of authorization:

Revoke STATEMENT on table name or view name to user name or role name with GRANT OPTION

Deny database user or role authorization:

Deny STATEMENT on table name or view name to user name or role name with GRANT OPTION

Create two login accounts

Execute sp_addlogin ' hyuser1 ', ' hy123456 ', ' db_company ', ' 中文版 '

Execute sp_addlogin ' hyuser2 ', ' hy111111 ', ' db_company ', ' 中文版 '

Create two database users using the two login accounts created, respectively,

Execute sp_grantdbaccess ' hyuser1 ', ' dbhyuser1 '

Execute sp_grantdbaccess ' hyuser2 ', ' dbhyuser2 '

Create and assign permissions to a database role

Execute sp_addrole ' Hyrole1 '

Grant Select,insert on warehouse to Hyrole1

Assigning permissions to database users

Grant Select,update on staff to Dbhyuser1

Deny database user authorization or deny role authorization

Deny update on staff to Dbhyuser1,hyrole1

Denial of Role authorization

Revoke select on warehouse to Hyrole1

SQL Learning Summary (10)--Security control technology