user

These commands are typically used by database administrators (DBAs) to manage users, permissions, and roles in the database.

Create a New User

ALTER SESSION SET CONTAINER = FREEPDB1;

CREATE USER "zhamri" IDENTIFIED BY "zhamri123";

Grant Privileges to a User

1. Grant System Privileges (e.g., creating tables, sessions):

GRANT CREATE SESSION TO "zhamri"; -- Allows user to log in
GRANT CREATE TABLE TO "zhamri";   -- Allows user to create tables

2. Grant Object Privileges (e.g., access to specific tables):

GRANT SELECT, INSERT, UPDATE ON "d1_student_realtime" TO "zhamri";

3. Grant Role (e.g., DBA role for full privileges):

GRANT DBA TO "zhamri";

Change a User’s Password

ALTER USER "zhamri" IDENTIFIED BY new_password;

Lock or Unlock a User Account

1. Lock a user account to prevent login:

ALTER USER username ACCOUNT LOCK;

2. Unlock a user account to allow login:

ALTER USER username ACCOUNT UNLOCK;

Drop (Delete) a User

Delete a user and all associated objects:

DROP USER username CASCADE;

Drop a user who is currently connected

SELECT sid, serial# 
FROM v$session 
WHERE username = 'ZHAMRI';


ALTER SYSTEM KILL SESSION 'sid,serial#';

## example
ALTER SYSTEM KILL SESSION '196,63300';

DROP USER ZHAMRI CASCADE;

View All Users

List all users you have access to:

SELECT username FROM all_users;

List all users (requires DBA privileges):

SELECT username FROM dba_users;

Check the Current User

SELECT USER FROM dual;

Check User Privileges

List system privileges:

SELECT * FROM user_sys_privs WHERE username = 'USERNAME';

List object privileges:

SELECT * FROM user_tab_privs WHERE grantee = 'USERNAME';

Revoke Privileges from a User

REVOKE privilege FROM username;

REVOKE CREATE TABLE FROM username;