mysql grant,mysql revoke
账户权限管理
首先进入Mysql控制台:
mysql -uroot -p
grant授权格式:grant 权限列表 on 库.表 to 'user_name'@'host name' identified by "密码";
user_name是用户名,host_name为主机名,即用户连接Mysql时所在主机的名字。
创建一个新用户(user)并为此用户分配权限(这里先分配对所有数据库的表增删改查的权限),identified by 后面填入你新用户的密码
grant select,insert,update,delete on *.* to 'user'@'%' identified by 'your password';
如果是给予全部权限那么就这样
grant all privileges on *.* to 'user'@'%' identified by 'your password';
记得每次操作完要刷新授权
flush privileges;
查看用户权限
show grants for user;
权限的撤销
当要撤销一个用户的权限。而又不希望将该用户从系统中删除时,可以使用revoke语句来实现
回收某些特定的权限
revoke回收权限格式:revoke 权限列表 on 库.表 from 用户名@'ip';
revoke select,insert,update,delete ON *.* from 'user'@'%';
回收特定用户的所有权限
revoke ALL PRIVILEGES ON *.* from 'user'@'%';
操作权限列表
参考MySQL官网说明 Privileges Provided by MySQL
Table 6.2 Permissible Privileges for GRANT and REVOKE
Privilege | Column | Context |
---|---|---|
ALL [PRIVILEGES] | Synonym for “all privileges” | Server administration |
ALTER | Alter_priv | Tables |
ALTER ROUTINE | Alter_routine_priv | Stored routines |
CREATE | Create_priv | Databases, tables, or indexes |
CREATE ROUTINE | Create_routine_priv | Stored routines |
CREATE TABLESPACE | Create_tablespace_priv | Server administration |
CREATE TEMPORARY TABLES | Create_tmp_table_priv | Tables |
CREATE USER | Create_user_priv | Server administration |
CREATE VIEW | Create_view_priv | Views |
DELETE | Delete_priv | Tables |
DROP | Drop_priv | Databases, tables, or views |
EVENT | Event_priv | Databases |
EXECUTE | Execute_priv | Stored routines |
FILE | File_priv | File access on server host |
GRANT OPTION | Grant_priv | Databases, tables, or stored routines |
INDEX | Index_priv | Tables |
INSERT | Insert_priv | Tables or columns |
LOCK TABLES | Lock_tables_priv | Databases |
PROCESS | Process_priv | Server administration |
PROXY | See proxies_priv table | Server administration |
REFERENCES | References_priv | Databases or tables |
RELOAD | Reload_priv | Server administration |
REPLICATION CLIENT | Repl_client_priv | Server administration |
REPLICATION SLAVE | Repl_slave_priv | Server administration |
SELECT | Select_priv | Tables or columns |
SHOW DATABASES | Show_db_priv | Server administration |
SHOW VIEW | Show_view_priv | Views |
SHUTDOWN | Shutdown_priv | Server administration |
SUPER | Super_priv | Server administration |
TRIGGER | Trigger_priv | Tables |
UPDATE | Update_priv | Tables or columns |
USAGE | Synonym for “no privileges” | Server administration |