Oracle 19c Applying the Principle of Least Privilege

Applying the Principle of Least Privilege in Oracle 19c is essential for maintaining robust database security. By granting users the minimum levels of access necessary for their roles, database administrators can mitigate security risks and ensure a more secure database environment. This guide will provide a comprehensive walkthrough on how to implement Least Privilege and manage Privs Management effectively.

Understanding the Principle of Least Privilege

The Principle of Least Privs is a security concept that entails granting users only the permissions they need to perform their tasks, and no more. This minimizes potential security vulnerabilities by reducing the attack surface and limiting the potential damage from compromised accounts.

Benefits of Applying Least Privilege

1. Enhanced Security: Reduces the risk of unauthorized access and potential security breaches.
2. Controlled Access: Ensures users have access only to the data and resources they need.
3. Regulatory Compliance: Helps in meeting compliance requirements by enforcing strict access controls.

Steps to Apply Least Privilege

Step 1: Assess User Roles and Privs

Before implementing the Principle of Least Privilege, assess the current roles and permissions assigned to users. Identify any excessive privs and unnecessary access rights.

Assess User Roles:

SQL> SELECT USERNAME, GRANTED_ROLE FROM DBA_ROLE_PRIVS;

Assess System Privs:

SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS;

Step 2: Define and Create Roles

Least Privilege – Define roles based on the tasks and responsibilities of different user groups. Create roles with the minimum privs required for their functions.

Create Roles:

SQL> CREATE ROLE read_only;
SQL> CREATE ROLE dba;

Step 3: Assign Privs to Roles

Grant the necessary permissions to the roles created in the previous step. Ensure that each role has only the privs needed for its intended purpose.

Grant Privs to Roles:

SQL> GRANT SELECT ON schema.table TO read_only;
SQL> GRANT CREATE SESSION TO read_only;

SQL> GRANT ALL PRIVILEGES TO dba;

Step 4: Assign Roles to Users

Assign the appropriate roles to users based on their job functions. Regularly review and update these assignments to reflect any changes in responsibilities.

Assign Roles to Users:

SQL> GRANT read_only TO user1;
SQL> GRANT dba TO admin_user;

📢 You might also like: Oracle 19c Creating and Assigning Profiles (Category: Oracle Database Admin)

Managing Privilege Management

Effective Privilege Management involves continuous monitoring and auditing of user permissions to ensure compliance with the Principle of Least Privilege.

Monitor User Privs:

Regularly monitor user privs to detect and address any deviations from the principle.

Monitoring User Privs:

SQL> SELECT * FROM DBA_USERS_WITH_DEFPWD; -- Identifies users with default passwords
SQL> SELECT * FROM DBA_TAB_PRIVS; -- Shows object privs granted to users
SQL> SELECT * FROM DBA_SYS_PRIVS; -- Lists system privs granted to users

Audit Privs:

Implement auditing to track priv usage and identify potential security issues.

Enable Auditing:

SQL> AUDIT ALL BY user1 BY ACCESS;
SQL> AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE BY read_only;

Implementing Fine-Grained Access Control

Least Privilege – For enhanced security, implement Fine-Grained Access Control (FGAC) to enforce row-level security policies.

Create Policy Function:

CREATE OR REPLACE FUNCTION secure_view (schema_name IN VARCHAR2, table_name IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
  RETURN 'department_id = ' || SYS_CONTEXT('USERENV', 'SESSION_USER');
END secure_view;

Create Policy:

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema => 'hr',
    object_name => 'employees',
    policy_name => 'emp_policy',
    function_schema => 'hr',
    policy_function => 'secure_view'
  );
END;

Conclusion

Applying the Principle of Least Privilege in Oracle 19c is a fundamental practice for maintaining a secure and compliant database environment. By following the steps outlined in this guide, DBAs can effectively implement this principle and manage Privilege Management, ensuring that users have only the access they need and reducing the risk of security breaches. Furthermore, regular monitoring and auditing help maintain compliance with the least privilege approach.

Start leveraging this approach in your Oracle environment today to enhance your database security and ensure robust Privilege Management.

See more on Oracle’s website!

Be Oracle Database Certified Professional, this world is full of opportunities for qualified DBAs!