Chapter 2 User Authentication, Authorization, and Security (5): use a fixed server role, authentication

Chapter 2 User Authentication, Authorization, and Security (5): use a fixed server role, authentication
Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

Previous Article: http://blog.csdn.net/dba_huangzj/article/details/38817915

 

Preface:

 

The Logon account allows you to connect to SQL Server. If a database user maps to this account, this account can also access the corresponding database. By default, they do not have server-level management permissions. The fixed server role allows you to simplify authorization and revoke permissions.

 

Implementation:

 

1. Open the logon attribute box and select the server role page:

The following server roles are displayed:

 

Their functions are described as follows:

Role name	Description
BulkadminBulkadmin	Members of a fixed server role can run the bulk insert statement.
Dbcreator	Members of a fixed server role can create, change, delete, and restore any database.
Diskadmin	Members of a fixed server role can manage disk files.
Processadmin	A member of a fixed server role can terminate a process running in a database engine instance.
Public	By default, all SQL Server users, groups, and roles belong to the public fixed Server role.
Securityadmin	Members of a fixed server role can manage the login name and its attributes. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. In addition, they can reset the password of the SQL Server login name.
Serveradmin	Members of a fixed server role can change the server range configuration options and disable the server.
Setupadmin	Fixed server role members can add or delete linked servers and execute certain system stored procedures.
Sysadmin	Members of a fixed server role can execute any activity in the database engine.

 

2. You can use the following statement to add a server role member:

ALTER SERVER ROLE <role_name> ADD MEMBER <login>;

 

3. You can use the following statement to view role members:

 

SELECT  role.name AS role ,         role.is_fixed_role ,         login.name AS login FROM    sys.server_role_members srm         JOIN sys.server_principals role ON srm.role_principal_id = role.principal_id         JOIN sys.server_principals login ON srm.member_principal_id = login.principal_id;

Principle:

 

By adding members to a server role, you can use the preset management permissions in the role. The public role is introduced from 2005. Each Logon account is automatically added to this role and cannot be removed from this role and its members. Unlike other fixed server roles, you can modify the public permissions to "initialize" the default permissions of all accounts. You can use the following statement to view the public role permissions:

 

SELECT permission_name, state_desc, SUSER_NAME(grantor_principal_id) grantor FROM sys.server_permissions WHERE grantee_principal_id = SUSER_ID('public');

Next article: http://blog.csdn.net/dba_huangzj/article/details/38867489

How to set the server

Set RADIUS server

Find an article about building a RADIUS server on Linux

Please smile:

How to build a RADIUS server on Linux

As a network administrator, you need to store user information for management for each network device you want to manage. However, network devices generally only support limited user management functions. Learn how to use an external RADIUS server on Linux to authenticate users. Specifically, an LDAP server is used for authentication, the user information stored on the LDAP server and verified by the RADIUS server can be centrally stored, which reduces the management overhead of user management and makes the remote login process safer.

As part of network security in modern systems, data security is as important as system security. Therefore, protecting data-ensuring confidentiality, integrity, and availability-is critical to administrators.

In this article, I will talk about the confidentiality of data security: ensure that protected data can only be accessed by authorized users or systems. You will learn how to create and configure a Remote Authentication Dial-In User Service Server (RADIUS) on Linux to perform User Authentication, authorization, and accounting (AAA ).

Introduction to components

First, let's talk about the RADIUS protocol, AAA components, how they work, and LDAP protocol.

The Remote Authentication Dial-In User Service protocol is defined In RFC 2865 of IET (see references for links ). It allows network access servers (NAS) to perform user authentication, authorization, and accounting. RADIUS is a UDP-Based Client/Server protocol. A radius client is a network access server. It is usually a vro, vswitch, or wireless access point (the access point is a specially configured node on the network, and WAP is a wireless version ). A radius server is usually a monitoring program running on a UNIX or Windows 2000 Server.

RADIUS and AAA

If NAS receives a user connection request, it will pass them to the specified RADIUS server, which verifies the user and returns the user configuration information to NAS. Then, NAS accepts or rejects connection requests.

The fully functional RADIUS server supports many different user authentication mechanisms. In addition to LDAP, it also includes:

PAP (Password Authentication Protocol, which is used together with PPP. In this mechanism, the Password is sent to the client in plaintext for comparison );

CHAP (Challenge Handshake Authentication Protocol, which challenges the Handshake verification Protocol, which is safer than PAP and uses both the user name and password );

Local UNIX/Linux System Password Database (/etc/passwd );

Other local databases.

In RADIUS, authentication and authorization are combined. If a user name is found and the password is correct, the RADIUS server returns an Access-Accept response, which includes some parameters (Attribute-value pairs) to ensure Access to the user. These parameters are configured in RADIUS, including the access type, protocol type, IP address specified by the user, an access control list (ACL), or a static route to be applied on NAS, there are other values.

The RADIUS accounting feature (defined in RFC 2866; see references for links) allows sending data at the beginning and end of a connection session, indicates the amount of resources that may be used during a session for security or billing, such as time, packets, and bytes.

Lightweight Directory Access Protocol

Lightweight Directory Access Protocol (LDAP) is an open standard that defines the full text for accessing and updating X.500 directories...>

VMware Authorization Service item cannot be started

The VMware Authorization Service is stopped due to a 6000002 Service error. The Windows Application LOG errors involved in this error include: 1. Failed to retrieve token for VMware user: Logon Failed: user requested login type not granted to this computer. (1385) 2. Failed to retrieve token for VMware user: Logon Failed: Disable the current account. (1331) 3. cocould not choose a' _ vmware_user _ 'password long enough (min length 0). Aborting.
First, when starting The vmvm, we sometimes encounter The VMware Authorization Service is not running .. The VM cannot be started. In fact, the VM service process VMware Authorization ServiceAuthorization and authentication service for starting and accessing virtual machines in WINDOWS is not enabled. At this time, it is very likely that the VM cannot start this service for various reasons. Generally, when you start the Service manually, the VMware Authorization Service is suspended due to a 6000002 Service error. Type error. At the same time, after this error occurs, the system will generate relevant causes for startup failure in the Event Viewer application. For example, the following are three common causes: 1. failed to retrieve token for VMware user: Logon Failed: user requested login type not granted to this computer. (1385) this situation is generally caused by group policies. You can enter GPEDIT at the start and run. MSC, then go to "Computer Configuration", "WINDOWS Settings", "Security Settings", "Local Policy ", "User Rights Assignment" is found in the right-side Details window to add _ vmware_user _ user or related user groups to the local Logon Policy, and in the deny local Logon Policy, delete _ vmware_user _ or related user groups. After modification, the service can be started normally. 2. Failed to retrieve token for VMware user: Logon Failed: Disable the current account. (1331) if the system record contains such an error, __vmware_user _ has been disabled, remove _ vmware_user _ from user management. 3. cocould not choose a' _ vmware_user _ 'password long enough (min length 0 ). aborting. this error indicates that the _ vmware_user _ user may have been deleted, and you can create a new user _ vmware_user _ in the user management column. It should be noted that after the user is established, a complicated password should be set for the operating system security, and the _ vmware_user _ user is denied from the network access to this computer policy in the Group Policy. Prevent unauthorized access to the system.