REVOKE <privileges>

This statement removes privileges from an existing user. Executing this statement requires the GRANT OPTION privilege and all privileges you revoke.

Synopsis

GrantStmt

REVOKE - 图1

PrivElemList

REVOKE - 图2

PrivElem

REVOKE - 图3

PrivType

REVOKE - 图4

ObjectType

REVOKE - 图5

PrivLevel

REVOKE - 图6

UserSpecList

REVOKE - 图7

  1. GrantStmt ::=
  2. 'GRANT' PrivElemList 'ON' ObjectType PrivLevel 'TO' UserSpecList RequireClauseOpt WithGrantOptionOpt
  3. PrivElemList ::=
  4. PrivElem ( ',' PrivElem )*
  5. PrivElem ::=
  6. PrivType ( '(' ColumnNameList ')' )?
  7. PrivType ::=
  8. 'ALL' 'PRIVILEGES'?
  9. | 'ALTER' 'ROUTINE'?
  10. | 'CREATE' ( 'USER' | 'TEMPORARY' 'TABLES' | 'VIEW' | 'ROLE' | 'ROUTINE' )?
  11. | 'TRIGGER'
  12. | 'DELETE'
  13. | 'DROP' 'ROLE'?
  14. | 'PROCESS'
  15. | 'EXECUTE'
  16. | 'INDEX'
  17. | 'INSERT'
  18. | 'SELECT'
  19. | 'SUPER'
  20. | 'SHOW' ( 'DATABASES' | 'VIEW' )
  21. | 'UPDATE'
  22. | 'GRANT' 'OPTION'
  23. | 'REFERENCES'
  24. | 'REPLICATION' ( 'SLAVE' | 'CLIENT' )
  25. | 'USAGE'
  26. | 'RELOAD'
  27. | 'FILE'
  28. | 'CONFIG'
  29. | 'LOCK' 'TABLES'
  30. | 'EVENT'
  31. | 'SHUTDOWN'
  32. ObjectType ::=
  33. 'TABLE'?
  34. PrivLevel ::=
  35. '*' ( '.' '*' )?
  36. | Identifier ( '.' ( '*' | Identifier ) )?
  37. UserSpecList ::=
  38. UserSpec ( ',' UserSpec )*

Examples

  1. mysql> CREATE USER 'newuser' IDENTIFIED BY 'mypassword';
  2. Query OK, 1 row affected (0.02 sec)
  3. mysql> GRANT ALL ON test.* TO 'newuser';
  4. Query OK, 0 rows affected (0.03 sec)
  5. mysql> SHOW GRANTS FOR 'newuser';
  6. +-------------------------------------------------+
  7. | Grants for newuser@% |
  8. +-------------------------------------------------+
  9. | GRANT USAGE ON *.* TO 'newuser'@'%' |
  10. | GRANT ALL PRIVILEGES ON test.* TO 'newuser'@'%' |
  11. +-------------------------------------------------+
  12. 2 rows in set (0.00 sec)
  13. mysql> REVOKE ALL ON test.* FROM 'newuser';
  14. Query OK, 0 rows affected (0.03 sec)
  15. mysql> SHOW GRANTS FOR 'newuser';
  16. +-------------------------------------+
  17. | Grants for newuser@% |
  18. +-------------------------------------+
  19. | GRANT USAGE ON *.* TO 'newuser'@'%' |
  20. +-------------------------------------+
  21. 1 row in set (0.00 sec)
  22. mysql> DROP USER 'newuser';
  23. Query OK, 0 rows affected (0.14 sec)
  24. mysql> SHOW GRANTS FOR 'newuser';
  25. ERROR 1141 (42000): There is no such grant defined for user 'newuser' on host '%'

MySQL compatibility

See also