Business ip access whitelist

When adding a read-write separation middleware before the UDB master-slave nodes, the IP address seen by UDB from the client becomes the middleware's IP instead of the actual business IP. As a result, MySQL’s functionality of managing access permissions based on username + IP address becomes ineffective. To address this issue, the UDB read-write separation middleware provides a business IP access whitelist mechanism. This mechanism includes two functions:

1. Business IP Access Whitelist: Only the business IP on this whitelist can log in to the middleware, otherwise login is denied.

2. Business Operation Whitelist: After the business Ip login, the operations initiated will be authenticated by the middleware. If this operation is on the operation whitelist, the middleware will pass it; otherwise it will be rejected.

The business ip access whitelist and the business operation whitelist can be configured through 4 custom SQLs. In order to reduce the learning cost of users, the syntax of these 4 custom SQLs is highly similar to MySQL user permission management statements: createuser, grant, revoke, drop user.

Example:

If a user needs to create an account named "robert" and restrict access to only the "10.10.1.%" and "10.10.2.%" subnets on UHost. Moreover, robert only has select permission when initiating access in the 10.10.1.%segment, and it does not have other permissions (such as create table, insert, etc.); robert, when initiating access in the 10.10.2.% segment, has all other permissions except create table, and can authorize other users.

To achieve this authorization configuration, you can do the following:

1. Log in to the read-write separation middleware (use high-privilege users, such as root)

2. Create MySQL user:

Use standard create user, grant commands, to the main udb node (can be directly connected or through the read and write separation middleware) to create users. Among them, the user ip must be %create user 'robert'@'%' identified by '123qwe'; grant all privileges on test.* to 'robert'@'%'; Note: udb and other cloud databases, cannot authorize the entire instance (.), but can only authorize by library or table

After successful creation, you can log in to the read-write separation middleware on any uhost with this username (robert).

1. Use the ucreate user command to create the ip access whitelist:

ucreate user 'robert'@'10.10.1.%';

After executing this command, it allows the robert account of the 10.10.1.% segment to log in to the middleware, and other ips are prohibited. However, after logging in, the permissions are only show databases and show processlist, and there are no other permissions temporarily.

2. Use ugrant and urevoke commands to configure the ip operation permissions whitelist. For example: ugrant select to 'robert'@'10.10.1.%'; Note: Different from the standard grant command, ugrant omits the syntax of specifying the authorization object (on .). The authorization object of ugrant is directly inherited from grant Execution of this command opens 'robert '@'10.10.1. %' User's select permission if you want to open other permissions other than create table for roert account on 10.10.2.%, you can do this: `ugrant all privileges to 'robert'@'10.10.2.%' with grant option;urevoke create from 'robert'@'10.10.2.%'; Execution of this command allows 'robert'@'10.10.2.%' to execute all other operations except create table, database; at the same time, it also supports cascading authorization, allowing 'robert'@'10.10.2.%' to grant permissions to other users (initiating ucreate user and ugrant commands).

3. Use the udrop command to delete the access ip access control whitelist. For example:

udrop user 'robert'@'10.10.1.%';

Special note: If all ip access white lists under the robert user are deleted, it is considered that the system has not configured the white list, and at this time you can log in from any uhost with the robert account.

Permission configuration query command is provided: ushow users;