Creating a user without deleting, updating and altering privileges in MySQL

Question

I have to provide direct access to my database to some users for auditing purposes, and should add a restriction to avoid that these new users don't have deleting, updating and altering privileges.

Answer 1

Just create a user and grant only SELECT privilege.

CREATE USER user_name@host_name identified by 'password';
GRANT SELECT ON db_name.* TO user_name@host_name;

To check what privileges a user has use

SHOW GRANTS FOR user_name@host_name;

and make sure that a user only has GRANT USAGE and GRANT SELECT ON db_name.*

---

Lets say I have my_db database with test table in it and I want to create a user with a name user1 who will be allowed to connect only from local host and will be able to read data from all tables in this database but won't be able to insert, change, and delete data.

mysql> create user user1@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user1@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> grant select on my_db.* to user1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user1@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT ON `my_db`.* TO 'user1'@'localhost'                                                             |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Now lets see what our user1 can and can't do

$ mysql -uuser1 -p 
mysql> use mysql
ERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'mysql'
mysql> use test
ERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'test'
mysql> use my_db
Database changed

As you can see our user1 can only connect to my_db database.

Now let see what that user can do with data in table test (the only table in that database)

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> insert into test values (3);
ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'test'
mysql> delete from test where id = 1;
ERROR 1142 (42000): DELETE command denied to user 'user1'@'localhost' for table 'test'
mysql> update test set id = 10 where id = 1;
ERROR 1142 (42000): UPDATE command denied to user 'user1'@'localhost' for table 'test'

Again as you can the user can only select from the table.