Security model for SQL Server (RUI)

Let's take a look at the security model of SQL Server, first take a picture: This is the three Layer Security management system of SQL Servers, let's say that you live in a fortified community, if you want to enter your room, of course, you need to break San Guan. The first level: you need to pass through the Community gate inspection, into the community; The second pass: to your Danyuanlou door, you also need the unit door key or doorbell password; third: After entering the unit door, you also need your own room key. Take a look at the above diagram, SQL Server security management is not very similar to the community of the three-layer authentication gateway? Similarly, we want to operate a database of a table also to break San Guan: The first level: we need to log on to the SQL Server system, that is, the need to login to the account; second level: We need to access a database (equivalent to our Danyuanlou), You need to be a third-level user of the database: we need to access the tables in our database (the equivalent of opening our rooms), which requires DBA authority from the database administrator, such as additions, modifications, deletions, queries, and other permissions. As we all know, there are two ways to login authentication:1. SQL authentication: For users who are not Windows platforms or for Internet users, you need to provide an account and password2. Windows authentication: Suitable for Windows platform users, no password required, and Windows Integrated authentication then there are two types of login accounts: SQL account and Windows account. Let's take a look at how to create a login account, how to create a database user, and how to authorize a user. 1Create Login • Add Windows login account EXEC sp_grantlogin ' Leno/123‘/*domain name/user name*/• Add SQL login account exec sp_addlogin ' Zhangsan', ' 1234 '/* username/password */ 2Create a database user to create a database user needs to call the system stored procedure sp_grantdbaccess, which is used: EXEC sp_grantdbaccess ' login account name ', ' database username ' where database user name is an optional parameter, default is login User name, which is the default for database users and the login account name. Use studbgoexec sp_grantdbaccess'leno/123','Lenodbuser'EXEC sp_grantdbaccess'Zhangsan','Zhangsandbuser'In fact, the system itself is built into two database users: q dbo User q indicates that the database owner (DB owner) Q cannot delete the dbo user, this user always appears in each database Q guest user Q For login accounts without database users access q each database can or can be deleted by the DBO user: The database owner is the owner of the databases, in general, who created the database, who is the owner of the database, but can be transferred, like the transfer of housing title card. Guest Guest User: Just as you are not an employee of a company, you enter the company as a guest (guest) identity. The guest user in the database has the same meaning: if you log in to SQL Server and want to access a database but not a user of that database, when you access it, SQL Server assumes that you are accessing the database as the guest user. As for whether you can access the database as a guest guest, it depends on the administrator's authorization. If an administrator grants access to the Guest account, you can access it. So what is called access? To make an analogy: for a house, the authority of the House refers to the right to sell the property (homeowner), sublet (may be the tenants do not live, but did not expire), can only live (rental people) and so on. For a database, access rights refer to the increase (insert), delete, change (update), check (Select) permissions, and other execution permissions. 3. Syntax grant permissions to the database user authorization [on table name] to database user use Studbgo/*--Assign a SELECT, INSERT, UPDATE permission for Zhangsandbuser to the table Stuinfo--*/GRANTSelect, INSERT, update on Stuinfo to Zhangsandbuser/*-- assigning permissions to the Lenodbuser to the table--*/grant CREATE table to Lenodbuser note that authorization can only be granted to database users, not to the login account. And the permissions of the database are limited to the current database, and if you want to access other databases, you also need to be users of other databases. So, now everyone should know: login accountAccess rights, database users, the three gateways are the security model for SQL Server.

Security model for SQL Server (RUI)