 |
| Database Security and Implementation, Advanced Database B1, Oracle |
Table of Contents
A. Introduction
1.
Data
and Database Security
Simply, Data
Security can be defined as the protective digital privacy measures that are
applied to protect data in computers, databases and websites from corruption
and unauthorized access which possess threat and could harm an organization or
an individual. The focus behind data security is to ensure privacy while
protecting personal or corporate data. Here, Data is the raw form of
information stored as columns and rows in our databases, network servers and
personal computers and may contain wide range of information from personal
files, images and intellectual property to market analytics and details
intended to top secret (Spamlaws.com, 2017).
In general terms,
Database is a collection of information stored in computer and security is
being free from danger. SO, Database Security can be described as the
mechanisms that protect the database against intentional or accidental threats.
In other words, 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 attack. It is a general term that includes a
multitude of processes, tools and methodologies that ensure security within a
database environment (Techopedia.com, 2017).
2.
Importance
of Data and Database Security
With the
development of information technology, every data is stored digitally in this
modern digital world. And these data could be in any forms like client
information, payment information, personal files, bank account details, etc.
and can be hard to replace and potentially dangerous if it falls into the wrong
hand. Losing data to hackers or a malware infection can have much greater
consequences than data lost due to disasters like flood and fire. Data loss
damages organizations in a large variety of ways and is expensive, with
estimated costs around USD200 per record breached; an average of USD6.8 million
per total breach (Anon, 2017). Besides this, Personal data of any one can be
used by hackers to create fake credit cards, drivers license, etc. Hence, the
data security is important to protect ourselves and organization from hackers
and other malicious attacks which can ruin our lives, property and business.
In the above
paragraph we discussed about the importance of data security, now let’s discuss
about the importance of database security. Security is an important issue in
database management because information stored in the database is very valuable
and many time, very sensitive commodity. It may contain data like transactions,
customers, employee info, financial data, etc. and much more. So, the data in
database should be protected from abuse and should be protected from
unauthorized access and updates (Thakur, 2017).
A threat is any situation, event or personal that will adversely affect
the database security and the smooth and efficient functioning of the
organization. Beside this, customers might lose trust on that organization if
any data is lost which may cause collapsing of the organization. Thus, the
organization should identify all the risk factors and weak elements from the
database security Perspective and find solutions to counter and neutralize each
such threat.
3.
Securing
Data and Database
As we discussed
about data security, database security and their importance in our day to day
lives. Hence, let’s discuss about the measures we can take to secure these data
and databases. Some of the measures to secure data are discussed below
a.
Using Internet
Security
Internet
Security or Antivirus is must in every computer as it protects from viruses,
malware and other online attacks. Different Anti viruses like Windows Defender,
PC Tune Up Utilities, Norton, etc. can be used in any PCs.
b.
Using VPN Service
Similarly,
using VPN service is another way of securing our data which encrypts the online
data. VPN provides the flow of encrypted data and anonymously web surfing
features.
c.
Using Data Back Up
Service
Among different
data security measures, it is also another important measure to secure the
data, as it is used to back up (save) data regularly into different devices
which helps to recover data from unwanted data loss. In these days, data can be
saved into different cloud services to be safe from natural or unnatural data
losses and access it from anywhere around the world.
Some of the measures to secure database of
any organization is discussed below:
a.
Database
administrator should understand the business value and importance of ensuring
database of the organization.
b.
In order to
encounter SQL Injections, parameterized queries should be used to keep
malicious queries out of our database.
c.
Static Code
Analysis is an essential tool for organizations developing applications as
portals to databases to slash SQL injection, buffer overflow, and
mis-configuration issues.
d.
In order to
prevent data loss due to forced shutdown, Uninterruptible Power Supply, or UPS
should be used.
e.
The features and
services that are essential for the company should be installed to work
smoothly with the database. More features and services means more things to
update, and the more holes hackers have a chance to poke through (Vonnegut,
2017).
f.
Users must be
authorized carefully to reduce the chance of any such user giving access to an
intruder in exchange for a bribe or other favors, etc.
B. Security Plans
1.
Placement
Process Control
Place U is a
recruiting company in the field of IT and media. And it has number of employees
(Consultant) who manage the business of meeting companies (accounts) and
understanding the individual they require for particular vacancy (placement).
Then consultants set up meeting between account and the interested candidate
(contractor). On the basis of feedback of interview they offer placement to the
contractor.
Let’s discuss
about the working architecture of Placement Process Control. Firstly, the consultant
adds the placement which is viewed by the contractor and apply for the
placement. Then the application is viewed by the consultant and the meeting
between the contractor and account is set.
2.
Necessity
of Security Feature(How it will be implemented)
As we discussed
above, Place U offers placement to different contractors in association with
Account. And while creating placement, consultant might make mistakes
intentionally or unintentionally like creating many placement a day, inserting
salary which is greater than a maximum salary, updating close placements, etc.
In order to check such human errors, security feature is applied to the
Placement Process Control. Three different security feature is implemented in
this section which is discussed below
i.
Allowing only
Consultant to the application who are DB User
Here,
the apex application should only allow the user who are DB User. It should show
an error message if any apex user or non DB user tries to login.
ii.
Allowing
consultant to ‘create’ only five placements a day
In
order to implement this security feature, an extra audit table will be created
which will save the logged in username, added date and operation made (i.e.
Placement Created). These data will be inserted using trigger. Firstly, the
total number of placement created on that day will be counted using the
trigger. If the total number of placement created on that day is greater than 5
then an error will be raised else the data will be inserted in the audit table
using trigger. At the same time placement will also be created using Apex form
if the total number of placement on that day is less than 5.
iii.
Audit whenever a
consultant attempts to add a placement with salary over the ‘max_salary’
In
order to check this security feature, a trigger is created which compares the
actual salary and max salary while creating placement. And if the actual salary
is greater than the max salary then the trigger will raise an error.
iv.
Only allowing a
consultant to update a currently ‘open’ placement
Here, an extra
column is added into the lds_placement table with the column heading ‘status’
and it declares whether the created placement is open or close. Whenever a
consultant attempts to make changes in placement whose status is ‘close’, then
the trigger will check whether the status is ‘open’ or ‘close’, if the status
is closed then the trigger will raise an error.
3.
Implementation
code of the Security Feature
In order to
implement the security feature in Placement Process Control, following codes
are used.
i.
Allowing only
Consultant to the application who are DB User
Two
Users created and granted permission
CREATE
USER vj identified vj123;
GRANT
CREATE SESSION TO vj;
GRANT
CONNECT, RESOURCE to vj;
CREATE
USER saiman identified saiman123;
GRANT
CREATE SESSION TO saiman;
GRANT
CONNECT, RESOURCE to saiman;
ii.
Creating trigger
to allow consultant to add only five placement a day.
Creating
Audit Table
CREATE TABLE
tbl_create_placement_audit(
audit_id int NOT
NULL PRIMARY KEY,
username VARCHAR2(150) NOT NULL,
addedDate Date NOT NULL,
Operation VARCHAR2(150) NOT NULL
);
Creating
Sequence for Audit Table
CREATE SEQUENCE
seq_audit_placement_id
START WITH 1
INCREMENT BY 1
nocache
nocycle;
Creating
Trigger for auto increment in Audit Table
CREATE OR REPLACE TRIGGER trg_create_replacement_audit
BEFORE INSERT
ON tbl_create_placement_audit
FOR EACH ROW
BEGIN
:new.audit_id := seq_audit_placement_id.nextval;
END;
Finally
creating Trigger to check the number of placement
create or replace TRIGGER trg_create_placement_check
BEFORE INSERT
ON lds_placement
FOR EACH ROW
DECLARE
no_placement INTEGER;
BEGIN
SELECT COUNT (*) INTO no_placement FROM
tbl_create_placement_audit WHERE username = UPPER(v('APP_USER')) AND to_date(ADDEDDATE) = to_date(sysdate);
IF no_placement > 4 THEN
raise_application_error (-2011,'Sorry! A consultant can only create five placements a day.');
ELSE
BEGIN
INSERT INTO
tbl_create_placement_audit(username,addedDate,Operation) VALUES (UPPER(v('APP_USER')),SYSDATE,'CREATE NEW PLACEMENT');
END;
END IF;
END;
iii.
Creating trigger
to raise an error if the actual salary is greater than max salary while
creating placement.
create or replace TRIGGER
trg_placement_salary_check
BEFORE INSERT OR UPDATE
ON lds_placement
FOR EACH ROW
BEGIN
IF :new.actual_salary > :new.max_salary THEN
raise_application_error (-2011,'Sorry! Actual Salary cannot be more than Maximum Salary.');
END IF;
END;
iv.
Creating trigger
to allow a consultant to update a currently ‘open’ placement
create or replace TRIGGER trg_placement_status_check
BEFORE UPDATE
ON lds_placement
FOR EACH ROW
BEGIN
IF :new.plt_status = 'close' THEN
raise_application_error (-2011,'Sorry! Only Open Placement can be Updated.');
END IF;
END;
4.
Evidence
of Security Feature Implementation
i.
All tables and
data loaded as provided
 |
| All Tables and Data Loaded |
ii.
Consultant (DB
User) with grants to session and Resources Created.
 |
| User VJ Created and Grant Session and Resource Given |
 |
| User Saiman Created and Grant Sessiona nd Resources |
iii.
Extra Table to
audit the number of placement made on a day is created
 |
| Table to audit the placement created |
iv.
Sequence created
for above audit table
 |
| Sequence Created for Audit Table |
v.
Trigger created to
add auto increment value in audit table above.
 |
| Trigger Created to insert Auto Increment Value in Audit Table |
vi.
Created trigger to
check total number of placement on a day made and raise error total number
exceeds 5.
 |
| Trigger Created to Check Total Number of Placement Created a Day |
vii.
Created trigger to
check the max salary and actual salary and raise error if the actual salary is
greater than max salary.
 |
| Trigger Created to Check Actual and Max Salary |
viii.
Created trigger
that raises error if a consultant attempts to make changes on a placement that
has ‘close’ status
 |
| Trigger Created to Check if the Placement Status is Open or Close |
5.
Security
Feature Testing
Testing done on the security feature implemented in
Placement Process Control are as follows
S.N.
|
Tested Date
|
What was tested?
|
Expected Output
|
Remarks
|
||
1.
|
27th Jan, 2017
|
Tried to login with apex user.
|
User should not get access and error
message should pop out.
|
Passed [User was not logged in and error
message was displayed]
|
||
Actual Output
|
||||||
|
||||||
S.N.
|
Tested Date
|
What was tested?
|
Expected Output
|
Remarks
|
||
2.
|
27th Jan, 2017
|
Login Tested with DB User.
|
User should be logged in and home page
should be displayed.
|
Passed [User was successfully added and
home page was displayed.]
|
||
Actual Output
|
||||||
|
||||||
S.N.
|
Tested Date
|
What was tested?
|
Expected Output
|
Remarks
|
||
3.
|
27th Jan, 2017
|
All the details on create placement form
was filled and create button was clicked.
|
New placement should be created
successfully.
|
Passed [Placement was successfully
created.]
|
||
Actual Output
|
||||||
|
||||||
S.N.
|
Tested Date
|
What was tested?
|
Expected Output
|
Remarks
|
||
4.
|
27th Jan, 2017
|
Sixth placement was added on same date
by same user.
|
Sixth placement should not be added and
error message should be displayed.
|
Passed [Error Message was displayed.]
|
||
Actual Output
|
||||||
|
||||||
S.N.
|
Tested Date
|
What was tested?
|
Expected Output
|
Remarks
|
||
5.
|
27th Jan, 2017
|
Tried to add placement with a salary
over the ‘max salary’.
|
Placement should not be added and error message
should be displayed.
|
Passed [Error message was displayed.]
|
||
Actual Output
|
||||||
|
||||||
S.N.
|
Tested Date
|
What was tested?
|
Expected Output
|
Remarks
|
||
6.
|
27th Jan, 2017
|
Tried to update placement with ‘close’
status.
|
Placement should not be updated and
error message should be displayed.
|
Passed [Placement was not updated and
error message was displayed.]
|
||
Actual Output
|
||||||
|
||||||
C. References
Anon, (2017). Why is data security important. [online] Available at: http://www.purdue.edu/business/Security/pdf/printoutwebctmaterial.pdf
[Accessed 27 Jan. 2017].
Spamlaws.com. (2017). What is Data Security?.
[online] Available at: http://www.spamlaws.com/data-security.html [Accessed 27
Jan. 2017].
Techopedia.com. (2017). What is Data Security? - Definition from Techopedia. [online] Available at:
https://www.techopedia.com/definition/26464/data-security [Accessed 27 Jan.
2017].
Thakur, D. (2017). What is Importance, Levels, Requirement of Security in
Database Environment?. [online]
Ecomputernotes.com. Available at:
http://ecomputernotes.com/database-system/adv-database/security-in-database-environment
[Accessed 27 Jan. 2017].
Vonnegut, S. (2017). The Importance of Database Security and Integrity. [online] Checkmarx. Available at:
https://www.checkmarx.com/2016/06/24/20160624the-importance-of-database-security-and-integrity/
[Accessed 27 Jan. 2017].
![Advertisement [ad]](https://blogger.googleusercontent.com/img/a/AVvXsEgVAiCox6-vLXsNZas8ks-nfos0PgdnL4yClmlqOkl92t7zGdYYiLBy9AHMZFxBYe06DVmN6JGQ9S0P3iClXk8l43FIQPDyAcx_uMmV0bN9JlKjTzOAi7YjmQo6cuvHgkEO76L-hcqV-TWE29v93eeFby8MOAOuJ8DcilHTPpfP8aKg8TG9uYCDaMxcr8H1=s600)
Comments