User management of MySQL database

Show MySQL Account

Mysql>Selectuser ();+----------------+| User () |+----------------+| [Email protected] |+----------------+1RowinchSet (0.00sec) MySQL>Selectuser,host from Mysql.user;+------+-------------+| user | Host |+------+-------------+| Root |127.0.0.1|| Rep |192.168.0.% || Web1 |192.168.0.% || Root | ::1|| | localhost | | Root |      localhost | | | Node80 | | Root | Node80 |+------+-------------+8RowsinchSet (0.00Sec

Delete MySQL Account

Mysql>Selectuser,host from Mysql.user;+------+-------------+| user | Host |+------+-------------+| Root |127.0.0.1|| Rep |192.168.0.% || Web1 |192.168.0.% || Root | ::1|| | localhost | | Root |      localhost | | | Node80 | | Root | Node80 |+------+-------------+MySQL> Drop User""@"localhost";MySQL> Delete from Mysql.user where user=""and host="Node80";MySQL>flush Privileges;MySQL>Selectuser,host from Mysql.user;+------+-------------+| user | Host |+------+-------------+| Root |127.0.0.1|| Rep |192.168.0.% || Web1 |192.168.0.% || Root | ::1|| Root | localhost | | Root | Node80 |+------+-------------+

User creation and authorization

1, first create user re-authorization

Mysql> CREATE USER'gtms1'@'localhost'Identified by'gtms1'; MySQL> GRANT all on gtms.* to'gtms1'@'localhost';MySQL> Show Grants for 'gtms1'@'localhost';+-------------------------------------------------------------------------------------------------------------- +| Grants for[Email protected] |+-------------------- ------------------------------------------------------------------------------------------+| GRANT USAGE on *. * To'gtms1'@'localhost'Identified by PASSWORD'*44fcf2e45166027ddedea23ff734dbe00232f308'|| GRANT all privileges on ' GTMs '.'gtms1'@'localhost'|+------------------------------------------------------------------------------------------------------------- -+

2, the creation of direct authorization (remote users through% matching IP)

Mysql> grant all privileges in gtms.* to'GTMS2'@'localhost'Identified by'GTMS2'; MySQL> Show Grants for 'GTMS2'@'localhost';+-------------------------------------------------------------------------------------------------------------- +| Grants for[Email protected] |+-------------------- ------------------------------------------------------------------------------------------+| GRANT USAGE on *. * To'GTMS2'@'localhost'Identified by PASSWORD'*ca1613dfed77e148d1b1a8ee2a86afe36a434d9f'|| GRANT all privileges on ' GTMs '.'GTMS2'@'localhost'|+------------------------------------------------------------------------------------------------------------- -+

#针对Mysql数据库管理的权限

Mysql>Select* FROM Mysql.user where user="gtms1"\g***************************1. Row ***************************host:localhost user:gtms1 Password:*44fcf2e45166027ddedea23ff734dbe00232f308 select_priv:n insert_priv:n update_priv:n           Delete_priv:n create_priv:n drop_priv:n reload_priv:n shutdown_priv:n Process_priv:n file_priv:n grant_priv:n references_priv:n Index_priv : N alter_priv:n show_db_priv:n super_priv:n create_tmp_table_priv:n lock_tables_p Riv:n execute_priv:n repl_slave_priv:n repl_client_priv:n create_view_priv:n Show_vie W_priv:n create_routine_priv:n alter_routine_priv:n create_user_priv:n event_priv:n Tri Gger_priv:ncreate_tablespace_priv:n ssl_type:ssl_cipher:x509_issuer:x50 9_subject:max_questions:0max_updates:0max_connections:0max_user_connections:0Plugin:authentication_string:NULL1RowinchSet (0.00Sec

mysql> SELECT * from Mysql.user where user= "Gtms1" \g

#针对库的权限

1. Row ***************************                 host:localhost                   db:gtms                 user:gtms1          select_priv:y          insert_priv:y          Update_priv:y          delete_priv:y          create_priv:y            drop_priv:y           grant_priv:n      references_priv:y           index_priv:y           Alter_priv:ycreate_tmp_table_priv:y     lock_tables_priv:y     create_view_priv:y       show_view_priv:y  create_routine_priv:y   Alter_routine_priv:y         execute_priv:y           event_priv:y         trigger_priv:y1 in Set (0.00 sec)

mysql> SELECT * from mysql.db where user= "Gtms1" \g

#可使用的权限列表

Https//dev.mysql.com/doc/refman/5.7/en/grant.htmlmeaning and grantable levelsall [privileges] grant all privileges at specified access level except Grant OPTION an D PROXY. Alter Enable use of ALTER TABLE. Levels:global, database, table. ALTER ROUTINE Enable stored routines to be altered or dropped. Levels:global, database, procedure. CREATE Enable database and table creation. Levels:global, database, table. CREATE ROUTINE Enable stored ROUTINE creation. Levels:global, database. CREATE tablespace Enable tablespaces and logfile groupsTo is created, altered, or dropped. Level:global. Create temporary TABLES Enable use of Create temporary TABLE. Levels:global, database. Create user Enable Use of the Create user, DROP user, RENAME user, and REVOKE all privileges. Level:global. CREATE VIEW Enable created or altered. Levels:global, database, table. Delete Enable use of delete. Level:global, database, table. DROP Enable databases, tables, and views to be dropped. Levels:global, database, table. EVENT Enable Use of events forThe Event Scheduler. Levels:global, database. Execute Enable the user to execute stored routines. Levels:global, database, table. FILE Enable the user to cause the server to read orWritefiles. Level:global. GRANT OPTION Enable privileges to is granted to or removed from the other accounts. Levels:global, database, table, procedure, Proxy. INDEX Enable indexes to be created or dropped. Levels:global, database, table. Insert Enable use of INSERT. Levels:global, database, table, column. Lock TABLES Enable use of the lock TABLES on TABLES for which You have the SELECT privilege. Levels:global, database. PROCESS Enable the user to see all processes with SHOW processlist. Level:global. PROXY Enable user proxying. Level:from user to user. REFERENCES Enable foreign key creation. Levels:global, database, table, column. RELOAD Enable use of FLUSH operations. Level:global. REPLICATION CLIENT Enable The user to ask where master or slave servers is. Level:global. REPLICATION SLAVE Enable REPLICATION slaves to read binary log events from the master. Level:global. Select Enable use of SELECT. Levels:global, database, table, column. Show DATABASES Enable show DATABASES to show all DATABASES. Level:global. Show view Enable use of Show CREATE view. Levels:global, database, table. SHUTDOWN Enable use of mysqladmin SHUTDOWN. Level:global. SUPER Enable Use of the other administrative operations such as change MASTER to, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin Debug command. Level:global. TRIGGER Enable TRIGGER operations. Levels:global, database, table. Update Enable use of update. Levels:global, database, table, column.  USAGE synonym for"No Privileges"

permissible privileges for GRANT and REVOKE

User management of MySQL database