Docs / Oracle

Connect Oracle

Enterprise relational database

Oracle connects with a service name and ordinary credentials. The setup below creates a user that can start a session and read — and, thanks to a zero tablespace quota, cannot create anything even if it were granted the privilege.

Credentials

Host
Hostname or IP, for example oracle.example.com.
Port
1521 unless your listener uses another.
Service name
The service name, not the SID — for example ORCL. Service names are the modern form and support load balancing.
Username
The read-only user created below.
Password
That user’s password.

Creating a read-only user

Connect as a DBA through sqlplus or SQL Developer.

1 · The user, with no ability to create objects

CREATE USER climpt_readonly IDENTIFIED BY "strong_password_here"
DEFAULT TABLESPACE users
QUOTA 0 ON users;

GRANT CREATE SESSION TO climpt_readonly;

QUOTA 0 is what stops the account writing to the tablespace at all. It is worth setting even though the grants below are read-only.

2 · Read access

-- Broad: every table in the database
GRANT SELECT ANY TABLE TO climpt_readonly;

-- Or tighter: one table at a time
GRANT SELECT ON schema_name.table_name TO climpt_readonly;

-- Optional: data dictionary views, for metadata
GRANT SELECT_CATALOG_ROLE TO climpt_readonly;

Per-table grants are the tightest option if you already know which tables you want analysed. SELECT ANY TABLE is simpler but reaches everything.

Encryption in transit

Oracle Net encryption is configured server-side in sqlnet.ora:

SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)

Then open port 1521 only to the addresses that need it, and confirm the TNS listener is running on the port you expect.

Managed Oracle

  • Oracle Cloud (OCI) — use the connection string from Autonomous Database. Both mTLS wallets and plain TLS are supported.
  • AWS RDS for Oracle — security groups for network access, with TLS via the rds-ca certificates.
  • Oracle on Azure VMs — NSG rules for port access.

The SQL above is unchanged on all of them.

Worth knowing

  • Oracle uses double quotes for case-sensitive identifiers: "My_Table".
  • Grant only CREATE SESSION and SELECT. Anything beyond that is access climpt will not use.
  • Password expiry is profile-based: ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90;