How to Manage Users in Oracle

How do I manage users in Oracle?

Manage users in Oracle by creating, altering, dropping accounts, and assigning roles/privileges for secure database access.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is user management in Oracle?

User management covers creating, modifying, and deleting database accounts and controlling their access through roles and system/object privileges.

How do I create a new user?

Use CREATE USER followed by GRANT to assign login and usage rights.

CREATE USER ecommerce_read IDENTIFIED BY strongPwd1; GRANT CREATE SESSION TO ecommerce_read; GRANT SELECT ON Orders TO ecommerce_read;

How do I alter an existing user?

Change passwords, default tablespaces, and quotas with ALTER USER.

ALTER USER ecommerce_read IDENTIFIED BY strongerPwd2; ALTER USER ecommerce_read DEFAULT TABLESPACE users;

How do I drop a user safely?

Remove accounts with DROP USER. Add CASCADE to delete owned objects.

DROP USER ecommerce_read CASCADE;

How do I grant roles and privileges?

Bundle permissions into roles, then grant them to users for easier administration.

CREATE ROLE order_manager; GRANT SELECT, INSERT, UPDATE ON Orders TO order_manager; GRANT order_manager TO john_doe;

Best practices for user management

Create least-privilege roles, enforce strong passwords, audit changes, and schedule periodic reviews.

Why How to Manage Users in Oracle is important

How to Manage Users in Oracle Example Usage


-- 1. Create reporting role with least privilege
CREATE ROLE reporting_role;
GRANT SELECT ON Customers, Orders, OrderItems TO reporting_role;

-- 2. Create user and assign role
CREATE USER report_user IDENTIFIED BY R3portPwd DEFAULT TABLESPACE users;
GRANT reporting_role TO report_user;

-- 3. Reset password after 90 days
ALTER USER report_user IDENTIFIED BY NewR3portPwd;

How to Manage Users in Oracle Syntax


-- Create user with password, default tablespace, quota
CREATE USER <username>
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE <tblspc>
  TEMPORARY TABLESPACE <temp_tblspc>
  QUOTA <n> M ON <tblspc>;

-- Grant system/object privileges or roles
GRANT <privilege>|<role> [, ...] TO <username> [WITH ADMIN OPTION];

-- Alter user properties
ALTER USER <username>
  IDENTIFIED BY <new_password>
  DEFAULT TABLESPACE <tblspc>
  ACCOUNT {LOCK|UNLOCK};

-- Drop user (CASCADE removes owned objects)
DROP USER <username> [CASCADE];

Common Mistakes

Granting system privileges directly to many users. Instead, create roles (e.g., reporting_role) and grant privileges to the role, then assign the role to users. This simplifies audits and revocation.
Dropping a user without CASCADE when the account owns objects. Oracle throws ORA-01922. Add CASCADE or reassign ownership before dropping.