Skip to content
DBAExperts
Oracle Users & permissions

How to create a user and grant permissions in Oracle

A new user should get only the permissions it needs. Over-granting is the most common cause of security problems.

  1. 1

    Create the user

    Set a password and the default tablespace. On container databases, decide whether it is common or local.

    CREATE USER app_user IDENTIFIED BY "Clave_Segura#2026"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 100M ON users;
  2. 2

    Grant connection privileges

    Without CREATE SESSION the user cannot log in even though it exists.

    GRANT CREATE SESSION TO app_user;
  3. 3

    Use roles instead of loose grants

    Group permissions into a role and assign it. It makes maintenance and auditing easier.

    CREATE ROLE app_rol;
GRANT SELECT, INSERT, UPDATE, DELETE ON ventas.pedidos TO app_rol;
GRANT app_rol TO app_user;
  4. 4

    Grant specific object privileges

    For targeted access, grant directly on the object. Avoid GRANT ANY except in justified cases.

    GRANT SELECT ON ventas.clientes TO app_user;
  5. 5

    Revoke and verify

    Remove what is unnecessary and review the user's effective privileges.

    SELECT * FROM dba_role_privs WHERE grantee = 'APP_USER';
SELECT * FROM dba_tab_privs WHERE grantee = 'APP_USER';

// common mistake

Granting DBA or CREATE ANY TABLE for convenience gives access to the entire database. An application user almost never needs it.

// when it's worth an expert

If the permission scheme has grown out of control or some users have more access than they should, it is worth auditing. At dba.mx we review and tidy up permissions at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.