Create Read-Only Database User

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.

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.

Oracle - Create Read-Only User

1

Connect as Administrator

Connect to your Oracle database as a privileged user (for example SYSTEM or another DBA account) using SQL*Plus, SQL Developer, or sqlcl:

sqlplus system/your_password@//localhost:1521/FREEPDB1

2

Create the Read-Only User

Create a dedicated user for Quemsi backups. Replace readonly_user and your_password with your desired username and password:

CREATE USER readonly_user IDENTIFIED BY "your_password"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 0 ON users;
        

3

Grant Catalog and Data Access

Quemsi needs to read metadata from Oracle data dictionary views and SELECT data from the schemas you configure. Replace APP_SCHEMA with the owner/schema you want to back up:

GRANT CREATE SESSION TO readonly_user;

GRANT SELECT ON APP_SCHEMA.table_name TO readonly_user;

-- Or grant read access to all tables in a schema
BEGIN
  FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'APP_SCHEMA') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON APP_SCHEMA.' || t.table_name || ' TO readonly_user';
  END LOOP;
END;
/

GRANT SELECT ON ALL_TABLES TO readonly_user;
GRANT SELECT ON ALL_TAB_COLUMNS TO readonly_user;
GRANT SELECT ON ALL_CONSTRAINTS TO readonly_user;
GRANT SELECT ON ALL_CONS_COLUMNS TO readonly_user;
GRANT SELECT ON ALL_INDEXES TO readonly_user;
GRANT SELECT ON ALL_IND_COLUMNS TO readonly_user;
GRANT SELECT ON ALL_SEQUENCES TO readonly_user;
GRANT SELECT ON ALL_CHECK_CONSTRAINTS TO readonly_user;
        

Schema Field in Quemsi

In Quemsi, the Schema field should contain the Oracle owner names you want to back up (for example HR or APP_SCHEMA). This is separate from the JDBC service name.

4

Verify the User

Connect as the new user and verify you can see the target schema metadata:

CONNECT readonly_user/your_password@//localhost:1521/FREEPDB1

SELECT owner, table_name
FROM all_tables
WHERE owner = 'APP_SCHEMA'
ORDER BY table_name;
        

Oracle Read-Only User Created

Your read-only user is ready. Example JDBC URL for Quemsi:

jdbc:oracle:thin:@//localhost:1521/FREEPDB1

Use service name format in the JDBC URL, and set the Quemsi Schema field to the Oracle owner name you want to back up.

MongoDB - Create Read-Only User

Do Not Use Admin/Root Credentials

Quemsi agents store connection settings locally. A read-only role reduces blast radius if credentials are shared or misused. Prefer a custom role limited to the databases you actually back up.

1

Connect to MongoDB as Administrator

Connect with an administrative account using mongosh:

mongosh "mongodb://admin:your_password@localhost:27017/admin"

2

Create a Database-Scoped Read Role

Create a dedicated role with only the privileges Quemsi needs for backups. Replace appdb with your target database name:

use admin
db.createRole({
  role: "quemsiBackupReader",
  privileges: [
    {
      resource: { db: "appdb", collection: "" },
      actions: [ "find", "listCollections", "listIndexes" ]
    }
  ],
  roles: []
})
        

3

Create the Read-Only User

Create a user and assign the custom role. Replace quemsi_ro and the password with your values:

db.createUser({
  user: "quemsi_ro",
  pwd: "replace-with-strong-password",
  roles: [
    { role: "quemsiBackupReader", db: "admin" }
  ]
})
        

Built-in Alternative

You can also grant MongoDB's built-in read role on a specific database:

db.createUser({
  user: "quemsi_ro",
  pwd: "replace-with-strong-password",
  roles: [ { role: "read", db: "appdb" } ]
})
          

4

Cluster-Wide Read (Optional)

Only if every database on the deployment should be readable:

use admin
db.createUser({
  user: "quemsi_ro_all",
  pwd: "replace-with-strong-password",
  roles: [ { role: "readAnyDatabase", db: "admin" } ]
})
        

5

Verify the User

Connect as the new user and confirm you can list collections and read documents:

mongosh "mongodb://quemsi_ro:PASSWORD@host:27017/appdb?authSource=admin"
db.getCollectionNames()
db.users.find().limit(1)
        

MongoDB Read-Only User Created

Your read-only user is ready. Example connection URI for Quemsi:

mongodb://quemsi_ro:PASSWORD@host:27017/appdb?authSource=admin

Set the datasource Db Name to appdb (or your target database), mark it read-only for backup-only agents, and run a test connection before scheduling flows.

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.

Create Read-Only Database User

Why Use Read-Only Users?

Security Benefits

Production Database Warning

Quick Navigation

MySQL - Create Read-Only User

Connect to MySQL as Administrator

Create the Read-Only User

Host Specification

Grant Read-Only Permissions

Apply the Changes

Verify the User

MySQL Read-Only User Created

PostgreSQL - Create Read-Only User

Connect to PostgreSQL as Administrator

Create the Read-Only User

Grant Connect Permission

Grant Usage on Schema

Grant Select on All Tables

Grant Select on Future Tables (Optional)

Default Privileges

Grant Select on Sequences (Optional)

PostgreSQL Read-Only User Created

SQL Server - Create Read-Only User

Connect to SQL Server as Administrator

Create the Read-Only User

Create User in Database

Add to db_datareader Role

Grant Additional Permissions (Optional)

Verify the User

SQL Server Read-Only User Created

SQL Server Authentication Note

Oracle - Create Read-Only User

Connect as Administrator

Create the Read-Only User

Grant Catalog and Data Access

Schema Field in Quemsi

Verify the User

Oracle Read-Only User Created

MongoDB - Create Read-Only User

Do Not Use Admin/Root Credentials

Connect to MongoDB as Administrator

Create a Database-Scoped Read Role

Create the Read-Only User

Built-in Alternative

Cluster-Wide Read (Optional)

Verify the User

MongoDB Read-Only User Created

Best Practices

🔒 Use Strong Passwords

🌐 Restrict Network Access

📊 Regular Permission Audits

🔄 Separate Accounts for Different Purposes

📝 Document Your Users

Ready to Use Read-Only Users in Quemsi?

Next Steps