首页 > 代码库 > Mysql数据库之用户管理
Mysql数据库之用户管理
显示Mysql账号
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> select user,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 | +------+-------------+ 8 rows in set (0.00 sec)
删除Mysql账号
mysql> select user,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> select user,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 | +------+-------------+
用户的创建与授权
1、先创建用户再授权
mysql> CREATE USER ‘gtms1‘@‘localhost‘ IDENTIFIED BY ‘gtms1‘; mysql> GRANT ALL ON gtms.* to ‘gtms1‘@‘localhost‘; mysql> show grants for ‘gtms1‘@‘localhost‘; +--------------------------------------------------------------------------------------------------------------+ | Grants for gtms1@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘gtms1‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*44FCF2E45166027DDEDEA23FF734DBE00232F308‘ | | GRANT ALL PRIVILEGES ON `gtms`.* TO ‘gtms1‘@‘localhost‘ | +--------------------------------------------------------------------------------------------------------------+
2、创建时直接授权(远程用户通过%匹配IP)
mysql> grant all privileges on gtms.* to ‘gtms2‘@‘localhost‘ identified by ‘gtms2‘; mysql> show grants for ‘gtms2‘@‘localhost‘; +--------------------------------------------------------------------------------------------------------------+ | Grants for gtms2@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘gtms2‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*CA1613DFED77E148D1B1A8EE2A86AFE36A434D9F‘ | | GRANT ALL PRIVILEGES ON `gtms`.* TO ‘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_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: authentication_string: NULL 1 row in set (0.00 sec)
#针对库的权限
*************************** 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: Y Create_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: Y 1 row in set (0.00 sec)
#可使用的权限列表
https://dev.mysql.com/doc/refman/5.7/en/grant.html Meaning and Grantable Levels ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION and 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 log file groups to be created, altered, or dropped. Level: Global. CREATE TEMPORARY TABLES Enable use of CREATE TEMPORARY TABLE. Levels: Global, database. CREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global. CREATE VIEW Enable views to be 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 for the 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 or write files. Level: Global. GRANT OPTION Enable privileges to be granted to or removed from 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 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 are. 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 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”
Mysql数据库之用户管理
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。