mysql revoke user_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.02sec)

mysql> selectuser,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 |

6 | GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'sunny'@'%' |

7 +-------------------------------------------------------------------+

8 2 rows in set (0.00sec)9

10 mysql>show grants;11 +-------------------------------------------------------------------+

12 | Grants for sunny@% |

13 +-------------------------------------------------------------------+

14 | GRANT USAGE ON *.* TO 'sunny'@'%' IDENTIFIED BY PASSWORD |

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.00sec)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)