How to create users and grant authorization in Mysql8

1. Enter mysql

mysql -uroot -p

2. Create user

create user 'testuser1'@'%' identified by '123456';

This means creating a user testuser1 that does not restrict IP login.

The user’s password is 123456

% means no restriction on ip login

Refresh the permissions, refresh them after each permission change

flush privileges;

You can log in to the user by creating a new local connection

When you open it, you will find that there is only one database information_schema

3. Empower users

grant all privileges on test_grant.* to 'testuser1'@'%' with grant option;

Here it means giving User testuser1 grants all permissions to all tables in the database test_grant (this is the database I created before)

with grant option means that the user can grant permissions to other users, but cannot exceed the user's permissions

Looking at this time, user testuser1 has an additional test_grant database

all privileges here can be replaced with select, insert, update, delete, drop, create, etc.

4. View user permissions

show grants for 'testuser1'@'%';

5. Revoke user permissions

revoke all privileges on test_grant.* from 'testuser1'@'%';

This means revoking all operating permissions of user testuser1 on database test_grant

Note: If you write like this here, you will find that you still have the database test_grant when you open it (but you cannot operate the database). This is because I used with grant option when I created it before, because all privileges are except All permissions with grant option

Execute the following statement to reclaim all user permissions

revoke all privileges,grant option from 'testuser1'@'%';

6. Delete users

drop user 'testuser1'@'%';

7. Query all users and their permissions

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

You can also do this

SELECT User, Host FROM mysql.user;

Attachment: View user permissions

show grants for ‘#userName'@'#host';

#userName represents the user name

#host represents the access permissions, as follows

• % represents wildcard all host address permissions (remote access is available)

• localhost is local permission (remote access is not possible)

• Specify special IP access rights such as 10.138.106.102

##????What this dog wants to check is testUser

show grants for 'testUser'@'%';

The above is the detailed content of How to create users and grant authorization in Mysql8. For more information, please follow other related articles on the PHP Chinese website!