Most web applications only need to read and write data — they never create tables, drop indexes, or manage stored procedures. By granting only SELECT, INSERT, UPDATE, and DELETE, you give your application exactly the permissions it needs and nothing more.
Why Data-Only Privileges Are Ideal for Applications
A standard web application performs four data operations:
- SELECT — Read rows from tables (loading pages, fetching records).
- INSERT — Add new rows (user registrations, form submissions).
- UPDATE — Modify existing rows (editing profiles, updating settings).
- DELETE — Remove rows (deleting posts, clearing sessions).
These four privileges cover virtually every query a CMS, e-commerce platform, or custom web app will ever run. Granting additional structure or admin privileges creates unnecessary risk.
Method 1: During User Creation
- Log in at mysql.nobregas.org.
- Go to Database Users > click Create User.
- Enter the username, password, and host.
- Select the target database from the Grant to Database dropdown.
- The Privileges section appears with ALL PRIVILEGES checked.
- Uncheck ALL PRIVILEGES to reveal individual checkboxes.
- Under Data, check: SELECT, INSERT, UPDATE, DELETE.
- Leave all Structure and Administration checkboxes unchecked.
- Click Create User.
Method 2: Via Manage (Existing User)
- Go to Database Users and click Manage on the user.
- In the Add Database Access section, select the database.
- Uncheck ALL PRIVILEGES.
- Check only SELECT, INSERT, UPDATE, DELETE.
- Click Add Grant.
Method 3: Downgrading an Existing Grant
If a user currently has ALL PRIVILEGES and you want to restrict to data-only:
- Click Manage on the user.
- Find the database in Current Grants and click Edit.
- Uncheck ALL PRIVILEGES.
- Check only SELECT, INSERT, UPDATE, DELETE.
- Click Save Changes.
What This User Can Do
| Operation | Example Query | Allowed? |
|---|---|---|
| Read data | SELECT * FROM users |
Yes |
| Add rows | INSERT INTO orders (...) |
Yes |
| Edit rows | UPDATE products SET price = 9.99 |
Yes |
| Remove rows | DELETE FROM sessions WHERE expired = 1 |
Yes |
| Create table | CREATE TABLE logs (...) |
No |
| Drop table | DROP TABLE users |
No |
| Alter structure | ALTER TABLE users ADD COLUMN age INT |
No |
When to Use This Setup
- WordPress, Joomla, Laravel, Django — These frameworks only need CRUD operations after initial setup.
- E-commerce stores — WooCommerce, PrestaShop, and similar platforms.
- Custom APIs — REST or GraphQL backends that read and write data.
- Shared hosting environments — Limit blast radius if credentials leak.
Security Benefit
If an attacker gains access to a data-only user, they cannot:
- Drop or truncate your tables.
- Alter your table structure.
- Create triggers or stored procedures.
- Cause irreversible structural damage.
Your data may still be at risk, but your database schema stays intact — making recovery significantly easier.
