mysql Creating Database Adding User User Authorization Practical Operation Method

  • 2021-12-13 10:03:25
  • OfStack

1. Create an mysql database

1. Create database syntax

-- Creates a file with the name " testdb "Database and set the encoding set to utf8

Step 2 Create a user

1. Create a new user

 -- Created the 1 The name is: test  The password is: 1234  Users of 
 create user 'test'@'localhost' identified by '1234';

"localhost" here means that the user can only log in locally, and cannot log in remotely on another machine. If you want to log in remotely, change "localhost" to "%", which means you can log in on any one computer. You can also specify that a machine can log in remotely.

2. Query users

-- Query user 
select user,host from mysql.user;

Step 3 Delete users

-- Delete user " test " 
drop user test@localhost ;
-- If the created user allows any computer to log in, delete the user as follows 
drop user test@'%';

Step 4 Change your password

-- Method 1 The password is updated in real time; Modify user " test The password for "is" 1122 " 
set password for test =password('1122');
-- Method 2 , need to refresh; Modify user " test The password for "is" 1234 " 
update mysql.user set password=password('1234') where user='test'
-- Refresh 
flush privileges;

5. User Assignment Permissions

-- Grant user test Through the external network IP To the database " testdb Full permissions of " 
grant all privileges on 'testdb'.* to 'test'@'%' identified by '1234'; 

-- Refresh permissions 
flush privileges; 

-- Grant user " test "Through the external network IP For this database " testdb Permissions to create, modify and delete tables in " , And the permissions of adding, deleting, checking and modifying table data 
grant create,alter,drop,select,insert,update,delete on testdb.* to test@'%';   

6. View User Permissions

-- View users " test " 
show grants for test;

Note: After modifying the permissions, 1 must refresh the service or restart the service. Refresh the service with flush privileges;

The above is all the relevant knowledge points introduced this time. Thank you for your study and support for this site.

Related articles: