Home / Tutorials / Create Read-Only Database User

Create Read-Only Database User

Learn how to create read-only users for MySQL, PostgreSQL, and SQL Server databases. Follow security best practices by using read-only accounts for production databases.

Why Use Read-Only Users?

Using read-only database users is a critical security best practice, especially for production databases. Here's why:

Security Benefits

  • Principle of Least Privilege: Users only get the minimum permissions needed for their tasks
  • Accident Prevention: Prevents accidental data modification or deletion
  • Audit Trail: Makes it easier to track who has write access vs. read access
  • Compliance: Helps meet security compliance requirements
  • Backup Safety: Read-only users can't accidentally corrupt backup processes

Production Database Warning

Always use read-only users for production databases when possible. Even if your application needs write access, create separate read-only accounts for backup tools, monitoring systems, and reporting tools like Quemsi.

Quick Navigation

MySQL - Create Read-Only User

1

Connect to MySQL as Administrator

First, connect to your MySQL server with an account that has administrative privileges (like root):

mysql -u root -p

Enter your root password when prompted.

2

Create the Read-Only User

Create a new user with a strong password. Replace readonly_user and your_password with your desired username and password:

CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'your_password';

Host Specification

The '%' allows connections from any host. For better security, you can restrict access:

  • 'localhost' - Only local connections
  • '192.168.1.%' - Only from specific subnet
  • 'specific-ip' - Only from specific IP address
3

Grant Read-Only Permissions

Grant SELECT permission on the specific database. Replace your_database with your actual database name:

GRANT SELECT ON your_database.* TO 'readonly_user'@'%';

If you want read-only access to all databases:

GRANT SELECT ON *.* TO 'readonly_user'@'%';
4

Apply the Changes

Flush privileges to apply the changes immediately:

FLUSH PRIVILEGES;
5

Verify the User

Verify that the user was created and has the correct permissions:

SHOW GRANTS FOR 'readonly_user'@'%';

You should see only SELECT privileges listed.

MySQL Read-Only User Created

Your read-only user is now ready to use. You can connect to MySQL using:

mysql -u readonly_user -p your_database

PostgreSQL - Create Read-Only User

1

Connect to PostgreSQL as Administrator

Connect to your PostgreSQL server as a superuser (typically postgres):

psql -U postgres

Or connect to a specific database:

psql -U postgres -d your_database
2

Create the Read-Only User

Create a new user with a password. Replace readonly_user and your_password with your desired username and password:

CREATE USER readonly_user WITH PASSWORD 'your_password';
3

Grant Connect Permission

Grant the user permission to connect to the database:

GRANT CONNECT ON DATABASE your_database TO readonly_user;
4

Grant Usage on Schema

Grant usage permission on the schema (typically public):

GRANT USAGE ON SCHEMA public TO readonly_user;

If you're using a different schema, replace public with your schema name.

5

Grant Select on All Tables

Grant SELECT permission on all existing tables in the schema:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
6

Grant Select on Future Tables (Optional)

To automatically grant SELECT on tables created in the future, set default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Default Privileges

This step ensures that any new tables created in the schema will automatically have SELECT permissions granted to the read-only user. This is especially useful in production environments where tables may be added over time.

7

Grant Select on Sequences (Optional)

If your database uses sequences (for auto-increment columns), grant SELECT on them:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly_user;

PostgreSQL Read-Only User Created

Your read-only user is now ready to use. You can connect to PostgreSQL using:

psql -U readonly_user -d your_database

SQL Server - Create Read-Only User

1

Connect to SQL Server as Administrator

Connect to your SQL Server instance using SQL Server Management Studio (SSMS) or sqlcmd with an account that has administrative privileges (like sa or a Windows administrator account):

sqlcmd -S localhost -U sa -P your_password

Or connect using SSMS with Windows Authentication or SQL Server Authentication.

2

Create the Read-Only User

Create a new SQL Server login. Replace readonly_user and your_password with your desired username and password:

CREATE LOGIN readonly_user WITH PASSWORD = 'your_Password1';
3

Create User in Database

Switch to your target database and create a user mapped to the login. Replace your_database with your actual database name:

USE your_database; GO CREATE USER readonly_user FOR LOGIN readonly_user; GO
4

Add to db_datareader Role

Add the user to the db_datareader role, which grants SELECT permission on all tables in the database:

ALTER ROLE db_datareader ADD MEMBER readonly_user; GO
5

Grant Additional Permissions (Optional)

If you need to grant SELECT on specific schemas or want to use views, you can grant additional permissions:

-- Grant SELECT on a specific schema
GRANT SELECT ON SCHEMA::dbo TO readonly_user;
GO

-- Grant SELECT on specific tables
GRANT SELECT ON dbo.your_table TO readonly_user;
GO
6

Verify the User

Verify that the user has the correct permissions:

-- Check role membership
SELECT r.name AS role_name, m.name AS member_name
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE m.name = 'readonly_user';
GO

You should see db_datareader in the results.

SQL Server Read-Only User Created

Your read-only user is now ready to use. You can connect to SQL Server using:

sqlcmd -S localhost -U readonly_user -P your_Password1 -d your_database

SQL Server Authentication Note

If you're setting up SQL Server for the first time, you may need to enable SQL Server Authentication and TCP/IP connections. See our tutorial on enabling TCP connections for detailed instructions.

Best Practices

🔒 Use Strong Passwords

Always use strong, unique passwords for database users. Consider using a password manager to generate and store complex passwords.

🌐 Restrict Network Access

When possible, restrict database user access to specific IP addresses or subnets. This adds an additional layer of security.

📊 Regular Permission Audits

Periodically review database user permissions to ensure they still match the principle of least privilege. Remove unnecessary permissions.

🔄 Separate Accounts for Different Purposes

Create separate read-only accounts for different purposes (backups, monitoring, reporting) to better track access and maintain security.

📝 Document Your Users

Keep documentation of all database users, their purposes, and their permission levels. This helps with security audits and troubleshooting.

Ready to Use Read-Only Users in Quemsi?

Now that you've created read-only users, you can safely connect Quemsi to your production databases with confidence that your data is protected.

Get Started Free

Next Steps