Question

Consider you are working as a Database Administrator (DBA) in a certain large organization. Protecting data is one of your major tasks in the day-to-day operations. As a DBA, discuss various steps you would take to ensure proper planning of the database instance redo logs. Your solution must include the relevant SQL commands and screenshots.

Answer 1

DBAs play an increasingly crucial role in security. The consequences of not safeguarding data or failing to comply with regulations for data security can include significant fines and jeopardize business operations and the reputation of your organization.

As a Database Administrator (DBA) maintaining Database security is very very important role and It is especially critical for organizations that rely heavily on their information systems and the databases forming the back-end for those systems. Examples are banks, insurance companies, hospitals, colleges and universities, telecommunication companies and many others. In most smaller organizations, the DBA also doubles up as a systems administrator because of resource constraints. Larger organizations are more likely to utilize dedicated DBAs, or even teams of DBAs.

Database security is generally planned, implemented and maintained by a database administrator and or other information security professional.

Database security refers to the collective measures used to protect and secure a database or database management software from illegitimate use and malicious threats and attacks.It is a broad term that includes a multitude of processes, tools and methodologies that ensure security within a database environment.

Some of the ways database security is analyzed and implemented include:

Most organizations, whether public or private sector, are facing exponential increases in the amount of information and data that they need to continuously track, manage, and protect to ensure organizational success, continuity of operations and long-term viability.

While many attackers still focus on denial of service attacks, cybercriminals often target the database because that is where sensitive information resides that would interest someone looking to steal credit card information or personal identities. With so much at potential risk, those involved with responsibility and control over the resources required to secure the databases must assume the role of stewards of the data and ensure that business operations are not threatened.

Here are a few best practices that can assist all organizations, regardless of industry or size, to secure their databases to make potential attackers move on to an easier target:

1. SEPARATE THE DATABASE AND WEB SERVERS

Always keep the database server separate from the web server.

Most vendors try to make things easier by having the database created on the same server that the application is installed. This also makes it easier for an attacker to access the data because they only need to crack the administrator account for one server to have access to everything.

Recommendation: Install the database on a separate database server located behind a firewall, not in the DMZ with the web server. While this makes for a more complicated setup, the security benefits outweigh the additional technical efforts required.

2. ENCRYPT STORED FILES AND BACKUPS

The stored files of a web application often contain information about the databases that the software needs to connect to. This information, if stored in plain text like many default installations do, provide the keys an attacker needs to access sensitive data.

Not all data theft or destructions happen as a result of an outside attack. Sometimes employees who were once trusted can be compelled to steal or destroy data as well. In addition, data that contains regulated information (HIPAA, SOX, DoD, etc.) must be encrypted if the storage media is ever out of your security authority.

Recommendation: Encrypt any files that have value to the organization and are stored on the application or database server. If they have value to your organization, they are of value to an attacker.

3. USE A WEB APPLICATION FIREWALL (WAF)

Many people are under the misconception that protecting the web server has nothing to do with the database. This is not true. In addition to protecting a site against cross-site scripting vulnerabilities and website vandalism, a good application firewall can thwart SQL injection attacks as well. By preventing the injection of SQL queries by an attacker, the firewall can help keep sensitive information stored in the database away from attackers.

Recommendation: Employ web application firewalls.

All web applications are available to customers/constituents—as well as attackers—24x7x365. For this reason, traditional IT security systems, such as firewalls or IDS/IPS, may be unable to guard against these attacks or do not offer comprehensive protection.

4. KEEP PATCHES CURRENT

Websites that utilize third-party applications, components, and various other plug-ins and add-ons are more susceptible to an exploit than those that have been patched.

Recommendation: Keep patches current to the most recent release.

5. ENABLE SECURITY CONTROLS

Though most databases enable security controls by default, administrators should always check the security controls to ensure that this is the case. It is important to remember that though most organizations may rely on a web developer to create a secure system, the DBA is ultimately responsible for ensuring that security is maintained once development and the implementation are complete.

Recommendation: Enable security controls on all databases and do not assume that this is the default. Ensure that there are corresponding business processes in place.

What is the Redo Log?

Redo Logs consist of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has associated online redo logs to protect the database in case of an instance failure.

Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

The Oracle Database uses only one redo log file at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file. Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.
If you have enabled archiving (the database is in ARCHIVELOG mode), then the database cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived the file. If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.

How to create Redo Log Groups?

To create a new group of redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause. The following statement adds a new group of redo logs to the database:

ALTER DATABASE
ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 100M;

You can also specify the number that identifies the group using the GROUP clause, e.g.:

ALTER DATABASE 
ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')
SIZE 100M BLOCKSIZE 512;

When using the ALTER DATABASE statement, you can alternatively identify the target group by specifying all of the other members of the group in the TO clause, as shown in the following example:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo'
TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

How to clear a Redo Log file?

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database. Example:

ALTER DATABASE CLEAR LOGFILE GROUP [grp#];

This statement overcomes two situations where dropping redo logs is not possible:

• If there are only two log groups
• The corrupt redo log file belongs to the current group

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE [grp#];

This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.

NOTE: If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. The database writes a message in the alert log describing the backups from which you cannot recover.

What are the Redo Log Data Dictionary Views?

The following views provide information on redo logs.

View	Description
V$LOG	Displays the redo log file information from the control file
V$LOGFILE	Identifies redo log groups and members and member status
V$LOG_HISTORY	Contains log history information

What is the Archived Redo log?

The Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.

An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group.