MySQL REVOKE
Summary: in this tutorial, you will learn how to use MySQL REVOKE
statement to revoke privileges from user accounts.
Introduction to the MySQL REVOKE
statement
The REVOKE
statement revokes one or more privileges from a user account.
The REVOKE
the statement has several forms.
Revoke one or more privileges
The following illustrates the basic syntax of the REVOKE
a statement that revokes one or more privileges from user accounts:
REVOKE
privilegee [,privilege]..
ON [object_type] privilege_level
FROM user1 [, user2] ..;
In this syntax:
- First, specify a list of comma-separated privileges that you want to revoke from a user account after the
REVOKE
keyword. - Second, specify the object type and privilege level of the privileges after the
ON
keyword; check it out theGRANT
statement for more information on the privilege level. - Third, specify one or more user accounts from which you want to revoke the privileges in the
FROM
clause.
Note that to execute this form of REVOKE
the statement, you must have GRANT OPTION
the privilege or you must have the privileges that you are revoking.
Revoke all privileges
To revoke all privileges from a user, you use the following form of the REVOKE ALL
statement:
REVOKE
ALL [PRIVILEGES],
GRANT OPTION
FROM user1 [, user2];
To execute the REVOKE ALL
the statement, you must have the global CREATE USER
privilege or the UPDATE
privilege for the mysql
system database.
Revoke Proxy
To revoke a proxy user, you use the REVOKE PROXY
command:
REVOKE PROXY
ON proxied_user
FROM proxy_user1[,proxy_user1]...;
A proxy user is a valid user in MySQL who can impersonate another user, therefore, the proxy user has all privileges of the user that it impersonates.
It is a good practice to show privileges of the user accounts using the SHOW GRANTS
statement before you revoke the privileges from the user:
SHOW GRANTS FOR user;
MySQL REVOKE
examples
Let’s take some examples of revoking privileges.
A) Using MySQL REVOKE
to revoke some privileges from a user account example
First, create a user account named rfc@localhost:
CREATE USER rfc@localhost
IDENTIFIED BY 'Secret1Pass!';
Second, grant rfc@localhost
the SELECT
, UPDATE
, and INSERT
privileges on the classicmodels
database:
GRANT SELECT, UPDATE, INSERT
ON classicmodels.*
TO rfc@localhost;
Third, display the granted privileges of the rfc@localhost user account:
SHOW GRANTS FOR rfc@localhost;
Fourth, revoke the UPDATE
and INSERT
privileges from rfc@localhost
:
REVOKE INSERT, UPDATE
ON classicmodels.*
FROM rfc@localhost;
Fifth, display the privileges of rfc@localhost
:
SHOW GRANTS FOR rfc@localhost;
B) Using MySQL REVOKE
to revoke all privileges from a user account example
First, grant the EXECUTE
privilege to the rfc@localhost
:
GRANT EXECUTE
ON classicmodels.*
TO rfc@localhost;
Second, show the currently granted privileges of rfc@localhost
:
Third, revoke all privileges of the rfc@localhost
user account by using the REVOKE ALL
statement:
REVOKE ALL, GRANT OPTION
FROM rfc@localhost;
Finally, show the privileges of the rfc@localhost
to verify the revoke:
SHOW GRANTS FOR rfc@localhost;
The rfc@localhost
has no privileges. Note that USAGE
privilege means no privileges in MySQL.
C) Using MySQL REVOKE
to revoke PROXY
privilege example
First, grant the PROXY
privilege to rfc@localhost
user account:
GRANT PROXY
ON root
TO rfc@localhost;
Second, show the granted privileges of rfc@localhost
:
SHOW GRANTS FOR rfc@localhost;
Third, revoke the PROXY
privilege from the rfc@localhost
:
REVOKE PROXY
ON root
FROM rfc@localhost;
Finally, show the granted privileges of rfc@lcoalhost to verify the revoke:
SHOW GRANTS FOR rfc@localhost;
When the MySQL REVOKE
command takes effect
The effect of REVOKE
statement depends on the privilege level:
Global level
The changes take effect when the user account connects to the MySQL Server in the subsequent sessions. The changes are not applied to all currently connected users.
Database level
The changes take effect after the next USE
statement.
Table and column levels
The changes take effect on all subsequent queries.
In this tutorial, you’ve learned how to use the MySQL REVOKE
statement to revoke privileges from user accounts.
0 Comments
CAN FEEDBACK
Emoji