There are times when you need to create a user only to have a read-only access to a database. The user can view or read the data in the database but they cannot make any changes to the data or the database structure.
Creating a New User Account
To create a read-only database user account for MySQL do the following steps:
- First, login as a MySQL administrator from your terminal / command prompt using the following command:
mysql -u root -p
- You’ll prompted to enter the password. Type the password for the
- Create a new MySQL user account.
CREATE USER 'report'@'%' IDENTIFIED BY 'secret';
% in the command above means that user
report can be used to connect from any host. You can limit the access by defining the host from where the user can connect. Omitting this information will only allow the user to connect from the same machine.
- Grant the
SELECTprivilege to user.
GRANT SELECT ON kodejava.* TO 'report'@'%';
- Execute the following command to make the privilege changes saved and take effect.
quitto exit from the MySQL shell.
Test the New User Account
- Now we can try the newly created user account. Start by login with the new user account and provide the corresponding password.
mysql -u report -p
- Try executing the
mysql> USE kodejava; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> DELETE FROM authors; ERROR 1142 (42000): DELETE command denied to user 'report'@'localhost' for table 'authors' mysql> UPDATE authors SET name = 'Wayan Saryada' WHERE id = 1; ERROR 1142 (42000): UPDATE command denied to user 'report'@'localhost' for table 'authors' mysql>