Oracle Database - SQL Research Paper
Oracle Database - Data Management, Data Security, Data Recovery
Abstract
A database is a set of physical and
logical data files that are accessed by database management software (Oracle).While
the global use of both central and distributed databases systems has shown the
importance of databases in supporting large business operations, it has also produced
a vital data security issue. The need occurred to shield facts and information
from intrusions, modifications, robbery and unauthorized disclosure. The
problems of database system security are more and more important now a days. There
are many paradigms to database management software. These elements of operability
consist of data safety, data confidentiality, accuracy, data integrity and
authenticity, round the clock availability and data recoverability.[1]
Objectives
The goals in data security, data management
and database recovery is to co-relate, how well the system possesses security,
the relationship between the security mechanisms featured by the operating system
and those provided by the database system, and the infrastructure of a secure
Database Management System.
Risks
As more and more companies are
opting e-modification, the goal of securely storing, managing, processing, and sharing
digital data continue to increase. With the average cost of a database breach comprising
$14M, direct financial losses in term of data can be disastrous for many companies,
and we are not even calculating the reputation lost in term of cost. Giant
“mega-breaches” that publically expose trillion to bytes of secret data records
can easily drive these rates up to zillions of dollars.
Conclusion
Oracle data components, system based
files, database objects, data dictionary, and other database security system
components that are necessary from a security point. The fundamental methods
for developing a stable reliable Oracle database, including forming a database security
plan, an audit plan, and a plan for backing up and restoring the database
system. We are analyzing the findings of this study that were adopted by our
company and applied in current Oracle database system.
Role of a Database Administrator
The Database Administrator (DBA) is
in charge of the database's conceptual schemas, logical schemas, and internal
schemas. All queries are managed by the stable DBMS module. Authorization rules
and security axioms are included [2].For discretionary controls,
authorization rules are used, and for mandatory controls, security axioms are
used, as shown below:
Security Problems in Oracle
Risks/threats to a database
protection include improper accesses, modification or deletions of information [3].The
access breaches may be categorized as following:
• Improper launch of records: Unauthorized
data publishing plan can allow network traffic to access the data and mishandle
its constraints.
• Improper
access to information:
Violation of data integrity by illegal user
access. Data authenticity is lost if illegal users are allowed to perform crud
on information.
• Denial of service: These create
a denial r blockage of service and keeping users from accessing the data. This
is caused by network level numerous hits, that choke the bandwidth and data is
unavailable.
• Natural disasters. The natural
calamities such as winds, storm, floods, quakes or physical accidents can cause
the loss of hardware and software devices.
• Errors
or programs in database software program: This may also instigate the illegal get right of data, studying
or changing of data, or the denial of get right of entry to the allowed users.
This motives a few high level trouble which cause loss to time and resources.
• Human
errors: This comprises accidental breaches
including the error expertise or wrong use of database causing failed software
protection policies. Fraudulent or mischievous threats are the disasters due to
it.
• Hostile
Hackers, incorrect users (out of doors and
inside) access into device and running harmful software program and/or device
hardware, improperly getting access into or transforming information. For
example, Spam viruses, Trojan Horses files and trapdoor programs are attacks by
adverse marketers.
• Authorized
users who can misuse their rights,
privileges and grants to generate a potential breach.
Adding Security in Oracle System
In Oracle database system, users and
groups query the tables. Oracle system grants the CREATE USER query for
creating the users, ALTER USER query for altering the users, and DROP
USER query for deleting the users present in the system. By the IDENTIFIED
BY clause of the corresponding SQL command, the admin can choose between credential-based
and operating-system-based assignment of users.
Using the ADMIN OPTION
command, a database administrator may assign system rights to database user who
can grant such rights of access to other users. In Oracle, a user role can be allowed
to other roles, making a structural organization of privileges/roles. This is
similar to the organization having user groups. The SET ROLE query is
used to assign a role to an application, which is enabled through user
password. Users have pre-set roles which are active at their login time. Three oracle
featured privileges are defined as Connect, Resource, and DBA.
The DBA privilege is the very
resourceful rights of the database that has all privileges comprising of making
consumer debts, defining roles, granting user roles, granting access rights,
etc. Sys, System and Public are 3 unique types which may
be installed in the Oracle database system. Sys and System have
the administrator / dba privilege. Public relations is to the primary
organization of Oracle, and all of the privileges which can be assigned to
Public are automatically granted to all different consumer through a regular client-server
database architecture. Design diagram with a PC (client) and (Database / Oracle
server) are as follows:
Developing an Oracle Data Security
Plan
There are many types of Oracle
users, Database Administrator dba and db Security manager who have rights for making
user accounts and observing user access and the database access level security,
database application manager who is tasked for handling the administrative
tasks for the oracle database in which their application is deployed, network
manager NOC who is responsible for handling the network products and nomenclature
of Servers and network configurations
for Oracle databases, application level schema owner who is the owner of a particular
oracle database and may need more rights than a normal user, and the normal
user whose rights are defined within the oracle system application.
Identify the username and password in
the oracle database system that includes
password expiration, password reuse, logging failed login attempts, tracking account
locking and unlocking by the dba.
Credentials Changing and Expiration to help to ensure that a password will not be compromised, the credentials
needs to be changed on a system at least every three months (recommended). The
longer a password remains in effect for an account, the higher the possibility
that the credentials can be leaked.
User Password reuse is recommended to exclude a user password that has been already
used from being used by that person repeatedly.
Failed login attempts that will be tolerated in a system should be determined.
Account locking and unlocking decision might be made to never enable account locking or never
enable automatic account unlocking. If account locking is going to be enabled,
we need to define the personnel who will be in charge of performing the account
unlocking.
Determine who has the admin
authority to approve the user accounts and
who is authorized for creating/dropping/managing CRUD based queries through accounts.
Determine a user tracking system and implementation.
Establish the standards and
structures for the roles, privileges, views,
triggers, etc.
Define a security breach and the proper penalty for each security breach.
Categorize all sensitive
information/data on the database
system and define the methods/steps to protect these data.
Find the types of monitoring to be used.
Determine the kinds of backup to the deployed and used for timely storage.
Developing an Oracle Audit Plan
Database auditing is the
administrative monitoring and logging of user activities taking place within a database. [4] The audit
helps to:
• Make sure that no unauthorized
users can play with the tables to which they do not have the access to access;
• Make sure that no restricted users
can delete data from the data dictionary that they do not have the rights to
see;
• Audit the specific tables
that help to determine the volume of access occurring at peak times.
The audit includes the Security
Auditing and the Performance Auditing. The Security Auditing is to
determine if someone is attempting to break into the database system. When some
problem has occurred in the system, this audit helps to determine the reasons.
For example, we found that some information may have been removed from a table
in which the data logged should not be truncated. The audit may be used to keep
track of the delete queries in that specific table. The Performance Auditing is
used to find the causes why the system is relatively slower. This audit helps
to determine the volume of user traffic interacting with the specific areas of
the database. The audit trail can be developed either directly to an operating
system file or to the database management system. Two oracle database parameters
in the INIT. ORA file control the auditing actions:
• AUDIT_FILE_DEST: This parameter
tells Oracle the directory name in which the audit trail is to be written to.
The default value for this parameter is
$ORACLE_HOME/RDBMS/A UDIT.
• AUDIT_TRAIL: This parameter hints
Oracle to allow or un-allow auditing. If the feature is set to be NONE, this stands
for non-default auditing to occur. If the system parameter that is set to be Operating
system, this implies that system-wide audit may perform and the outputs will be
stored to a flat file in the folder which is marked through the AUDIT FILE DEST
parameter. If the key kpi that is set to be DB, this stands for the system-wide
auditing to be turned on and the output may be written to a table SYS.A UD$ in
the SYS schema in user readable form. The standard auditing features in oracle
may be accessed using the following
DBA_AUDITIONJECT, DBA AUDIT SESSION,
DBA_AUDIT STATENMENT,
DBA AUDITJTRAIL, DBA_AUD1T ONJECT,
AUDIT ACTIONS, etc.
There are three different types of
auditing in the Oracle database system which are statement-level auditing,
system-level or privilege auditing, and object-level auditing.
• Statement-level auditing:
This auditing comprises the checking for Data Definition
Language statements and the audit of
Data Manipulation Language statements.
For example, a statement-level audit
can audit any action performed on the tables, such as CREATE TABLE, ALTER
TABLE, DROP TABLE, TRUNCATE TABLE, etc.
If we wanted to capture number of
wrong logon attempts that were carried, then oracle gives dba special insight
through the following
SQL> AUDIT CONNECT WHENEVER
UNSUCCESSFUL;
The summary table for audit periods
might comprise of a count, by 24-hour or 12-hour time period. The hacker’s login
attempt with brute force having wrong credentials is counted and maintained as
failed login attempts. A high than usual number of failed attempts in the user
login summary table might show that hacker was trying to login into the
database by guessing account credentials. Through this auditing, we will be
able to identify a suspicious users login attempts to gain the access to the oracle
system.
• Privilege auditing: We can
enable the auditing on specific privileges via the AUDIT
Command. The observer of who has
which privileges is a manual or automated set of operation which is performed
through SQL code, for example:
SELECT grantee, privilege FROM dba_sys_privs
WHERE GENTEE NOT IN ('SYS ", ‘SYSTEM’, ‘RECOVERY_CATALOG_OWNER’,TMP_FULL_DA
TABASE ‘EXP_FULL_DATABASE', 'DBA', ‘CONNECT’,‘RESOURCE');
Using this auditing, we can see who
has been issue the specific rights on the database. The hacker will have a larger
amount of time to build ‘hidden’ rights to the database tables and system.
• Object-level auditing: By
this kind of auditing, we can take track of all the crud operations performed.
A timestamp, user name and history of operation performed wither insert, update
or delete on any table is kept so that a security check may be maintained on
the integrity of data.
Log files must be minimal and only
for specific operational tables, as the data grows such file size also grows.
No one will be able to foresee into such huge log files. Thus proper truncate
policy must be implemented to check the file size. The summary tables must also
be maintained by a dba to make sure the detailed logs are deleted and not kept
creating an overheard [25]:
• We enable removal of information
from the underlying SYS.AUD$ audit table to conserve storage space.
• Audit trial information is used as
below. The audit trial information is auto created by the oracle and it cannot
be re-created, edited or deleted by an user when an administrator
AUDIT delete ON sys.aud BY ACCESS
To make sure the audit trial is kept
safe and always available and no one can mistakenly delete it, a dba should run
following:
DELETE CATALOG ROLE
4.3.3 Backing Up and Recovering the
Database
Availability is a key resource of
any company. All the data must be kept safe and regularly backed up to ensure
smooth working for the users of a database. Oracle is becoming a disaster free
database due to its unique logging and storage features that enables dba to
search and rescue data at any point in time. [5].
Backing Up the Database System
There are several different kinds of
backups that are taken to make sure data is kept safe and may be restored as
and when needed:
Cold database backups: In this scenario all the database connections are dropped,
database is usually offline for the connections and the dba performs backup. All
log files, init.ora, control files redo logs, backup files and connection files
are backed up on separate location.
Then a backup manager or utility is
run on selective database tables to perform a backup on separate location
either on same server or on a media device that is detachable. Database is shut
down and then restarted. This activity is carried usually at night times when
the traffic is minimum and loss of service is bearable by the users. If a
disaster is seen then the dba recovers the old backup and log files so that
database is reset and then the cause of trouble is analyzed to ensure smooth
working of the system.
Hot database backups: Hot backup and recovery means that a system is live and running
in the production. Connections to the tables are made and users are performing
live CRUD actions on the database. Such cases have backups stored on separated
location as of cold backups and then restored using the following commands:
ALTER TABLESPACE <tablespace_name> BEGIN BACKUP
Logical database backups: Logical database backup means database must be up and running.
All the tables, schemas and meta-data are stored in logical format and exported
in files or chunks for later recovery. Data export is made from time to time
and we can restore any data only up to the latest import performed through
historical backlogging. Export files are large for big database and can be
mobilized from one system and recovered in another system with all the states
that are working. Off-site tape or file storage needs a database to be
restarted and all the connections are to be reset. This allows the last point
in time recovery and data is accessible through proper backup and timely action
plan.
Recovering the Database System
There are several many ways for
database recovery. Generally, there are two approaches:[5]
• Recovery from export is a
technique to export all the data and table designs in other format files. These
files can be mobilized and restored at any new system.
• Recovery from the file data
files related to oracle database are stored in file format and restored
whenever data is corrupted or unavailable.
More specifically, there are three
basic types of recovery - online block recovery, thread recovery, and media
recovery.
• Block-level recovery: In
case Oracle discovers any query or table locked, it automatically activates the
block level recovery. All the blocks are dropped, connections are terminated
and data is made accessible to all the users of a schema.
• Thread recovery: Thread
recovery, restores data in short thread form. It reinstates all the database
state to a point in time where it was working fine. For this recovery method,
proper backup facility needs to be ensured at regular time intervals,
• Media recovery: Media
recovery is done by using any third party external media that has source file
of Database. Any database where the connection is stuck and there is no way of
letting a user into the database file, this technique is used.
• Database recovery: All the
database backup is restored and recovered with all database environment.
• Table space recovery: Media
recovery for specific tables can be made using the table recovery space
commands.
• Data file recovery: Specific
data file recovery is also made possible using the particular table select
recovery procedure.
The database recovery can be carried
out by any online of offline data recovery tool or by using the command line
query interface using TOAD or any Oracle SQL optimizer.
The recovery commands are mentioned
as below
·
RECOVER TABLE SPACE
·
RECOVER
DATAFILE.
From the external database we can
use the imports of schema, tables, meta-data and data itself. The import comprises
of full and schema database mode level. This is defined according to the
administrator policy that is set at the time of building a structure.
For an offline recovery with the database closed, we can do OFFLINE DROP, it will drop all
the current locks and start recovering the data that was working and perfectly
healthy in point of time where system was working fine.
References
[1] https://blogs.oracle.com/platformleader/oracle-autonomous-database-helps-eliminate-costly-data-breaches-v2
[2] https://blogs.oracle.com/security/
[3] https://www.oracle.com/in/database/technologies/security.html
[4] https://docs.oracle.com/cd/E19683-01/817-0365/auditplan-1/index.html
[5] https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcomre.htm
Further References:
https://www2.deloitte.com/global/en/pages/technology/articles/oracle-tech-trends-2020.html
Comments
Post a Comment