SQL Server establishes application security and role

The security system in Microsoft SQL Server is implemented at the lowest level, that is, the database itself. No matter what applications are used to communicate with SQLServer, this is the best way to control user activities. However, custom security control is sometimes required to meet the special needs of individual applications, especially when dealing with complex databases and the number of large tables

The security system in Microsoft SQL Server is implemented at the lowest level, that is, the database itself. No matter what applications are used to communicate with SQL Server, this is the best way to control user activities. However, custom security control is sometimes required to meet the special needs of individual applications, especially when dealing with complex databases and the number of large tables

Microsoft®SQL Server™InSecurityThe system is at the lowest level, that isDatabase. No matter what you useApplicationProgramCommunication with SQL Server is the best way to control user activities. However, sometimes you must customize security control to adapt to individualApplicationProgramEspecially when processing complex databases and databases with large tables.

In addition, you may want to restrict usersApplicationProgram(For example, using SQL query analyzer or Microsoft Excel) to access data or prevent users from directly accessing data. This access method restricts the use of users.ApplicationProgram(For example, SQL query analyzer) connects to the SQL Server instance and executes poorly written queries to avoid negative impact on the performance of the entire Server.

SQL ServerApplicationProgramRoleAdapt to these requirements.ApplicationProgramRoleAnd standardsRoleThere are the following differences:

◆ApplicationProgramRoleDoes not contain members.

Microsoft Windows NT®4.0 or Windows®2000 Groups, users andRoleAddApplicationProgramRoleWhenApplicationProgramActivate a user connectionApplicationProgramRoleTheApplicationProgramRole. The reason why the user andApplicationProgramRoleAssociation, because the user can run to activateRoleOfApplicationProgramInsteadRoleMember.

◆ By default,ApplicationProgramRoleIt is non-active and requires a password to activate it.

◆ApplicationProgramRoleDo not use standard permissions.

WhenApplicationProgramRoleThisApplicationProgramWhen activated for connection, the connection will permanently lose all the permissions, user accounts, other groups, or databases used for login in the database during the connection.Role. Connected to the databaseApplicationProgramRoleAssociated permissions,ApplicationProgramRoleThis database exists. BecauseApplicationProgramRoleOnlyApplicationBecause they exist in the database, the connection can only be accessed to another database by granting permissions to the guest user account in other databases. Therefore, if the database does not have a guest user account, the connection cannot access the database. If the guest user account does exist in the database, but the permission to access the object is not explicitly granted to the guest, no matter who created the object, the connection cannot access the object. User-DefinedApplicationProgramRoleUntil the connection exits from SQL Server.

To make sure that you can executeApplicationProgramAll functions, the connection must be lost during the connectionApplicationFor Login and user accounts or other groups or databases in all databasesRoleAnd obtainApplicationProgramRoleAssociated permissions. For example, ifApplicationProgramIf you must access a table that generally denies access from a user, you should revoke the access permission denied to the user so that the user can use the tableApplicationProgram.ApplicationProgramRoleTemporarily suspend the user's default permissions and assign them onlyApplicationProgramRoleTo overcome any conflicts with the user's default permissions.

　　ApplicationProgramRoleAllowApplicationProgram(Rather than SQL Server) takes over the responsibility for authenticating the user identity. However, SQL ServerApplicationProgramIt still needs to be verified when accessing the database, soApplicationProgramPassword is required because there is no other method to verifyApplicationProgram.

If you do not need to perform special access to the database, you do not need to grant users and Windows NT 4.0 or Windows 2000 any permissions, because all permissions can be used to access the database.ApplicationProgramAssign. In this environment, assume thatApplicationProgramThe access is safe, and is assignedApplicationProgramRoleIs possible.

There are several options for managementApplicationProgramRolePassword without hard CodingApplicationProgram. For example, you can use the encryption key stored in the registry (or SQL Server database), onlyApplicationProgramThe decryption code with the encryption key.ApplicationProgramRead key, decrypt it, and set its valueApplicationProgramRole. If multi-protocol Net-Library is used, network packets containing passwords can also be encrypted. In addition, whenRoleWhen activated, You can encrypt the password before sending it to the SQL Server instance.

IfApplicationProgramWhen you connect to an SQL Server instance in Windows Authentication modeApplicationProgramYou can useApplicationProgramRoleSet the permissions of Windows NT 4.0 or Windows 2000 users in the database. This method enables usersApplicationProgramWindows NT 4.0 or Windows 2000 audit and user permission control are easy to maintain.

If SQL Server authentication is used and user access in the database is not required to be reviewedApplicationProgramIt is easier to use a predefined SQL Server to log on to the SQL Server instance. For example, order InputApplicationProgramVerify that thisApplicationProgramAnd then use the same OrderEntry to log on to the SQL Server instance. All connections use the same logon.

DescriptionApplicationProgramRoleIt can be used with two authentication modes.

　　Example

AsApplicationProgramRoleFor example, assume that the user Sue runs the salesApplicationProgram,ApplicationProgramThe SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in the database Sales are required, however, she does not have the SELECT, INSERT, or UPDATE permission when accessing the Products or Orders table using the SQL query analyzer or any other tool. To ensure this, you can create a user-database that rejects the SELECT, INSERT, or UPDATE permissions on the Products and Orders tables.RoleAnd then add Sue as the database.Role. Then, create the SELECT, INSERT, and UPDATE permissions for the Products and Orders tables in the Sales database.ApplicationProgramRole. WhenApplicationProgramDuring runtime, it uses sp_setapprole to provide password ActivationApplicationProgramTo access the Products and Orders tables. If Sue tries to useApplicationProgramIf you log on to the SQL Server instance using other tools, you cannot access the Products or Orders table.