Oracle DB: Create New Users, Assign Roles, Privileges, and Table Spaces

Here are the steps for creating new users and granting privileges to them on an Oracle database.

(01) Get a session to your current DB as SYSDBA or other privileged user using a tool like Oracle SQL Developer or Oracle-Docker Instant Client.

(02) Create a new user with new password.

// set session
SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=true;

// create user
SQL> CREATE USER app_user IDENTIFIED BY pf123;

// verify 
SQL> SELECT username, default_tablespace, profile, authentication_type FROM dba_users WHERE account_status = 'OPEN';

(03) Create table space.

SQL> CREATE TABLE <schema_name>.<table_name>(....)

SQL> CREATE TABLE app_user.pf_app_records(
        sid VARCHAR2(30 BYTE), 
        value NUMBER,
        create_date DATE,
        status CHAR(1 BYTE))

(04) Verify table creation.

SQL> DESCRIBE pf_app_records;
         Name                                      Null?    Type
         ----------------------------------------- -------- ----------------------------
         SID                                                VARCHAR2(30)
         VALUE                                              NUMBER
         CREATE_DATE                                        DATE
         STATUS                                             CHAR(1)

(05) Grant unlimited permissions to new user for all tablespaces of the database. However, this is not recommended. Always provide minimum privileges for users.

SQL> SELECT default_tablespace FROM dba_users WHERE username = 'app_user';
        DEFAULT_TABLESPACE
        ------------------------------
        USERS

SQL> ALTER USER app_user QUOTA UNLIMITED ON users;

(06) Here are the most common ways of providing privileges for individuals.

// allow user to login to database and create a session using a db client tool
SQL> GRANT CREATE SESSION TO app_user;

// allow user to create tables
SQL> GRANT CREATE TABLE TO app_user;

// allow user to use disk space in the system to actually create or modify tables and data
SQL> GRANT UNLIMITED TABLESPACE TO app_user;

(07) Also, you can provide privileges per tablespace.

SQL> GRANT
       SELECT,
       INSERT,
       UPDATE,
       DELETE
     ON
       schema.pf_app_records
     TO
       app_user;

(08) Here are the most common ways of assigning roles that includes a set of privileges.

// allow user to login
SQL> GRANT CONNECT TO app_user;

// allow user to create named types for custom schemas
SQL> GRANT RESOURCE TO app_user;

// allow user to not only create custom named types but alter and destroy them
SQL> GRANT DBA TO app_user;

---

✅ Tested DB	: Oracle 12c
✅ Tested OS's	: RHEL 7+, CentOS 7+, Ubuntu 18.04+, Debian 8+
✅ Tested Gear	: Cloud (AWS EC2 T2), On-Prem (Bare Metal)

---

👉 Any questions? Please comment below.

---