I granted the wrong user the wrong access, and it took three days to recover the database.

Permission management in SQL*Plus is not a side task. It is the backbone of secure and stable systems. One wrong GRANT or REVOKE can open the door to data loss, downtime, or worse. Knowing exactly how to give — and take back — the right privileges is essential when working with Oracle databases from SQL*Plus.

SQL*Plus is a command-line tool. It connects directly to the database and allows you to execute powerful commands instantly. This access means you can create users, assign system privileges, and fine-tune object permissions without a single GUI click. It also means mistakes propagate fast. Good permission management starts with mastering the core commands.

Creating and Managing Users

First, connect with a privileged account:

sqlplus sys as sysdba

Then, create a new user:

CREATE USER dev_user IDENTIFIED BY strong_password;

This user can log in but cannot do much until given privileges.

Granting Privileges

To allow the user to perform actions:

Continue reading? Get the full guide.

Database Access Proxy + Customer Support Access to Production: Architecture Patterns & Best Practices

Free. No spam. Unsubscribe anytime.

GRANT CREATE SESSION TO dev_user;
GRANT CREATE TABLE TO dev_user;

CREATE SESSION is the minimum required for login. Layer additional privileges only when needed. Avoid giving DBA unless absolutely necessary.

Object-Level Control

For fine-grained control:

GRANT SELECT, INSERT ON hr.employees TO dev_user;

This limits access to only what is needed.

Revoking Access

If a user no longer needs permissions:

REVOKE INSERT ON hr.employees FROM dev_user;

Or remove all privileges and the account:

DROP USER dev_user CASCADE;

Auditing and Best Practices

Review active permissions regularly.
Favor least privilege over convenience.
Keep a changelog for all GRANT and REVOKE actions.
Use roles to group common permissions for easy updates.

Good permission management in SQL*Plus keeps your environment predictable and safe. It lets you move fast without risking data integrity. The cost of one mistake is high. The payoff for precision is higher.