Implementing Effective Auditing in Oracle RAC

Security and auditing in Oracle RAC (Real Application Clusters) are critical for ensuring data confidentiality, integrity, and regulatory compliance. In this chapter, we will cover essential security practices such as securing the RAC environment, configuring role-based access control, network security, auditing, and managing user access.

---

1. Securing the RAC Environment

Oracle RAC involves multiple components (nodes, databases, networks, etc.), so securing the entire environment is essential to prevent unauthorized access and data breaches. Key practices include securing file permissions, locking down unused services, and configuring proper authentication mechanisms.

• Securing Oracle RAC Files: Ensure that all Oracle-related files are protected with proper file system permissions to prevent unauthorized access.

Example Command:

chmod 750 $ORACLE_HOME
chown -R oracle:dba $ORACLE_HOME

This ensures that only the oracle user and the DBA group have access to the Oracle home directory.

Enforcing Strong Password Policies: Implement strong password policies to enforce complex passwords for all Oracle RAC users.

Example Command to Set Password Policy:

ALTER PROFILE DEFAULT LIMIT 
FAILED_LOGIN_ATTEMPTS 5 
PASSWORD_LIFE_TIME 90 
PASSWORD_REUSE_TIME 365 
PASSWORD_REUSE_MAX 10;

• This example limits failed login attempts to 5, forces a password change every 90 days, and enforces password reuse policies.

---

2. Implementing Role-Based Access Control (RBAC)

Role-Based Access Control (RBAC) is a security mechanism that restricts user access based on their role within the system. Oracle RAC allows administrators to implement roles to limit privileges and prevent unauthorized actions.

• Creating Roles in Oracle RAC: Define roles for different user groups to ensure appropriate access control. For example, create roles for DBA, application users, and auditors.
• Example Command to Create a DBA Role:

CREATE ROLE dba_role;
GRANT CREATE SESSION, ALTER DATABASE, ALTER SYSTEM TO dba_role;

This grants the dba_role to the user “alice,” giving her specific privileges without directly granting high-level system privileges.

Revoking Privileges from Roles: If a user no longer needs certain privileges, you can revoke them without affecting other users with the same role.

Example Command:

REVOKE ALTER SYSTEM FROM dba_role;

3. Configuring Network Security and Interconnect Encryption

Network security is critical for Oracle RAC environments, especially considering the communication between RAC nodes via the private interconnect. Encrypting the communication between nodes ensures that sensitive data, such as inter-node Cache Fusion traffic, is protected from unauthorized interception.

• Securing the Public Network: Ensure that the public network used by the RAC environment is secured with appropriate firewalls, and only authorized IPs can access the network.Configuring Oracle Network Firewall:
  • Ensure that the RAC nodes’ public and private IP addresses are restricted at the firewall level.
  • Use Oracle Net Manager to configure encrypted connections using SSL/TLS for secure public network communication.
  Example of SQLNet.ora Configuration: Add the following to the sqlnet.ora file to enable SSL/TLS:

SSL_VERSION = 1.2
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)

Configuring Interconnect Encryption: The interconnect between RAC nodes can also be encrypted to protect Cache Fusion traffic. This prevents unauthorized access to sensitive inter-node communication.

Example Command to Enable Interconnect Encryption:

ALTER SYSTEM SET CLUSTER_INTERCONNECTS_ENCRYPTION = 'AES256' SCOPE=BOTH;

• This command enables AES-256 encryption for traffic on the private interconnect between RAC nodes.

---

4. Auditing in Oracle RAC: Ensuring Compliance and Security Monitoring

Auditing is a crucial part of maintaining security and ensuring compliance in Oracle RAC. It allows you to track user activities, database access, and configuration changes, helping to detect and prevent unauthorized actions.

• Enabling Unified Auditing: Oracle’s Unified Auditing allows administrators to audit a wide range of activities, including SQL queries, DML operations, and login/logout attempts.
• Example Command to Enable Unified Auditing:

AUDIT POLICY ORA_SECURECONFIG;

The ORA_SECURECONFIG policy enables auditing of sensitive operations, such as changes to the database configuration and access to critical data.

Viewing Audit Logs: Audit logs can be queried using the DBA_AUDIT_TRAIL view.

Example Query to View Audits:

SELECT username, action_name, timestamp 
FROM dba_audit_trail
WHERE timestamp > SYSDATE - 1;

This query retrieves all actions performed in the last 24 hours.

Auditing Specific User Actions: You can configure auditing for specific user actions, such as when users access sensitive tables.

Example Command to Audit SELECT on a Table:

AUDIT SELECT ON hr.employees BY alice BY SESSION;

• This audits any SELECT queries on the employees table made by the user “alice”.

---

5. Managing User Access in a RAC Environment

Managing user access in Oracle RAC is crucial for ensuring that only authorized users can perform certain actions. User access can be controlled through profiles, roles, and system privileges.

• Creating a New User in Oracle RAC: Create a new user for the RAC environment with specific privileges.
• Example Command to Create a User:

CREATE USER john IDENTIFIED BY password123;
GRANT CREATE SESSION TO john;

This command creates a new user “john” and grants him permission to log into the database.

Assigning Privileges to Users: Assign privileges to users based on their roles and responsibilities.

Example Command to Grant Privileges:

GRANT CONNECT, RESOURCE TO john;

This grants the user john the ability to connect to the database and create resources like tables and indexes.

Revoking Privileges from Users: If a user no longer requires certain privileges, you can revoke them.

Example Command to Revoke Privileges:

REVOKE CONNECT FROM john;

This revokes the CONNECT privilege from the user “john.”

Managing Users with srvctl in RAC: RAC environments can use srvctl to manage database services and users across multiple nodes.

Example of Adding a User to an RAC Service:

srvctl add service -db racdb -service hr_service -pdb hr_pdb -role PRIMARY

Conclusion

Oracle RAC security and auditing ensure that sensitive data is protected and that unauthorized actions are prevented. By implementing role-based access control (RBAC), encrypting network communication, and auditing user activities, administrators can safeguard their RAC environments from internal and external threats. Additionally, managing user access with granular control enhances security across the RAC cluster, ensuring that only authorized users can perform specific actions.