Oracle User and Security Management

ADVERTISEMENT

Managing users and security is vital to protect your Oracle database. This post explains key topics like users, roles, privileges, profiles, authentication, and auditing to help DBAs keep the database safe and well-managed.

1. What is the difference between a user and a schema in Oracle?

  • A user is an account that can connect to the database.
  • A schema is a collection of database objects (tables, views, etc.) that belong to a user. Every user owns a schema with the same name.

2. How do you create a new user in Oracle? What are the key clauses used?

  • Use the CREATE USER statement with clauses like IDENTIFIED BY for password, DEFAULT TABLESPACE for storage, and TEMPORARY TABLESPACE.
    Example:
CREATE USER hr IDENTIFIED BY hr_password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

3. What is a role in Oracle and why is it used?

  • A role is a named group of privileges. Instead of granting privileges to users individually, you grant them to roles, and then assign roles to users. It simplifies privilege management.

4. How do you grant and revoke privileges in Oracle? Explain system vs object privileges.

  • Use GRANT to give privileges and REVOKE to remove them.
  • System privileges allow actions like creating tables or users (CREATE TABLE).
  • Object privileges control access to specific objects like tables (SELECT, INSERT).

5. What are system privileges and object privileges? Give examples.

  • System privileges: CREATE USER, ALTER DATABASE.
  • Object privileges: SELECT on a table, EXECUTE on a procedure.

6. How can you check which privileges a user or role has?

  • Query dictionary views like DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS.

7. What is a profile in Oracle? How is it used for security?

  • A profile sets limits on user resources and password rules (like password expiration, failed login attempts). It helps enforce security policies.

8. How do you assign a profile to a user?

  • While creating or altering a user, use the PROFILE clause:
ALTER USER hr PROFILE secure_profile;

9. What are password complexity and expiration policies? How do you enforce them?

  • Password complexity rules (like length, special characters) and expiration (how often password must be changed) are set in profiles and enforced by password verify functions.

10. How do you lock and unlock a user account?

  • Lock: ALTER USER hr ACCOUNT LOCK;
  • Unlock: ALTER USER hr ACCOUNT UNLOCK;

11. What is the difference between authentication and authorization?

  • Authentication verifies who you are (login).
  • Authorization determines what you can do after logging in.

12. What is the difference between local and global users in Oracle?

  • Local users are defined in the database.
  • Global users are managed externally (like LDAP or Active Directory) and use external authentication.

13. How can you audit user activities in Oracle?

  • Enable auditing via AUDIT statements or use Oracle Unified Auditing to track actions like logins, DML, and DDL operations.

14. What is Oracle Database Vault? How does it enhance security?

  • Database Vault adds extra controls to restrict access, enforce separation of duties, and protect sensitive data beyond normal privileges.

15. What is Transparent Data Encryption (TDE)? How is it configured?

  • TDE encrypts data at rest (in datafiles) automatically, protecting sensitive data without changing application code. It’s configured at tablespace or column level.

16. What is the difference between authentication methods like password, OS authentication, and proxy authentication?

  • Password authentication: Users provide username and password.
  • OS authentication: Users are authenticated by the operating system.
  • Proxy authentication: One user connects on behalf of another.

17. How do you manage user sessions and resources in Oracle?

  • Use profiles to set limits on CPU time, sessions, idle time, and concurrent connections.

18. What are common security best practices for Oracle users and privileges?

  • Use least privilege principle, assign roles instead of direct privileges, regularly audit accounts, enforce strong password policies, and lock inactive accounts.

19. How do you create and manage roles in Oracle? How can you assign roles to users?

  • Create role: CREATE ROLE analyst;
  • Grant privileges to role: GRANT SELECT ON sales TO analyst;
  • Assign role: GRANT analyst TO hr;

20. What is the principle of least privilege? How do you implement it in Oracle?

  • Give users only the minimum privileges needed to perform their tasks. Implement by carefully granting privileges and using roles.

21. How do you revoke privileges that have been granted through roles?

  • Revoke from roles: REVOKE SELECT ON sales FROM analyst;
  • To revoke a role from a user: REVOKE analyst FROM hr;

22. What is the use of the DBA_USERS and DBA_ROLE_PRIVS views?

  • DBA_USERS shows info about all users.
  • DBA_ROLE_PRIVS shows roles granted to users and other roles.

23. How do you enable and configure Oracle auditing?

  • Use AUDIT commands for specific actions or configure Unified Auditing policies for more granular control.

24. How can you track failed login attempts?

  • Check DBA_AUDIT_SESSION or DBA_FGA_AUDIT_TRAIL views or use profiles to lock accounts after failed attempts.

25. What is the difference between explicit and implicit privileges?

  • Explicit privileges are directly granted to users or roles.
  • Implicit privileges are automatically granted by the system, e.g., privileges to access objects in your own schema.

26. How can you prevent privilege escalation in Oracle?

  • Avoid granting powerful system privileges directly, use roles, monitor privilege grants, and enforce separation of duties.

27. What are common causes and fixes for user account locking?

  • Causes: multiple failed login attempts or manual locks.
  • Fix: unlock the account and reset password if needed.

28. How do you handle expired passwords for users?

  • Users must change passwords at next login. DBAs can reset passwords using ALTER USER.

29. What are some tools or commands used by DBAs to manage users and security?

  • SQL*Plus, Enterprise Manager, CREATE/ALTER USER, GRANT/REVOKE, audit utilities.

30. How do you use Oracle Enterprise User Security (EUS)?

  • EUS integrates Oracle users with enterprise directories like LDAP, centralizing authentication and simplifying user management.

ADVERTISEMENT