{"id":3789,"date":"2025-06-08T02:10:00","date_gmt":"2025-06-08T02:10:00","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=3789"},"modified":"2025-11-01T10:22:17","modified_gmt":"2025-11-01T10:22:17","slug":"oracle-user-management","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/oracle-user-management\/","title":{"rendered":"Oracle User Management"},"content":{"rendered":"\n

An Oracle user<\/strong> is an account that can connect to the database and perform operations. Managing users ensures proper access control, security, and organization of database activities.<\/p>\n\n\n\n

1. Creating a New User<\/h2>\n\n\n\n
CREATE USER W3BUDDY IDENTIFIED BY Welcome#123\nPROFILE DEFAULT\nDEFAULT TABLESPACE USERS\nTEMPORARY TABLESPACE TEMP;<\/code><\/code><\/pre>\n\n\n\n
Grant Minimum Privilege to Connect<\/h3>\n\n\n\n
GRANT CREATE SESSION TO W3BUDDY;<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udca1 Tip:<\/strong> You may also grant roles like CONNECT<\/code>, RESOURCE<\/code>, or custom roles if the user needs access to create tables, procedures, etc.<\/em><\/p>\n\n\n\n
Check User Status<\/h2>\n\n\n\n
SET LINESIZE 180\nSET PAGESIZE 100\n\nCOLUMN USERNAME FORMAT A20\nCOLUMN ACCOUNT_STATUS FORMAT A25\nCOLUMN PROFILE FORMAT A20\nCOLUMN CREATED FORMAT A20\nCOLUMN LOCK_DATE FORMAT A20\nCOLUMN EXPIRY_DATE FORMAT A20\nCOLUMN DEFAULT_TABLESPACE FORMAT A20\nCOLUMN TEMPORARY_TABLESPACE FORMAT A20\n\nSELECT \n    USERNAME,\n    ACCOUNT_STATUS,\n    PROFILE,\n    TO_CHAR(CREATED, 'DD-MON-YYYY HH24:MI:SS') AS CREATED,\n    TO_CHAR(LOCK_DATE, 'DD-MON-YYYY HH24:MI:SS') AS LOCK_DATE,\n    TO_CHAR(EXPIRY_DATE, 'DD-MON-YYYY HH24:MI:SS') AS EXPIRY_DATE,\n    DEFAULT_TABLESPACE,\n    TEMPORARY_TABLESPACE\nFROM \n    DBA_USERS\nWHERE \n    USERNAME = UPPER('&username');<\/code><\/pre>\n\n\n\n
2. Managing User Passwords<\/h2>\n\n\n\n
Change Password<\/h3>\n\n\n\n
ALTER USER W3BUDDY IDENTIFIED BY NewPass12345#;<\/code><\/code><\/pre>\n\n\n\n
Expire Password (forces password change on next login)<\/h3>\n\n\n\n
ALTER USER W3BUDDY PASSWORD EXPIRE;<\/code><\/pre>\n\n\n\n
\ud83d\udca1 Tip:<\/strong> Expiring the password is useful for enforcing first-time password changes.<\/em><\/p>\n\n\n\n
3. Locking and Unlocking User Accounts<\/h2>\n\n\n\n
Lock User<\/h3>\n\n\n\n
ALTER USER W3BUDDY ACCOUNT LOCK;<\/code><\/code><\/pre>\n\n\n\n
Unlock User<\/h3>\n\n\n\n
ALTER USER W3BUDDY ACCOUNT UNLOCK;<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udca1 Tip:<\/strong> Always lock unused accounts or those under investigation.<\/em><\/p>\n\n\n\n
4. Managing Default Tablespace<\/h2>\n\n\n\n
View Default Tablespace<\/h3>\n\n\n\n
SET LINESIZE 150\nCOLUMN DEFAULT_TABLESPACE FORMAT A20\n\nSELECT USERNAME, DEFAULT_TABLESPACE \nFROM DBA_USERS \nWHERE USERNAME = 'W3BUDDY';<\/code><\/code><\/pre>\n\n\n\n
Change Default Tablespace<\/h3>\n\n\n\n
ALTER USER W3BUDDY DEFAULT TABLESPACE DATA_TBS;<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udca1 Tip:<\/strong> Ensure the new tablespace has sufficient quota before assigning.<\/em><\/p>\n\n\n\n
5. Managing Temporary Tablespace<\/h2>\n\n\n\n
View Temporary Tablespace<\/h3>\n\n\n\n
SET LINESIZE 150\nCOLUMN TEMPORARY_TABLESPACE FORMAT A20\n\nSELECT USERNAME, TEMPORARY_TABLESPACE \nFROM DBA_USERS \nWHERE USERNAME = 'W3BUDDY';<\/code><\/code><\/pre>\n\n\n\n
Change Temporary Tablespace<\/h3>\n\n\n\n
ALTER USER W3BUDDY TEMPORARY TABLESPACE TEMP2;<\/code><\/code><\/pre>\n\n\n\n
6. Profile Management<\/h2>\n\n\n\n
Profiles define resource limits and password policies.<\/p>\n\n\n\n
View Profile Settings<\/h3>\n\n\n\n
SET LINESIZE 200\nSET PAGESIZE 100\nCOLUMN RESOURCE_NAME FORMAT A30\nCOLUMN LIMIT FORMAT A20\n\nSELECT PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT \nFROM DBA_PROFILES \nWHERE PROFILE = 'DEFAULT';<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udca1 Tip:<\/strong> Regularly review profile limits for compliance with security standards.<\/em><\/p>\n\n\n\n
Create a Custom Profile<\/h3>\n\n\n\n
CREATE PROFILE TEST_PROFILE LIMIT\n  COMPOSITE_LIMIT UNLIMITED\n  SESSIONS_PER_USER UNLIMITED\n  CPU_PER_SESSION UNLIMITED\n  CPU_PER_CALL UNLIMITED\n  LOGICAL_READS_PER_SESSION UNLIMITED\n  LOGICAL_READS_PER_CALL UNLIMITED\n  IDLE_TIME 90\n  CONNECT_TIME UNLIMITED\n  PRIVATE_SGA UNLIMITED\n  FAILED_LOGIN_ATTEMPTS 10\n  PASSWORD_LIFE_TIME 180\n  PASSWORD_REUSE_TIME UNLIMITED\n  PASSWORD_REUSE_MAX UNLIMITED\n  PASSWORD_VERIFY_FUNCTION NULL\n  PASSWORD_LOCK_TIME UNLIMITED\n  PASSWORD_GRACE_TIME UNLIMITED;<\/code><\/code><\/pre>\n\n\n\n
Assign Profile to a User<\/h3>\n\n\n\n
ALTER USER W3BUDDY PROFILE TEST_PROFILE;<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udca1 Tip:<\/strong> Assign stricter profiles to production users and more relaxed ones to developers\/testers.<\/em><\/p>\n\n\n\n
7. System Privileges<\/h2>\n\n\n\n
System privileges control operations across the database.<\/p>\n\n\n\n
List All System Privileges<\/h3>\n\n\n\n
SELECT DISTINCT PRIVILEGE FROM DBA_SYS_PRIVS;\n<\/code><\/code><\/pre>\n\n\n\n
Grant System Privileges<\/h3>\n\n\n\n
GRANT CREATE ANY TABLE, ALTER ANY TABLE TO W3BUDDY;\n<\/code><\/code><\/pre>\n\n\n\n
Revoke System Privileges<\/h3>\n\n\n\n
REVOKE CREATE ANY TABLE FROM W3BUDDY;\n<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udca1 Tip:<\/strong> Avoid granting powerful system privileges directly to users. Use roles when possible.<\/em><\/p>\n\n\n\n
8. Object Privileges<\/h2>\n\n\n\n
Object privileges are specific to tables, views, procedures, etc.<\/p>\n\n\n\n
List Object Privileges<\/h3>\n\n\n\n
SELECT DISTINCT PRIVILEGE FROM DBA_TAB_PRIVS;\n<\/code><\/code><\/pre>\n\n\n\n
Grant Object Privileges<\/h3>\n\n\n\n
GRANT INSERT, UPDATE, DELETE ON APPUSER.TEST2 TO W3BUDDY;\nGRANT EXECUTE ON SIEBEL.DAILYPROC TO W3BUDDY;<\/code><\/code><\/pre>\n\n\n\n
Revoke Object Privileges<\/h3>\n\n\n\n
REVOKE UPDATE ON APPUSER.TEST2 FROM W3BUDDY;\n<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udca1 Tip:<\/strong> Always grant the least privilege necessary for the task.<\/em><\/p>\n\n\n\n
9. Role Management<\/h2>\n\n\n\n
Roles group privileges for easier management and auditing.<\/p>\n\n\n\n
Create a Role<\/h3>\n\n\n\n
CREATE ROLE TEST_ROLE;\n<\/code><\/code><\/pre>\n\n\n\n
Grant Privileges to a Role<\/h3>\n\n\n\n
GRANT CREATE SESSION TO TEST_ROLE;\nGRANT SELECT ANY TABLE TO TEST_ROLE;<\/code><\/code><\/pre>\n\n\n\n
Assign Role to a User<\/h3>\n\n\n\n
GRANT TEST_ROLE TO W3BUDDY;\n<\/code><\/code><\/pre>\n\n\n\n
View Roles Assigned to Users<\/h3>\n\n\n\n
SELECT GRANTEE, GRANTED_ROLE \nFROM DBA_ROLE_PRIVS \nWHERE GRANTED_ROLE = 'TEST_ROLE';<\/code><\/code><\/pre>\n\n\n\n
Drop a Role<\/h3>\n\n\n\n
DROP ROLE TEST_ROLE;<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udca1 Tip:<\/strong> Use roles for environment-based access (e.g., DEV_ROLE<\/code>, PROD_ROLE<\/code>) for scalability.<\/em><\/p>\n\n\n\n
10. Dropping a User<\/h2>\n\n\n\n
Drop User and All Objects<\/h3>\n\n\n\n
DROP USER W3BUDDY CASCADE;\n<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udca1 Tip:<\/strong> Be cautious with CASCADE<\/code> \u2014 it removes all schema objects owned by the user.<\/em><\/p>\n\n\n\n
11. Useful Queries for Auditing and Monitoring<\/h2>\n\n\n\n
List All Users and Their Status<\/h3>\n\n\n\n
SET LINESIZE 150\nCOLUMN ACCOUNT_STATUS FORMAT A20\nCOLUMN DEFAULT_TABLESPACE FORMAT A20\n\nSELECT USERNAME, ACCOUNT_STATUS, PROFILE, DEFAULT_TABLESPACE \nFROM DBA_USERS;<\/code><\/code><\/pre>\n\n\n\n
Check User\u2019s Privileges and Roles<\/h3>\n\n\n\n
-- System Privileges\nSELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'W3BUDDY';\n\n-- Object Privileges\nSELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'W3BUDDY';\n\n-- Roles\nSELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'W3BUDDY';<\/code><\/code><\/pre>\n\n\n\n
Best Practices for Oracle DBAs<\/h2>\n\n\n\n
    \n
  • Use roles<\/strong> instead of granting privileges directly to users.<\/li>\n\n\n\n
  • Assign custom profiles<\/strong> with appropriate password policies.<\/li>\n\n\n\n
  • Regularly audit user access<\/strong> and revoke unused privileges<\/strong>.<\/li>\n\n\n\n
  • Lock or drop inactive accounts<\/strong> to reduce attack surface.<\/li>\n<\/ul>\n\n\n\n
    Summary<\/h2>\n\n\n\n
    This guide provides a complete reference for Oracle user management, covering:<\/p>\n\n\n\n
      \n
    • User creation, password changes, tablespaces<\/li>\n\n\n\n
    • Profiles and resource control<\/li>\n\n\n\n
    • Privileges (system and object)<\/li>\n\n\n\n
    • Role-based access control<\/li>\n\n\n\n
    • Account and access audits<\/li>\n<\/ul>\n\n\n\n
      With these scripts and tips, you can manage Oracle users efficiently and securely in any environment.<\/p>\n","protected":false},"excerpt":{"rendered":"
      An Oracle user is an account that can connect to the database and perform operations. Managing users ensures proper access control, security, and organization of database activities. 1. Creating a New User Grant Minimum Privilege to Connect \ud83d\udca1 Tip: You may also grant roles like CONNECT, RESOURCE, or custom roles if the user needs access […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-3789","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3789","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3789"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3789"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}