Create a read-only MySQL user
Sometimes you just need to give access to some users of your database. As you don’t want theses users to modify or accidentally delete some data, it’s advisable to grant them only read access to the database.
Here’s how to provide only read privilege to a user:
grant select on database_name.* to 'username'@'%' identified by 'userpasswd';
grant select on otherdb_name.* to 'username'@'%' identified by 'userpasswd';
show grants for username;
+----------------------------------------------------------------------------------------------+
| Grants for username@% |
+----------------------------------------------------------------------------------------------+
| GRANT SELECT ON database_name.* TO 'username'@'%' IDENTIFIED BY PASSWORD '*DE425F65DC78103D' |
+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
flush privileges;
Tags:
You may also enjoy:
Last rowcount in MySQL
#mysqlSuppose you want to count the number of lines returned by the last SQL statement issued. For **select** statements you can use the *FOUND_ROWS* construct: ```sql SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS(); ``` This will return the number of rows in the last **select** query (or if the first query ... Read more
20 Sep 2016 - less than 1 minute read
MySQL punch user creation DDL
#mysqlYou can get the DDL necessary to duplicate an existing user with the following system command: ``` MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user'' " | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants... Read more
20 Jul 2016 - less than 1 minute read
InnoDB table rebuild
#mysql### PROBLEM On a **MySQL 5.6** database server, the *ibdata1* file includes 5 InnoDB tables in the mysql schema. ```sql mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table... Read more
20 Jun 2016 - 1 minute read