Sometimes you need someone to look at your website's data without being able to change it. Maybe you're hiring a developer to review a problem, sharing figures with an accountant, or connecting a reporting tool. In these cases, giving that database user full privileges is more access than they need. You can limit a MySQL user to read-only access instead, so they can run SELECT queries but can't insert, update, or delete anything.
Why Limit a Database User to Read-Only Access
A database user with full privileges can change or delete any row in the tables they're connected to, intentionally or by mistake. If someone only needs to view data, read-only access protects you from accidental changes while still letting them do their job.
This is different from creating a brand-new user. If the user already exists and currently has full privileges, you're simply editing what that user is allowed to do on a specific database.
In cPanel
- Log in to your hosting control panel and open MySQL® Databases under the Databases section.
- Scroll to Current Databases and find the database and user pairing you want to adjust.
- Click the option to manage that user's privileges on the database (this removes and re-adds the user to the database so you can set new privileges).
- On the Manage User Privileges page, first make sure All Privileges is unchecked.
- Tick only SELECT, then click Make Changes.
The user can now read data from that database but cannot insert, update, delete, or alter anything in it.
In DirectAdmin
- Log in to your hosting control panel and open MySQL Management.
- Click on the database name to open its user list.
- Find the user under Modify a User's Access, then uncheck All in the privileges list.
- Check only the SELECT box, then click Modify.
DirectAdmin applies the change immediately, so the user's next connection will use the new, restricted privileges.
Test the connection after making the change, ideally using the tool the person will actually use (phpMyAdmin, a reporting app, or a database client). If they try to run an update or delete and it's rejected, the restriction is working correctly.
When You Might Need More Than SELECT
Some tools that only display data still need a little more than plain SELECT to function properly, such as temporary table creation for complex reports. If a read-only tool throws permission errors, check its documentation for the exact privileges it needs rather than defaulting back to All Privileges.
If you need to reverse this later and restore full access, follow the same steps and tick All Privileges instead.
If you haven't added this user to a database before, see How to Grant a Database User Access to a Database first.