mysql -uroot -p
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
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.
show grants for 'testuser1'@'%';
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'@'%';
drop user 'testuser1'@'%';
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
You can also do this
SELECT User, Host FROM mysql.user;
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
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!