CODEWITHSUNDEEP
mysqlsqlprivileges
BadHorsie
BadHorsie

Reputation: 14564

How to revoke MySQL user privileges for one table?

When I have granted privileges to a user for some specific tables:

GRANT ALL PRIVILEGES ON table1.* TO 'user1'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON table2.* TO 'user1'@'localhost' IDENTIFIED BY 'password';

How do I revoke the privileges for this user, just for table1?

Upvotes: 5

Views: 6661

Answers (2)

Noel
Noel

Reputation: 31

@Nadeem Taj. You are not correct. If you previously granted all privileges on. .

REVOKE ALL PRIVILEGES on Tblname.* treats Tblname as a schema name.

Proof:

mysql<[email protected]:[amp]> create user 'myuser'@'%' identified by 'password';
Query OK, 0 rows affected (0.02 sec)

mysql<[email protected]:[mysql]> grant all privileges on  *.* to 'myuser'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql<[email protected]:[mysql]> create database specialdb;
Query OK, 1 row affected (0.01 sec)

mysql<[email protected]:[mysql]> use specialdb;
Database changed
mysql<[email protected]:[specialdb]> create table tbl1(col1 int primary key);
Query OK, 0 rows affected (0.03 sec)

mysql<[email protected]:[mysql]> insert into specialdb.tbl1(col1) values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql<[email protected]:[specialdb]> REVOKE ALL PRIVILEGES ON tbl1.* FROM 'myuser'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql<[email protected]:[specialdb]> exit
Bye

$ mysql mysql  -umyuser -p
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql<[email protected]:[mysql]> select * from specialdb.tbl1;
+------+
| col1 |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

mysql<[email protected]:[mysql]>

Upvotes: 3

wally
wally

Reputation: 3592

Google is your friend! http://dev.mysql.com/doc/refman/5.7/en/revoke.html

Syntax:

REVOKE ALL PRIVILEGES ON table1.* FROM 'user1'@'localhost';

To further explain this answer - I'll teach how to fish (rather than just give you a fish).

The MySQL documentation can look confusing at first - the "syntax" for REVOKE looks like this:

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...

It means there are 3 "ways" of calling it:

  1. REVOKE priv_type ...
  2. REVOKE ALL PRIVILEGES, GRANT ...
  3. REVOKE PROXY ON ...

These three are separated by the blank lines in the MySQL doc page.

For each of these, there are "optional" parameters/settings/values. These are denoted by the square brackets, for example:

REVOKE priv_type [(column_list)] ...

The (column_list) is optional. You can supply it, but you don't have to.

(Updated note, Dec 2019:

The priv_type is what specifically lets us know we can specify ALL PRIVILEGES; for we are told in the documentation linked above:

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.4, “GRANT Statement”.

Section 13.7.1.4 states this:

Privileges Supported by MySQL

The following table summarizes the permissible priv_type privilege types that can be specified for the GRANT and REVOKE statements, and the levels at which each privilege can be granted.

  • ALL [PRIVILEGES] Grant all privileges at specified access

End update.)

Similarly you can chain these together - they've indented the next line to indicate this (and used ... to show you can continue repeating):

priv_type [(column_list)]
  [, priv_type [(column_list)]] ...    <-- indented, and note the "..."

More complicated examples exist in the MySQL documentation - like for CREATE TABLE you have lists of optional flags:

[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]

This {x|y|z} syntax indicates you must specify one of them (the {...} is non-optional, the [...] means everything inside is optional - so if you specify COLUMN_FORMAT, one of the three following flags is required), the pipes (|) indicate you can only specify one of the list (FIXED / DYNAMIC / DEFAULT).

One final thing to say - be very aware of the MySQL documentation version. It's stated in several places on the website - personally I just look at the URL:

http://dev.mysql.com/doc/refman/5.7/en/create-table.html

Note it says 5.7 in it. This means the documentation you're reading may not be applicable to any version other than MySQL 5.7. That's bitten me a lot of times ... usually when I'm under the gun trying to fix something in a panic! Always double-check it.

Upvotes: 12

Related Questions