How to create a read-only user in Oracle Database

Before connecting your Oracle database to any external tool, create a dedicated read-only user. This limits the blast radius if credentials are ever leaked and prevents accidental writes from queries that go wrong.

In Oracle, a user and a schema are the same thing. The read-only account you create here owns no objects. It is only granted SELECT on the schema that holds your data.

Step 1: Create the user

Connect as a privileged account (for example SYSTEM, or SYS AS SYSDBA) to the pluggable database that holds your data:

CREATE USER byesql_reader IDENTIFIED BY "Choose_a_strong_password1";

-- Minimum privilege needed to connect:
GRANT CREATE SESSION TO byesql_reader;

Step 2: Grant read access to your schema

To let the user read every table and view owned by, say, the APP schema:

-- Oracle 18c+: a clean, read-only system privilege
GRANT READ ANY TABLE TO byesql_reader;

READ ANY TABLE allows SELECT but not LOCK TABLE or SELECT ... FOR UPDATE, so it cannot be used to block writers. On older versions use GRANT SELECT ANY TABLE instead (functionally similar for this use case).

If READ ANY TABLE is too broad (it covers every schema), grant per object instead, as shown below.

Step 3: Connect ByeSQL

In the connection form use:

Host / Port: your Oracle host and listener port (default 1521)
Database: the service name (for example freepdb1 or ORCLPDB1), not the SID
Schema: the schema that owns your tables, e.g. APP (uppercase). If left blank, ByeSQL uses the connecting username as the schema
Username / Password: BYESQL_READER and its password

Limiting access to specific tables

To expose only certain tables, hiding for example a PAYMENT_METHODS or USER_CREDENTIALS table, skip READ ANY TABLE and grant SELECT on each object:

GRANT SELECT ON app.orders    TO byesql_reader;
GRANT SELECT ON app.products  TO byesql_reader;
GRANT SELECT ON app.customers TO byesql_reader;

The user, and the schema ByeSQL introspects, will see only those three tables.

Verify the setup

Connect as the read-only user and test:

SELECT * FROM app.orders FETCH FIRST 1 ROWS ONLY;   -- should succeed
INSERT INTO app.orders (quantity) VALUES (1);        -- should fail: insufficient privileges

Revoking access

DROP USER byesql_reader CASCADE;

A note on defense in depth

A read-only Oracle user is your database-level safeguard. ByeSQL adds a second layer through SAFE mode: all write operations (INSERT, UPDATE, DELETE, DROP, TRUNCATE, ALTER, MERGE) are blocked at the application level, and every generated query is capped with a FETCH FIRST n ROWS ONLY clause before it reaches your database.

Both controls together mean that even if one layer has a gap, the other catches it. Use both.