mysql授权 REVOKE 添加用户等
添加用户并授权
一：直接用grant添加用户并授权
1：基本语法：
grant all privileges on 'database'.'table' to 'user'@'hostname' identified by 'password';
给hsm从10.0.0.20远程登陆的权限，并且对mysql数据库的user表有所有权限。也可以直接用grant all 不加privileges。
mysql> grant all privileges on mysql.user to 'hsm'@'10.0.0.20' identified by 'hsm'; Query OK, 0 rows affected (0.02 sec) mysql> select user,host from mysql.user; +-------+-----------+ | user | host | +-------+-----------+ | mysql | 10.0.0.% | | hsm | 10.0.0.20 | | old | localhost | | root | localhost | | root | mysql | +-------+-----------+ 5 rows in set (0.00 sec)
提示：主机名可以用 ‘ % ’ 通配符代表所有，数据库和表可以用 ‘ * ’ 号代表所有
hsm可以从10.0.0.x内网登录并且对所有库表有操作权限。
grant all privileges on *.* to 'hsm'@'10.0.0.%' identified by 'hsm';
2：如果要严格控制权限，可以对不同用户指定特定的操作权限
授权sunny用户对test数据库的所有表有select,insert,update的权限
grant select,insert,update on test.* to 'sunny'@'%' identified by 'sunny';
3：追加新权限
现在上面的sunny账户可以对test库里的表执行select,insert,update操作，执行delete操作时会提示权限错误
ERROR 1142 (42000): DELETE command denied to user 'sunny'@'10.0.0.20' for table 'test1'
现在我们给sunny授予delete权限
mysql> grant delete on test.* to 'sunny'@'%';
好了，现在对比一下前后权限，发现多了DELETE
1 mysql> show grants; 2 +-------------------------------------------------------------------+ 3 | Grants for sunny@% | 4 +-------------------------------------------------------------------+ 5 | GRANT USAGE ON *.* TO 'sunny'@'%' IDENTIFIED BY PASSWORD <secret> | 6 | GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'sunny'@'%' | 7 +-------------------------------------------------------------------+ 8 2 rows in set (0.00 sec) 9 10 mysql> show grants; 11 +-------------------------------------------------------------------+ 12 | Grants for sunny@% | 13 +-------------------------------------------------------------------+ 14 | GRANT USAGE ON *.* TO 'sunny'@'%' IDENTIFIED BY PASSWORD <secret> | 15 | GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'sunny'@'%' | 16 +-------------------------------------------------------------------+ 17 2 rows in set (0.00 sec)
测试一下，已经有delete权限
mysql> delete from test1; Query OK, 1 row affected (0.03 sec)
4：收回权限revoke
语法：revoke 'privileges' on 'database'.'table' from 'user'@'hostname';
收回sunny的查询权限
1 mysql> revoke select on test.* from sunny@'%'; 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql> flush privileges; 5 Query OK, 0 rows affected (0.00 sec)
注意：如果sunny已经登录，sunny需要quit退出重新登录新权限才会生效。如果不退出还是可以使用select，即使server端执行了flush privileges;
全部权限字段
mysql> show privileges\G;
*************************** 1. row ***************************
Privilege: Alter
Context: Tables
Comment: To alter the table
*************************** 2. row ***************************
Privilege: Alter routine
Context: Functions,Procedures
Comment: To alter or drop stored functions/procedures
*************************** 3. row ***************************
Privilege: Create
Context: Databases,Tables,Indexes
Comment: To create new databases and tables
*************************** 4. row ***************************
Privilege: Create routine
Context: Databases
Comment: To use CREATE FUNCTION/PROCEDURE
*************************** 5. row ***************************
Privilege: Create temporary tables
Context: Databases
Comment: To use CREATE TEMPORARY TABLE
*************************** 6. row ***************************
Privilege: Create view
Context: Tables
Comment: To create new views
*************************** 7. row ***************************
Privilege: Create user
Context: Server Admin
Comment: To create new users
*************************** 8. row ***************************
Privilege: Delete
Context: Tables
Comment: To delete existing rows
*************************** 9. row ***************************
Privilege: Drop
Context: Databases,Tables
Comment: To drop databases, tables, and views
*************************** 10. row ***************************
Privilege: Event
Context: Server Admin
Comment: To create, alter, drop and execute events
*************************** 11. row ***************************
Privilege: Execute
Context: Functions,Procedures
Comment: To execute stored routines
*************************** 12. row ***************************
Privilege: File
Context: File access on server
Comment: To read and write files on the server
*************************** 13. row ***************************
Privilege: Grant option
Context: Databases,Tables,Functions,Procedures
Comment: To give to other users those privileges you possess
*************************** 14. row ***************************
Privilege: Index
Context: Tables
Comment: To create or drop indexes
*************************** 15. row ***************************
Privilege: Insert
Context: Tables
Comment: To insert data into tables
*************************** 16. row ***************************
Privilege: Lock tables
Context: Databases
Comment: To use LOCK TABLES (together with SELECT privilege)
*************************** 17. row ***************************
Privilege: Process
Context: Server Admin
Comment: To view the plain text of currently executing queries
*************************** 18. row ***************************
Privilege: Proxy
Context: Server Admin
Comment: To make proxy user possible
*************************** 19. row ***************************
Privilege: References
Context: Databases,Tables
Comment: To have references on tables
*************************** 20. row ***************************
Privilege: Reload
Context: Server Admin
Comment: To reload or refresh tables, logs and privileges
*************************** 21. row ***************************
Privilege: Replication client
Context: Server Admin
Comment: To ask where the slave or master servers are
*************************** 22. row ***************************
Privilege: Replication slave
Context: Server Admin
Comment: To read binary log events from the master
*************************** 23. row ***************************
Privilege: Select
Context: Tables
Comment: To retrieve rows from table
*************************** 24. row ***************************
Privilege: Show databases
Context: Server Admin
Comment: To see all databases with SHOW DATABASES
*************************** 25. row ***************************
Privilege: Show view
Context: Tables
Comment: To see views with SHOW CREATE VIEW
*************************** 26. row ***************************
Privilege: Shutdown
Context: Server Admin
Comment: To shut down the server
*************************** 27. row ***************************
Privilege: Super
Context: Server Admin
Comment: To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
*************************** 28. row ***************************
Privilege: Trigger
Context: Tables
Comment: To use triggers
*************************** 29. row ***************************
Privilege: Create tablespace
Context: Server Admin
Comment: To create/alter/drop tablespaces
*************************** 30. row ***************************
Privilege: Update
Context: Tables
Comment: To update existing rows
*************************** 31. row ***************************
Privilege: Usage
Context: Server Admin
Comment: No privileges - allow connect only
31 rows in set (0.00 sec)
