I would like to grant selection rights for a specific table in a database in SQL. I know how to grant the rights for all tables in the database:
GRANT SELECT ON `web01\_database\_home`.* TO `user-1_ert`@`%` ;
The database web01\_database\_home has a table called test_table_1, so I tried to replace the * with that table name:
GRANT SELECT ON `web01\_database\_home`.test_table_1 TO `user-1_ert`@`%` ;
GRANT SELECT ON `web01\_database\_home.test_table_1` TO `user-1_ert`@`%` ;
Neither of those worked. How can I do this?
CodePudding user response：
Ahh, I see it says MariaDB - I just looked that up and found there was a manual (the GRANT section: https://mariadb.com/kb/en/grant/#grant-option) that contains this line, regarding giving permissions to specific columns of a table:
GRANT SELECT (name, position) on Employee to 'jeffrey'@'localhost';
Strangely, it doesn't seem to specify how to do it for a full table, but perhaps replacing (name, permission) with * might do it?
Just spitballing, but good luck
CodePudding user response：
You should use:
grant select on `web01\_database\_home`.`test_table_1` to 'user-1_ert'@'%';
Check the user if with the following command
select user,host from mysql.user;
Check if table exists as well
use `web01\_database\_home` ;
show tables like '%test_table_1%'
Check https://dev.mysql.com/doc/refman/8.0/en/grant.html
Tested on MariaDB 5.5.68
MariaDB [(none)]> select version();
----------------
| version() |
----------------
| 5.5.68-MariaDB |
----------------
1 row in set (0.00 sec)
MariaDB [(none)]> create database `web01\_database\_home` ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create user 'user-1_ert'@'%' identified by 'Some password';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> use `web01\_database\_home`;
Database changed
MariaDB [web01\_database\_home]> create table `test_table_1` (id int);
Query OK, 0 rows affected (0.00 sec)
MariaDB [web01\_database\_home]> show tables;
---------------------------------
| Tables_in_web01\_database\_home |
---------------------------------
| test_table_1 |
---------------------------------
1 row in set (0.00 sec)
MariaDB [web01\_database\_home]> use mysql;
Database changed
MariaDB [mysql]> grant select on `web01\_database\_home`.`test_table_1` to 'user-1_ert'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> show grants for 'user-1_ert'@'%';
-----------------------------------------------------------------------------------------------------------
| Grants for user-1_ert@% |
-----------------------------------------------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'user-1_ert'@'%' IDENTIFIED BY PASSWORD '*53D389B032BD7688B15F9B0FCA6AAAD803534255' |
| GRANT SELECT ON `web01\_database\_home`.`test_table_1` TO 'user-1_ert'@'%' |
-----------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)
Note Check if you have GRANT OPTION privilege for the user you are using.
MariaDB [mysql]> select current_user; ---------------- | current_user | ---------------- | root@localhost | ---------------- 1 row in set (0.00 sec) MariaDB [mysql]> show grants for root@localhost; --------------------------------------------------------------------- | Grants for root@localhost | --------------------------------------------------------------------- | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | --------------------------------------------------------------------- 2 rows in set (0.00 sec)
CodePudding user response：
I believe if you add OBJECT:: it may work for you:
GRANT SELECT ON OBJECT::test_table_1 TO user-1_ert;