dev.mysql.com
Open in
urlscan Pro
2a02:26f0:480:995::2e31
Public Scan
URL:
https://dev.mysql.com/doc/refman/8.0/en/revoke.html
Submission: On July 17 via api from FI — Scanned from FI
Submission: On July 17 via api from FI — Scanned from FI
Form analysis 2 forms found in the DOM
GET https://www.oracle.com/search/results
<form id="l1-search-form" method="get" action="https://www.oracle.com/search/results">
<input type="hidden" name="cat" value="mysql">
<input type="hidden" name="Ntk" value="SI-ALL5">
<input id="l1-search-input" type="search" class="icon-search" placeholder="Search" aria-label="Search" name="Ntt">
</form>GET /doc/search/
<form method="get" action="/doc/search/">
<input type="hidden" name="d" id="d" value="201">
<input type="hidden" name="p" id="p" value="1">
<input type="text" name="q" id="q" title="Search this Manual" value="Search this Manual" style="color: #bbb;" onfocus="if(this.value == 'Search this Manual') {this.value='';this.style.color='#555';}"
onblur="if(this.value == '') {this.value='Search this Manual';this.style.color='#bbb';}">
<button class="docs-sidebar-search-btn" aria-label="Search" title="Search" type="submit">
<span class="icon-search"></span>
</button>
</form>Text Content
Skip to Main Content
The world's most popular open source database
Contact MySQL |
Login | Register
* MySQL.com
* Downloads
* Documentation
* Developer Zone
Developer Zone Downloads MySQL.com
Documentation
* MySQL Server
* MySQL Enterprise
* Workbench
* InnoDB Cluster
* MySQL NDB Cluster
* Connectors
* More
* MySQL.com
* Downloads
* Developer Zone
Section Menu:
Documentation Home
--------------------------------------------------------------------------------
MySQL 8.0 Reference Manual
* Preface and Legal Notices
* General Information
* Installing and Upgrading MySQL
* Tutorial
* MySQL Programs
* MySQL Server Administration
* Security
* Backup and Recovery
* Optimization
* Language Structure
* Character Sets, Collations, Unicode
* Data Types
* Functions and Operators
* SQL Statements
* Data Definition Statements
* Atomic Data Definition Statement Support
* ALTER DATABASE Statement
* ALTER EVENT Statement
* ALTER FUNCTION Statement
* ALTER INSTANCE Statement
* ALTER LOGFILE GROUP Statement
* ALTER PROCEDURE Statement
* ALTER SERVER Statement
* ALTER TABLE Statement
* ALTER TABLE Partition Operations
* ALTER TABLE and Generated Columns
* ALTER TABLE Examples
* ALTER TABLESPACE Statement
* ALTER VIEW Statement
* CREATE DATABASE Statement
* CREATE EVENT Statement
* CREATE FUNCTION Statement
* CREATE INDEX Statement
* CREATE LOGFILE GROUP Statement
* CREATE PROCEDURE and CREATE FUNCTION Statements
* CREATE SERVER Statement
* CREATE SPATIAL REFERENCE SYSTEM Statement
* CREATE TABLE Statement
* Files Created by CREATE TABLE
* CREATE TEMPORARY TABLE Statement
* CREATE TABLE ... LIKE Statement
* CREATE TABLE ... SELECT Statement
* FOREIGN KEY Constraints
* CHECK Constraints
* Silent Column Specification Changes
* CREATE TABLE and Generated Columns
* Secondary Indexes and Generated Columns
* Invisible Columns
* Generated Invisible Primary Keys
* Setting NDB Comment Options
* CREATE TABLESPACE Statement
* CREATE TRIGGER Statement
* CREATE VIEW Statement
* DROP DATABASE Statement
* DROP EVENT Statement
* DROP FUNCTION Statement
* DROP INDEX Statement
* DROP LOGFILE GROUP Statement
* DROP PROCEDURE and DROP FUNCTION Statements
* DROP SERVER Statement
* DROP SPATIAL REFERENCE SYSTEM Statement
* DROP TABLE Statement
* DROP TABLESPACE Statement
* DROP TRIGGER Statement
* DROP VIEW Statement
* RENAME TABLE Statement
* TRUNCATE TABLE Statement
* Data Manipulation Statements
* CALL Statement
* DELETE Statement
* DO Statement
* EXCEPT Clause
* HANDLER Statement
* IMPORT TABLE Statement
* INSERT Statement
* INSERT ... SELECT Statement
* INSERT ... ON DUPLICATE KEY UPDATE Statement
* INSERT DELAYED Statement
* INTERSECT Clause
* LOAD DATA Statement
* LOAD XML Statement
* Parenthesized Query Expressions
* REPLACE Statement
* SELECT Statement
* SELECT ... INTO Statement
* JOIN Clause
* Set Operations with UNION, INTERSECT, and EXCEPT
* Subqueries
* The Subquery as Scalar Operand
* Comparisons Using Subqueries
* Subqueries with ANY, IN, or SOME
* Subqueries with ALL
* Row Subqueries
* Subqueries with EXISTS or NOT EXISTS
* Correlated Subqueries
* Derived Tables
* Lateral Derived Tables
* Subquery Errors
* Optimizing Subqueries
* Restrictions on Subqueries
* TABLE Statement
* UPDATE Statement
* UNION Clause
* VALUES Statement
* WITH (Common Table Expressions)
* Transactional and Locking Statements
* START TRANSACTION, COMMIT, and ROLLBACK Statements
* Statements That Cannot Be Rolled Back
* Statements That Cause an Implicit Commit
* SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements
* LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements
* LOCK TABLES and UNLOCK TABLES Statements
* SET TRANSACTION Statement
* XA Transactions
* XA Transaction SQL Statements
* XA Transaction States
* Restrictions on XA Transactions
* Replication Statements
* SQL Statements for Controlling Source Servers
* PURGE BINARY LOGS Statement
* RESET MASTER Statement
* SET sql_log_bin Statement
* SQL Statements for Controlling Replica Servers
* CHANGE MASTER TO Statement
* CHANGE REPLICATION FILTER Statement
* CHANGE REPLICATION SOURCE TO Statement
* RESET REPLICA Statement
* RESET SLAVE Statement
* START REPLICA Statement
* START SLAVE Statement
* STOP REPLICA Statement
* STOP SLAVE Statement
* SQL Statements for Controlling Group Replication
* START GROUP_REPLICATION Statement
* STOP GROUP_REPLICATION Statement
* Prepared Statements
* PREPARE Statement
* EXECUTE Statement
* DEALLOCATE PREPARE Statement
* Compound Statement Syntax
* BEGIN ... END Compound Statement
* Statement Labels
* DECLARE Statement
* Variables in Stored Programs
* Local Variable DECLARE Statement
* Local Variable Scope and Resolution
* Flow Control Statements
* CASE Statement
* IF Statement
* ITERATE Statement
* LEAVE Statement
* LOOP Statement
* REPEAT Statement
* RETURN Statement
* WHILE Statement
* Cursors
* Cursor CLOSE Statement
* Cursor DECLARE Statement
* Cursor FETCH Statement
* Cursor OPEN Statement
* Restrictions on Server-Side Cursors
* Condition Handling
* DECLARE ... CONDITION Statement
* DECLARE ... HANDLER Statement
* GET DIAGNOSTICS Statement
* RESIGNAL Statement
* SIGNAL Statement
* Scope Rules for Handlers
* The MySQL Diagnostics Area
* Condition Handling and OUT or INOUT Parameters
* Restrictions on Condition Handling
* Database Administration Statements
* Account Management Statements
* ALTER USER Statement
* CREATE ROLE Statement
* CREATE USER Statement
* DROP ROLE Statement
* DROP USER Statement
* GRANT Statement
* RENAME USER Statement
* REVOKE Statement
* SET DEFAULT ROLE Statement
* SET PASSWORD Statement
* SET ROLE Statement
* Resource Group Management Statements
* ALTER RESOURCE GROUP Statement
* CREATE RESOURCE GROUP Statement
* DROP RESOURCE GROUP Statement
* SET RESOURCE GROUP Statement
* Table Maintenance Statements
* ANALYZE TABLE Statement
* CHECK TABLE Statement
* CHECKSUM TABLE Statement
* OPTIMIZE TABLE Statement
* REPAIR TABLE Statement
* Component, Plugin, and Loadable Function Statements
* CREATE FUNCTION Statement for Loadable Functions
* DROP FUNCTION Statement for Loadable Functions
* INSTALL COMPONENT Statement
* INSTALL PLUGIN Statement
* UNINSTALL COMPONENT Statement
* UNINSTALL PLUGIN Statement
* CLONE Statement
* SET Statements
* SET Syntax for Variable Assignment
* SET CHARACTER SET Statement
* SET NAMES Statement
* SHOW Statements
* SHOW BINARY LOGS Statement
* SHOW BINLOG EVENTS Statement
* SHOW CHARACTER SET Statement
* SHOW COLLATION Statement
* SHOW COLUMNS Statement
* SHOW CREATE DATABASE Statement
* SHOW CREATE EVENT Statement
* SHOW CREATE FUNCTION Statement
* SHOW CREATE PROCEDURE Statement
* SHOW CREATE TABLE Statement
* SHOW CREATE TRIGGER Statement
* SHOW CREATE USER Statement
* SHOW CREATE VIEW Statement
* SHOW DATABASES Statement
* SHOW ENGINE Statement
* SHOW ENGINES Statement
* SHOW ERRORS Statement
* SHOW EVENTS Statement
* SHOW FUNCTION CODE Statement
* SHOW FUNCTION STATUS Statement
* SHOW GRANTS Statement
* SHOW INDEX Statement
* SHOW MASTER STATUS Statement
* SHOW OPEN TABLES Statement
* SHOW PLUGINS Statement
* SHOW PRIVILEGES Statement
* SHOW PROCEDURE CODE Statement
* SHOW PROCEDURE STATUS Statement
* SHOW PROCESSLIST Statement
* SHOW PROFILE Statement
* SHOW PROFILES Statement
* SHOW RELAYLOG EVENTS Statement
* SHOW REPLICAS Statement
* SHOW SLAVE HOSTS | SHOW REPLICAS Statement
* SHOW REPLICA STATUS Statement
* SHOW SLAVE | REPLICA STATUS Statement
* SHOW STATUS Statement
* SHOW TABLE STATUS Statement
* SHOW TABLES Statement
* SHOW TRIGGERS Statement
* SHOW VARIABLES Statement
* SHOW WARNINGS Statement
* Other Administrative Statements
* BINLOG Statement
* CACHE INDEX Statement
* FLUSH Statement
* KILL Statement
* LOAD INDEX INTO CACHE Statement
* RESET Statement
* RESET PERSIST Statement
* RESTART Statement
* SHUTDOWN Statement
* Utility Statements
* DESCRIBE Statement
* EXPLAIN Statement
* HELP Statement
* USE Statement
* MySQL Data Dictionary
* The InnoDB Storage Engine
* Alternative Storage Engines
* Replication
* Group Replication
* MySQL Shell
* Using MySQL as a Document Store
* InnoDB Cluster
* InnoDB ReplicaSet
* MySQL NDB Cluster 8.0
* Partitioning
* Stored Objects
* INFORMATION_SCHEMA Tables
* MySQL Performance Schema
* MySQL sys Schema
* Connectors and APIs
* MySQL Enterprise Edition
* MySQL Workbench
* MySQL on the OCI Marketplace
* MySQL 8.0 Frequently Asked Questions
* Error Messages and Common Problems
* Indexes
* MySQL Glossary
Related Documentation
MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
Download this Manual
PDF (US Ltr) - 42.8Mb
PDF (A4) - 42.9Mb
Man Pages (TGZ) - 273.7Kb
Man Pages (Zip) - 385.2Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
Security in MySQL
Starting and Stopping MySQL
MySQL and Linux/Unix
MySQL and Windows
MySQL and macOS
MySQL and Solaris
Building MySQL from Source
MySQL Restrictions and Limitations
MySQL Partitioning
MySQL Tutorial
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL NDB Cluster 8.0
version 8.0
5.7
8.0 Japanese
MySQL 8.0 Reference Manual / ... / SQL Statements / Database
Administration Statements / Account Management Statements / REVOKE Statement
13.7.1.8 REVOKE STATEMENT
Press CTRL+C to copy
REVOKE [IF EXISTS]
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user_or_role [, user_or_role] ...
[IGNORE UNKNOWN USER]
REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTION
FROM user_or_role [, user_or_role] ...
[IGNORE UNKNOWN USER]
REVOKE [IF EXISTS] PROXY ON user_or_role
FROM user_or_role [, user_or_role] ...
[IGNORE UNKNOWN USER]
REVOKE [IF EXISTS] role [, role ] ...
FROM user_or_role [, user_or_role ] ...
[IGNORE UNKNOWN USER]
user_or_role: {
user (see Section 6.2.4, “Specifying Account Names”)
| role (see Section 6.2.5, “Specifying Role Names”
}
The REVOKE statement enables system administrators to revoke privileges and
roles, which can be revoked from user accounts and roles.
For details on the levels at which privileges exist, the permissible priv_type,
priv_level, and object_type values, and the syntax for specifying users and
passwords, see Section 13.7.1.6, “GRANT Statement”.
For information about roles, see Section 6.2.10, “Using Roles”.
When the read_only system variable is enabled, REVOKE requires the
CONNECTION_ADMIN or privilege (or the deprecated SUPER privilege), in addition
to any other required privileges described in the following discussion.
Beginning with MySQL 8.0.30, all the forms shown for REVOKE support an IF EXISTS
option as well as an IGNORE UNKNOWN USER option. With neither of these
modifications, REVOKE either succeeds for all named users and roles, or rolls
back and has no effect if any error occurs; the statement is written to the
binary log only if it succeeds for all named users and roles. The precise
effects of IF EXISTS and IGNORE UNKNOWN USER are discussed later in this
section.
Each account name uses the format described in Section 6.2.4, “Specifying
Account Names”. Each role name uses the format described in Section 6.2.5,
“Specifying Role Names”. For example:
Press CTRL+C to copy
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
REVOKE SELECT ON world.* FROM 'role3';
The host name part of the account or role name, if omitted, defaults to '%'.
To use the first REVOKE syntax, you must have the GRANT OPTION privilege, and
you must have the privileges that you are revoking.
To revoke all privileges, use the second syntax, which drops all global,
database, table, column, and routine privileges for the named users or roles:
Press CTRL+C to copy
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user_or_role [, user_or_role] ...
REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke any roles.
To use this REVOKE syntax, you must have the global CREATE USER privilege, or
the UPDATE privilege for the mysql system schema.
The syntax for which the REVOKE keyword is followed by one or more role names
takes a FROM clause indicating one or more users or roles from which to revoke
the roles.
The IF EXISTS and IGNORE UNKNOWN USER options (MySQL 8.0.30 and later) have the
effects listed here:
* IF EXISTS means that, if the target user or role exists but no such privilege
or role is found assigned to the target for any reason, a warning is raised,
instead of an error; if no privilege or role named by the statement is
assigned to the target, the statement has no (other) effect. Otherwise,
REVOKE executes normally; if the user does not exist, the statement raises an
error.
Example: Given table t1 in database test, we execute the following
statements, with the results shown.
Press CTRL+C to copy
mysql> CREATE USER jerry@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> REVOKE SELECT ON test.t1 FROM jerry@localhost;
ERROR 1147 (42000): There is no such grant defined for user 'jerry' on host
'localhost' on table 't1'
mysql> REVOKE IF EXISTS SELECT ON test.t1 FROM jerry@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1147
Message: There is no such grant defined for user 'jerry' on host 'localhost' on
table 't1'
1 row in set (0.00 sec)
IF EXISTS causes an error to be demoted to a warning even if the privilege or
role named does not exist, or the statement attempts to assign it at the
wrong level.
* If the REVOKE statement includes IGNORE UNKNOWN USER, the statement raises a
warning for any target user or role named in the statement but not found; if
no target named by the statement exists, REVOKE succeeds but has no actual
effect. Otherwise, the statement executes as usual, and attempting to revoke
a privilege not assigned to the target for whatever reason raises an error,
as expected.
Example (continuing from the previous example):
Press CTRL+C to copy
mysql> DROP USER IF EXISTS jerry@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> REVOKE SELECT ON test.t1 FROM jerry@localhost;
ERROR 1147 (42000): There is no such grant defined for user 'jerry' on host
'localhost' on table 't1'
mysql> REVOKE SELECT ON test.t1 FROM jerry@localhost IGNORE UNKNOWN USER;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 3162
Message: Authorization ID jerry does not exist.
1 row in set (0.00 sec)
* The combination of IF EXISTS and IGNORE UNKNOWN USER means that REVOKE never
raises an error for an unknown target user or role or for an unassigned or
unavailable privilege, and the statement as whole in such cases succeeds;
roles or privileges are removed from existing target users or roles whenever
possible, and any revocation which is not possible raises a warning and
executes as a NOOP.
Example (again continuing from example in the previous item):
Press CTRL+C to copy
# No such user, no such role
mysql> DROP ROLE IF EXISTS Bogus;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------+
| Note | 3162 | Authorization ID 'Bogus'@'%' does not exist. |
+-------+------+----------------------------------------------+
1 row in set (0.00 sec)
# This statement attempts to revoke a nonexistent role from a nonexistent user
mysql> REVOKE Bogus ON test FROM jerry@localhost;
ERROR 3619 (HY000): Illegal privilege level specified for test
# The same, with IF EXISTS
mysql> REVOKE IF EXISTS Bogus ON test FROM jerry@localhost;
ERROR 1147 (42000): There is no such grant defined for user 'jerry' on host
'localhost' on table 'test'
# The same, with IGNORE UNKNOWN USER
mysql> REVOKE Bogus ON test FROM jerry@localhost IGNORE UNKNOWN USER;
ERROR 3619 (HY000): Illegal privilege level specified for test
# The same, with both options
mysql> REVOKE IF EXISTS Bogus ON test FROM jerry@localhost IGNORE UNKNOWN USER;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 3619 | Illegal privilege level specified for test |
| Warning | 3162 | Authorization ID jerry does not exist. |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)
Roles named in the mandatory_roles system variable value cannot be revoked. When
IF EXISTS and IGNORE UNKNOWN USER are used together in a statement that tries to
remove a mandatory privilege, the error normally raised by attempting to do this
is demoted to a warning; the statement executes successfully, but does not make
any changes.
A revoked role immediately affects any user account from which it was revoked,
such that within any current session for the account, its privileges are
adjusted for the next statement executed.
Revoking a role revokes the role itself, not the privileges that it represents.
Suppose that an account is granted a role that includes a given privilege, and
is also granted the privilege explicitly or another role that includes the
privilege. In this case, the account still possesses that privilege if the first
role is revoked. For example, if an account is granted two roles that each
include SELECT, the account still can select after either role is revoked.
REVOKE ALL ON *.* (at the global level) revokes all granted static global
privileges and all granted dynamic privileges.
A revoked privilege that is granted but not known to the server is revoked with
a warning. This situation can occur for dynamic privileges. For example, a
dynamic privilege can be granted while the component that registers it is
installed, but if that component is subsequently uninstalled, the privilege
becomes unregistered, although accounts that possess the privilege still possess
it and it can be revoked from them.
REVOKE removes privileges, but does not remove rows from the mysql.user system
table. To remove a user account entirely, use DROP USER. See Section 13.7.1.5,
“DROP USER Statement”.
If the grant tables hold privilege rows that contain mixed-case database or
table names and the lower_case_table_names system variable is set to a nonzero
value, REVOKE cannot be used to revoke these privileges. It is necessary in such
cases to manipulate the grant tables directly. (GRANT does not create such rows
when lower_case_table_names is set, but such rows might have been created prior
to setting the variable. The lower_case_table_names setting can only be
configured when initializing the server.)
When successfully executed from the mysql program, REVOKE responds with Query
OK, 0 rows affected. To determine what privileges remain after the operation,
use SHOW GRANTS. See Section 13.7.7.21, “SHOW GRANTS Statement”.
PREV HOME UP NEXT
Related Documentation
MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
Download this Manual
PDF (US Ltr) - 42.8Mb
PDF (A4) - 42.9Mb
Man Pages (TGZ) - 273.7Kb
Man Pages (Zip) - 385.2Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
Security in MySQL
Starting and Stopping MySQL
MySQL and Linux/Unix
MySQL and Windows
MySQL and macOS
MySQL and Solaris
Building MySQL from Source
MySQL Restrictions and Limitations
MySQL Partitioning
MySQL Tutorial
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL NDB Cluster 8.0
Contact MySQL Sales
USA/Canada: +1-866-221-0634 (More Countries »)
© 2023 Oracle
* Products
* MySQL HeatWave
* MySQL Enterprise Edition
* MySQL Standard Edition
* MySQL Classic Edition
* MySQL Cluster CGE
* MySQL Embedded (OEM/ISV)
* Services
* Training
* Certification
* Support
* Downloads
* MySQL Community Server
* MySQL NDB Cluster
* MySQL Shell
* MySQL Router
* MySQL Workbench
* Documentation
* MySQL Reference Manual
* MySQL Workbench
* MySQL NDB Cluster
* MySQL Connectors
* Topic Guides
* About MySQL
* Contact Us
* Blogs
* How to Buy
* Partners
* Job Opportunities
* Site Map
© 2023 Oracle
Privacy / Do Not Sell My Info | Terms of Use | Trademark Policy |
Evästeasetukset