Oracle Database Architecture and User Security

Architecture Oracle Database

• An Oracle database consists of an Oracle instance and a database.
• The Oracle instance includes memory structures and background processes.
• The database includes data files, redo log files, and a control file.
• The System Global Area (SGA) and the Program Global Area (PGA) are memory structures.
• The SGA is shared memory that stores data and code for the Oracle instance.
• The PGA is private memory that stores data and code for a server process.
• Background processes perform tasks such as writing data to disk.
• Data files store data in the database; redo log files record database changes.
• The control file contains metadata about the database.

User Security

• User security in Oracle is managed via user accounts, roles, and privileges.
• A user account consists of a username and password for database login.
• A role is a group of privileges that can be granted to users or other roles.
• A privilege is the right to perform a specific database action.

User Creation

• Users are created using the CREATE USER command.
• The basic syntax is CREATE USER username IDENTIFIED BY password;
• Strong passwords are vital, options like ACCOUNT LOCK can lock accounts.

Privilege Assignment

• System privileges allow general database operations.
• Object privileges allow actions on specific objects like tables.
• Privileges are granted using the GRANT command.
• GRANT CREATE SESSION TO username; allows the user to connect.
• GRANT SELECT ON table_name TO username; allows the user to read data.
• WITH GRANT OPTION enables the user to grant privileges to others.

Roles

• Roles simplify privilege management for multiple users.
• Predefined roles include CONNECT, RESOURCE, and DBA.
• Custom roles can be created with CREATE ROLE.
• Privileges are granted to roles with GRANT.
• Roles are assigned to users with GRANT role_name TO username; and revoked with REVOKE role_name FROM username;

Profiles

• Profiles limit the resources used by a user, such as CPU, connection time, and disk space.
• Profiles are created with CREATE PROFILE and assigned during user creation or modification.
• CREATE PROFILE limit_profile LIMIT CONNECT_TIME 60 IDLE_TIME 30; creates a profile that limits connection and idle times
• Assign a profile to user: ALTER USER username PROFILE limit_profile;

Authentication

• Password authentication is the most common method.
• Kerberos and other external authentication methods can be configured.
• Centralized authentication manages users and passwords in one location.

Audit

• Auditing tracks actions performed in the database.
• Auditing is configured with the AUDIT command, and audit information is stored in audit tables.
• Logs can be analyzed to detect suspicious activity.

Network Security

• The Oracle Listener process listens for incoming connections.
• Securing the Listener is important for preventing attacks by using TLS to encrypt communications.
• Firewall rules are essential for limiting database access.

Data security

• Data encryption protects sensitive information.
• Oracle offers encryption options like Transparent Data Encryption (TDE).
• Data masking hides sensitive information in development/test environments.
• Virtual Private Database (VPD) defines access rules based on context.

Password Management

• Complex password policies are a security best practice.
• Password requirements encompass length, complexity, and history.
• Passwords should be changed regularly; use ALTER USER username PASSWORD EXPIRE; to force a password change upon next login.

User Deletion

• Users are deleted with the command DROP USER username;
• CASCADE is needed to drop any objects the user owns, like this: DROP USER username CASCADE;

Key SQL Commands

• CREATE USER: Creates a new database user.
• ALTER USER: Modifies a user account.
• DROP USER: Removes a user from the database.
• GRANT: Grants system or object privileges to a user or role.
• REVOKE: Revokes privileges.
• CREATE ROLE: Creates a new role.
• ALTER ROLE: Modifies a role.
• DROP ROLE: Deletes a role.
• CREATE PROFILE: Creates a profile to limit user resources.
• ALTER PROFILE: Modifies an existing profile.
• DROP PROFILE: Deletes a profile.
• ALTER USER: Assigns a profile to a user.

Advanced Auditing

• Standard auditing can be combined with Fine Grained Auditing (FGA).

Continuous Monitoring

• Oracle Enterprise Manager (OEM) monitors user activity.

Application Security

• Applications should connect to the database using accounts with limited privileges and avoid embedding database credentials directly in application code.