Specific operation commands for adding and deleting new users in mysql, and specific operations in mysql

Specific operation commands for adding and deleting new users in mysql, and specific operations in mysql
Method 1: Use the mysql root (root permission) user to log on and directly grant permissions to users. You can also create users.
/Usr/bin/mysqladmin-u root password 123456



Mysql-uroot-p
Password
View all user names and passwords
Select host, user, password from user;



Grant all on ec. * to 'root' @ '%' identified by '123 ';
Grant all privileges on ec. * to 'cockpit' @ '% 'identified by '123 ';
Grant all on ec. * to 'cockpit' @ '%' identified by '123 ';
Grant all privileges on ec. * to 'cockpit' @ '% 'identified by '123 ';
Flush privileges ;;

Change Database Password
User mysql

Modify the password of a mysql database
UPDATE user SET Password = PASSWORD ('000000') where USER = 'root ';
Mysql root Password is empty. No Password is required for login.
UPDATE user SET Password = PASSWORD (null) where USER = 'root ';
Flush privileges;

Method 2:
1. Create a user.
// Log on to MYSQL
@> Mysql-u root-p
@> Password
// Create a database (testDB) for the user first)
Mysql> create database testDB default character set utf8 ;;
// Create a user
Mysql> insert into mysql. user (Host, User, Password) values ("localhost", "test", password ("1234 "));
// Refresh the system permission list
Mysql> flush privileges;
In this way, a user named: test password: 1234 is created.
Then log on.
Mysql> exit;
@> Mysql-u phplamp-p
@> Enter the password
Mysql> logon successful
2. Authorize the user.
Format: grant permission on database. * to username @ login host identified by "password ";
> Grant all privileges on phplampDB. * to phplamp @ localhost identified by '100 ";

Authorize the test user to have certain permissions on all databases:
Mysql> grant select, delete, update, create, drop on *. * to test @ "%" identified by "1234 ";

// The test user has the select, delete, update, create, and drop permissions on all databases.

// @ "%" Indicates authorizing all non-local hosts, excluding localhost. (The localhost address is set to 127.0.0.1. If it is set to a real local address, you do not know if it is OK. No verification is performed .)

// Authorize localhost: add the grant all privileges on testDB. * to test @ localhost identified by '20140901.



3. delete a user.
@> Mysql-u root-p
@> Password
Mysql> delete from user WHERE User = "test" and Host = "localhost ";
Mysql> flush privileges;
// Delete the user's database
Mysql> drop database testDB;
4. Modify the password of the specified user.
@> Mysql-u root-p
@> Password
Mysql> update mysql. user set password = password ('new password') where User = "test" and Host = "localhost ";
Mysql> flush privileges;

Delete from user where User = "test" and Host = "localhost ";

You can also try:

Delete account and permissions:> drop user username @ '% ';

> Drop user username @ localhost;

Log on to the database and enter show databases. mysql and test are displayed. Then, run the grant command to add user A and grant the permission to delete user.

Mysql is a system database, and the user table is an authorization table.
If you delete user A, user A does not exist in the authorization table.
Of course, if you directly modify the user table, you need to refresh the authorization and the modification will take effect. The refresh method is described above.

How does MySQL authorize a self-created user such as daitest to create a new database? Command

Take a look
In mysql, you can grant one or more permissions to a user, such as select, insert, update, and delete. The grant command is used in the following format:
Grant permission on database objects to users
1. grant normal data users the right to query, insert, update, and delete all table data in the database.
Grant select on testdb. * to common_user @ '%'
Grant insert on testdb. * to common_user @ '%'
Grant update on testdb. * to common_user @ '%'
Grant delete on testdb. * to common_user @ '%'
Alternatively, replace the following with a mysql command:
Grant select, insert, update, delete on testdb. * to common_user @ '%'

2. grant database developers to create tables, indexes, views, stored procedures, and functions... .
Grant permissions to create, modify, and delete mysql Data Table structures.
Grant create on testdb. * to developer @ '192. 192.% ';
Grant alter on testdb. * to developer @ '192. 192.% ';
Grant drop on testdb. * to developer @ '192. 192.% ';
Grant the mysql foreign key operation permission.
Grant references on testdb. * to developer @ '192. 192.% ';
Grant the permission to operate mysql temporary tables.
Grant create temporary tables on testdb. * to developer @ '2017. 192.% ';
Grant the permission to operate mysql indexes.
Grant index on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate the mysql view and view the source code.
Grant create view on testdb. * to developer @ '192. 192.% ';
Grant show view on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate mysql stored procedures and functions.
Grant create routine on testdb. * to developer @ '192. 192.% ';-now, can show procedure status
Grant alter routine on testdb. * to developer @ '192. 192.% ';-now, you can drop a procedure
Grant execute on testdb. * to developer @ '192. 192.% ';

3. grant common dba permission to manage a mysql database.
Grant all privileges on testdb to dba @ 'localhost'
The keyword "privileges" can be omitted.

4. grant senior dba permission to manage all databases in mysql.
Grant... the remaining full text>