There are times when you need to create a user only to have 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 be 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>
Latest posts by Wayan (see all)
- How do I split large excel file into multiple smaller files? - April 15, 2023
- How do I get the number of processors available to the JVM? - March 29, 2023
- How do I show Spring transaction in log / console? - March 29, 2023
It is really helpful for creating new account in SQL, if someone looking for IT Training courses connect with us.
Very good information given to readers. I highly recommend this website to my friends.
Wonderful and more useful blog