how to set read only for database and all its tables in mysql server for mysql user

D_wathi
D_wathi used Ask the Experts™
on
Dear Experts
Would like to create power BI user account into MySQL transnational DB (CRM application -based on LAMP stack) but we would like to apply security permissions of the PowerBI account to have ‘Read-Only’ to the database and all it is tables.
The objective is the MySQL user which being used as power BI connector account to have only “Read-Only” privileges therefore no write/update/modifications/delete is possible from PowerBI to the Database of CRM applicaiton, please suggest the privileges that to be set for the database for the power BI user account.
 The mysql user name is sqlconuser and database name production, please help with the command in the mysql prompt command line in linux system on how to set privileges read only  for the database name "production " for the MySQL user sqlconuser think should also provide access to access to all Tables, Views and Triggers, please provide compete command that to be executed, thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator / Software Engineer
Commented:
Hi,

To let user have read-only access to a database you grant only select to the database/tables that the user needs.

This grants the user select privilege on all tables in mydatabase
GRANT SELECT ON mydatabase.* TO 'user'@'users-host';

Open in new window


So in your case it would look like this
GRANT SELECT ON production.* TO 'sqlconuser'@'sqlconuser-host';

Open in new window


Note that sqlconuser-host is the hostname and/or IP/IP-range that the user is allowed to connect from to the database.

https://dev.mysql.com/doc/refman/8.0/en/grant.html

Regards,
   Tomas Helgi

Author

Commented:
Thank you very much, should we have to also include Views and Triggers in the command OR only SELECT is enough please suggest.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

If the user is not updating a table then trigger privilege is not needed.
If you grant select on production.* like in the example above then the user has read access to both tables and views.

Regards,
    Tomas Helgi

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial