SRM Institute of Science and Technology

Faculty of Engineering and Technology SET - A

School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA - T1 Date: 11-08-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 50 Minutes
Year & Sem: IV / VII Max. Marks: 25

Course Articulation Matrix: (to be placed)

Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO1 3 3 - - - - - - - - - -

2 CO2 3 - - - - - - - - - - -

PART - A
(10 x 1 = 10 Marks)
Instructions: Answer all
Q. Mark PI
Questions BL CO PO
No s Code
1. _______ is a set of processes and procedure that transform 1 L1 1 1 1.6.1
data into information and knowledge.
A Knowledge system
B Information system
C Database system
D Computer system
2. A(n) _________ is a collection of programs that allows the 1 L1 1 1 1.6.1
user to operate the computer hardware.
A information system
B database
C DBA
D operating system
3. The main component of the operating system security 1 L1 1 1 1.6.1
environment is____________
A services
B file transfer
C memory
D file sharing
4. A ___________ is a place where database security must be 1 L1 1 1 1.6.1
protected and applied.
A security gap
B security access point
C security threat
D security vulnerability
5. _______is used for tactical management tasks and 1 L1 1 1 1.6.1
contains collection of business models
A Transaction Processing System (TPS)
B Decision Support System (DSS)
 C Expert System (ES)
D Client / Server
6. 764 File Permission means? 1 L1 1 1 1.6.1
A Everyone can read, write and execute.
B Everyone can read, group can execute only and the
owner can read and write.
C Everyone can read, write and execute.
D Everyone can read, group including owner can
write, owner can execute.
7. _______is used by network devices to provide a centralized 1 L1 1 1 1.6.1
authentication mechanism
A SSL
B RADIUS
C SRP
D PKI
8. _______ is a process that decides whether users are 1 L1 1 1 1.6.1
permitted to perform the functions they request.
A Identification
B Authentication
C Authorization
D Verification
9. ___________ is a weakness that can be exploited by 1 L1 1 1 1.6.1
attackers.
A System with Virus
B System without firewall
C System with vulnerabilities
D System with strong password
10. __________ allows you to sign on once to a server (host 1 L1 1 1 1.6.1
machine) and then not have to sign on again if you go to
another server where you have an account.
A Password history
B Password reuse
C Logon retries
D Single sign-on
 PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
11. Sketch out the Information Security Architecture. 5 L3 1 1 1.6.1

12. List the few DBMS functionalities and the major 5 L3 1 1 1.6.1
responsibilities of a database administrator.
DBMS functionalities:
✓ Allow developer and administrators to Organize
data
✓ Allow user to Store and retrieve data efficiently
✓ Allow user to Manipulate data (update and
delete)
✓ Enforce referential integrity and consistency
✓ Enforce and implement data security policies
and procedures
✓ Back up, recover, and restore data
Major responsibilities of a database administrator.
✓ Software Installation and Maintenance
✓ Data Extraction, Transformation, and Loading
✓ Specialised Data Handling
✓ Database Backup and Recovery
✓ Security
✓ Authentication
✓ Capacity Planning
✓ Performance Monitoring
✓ Database Tuning
✓ 10. Troubleshooting
13. Describe the categories of database security threats. 5 L3 1 1 1.6.1
✓ Threat is defined as “ An indication of
impending(i.e. will happen soon) danger or
harm”
✓ Vulnerabilities can escalate into threats
✓ DBA , IS Administrator should aware of
vulnerabilities and threats
✓ Four types of threats contribute to security risks
as shown in below figure
 ✓

14. Discuss the any two digital authentication mechanisms 5 L3 1 1 1.6.1

used by operating systems.
Digital Certificate
✓ Widely used in e-commerce
✓ Is a passport that identifies and verifies the
holder of the certificate
✓ Is an electronic file issued by a trusted party (
Known as certificate authority ) and cannot be
forged or tampered with.
Digital Token (Security Token)
✓ Is a small electronic device that users keep with
them to be used for authentication to a computer
or network system.
✓ This device displays a unique number to the
token holder, which is used as a PIN ( Personal
Identification Number) as the password
Digital Card
✓ Also known as security card or smart card
✓ Similar to credit card in dimensions but instead
of magnetic strip
✓ It has an electronic circuit that stores the user
identification information
Kerberos
✓ Developed by Massachusetts Institute of
Technology (MIT) , USA
✓ It is to enable two parties to exchange
information over an open network by assigning
a unique key. Called ticket , to each user.
✓ The ticket is used to encrypt communicated
messages
 SRM Institute of Science and Technology
Faculty of Engineering and Technology SET - B
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA - T1 Date: 11-08-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 50 Minutes
Year & Sem: IV / VII Max. Marks: 25

Course Articulation Matrix: (to be placed)

Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO1 3 3 - - - - - - - - - -

2 CO2 3 - - - - - - - - - - -

PART - A
(10 x 1 = 10 Marks)
Instructions: Answer all
Q. PI
Questions Marks BL CO PO
No Code
1. Which of the following system supports non-structured 1 L1 1 1 1.6.1
problems and provide recommendations or answer to solve
these problems?
A Decision Support System
B Transaction Processing System
C Expert System
D Database Management system
2. The concept behind a(n) ________ application is based on 1 L1 1 1 1.6.1
the business model of a customer ordering a service or
product and the representative of a business granting that
request.
A information system
B C.I.A. triangle
C DBMS
D client/server
3. In CIA triangle, A stands for_______ 1 L1 1 1 1.6.1
A Atomicity
B Accessibility
C Authority
D Availability
4. A ________ is a security violation or attack that can 1 L1 1 1 1.6.1
happen any time because of a security vulnerability.
A Security risk
B Security privilege
C Security policy
D Security threat
5. Business application, in house programs, purchased 1 L1 1 1 1.6.1
software, operating systems, databases are example of
 ________
A Physical Asset
B Logical Asset
C Intangible Asset
D Human Asset
6. ________code that compromises the integrity and state of 1 L1 1 1 1.6.1
the system
A Worm
B Spoofing Code
C Virus
D Trojan Horse
7. LDAP stands for ____________. 1 L1 1 1 1.6.1
A Lightweight Direct Access Protocol
B Lightweight Directory Access Protocol
C Lightweight Directory Access Permission
D Limited Directory Access Protocol
8. Authentication information is transmitted over the network 1 L1 1 1 1.6.1
in an encrypted form using_______.
A SRP
B RADIUS
C SSL
D PKI
9. How many types of permissions a file has in UNIX? 1 L1 1 1 1.6.1
A 1
B 2
C 3
D 4
10. _________ tells the system how many days a password can 1 L1 1 1 1.6.1
be in effect before it must be changed
A Password aging
B Password Limit
C Password Validity
D Password reuse
 PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
11. Enumerate the various components of Information 5 L3 1 1 1.6.1
systems.

Data– The information stored in the Database for

future references or processing
Procedures– Manual , Guidelines, Business rules and
Policies
Hardware – Computer System, Fax, Scanner, Printer,
Disk
Software – DBMS, OS, Programming Languages,
Other Utilities or Tools
Network –Communication Infrastructure
People – DBA, System Admin, Programmers, Users,
Business Analyst, System Analyst
12. Elaborate the typical use of system applications at 5 L3 1 1 1.6.1
various management levels.
✓ An information can be a back bone of the day-
to-day operations of a company as well as the
beacon of long-term strategies and vision.
✓ Information systems are categorized based on
usage.
✓ The following figure shows the typical use of
system applications at various management
levels
 ✓ Information System mainly classified into three
categories
1) Transaction Processing System (TPS)
2) Decision Support System (DSS)
3) Expert System (ES)
13. Describe the categories of database security risks. 5 L3 1 1 1.6.1
✓ Risks are simply the a part of doing business
✓ Managers at all the levels are constantly
working to assess and mitigate risks to ensure
the continuity of the department operations.
✓ Administrators should understand the weakness
and threats related to the system
✓ Categories of database security risks are shown
in the below figure

14. Discuss the different categories of information assets 5 L3 1 1 1.6.1

and their values.
✓ People always tend to protect assets regardless
of what they are
✓ Corporations treat their assets in the same way
✓ Assets are the infrastructure of the company
operation
There are four main types of assets
▪ Physical assets – Also known as
tangible assets, these include buildings,
cars, hardware and so on…
▪ Logical assets – Logical aspects of an
information system such as business
applications, in-house programs,
purchased software, OS, DBs, Data
▪ Intangible assets – Business reputation,
quality, and public confidence
▪ Human assets – Human skills,
knowledge and expertise
 SRM Institute of Science and Technology
Faculty of Engineering and Technology SET - C
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA - T1 Date: 11-08-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 50 Minutes
Year & Sem: IV / VII Max. Marks: 25

Course Articulation Matrix: (to be placed)

Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO1 3 3 - - - - - - - - - -

2 CO2 3 - - - - - - - - - - -

PART - A
(10 x 1 = 10 Marks)
Instructions: Answer all
Q. Mark PI
Questions BL CO PO
No s Code
1. Data is processed or transformed by a collection of 1 L1 1 1 1.6.1
components working together to produce and generate
accurate information. These components are known as
a(n)_____________.
A information system
B database
C DBA
D operating system
2. NSTISSC stand for _____________ 1 L1 1 1 1.6.1
A National Service Telecommunications & Information
Systems Security Committee
B National Security Telecommunications &
Information Systems Security Committee
C National Security Telecommunications & Information
Systems Security Company
D National Security Telecommunications & Integration
Systems Security Committee
3. _____ means the protection of data from modification by 1 L1 1 1 1.6.1
unknown users.
A Confidentiality
B Integrity
C Authentication
D Non-repudiation
4. The model designed for guiding the policies of Information 1 L1 1 1 1.6.1
security within a company, firm or organization is referred
as______._
A Confidentiality
B Non-repudiation
C CIA Triangle
 D Authenticity
5. From the following, which is not common file permission? 1 L1 1 1 1.6.1
A Write
B Execute
C Stop
D Read
6. Software that defines a database, stores the data, supports a 1 L1 1 1 1.6.1
query language, produces reports and creates data entry
screens is a___
A Data Dictionary
B Database Management System
C Decision Support System
D Relational Database
7. ___________ enforce and implement data security policies 1 L1 1 1 1.6.1
and procedures on data base levels.
A Database designer
B Database analyst
C Database Administrator
D Database manager
8. Which layer authenticates information that is transmitted 1 L1 1 1 1.6.1
over the network in an encrypted form?
A Socket base layer
B Secure socket layer
C Security application layer
D Security software
9. Which of the following method is efficient for reading but 1 L1 1 1 1.6.1
not suited for frequently changing information?
A Public Key Infrastructures (PKI)
B Secure Remote Password (SRP)
C Lightweight Directory Access Protocol
(LDAP)
D NT LAN Manager (NTLM)
10. __________is a small electronic device that users keep 1 L1 1 1 1.6.1
with them to be used for authentication of a computer or
network
A Kerberos
B Digital Card
C Digital Token
D Digital Certificate
 PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
11. Annotate CIA triangle. 5 L2 1 1 1.6.1

12. Describe the various characteristics of information 5 L3 1 1 1.6.1

system categories.
13. Discuss the different components of an OS Security 5 L3 1 1 1.6.1
Environment.
✓ The three components (layers) of the OS are
represented in the figure
✓ Memory component is the hardware memory
available on the system
✓ Files component consists of files stored on the
disk
✓ Service component compromise such OS
features and functions as N/W services, File
Management and Web services

14. Define Database security and give various database 5 L3 1 1 1.6.1

security access points.

Database security is a collection of security polices

and procedures, data constraints, security methods ,
security tools blended together to implement all
necessary measures to secure the integrity, accessibility
and confidentiality of every component of the
database environment.
• One of the functions of DBMS is to empower
DBA to implement and enforce security at all
 levels of security
• A security access point is a place where
database security must be protected and applied
• The Major Security access points illustrated in
the below figure

✓ People – Individuals who have been granted

privileges and permissions to access networks,
workstations, servers, databases, data files and
data
✓ Applications – Application design and
implementation which includes
privileges and permissions granted to people
✓ Network – One of the most sensitive security
access points. Protect the network and provide
network access only to applications,
operating systems and databases.
✓ Operating Systems – This access point is
defined as authentication to the
system, the gateway to the data
✓ DBMS – The logical structure of the database,
which includes memory ,
executables and other binaries
✓ Data files – Another access point that influences
database security enforcement is access to
data files where data resides.
✓ Data – The data access point deals with data
design needed to enforce data integrity
 SRM Institute of Science and Technology
Faculty of Engineering and Technology SET - D
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA - T1 Date: 11-08-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 50 Minutes
Year & Sem: IV / VII Max. Marks: 25

Course Articulation Matrix: (to be placed)

Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO1 3 3 - - - - - - - - - -

2 CO2 3 - - - - - - - - - - -

PART - A
(10 x 1 = 10 Marks)
Instructions: Answer all
Q. PI
Questions Marks BL CO PO
No Code
1. ______is a gateway of the database access. 1 L1 1 1 1.6.1
A Server
B Operating System
C Network
D Internet
2. ________is a collection of security polices and 1 L1 1 1 1.6.1
procedures, data constraints, security methods , security
tools blended together to implement all necessary
measures to secure the integrity, accessibility and
confidentiality of every component of the database
environment.
A Operating System
B Firewall
C Database security
D Gateway
3. In CIA triangle, I stands for_______ 1 L1 1 1 1.6.1
A Information
B Integrity
C Issues
D Identification
4. Operating system, database and data are the example 1 L1 1 1 1.6.1
of____.
A Physical assets
B Logical assets
C Intangible assets
D Human assets
5. Database security is the_______to which all the data is 1 L1 1 1 1.6.1
fully protected from tampering and unauthorized acts.
 A Degree
B Reliability
C Durability
D Percentile
6. _________device displays a unique number to the token 1 L1 1 1 1.6.1
holder, which is used as a PIN ( Personal Identification
Number) as the password.
A Digital Token
B Digital Card
C Digital Certificate
D Kerberos
7. The __________method is the process of verifying the 1 L1 1 1 1.6.1
identity of the user by means of a digital mechanism or
software.
A Digital Certificate .
B Digital Token
C Digital Card
D Digital authentication
8. In UNIX, a file can be recognized as an ordinary file or 1 L1 1 1 1.6.1
directory by ____ symbol.
A #
B $
C -
D *
9. Two parties to exchange information over an open 1 L1 1 1 1.6.1
network by assigning a unique key is called______.
A Token
B Ticket
C Keys
D Password
10. Malicious code that looks like a legitimate code is known 1 L1 1 1 1.6.1
as _______
A Passcode
B Spoofing code
C Virus code
D Trojan code
 PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
11. Explain about database and database environment. 5 L3 1 1 1.6.1
✓ A collection of meaningful Integrated
Information System
✓ It is both Physical and Logical
✓ Representing the logical information in a
physical device
✓ Mainly used for storing and retrieving
the data for processing
✓ Using CLIENT / SERVER Architecture
✓ Request and Reply protocols are used to
communicate client and server

12. Write short note on database security vulnerabilities. 5 L3 1 1 1.6.1

✓ Vulnerability means “ Susceptible to Attacks” (
Source :www.dictionary.com)
✓ Intruders, Attackers and Assailers exploit
vulnerabilities in Database environment to
prepare and start their attacks.
✓ Hackers usually explore the weak points of a
system until they gain entry
✓ Once the intrusion point is identified , Hackers
unleash their array of attacks
▪ Virus
▪ Malicious Code
▪ Worms
▪ Other Unlawful violations
✓ To protect the system the administrator should
understand the types of vulnerabilities
✓ The below figure shows the types of
vulnerabilities
13. Describe the various security methods that are used to 5 L3 1 1 1.6.1
protect the different components of a database
environment.

Security methods used to protect database environment

components

Database Security Methods

Component
Protected
People ✓ Physical limits on access
to hardware and
documents
✓ Through the process of
identification and
authentication make
certain that the individual
is who is claim s to be
through the use of
devices, such as ID cards,
eye scans, and passwords
✓ Training courses on the
importance of security
and how to guard assets
✓ Establishment of security
policies and procedures
 Applications ✓ Authentication of users
who access applications
✓ Business rules
✓ Single sign-on ( A method
for signing on once for
different applications and
web sites)
Network ✓ Firewalls to block
network intruders
✓ Virtual Private Network
(VPN)
✓ Authentication
14. Write short note on E-Mail security. 5 L3 1 1 1.6.1

✓ E-mail may be the tool most frequently used by

hackers to exploit viruses, worms, and other
computer system invaders.
✓ E-mail is widely used by public and private
organizations as a means of communication
✓ E-mail was the medium used in many of the
most famous worm and virus attacks
✓ For example :
▪ Love Bug Worm
▪ Mydoom worm
▪ Melissa virus
✓ E-mail is not only to used to send viruses and
worms, nut to send spam e-mail, private and
confidential data as well as offensive messages
✓ To prevent from these activities ,
▪ Do not configure e-mail server on a
machine in which the sensitive data
resides
▪ Do not disclose the e-mail server
technical details
 SRM Institute of Science and Technology
Faculty of Engineering and Technology SET - A
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA2 Date: 10-10-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 1 Hour 40 Minutes
Year & Sem: IV / VII Max. Marks: 50

Course Articulation Matrix: (to be placed)

Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO3 1 - 1 2 2 1 - - - - - -

2 CO4 1 - 3 1 1 1 - - - - - -

PART - A
(15 x 1 = 15 Marks)
Instructions: Answer all
Q. PI
Questions Marks BL CO PO
No Code
When we update any tuple in the relation, which
1. 1 1 3 4,5 1.3.1
Authorization on a relation allows a user to?
A Select authorization
B Update authorization
C Grant authorization
D Define authorization
2. Which statement is used to revoke an authorization, 1 1 3 4,5 1.3.1
A Revoke
B Modify
C grant
D alter
3. Who are the oracle default users? 1 1 3 4,5 1.3.1
A SYS and SYSTEM
B SYSTEM and SCOTT
C SYS, SYSTEM and SCOTT
D SYS and SCOTT
4. Identify the other two names used by ORACLE to refer VPD? 1 1 4 3 1.3.1
A Column level security and Row level security
B Column level security and Fine grained access
C Row level security and Fine grained access
D Fine grained access and information security
________algorithm uses same cryptographic keys for both
5. 1 1 4 3 1.3.1
encryption and decryption of cipher text
A Asymmetric key encryption
B Private key encryption
C Public key encryption
D Symmetric key encryption
Oracle _______ views enable you to see everything created
6. 1 1 4 3 1.3.1
and stored in the database.
A Storage
 B Data dictionary
C Service
D Users
__________ is the indication that a password has a limited
7. 1 1 3 4,5 1.3.1
time left before it expires.
A Password complexity
B Password storage
C Password usage
D Password aging
8. Command that comes under DCL is/are - 1 1 3 4,5 1.3.1
A grant
B revoke
C Both A & B
D None of the above
9. Which command can be used to obtain the table’s records? 1 1 3 4,5 1.3.1
A retrieve
B select
C create
D alter
Give the command to change the default password TIGER
10. 1 1 3 4,5 1.3.1
to LION for the user SCOTT
A alter user identified by lion;
B alter user scott identified by lion;
C alter user scott by lion;
D alter user identified by lion;
What does the following code snippet do?
11 1 1 3 4,5 1.3.1
Delete from students where age=15; Rollback;
A Performs an undo operation on the delete operation
B Delete the rows from the table where age=15
C Deletes the entire table
D None of the above
12 Virtual private database is a function of 1 1 4 3 1.3.1
A Oracle
B Java
C SQL
D DB2
Virtual private database provides authorization at the level
13 1 1 4 3 1.3.1
of
A Rows
B Tuples
C Relations
D All of the above
14 To add or remove server role membership use 1 1 3 4,5 1.3.1
A Alter role
B Alter any login
C Alter server role
D None of the mentioned
Farmer goes to ATM center to withdraw an amount of
15 1 1 4 3 1.3.1
Rs.300/- . Which type of user farmer is?
A Application programmer
B Unsophisticated User
C Sophisticated User
D Specialized User
 PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
16. Describe the various privileges available in the security
data model.
Privilege is a method to permit or deny access
to data or to perform database Operations (Data
Manipulation).
⮚ System Privileges – Privileges granted only
by DBA or users who have been granted
the administration option.
⮚ Object Privileges – Privileges granted to an
ORACLE user by the scheme owner of a
database object or a user who has been granted 5 2 3 4 1.6.1
the GRANT option.
System Privileges
ADMIN, ALTER ANY CACHE GROUP, ALTER
ANY TABLE, CREATE ANY PROCEDURE,
CREATE ANY TABLE
Object privileges:
DELETE, EXECUTE, INSERT, SELECT, UPDATE

17.
Summariz
data
method
which
only
informat
authoriz
can
encoded
a way
reae that
i
Note: Write single line description about each privilege.

Explain in detail data encryption.

Data encryption is a method of protecting data by
encoding it in such a way that it can only be decrypted
or accessed by an individual who holds the correct
encryption key. When a person or entity accesses
encrypted data without permission, it appears
scrambled or unreadable.
How does data encryption work?
The data that needs to be encrypted is termed plaintext
or clear text. The plaintext needs to be passed via some
encryption algorithms, which are mathematical
calculations to be done on raw information. There are
multiple encryption algorithms, each of which differs
by application and security index.
5 1 4 3 1.6.1

Apart from the algorithms, one also needs an

encryption key. Using said key and a suitable
encryption algorithm, the plaintext is converted into the
encrypted piece of data, also known as cipher text.
Instead of sending the plaintext to the receiver, the
cipher text is sent through insecure channels of
 communication.
Once the cipher text reaches the intended receiver,
he/she can use a decryption key to convert the cipher
text back to its original readable format i.e. plaintext.
This decryption key must be kept secret at all times,
and may or not be similar to the key used for
encrypting the message.

18. Compare the access modes model's static and dynamic

modes.

5 1 4 3 1.6.1

19. Write about the following in SQL server

I. Removing user
II. Modifying user
Removes a user from the current database.
Syntax:
DROP USER [ IF EXISTS ] user_name
Example:
DROP USER AbolrousHazem;
GO
To change the name of a user requires the ALTER
5 3 3 4 1.6.1
ANY USER permission. To change the target login of a
user requires the CONTROL permission on the
database.
Syntax:
Rename user
ALTER USER user_name
WITH NAME new_name;
Create login srm with password =’srm’;
Create user Robert for login srm;
alter user srm with name=srmist;
 PART – C
(2 x 10 = 20 Marks)
20. Describe the many kinds of authentication techniques
used for database security.
Common Authentication Techniques are
Password-based Authentication
Multi-Factor Authentication
Certificate-based Authentication
Biometric Authentication
Token-based Authentication
1. Password-based Authentication
❖ Passwords are the most common methods of
authentication. Passwords can be in the form of
a string of letters, numbers, or special
characters. To protect yourself you need to
create strong passwords that include a
combination of all possible options.
❖ The truth is that there are a lot of passwords to
remember. As a result, many people choose
convenience over security. Most people use
simple passwords instead of creating reliable
passwords because they are easier to
remember.
❖ The bottom line is that passwords have a lot of
weaknesses and are not sufficient in
protecting online information. Hackers can
easily guess user credentials by running through
all possible combinations until they find a
10 1 3 5 6.1.1
match.
2. Multi-factor Authentication
❖ Multi-Factor Authentication (MFA) is an
authentication method that requires two or more
independent ways to identify a user. Examples
include codes generated from the user’s
smartphone, Captcha tests, fingerprints, voice
biometrics or facial recognition.
❖ MFA authentication methods and technologies
increase the confidence of users by adding
multiple layers of security. MFA may be a good
defense against most account hacks, but it has
its own pitfalls. People may lose their phones or
SIM cards and not be able to generate an
authentication code.
3. Certificate-based Authentication
❖ Certificate-based authentication technologies
identify users, machines or devices by using
digital certificates. A digital certificate is an
electronic document based on the idea of a
driver’s license or a passport.
❖ The certificate contains the digital identity of a
user including a public key, and the digital
signature of a certification authority. Digital
certificates prove the ownership of a public key
and issued only by a certification authority.
 ❖ Users provide their digital certificates when
they sign in to a server. The server verifies the
credibility of the digital signature and the
certificate authority. The server then uses
cryptography to confirm that the user has a
correct private key associated with the
certificate.
4. Biometric Authentication
❖ Biometrics authentication is a security process
that relies on the unique biological
characteristics of an individual. Here are key
advantages of using biometric authentication
technologies:
❖ Biological characteristics can be easily
compared to authorized features saved in a
database.
❖ Biometric authentication can control physical
access when installed on gates and doors.
❖ You can add biometrics into your multi-
factor authentication process.
5. Token-based Authentication
Token-based authentication technologies enable users
to enter their credentials once and receive a unique
encrypted string of random characters in exchange. You
can then use the token to access protected systems
instead of entering your credentials all over again. The
digital token proves that you already have access
permission.
OR
21. Describe in detail the following.
I. Database links
A database link, is a mechanism in a database
management system that allows a user to access data
from a remote database. It creates a connection
between two databases, allowing the user to query,
insert, update, and delete data from the remote database
as if it were a local database. This is useful when you
need to access data from multiple databases, but you
don't want to replicate the data in all of them.

10 1 3 4 2.2.4
To create a shared database link, use the keyword
SHARED in the CREATE DATABASE LINK
statement:
Syntax:
CREATE SHARED DATABASE LINK dblink_name
[CONNECT TO username IDENTIFIED BY
password]|[CONNECT TO CURRENT_USER]
AUTHENTICATED BY schema_name IDENTIFIED
BY password
[USING 'service_name'];
Example:
CREATE SHARED DATABASE LINK link2sales
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY linkuser IDENTIFIED BY
ostrich
USING 'sales';
What is DB link good for?
1. Sharing of data between two databases.
2. For import/export style purposes.
3. You can get direct access to the database of a
different application.
4. For Security and Confidentiality.
II. Linked Server
The linked server is basically the way you defined a
‘connection’ between two servers. i.e. Connecting a
Database-A from Server 1 with Database-B from
Server 2 and doing queries from both databases.

Querying Data Over a SQL Server Linked Server

To read data from a Linked Server any tables or views
must be referenced using a 4-part identifier consisting
of the Linked Server name, database name, schema
name, and object name – in that order.
For example:
SELECT * FROM
[.\SECURITY_TEST].master.sys.databases
SELECT * FROM
[.\SECURITY_TEST].WideWorldImporters.Sales.
Orders
First the Linked Server name is provided which is
[.\SECURITY_TEST] for our example. In this example
it must be bracketed due its format. Not all Linked
Server names must be bracketed. Next is the database
name, master and WideWorldImporters respectively.
Third is the schema name, sys and Sales respectively.
Lastly, the object name is listed. In these examples the
objects are databases and Orders.
22. Define the security model. Describe several database
application security model types with a clear diagram.
❖ Security models are useful tools for evaluating
and comparing security policies.
❖ Security models allow us to test security
policies for completeness and consistency.
They describe what mechanism are necessary
to implement security policy.
❖ To eliminate threats, it is necessary to define
proper security policy. Security policies are
governing principles adopted by organizations.
❖ They capture the security requirements of an
organization, specify what security properties
the system must provide and describe steps an
organization must take to achieve security.
Security models are described in terms of the
following elements:
❖ Subjects: Entities that request access to
objects.
❖ Objects: Entities for which access request is
being made by subjects.
❖ Access Modes: Type of operation performed
by subject on object (read, write, create etc.).
❖ Policies: Enterprise wide accepted security
rules.
❖ Authorizations: Specification of access modes
for each subject on each object. 10 1 4 4 6.1.1
❖ Administrative Rights: Who has rights in
system administration and what responsibilities
administrators have.
❖ Axioms: Basic working assumptions.
Access Matrix Model
It represents two main entities
1. Objects
2. Subjects
Columns represent objects and rows represent subjects.
Object can be a tables, views, procedures, database
objects.
Subjects can be a users, roles, privileges, modules.
Authorization cells- Access details on the objects
granted to the subject, access, operation, or commands

Access Modes Model

It uses objects and subjects
It specifies access modes: static and dynamic modes
 Access levels: A subject has access to objects at its
level and all levels below it.
OR
23. Justify, how oracle helps in implementing VPD using
views.
Virtual Private Database(VPD) is the most popular
secured database which was introduced by Oracle
Database Enterprise. It is used when the object
privileges and database roles are inadequate to
achieve security requirements. The policies or
protocols are directly proportional to security
requirements.
When a user directly or indirectly accesses a table,
view, or synonym that is protected with an Oracle
Virtual Private Database policy, Oracle Database
dynamically modifies the SQL statement of the user.
This modification creates a WHERE condition (called a
predicate) returned by a function implementing the
security policy. Oracle Database modifies the statement
dynamically, transparently to the user, using any
condition that can be expressed in or returned by a
function. You can apply Oracle Virtual Private
Database policies to SELECT, INSERT, UPDATE,
INDEX, and DELETE statements.
For example, suppose a user performs the following
query:
SELECT * FROM OE.ORDERS;
The Oracle Virtual Private Database policy
dynamically appends the statement with a WHERE 10 3 4 5 1.7.1
clause. For example:
SELECT * FROM OE.ORDERS WHERE
SALES_REP_ID = 159;
In this example, the user can only view orders by Sales
Representative 159.
Advantages of VPD
✓ Higher Accessibility: Users can easily access
the data from anywhere.
✓ Flexibility: It can be easily modified without
breaking the control flow.
✓ Higher Recovery Rate: The data can be
retrieved very easily.
✓ Dynamically Secured: No need to maintain
complex roles.
✓ No back doors: The security policy is attached
to the data so no by-passing is allowed.
Dis-Advantages of VPD
• Difficult column-level security.
• Oracle account ID is required to use this
service.
• Hard to examine.
1. First we will create the users needed for our
environment
a) Owner of the schema which will have the objects
of tables
 b) Security admin_user
c) Non owner user or user which will have limited
access as per data in the rows.
a) Owner of the schema which will have the objects
of tables
SQL> create user schemaowner identified by
schemaowner
default tablespace users temporary tablespace temp
quota unlimited on users;
The schema owner represents the Oracle user that owns
all your database objects, while application users are
Oracle users that need access to those schema objects.
SQL> grant connect, resource to schemaowner;
grant succeeded.
b) Create Security admin_user
SQL> grant execute on dbms_session to sec_adm;
grant succeeded.
SQL> grant execute on dbms_rls to sec_adm;
grant succeeded.
//RLS is for Row Level Security
c) Create users with restricted access on table
SQL>create user user1 identified by user1
default tablespace users temporary tablespace temp;
user created.
SQL> grant connect,resource to user1;
grant succeeded.
SQL> create user user2 identified by user2
default tablespace users temporary tablespace temp;
user created.
SQL> grant connect,resource to user2;
grant succeeded

create or replace function vpdf1(schema

varchar2,object varchar2) return varchar2
as begin
return
'sname=sys_context("userenv","session_user")';
end;
/
exec
dbms_rls.add_policy('s_owner','emp','india','s_owner','v
pdf');
BEGIN
dbms_rls.add_policy('s_owner','emp','india','s_owner','v
pdf'); END;
 SRM Institute of Science and Technology
Faculty of Engineering and Technology SET - B
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA – T2 Date: 10-10-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 2 Hours
Year & Sem: IV / VII Max. Marks: 50

Course Articulation Matrix: (to be placed)

Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO3 3 - - - - - - - - - - -

2 CO4 3 3 - - - - - - - - - -

PART - A
(15 x 1 = 15 Marks)
Instructions: Answer all
Q. Mark PI
Questions BL CO PO
No s Code
1. Which of the following is not a privilege in SQL Server? 1 L1 3 1 1.6.1

a) Diskadmin
b) Bulkadmin
c) Serveradmin
d) Clientadmin
2. This password parameter specifies the number of days before a 1 L1 3 1 1.6.1
user can reuse a password.

a) PASSWORD_REUSE_TIME
b) PASSWORD_REUSE_MAX
c) PASSWORD_REUSE_DAYS
d) PASSWORD_REUSE_MIN
3. When creating a DB link, which cannot be the authentication 1 L1 3 1 1.6.1
method?

a) CURRENT USER
b) FIXED USER
c) CONNECT USER
d) DYNAMIC USER
4. Which of the following is not an account status? 1 L1 3 1 1.6.1

a) OPEN
b) CLOSE
c) EXPIRED
d) LOCKED
5. Which statement creates the same role as an external role? 1 L1 3 1 1.6.1
a) CREATE ROLE dw_manager IDENTIFIED BY
warehouse;
 b) CREATE ROLE warehouse_user IDENTIFIED
GLOBALLY;
c) CREATE ROLE warehouse_user IDENTIFIED
EXTERNALLY;

d) CREATE ROLE dw_manager IDENTIFIED

LOCALLY;

6. When a user logs on to the DB through the machine where 1 L1 3 1 1.6.1

the DB is not located, called as _________
a) Local user
b) Internal user
c) External user
d) Remote user
7. Which database privilege explicitly denied SELECT and 1 L1 3 1 1.6.1
READTEXT statements?
a) db_datareaderdenial
b) db_denieddatareader
c) db_denialdatareader
d) db_denydatareader
8. An account that has access to the database through another 1 L1 1 1 1.6.1
database account; a virtual user is referred to in some cases
as a ____________________
a) Schema Owner
b) Application User
c) Database User
d) Proxy User
9. A conceptual model that specifies the right that each 1 L1 1 1 1.6.1
subject possesses for each object is ________________
a) Static access mode
b) Dynamic access mode
c) Access Matrix
d) Subject Object Matrix
10. The static access mode in the level 2 is ______________ 1 L1 1 1 1.6.1
a) Create
b) Delete
c) Read
d) Use
11. The Dynamic access mode Revoke is at level 1 L1 1 1 1.6.1
_____________
a) 1
b) 2
c) 3
d) 4
12. This component found in Client/Server architecture 1 L1 1 1 1.6.1
Contains all the codes related to data validations.
a) User interface
b) Business Logic
c) Data Logic
d) Data Access
13. Application server layer in the Web application
architecture is found in ______
a) Tier 1
 b) Tier 2
c) Tier 3
d) Tier 4
14. The ______ package is used to apply the security policy.
a) DBMS_RLS
b) RLS_DBMS
c) PL/SQL
d) Security_Package
15. This model is business-function specific

a) Database Role based

b) Application Role based
c) Application Function based
d) Application Table based

PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
16. Brief about the creation and dropping a role in ORACLE. 5 L3 3 1 1.6.1
o The following statement creates the
role dw_manager:

CREATE ROLE dw_manager;

o You can add a layer of security to roles by

specifying a password, as in the following example:

CREATE ROLE dw_manager IDENTIFIED BY

warehouse;

o The following statement creates global

role warehouse_user:

CREATE ROLE warehouse_user IDENTIFIED

GLOBALLY;

o The following statement creates the same role as an

external role:

CREATE ROLE warehouse_user IDENTIFIED

EXTERNALLY;

To drop the role dw_manager, issue the following

statement

o DROP ROLE dw_manager;

17. Brief about the creation of a SQL server User. 5 L3 3 1 1.6.1

• To create a login id in SQL server can be member of

SYSTEMADMIN OR SECURITYADMIN
 • There are two types of login IDs:

o Windows Integrated (Trusted) Logins

o User can associate a Microsoft Windows account or

group with either the server in which SQL Server is
installed or the domain in which the server is a
member

o SQL Server Login

18. Briefly explain about the different Application Types. 5 L3 4 1 1.6.1

o Mainframe applications
o Client / Server Applications
o Web Applications
o Data warehouse applications

19. Briefly about the Column level Security with SQL Server. 5 L3 4 1 1.6.1
Column level Security with SQL Server

✓ Column level permissions provide a more granular

level of security for data in your database. You do
not need to execute a separate GRANT or DENY
statements for each column; just name them all in a
query:

GRANT SELECT ON data1.table (column1, column2) TO

user1;

GO

DENY SELECT ON data1.table (column3) TO user1;

GO

✓ If you execute a DENY statement at table level to a

column for a user, and after that you execute a
GRANT statement on the same column, the DENY
permission is removed and the user can have access
to that column. Similarly, if you execute GRANT
and then DENY, the DENY permission will be in
force.

PART – C
(2 x 10 = 20 Marks)
Instructions: Answer all the Questions
20. a) Explain the architecture of security data model based on 10 L3 3 1 1.6.1
Application roles.

• Architecture of Security model

10 L3
3 1 1.6.1

• Characteristics

OR

b) Elaborate on the different Application Tables.

21. • Architecture of Security model

• Characteristics

22. a) Elaborate on the creation of Profiles in ORACLE. ✓ L3 4 2 1.6.1

✓ Define a Profile

✓ Resource parameters

✓ Password parameters

✓ Setting Profile Resource Limits

✓ Modify a limit for Profile

✓ Assign a profile

23. OR
b) Define a Database Link. Discuss the different
ways of creating the Database Links. Explain the
different methods of creating a Database Link.
o It is a connection from one DB to another DB

The linked DBs can be like

o Both be ORACLE10g

o Both be SQL Server

o Mix of ORACLE10g and SQL Server

A DB link enables a user to perform Data L3 4 2 1.6.1

Manipulation Language (DML) or any other valid
SQL statements on a DB.

In Oracle 10g ,DB Links can be created in two ways

as

o 1. Public – Which makes the database links

accessible by every user in DB

o 2.Private – Which gives the ownership of the

database to a user

o The DB is not accessible by any other user unless

the user has been access by the owner

o Authentication Method 1: CURRENT USER

o This authentication method orders

ORACLE10g to use the current user
credentials for authentication to the DB to
which the user is trying to link.

o Authentication Method 2: FIXED USER

o This authentication method orders

ORACLE10g to use the user password
provided in this clause for authentication to
the DB to which the user is trying to
link.

o Authentication Method 3: CONNECT USER

o This authentication method orders

ORACLE10g to use credentials of the
connected user who has an existing account
in the database to which the user is
trying to link.
 SRM Institute of Science and Technology
Faculty of Engineering and Technology
SET - C
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA 2 Date: 10-10-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 1 Hour 40 Minutes
Year & Sem: IV / VII Max. Marks: 50

Course Articulation Matrix: (to be placed)

Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO3 1 - 1 2 2 1 - - - - - -

2 CO4 1 - 3 1 1 1 - - - - - -

PART - A
Instructions: Answer all (15 x 1 = 15 Marks)
Q. PI
Questions Marks BL CO PO
No Code
1. External name for___________authenticated user. 1 1 3 4,5 1.3.1
A Global
B Drop
C Expire
D Private
User which interact with the system using database query
2. 1 1 4 3 1.3.1
language is called as
A Application Programmer
B Sophisticated User
C Specialized User
D Naive User
_________ is the indication of how long a password can be
3. 1 1 3 4,5 1.3.1
used before it expires.
A Password Complexity
B Password storage
C Password Usage
D Password Aging
4. Command that comes under DCL is/are - 1 1 3 4,5 1.3.1
A grant
B revoke
C Both A & B
D None of the above
5. ……….. is level 3 access mode in static mode? 1 1 4 3 1.3.1
A update
B use
C grant
D delete
6. Which command used to give privileges to oracle user? 1 1 3 4,5 1.3.1
A grant
B revoke
C expire
 D identify
7. Virtual private database is a function of 1 1 4 3 1.3.1
A Java
B Oracle
C SQL
D DB2
8. What is general syntax to create user in oracle? 1 1 3 4,5 1.3.1
A create user <username> identified by
B create user <user> identified by <password>;
C create user <usdrname> identified by <password>;
D create user <username> <password>;
Row and Column access can be implemented by using the
9. 1 1 4 3 1.3.1
database object.
A Edit
B View
C Delete
D Drag
Which user plays a super role that allows assigned user to
10. 1 1 3 4,5 1.3.1
perform any task within SQL SERVER?
A SYSADMIN
B ADMIN
C SYSSERVER
D SQLSERVER
Which among the following is not included in virtual
11 1 1 4 3 1.3.1
private database?
A Setup Test Environment
B Create an Application Context
C Create Security Policies
D Documentation in administration
12 ………command is used to drop a user who owns objects? 1 1 3 4,5 1.3.1
A CASCADE
B DROP
C GRANT
D DELETE
What does the following code snippet do?
13 1 1 3 4,5 1.3.1
Delete from students where age=15; Rollback;
A Performs an undo operation on the delete operation
B Delete the rows from the table where age=15
C Deletes the entire table
D None of the above
14 Schema definition is written by 1 1 4 3 1.3.1
A Database administrator
B Application programmer
C Sophisticated user
D Naïve user
Business layer level …… layer contains a program that
15 1 1 3 4,5 1.3.1
implements business rules in web application architecture?
A Application layer
B Web application
C Business logic layer
D Service oriented
 PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
16. Explain the process of granting and revoking privileges
in oracle/sql server.
We can GRANT and REVOKE privileges on various
database objects (Table, View) in SQL Server.
Data Control Language is used to control privileges in
Databases. In Data Control Language we have two
commands.
GRANT:- GRANT command is used to provide access
or privileges on the database.
REVOKE:- REVOKE command removes user access
rights or privileges to the database objects. 5 3 3 4,5 1.6.1
You can grant users various privileges to tables. These
permissions can be any combination of SELECT,
INSERT, UPDATE, DELETE, REFERENCES,
ALTER, or ALL.
Example:
GRANT SELECT, INSERT, UPDATE, DELETE ON
employees TO student;
GRANT ALL ON employees TO student;

17.
Summariz
data
method
which
only
informat
authoriz
can
encoded
a way
reae that
i
REVOKE DELETE ON employees FROM student;

Discuss the many components of the database security

model.
Security models are described in terms of the
following elements:
❖ Subjects: Entities that request access to
objects.
❖ Objects: Entities for which access request is
being made by subjects.
❖ Access Modes: Type of operation performed
by subject on object (read, write, create etc.). 5 1 4 3 1.6.1
❖ Policies: Enterprise wide accepted security
rules.
❖ Authorizations: Specification of access modes
for each subject on each object.
❖ Administrative Rights: Who has rights in
system administration and what responsibilities
administrators have.
❖ Axioms: Basic working assumptions.

18. Write notes on the creation, assignment, and

withdrawal of user roles in oracle/sql server.
Roles are a collection of privileges or access rights.
When there are many users in a database it becomes
difficult to grant or revoke privileges to users.
Therefore, if you define roles, you can grant or revoke 5 1 3 4,5 1.6.1
privileges to users, thereby automatically granting or
revoking privileges. You can either create Roles or use
the system roles pre-defined by oracle.
Syntax:
CREATE ROLE role_name
 [IDENTIFIED BY password];
CREATE ROLE testing
Second, grant a CREATE TABLE privilege to the
ROLE testing. You can add more privileges to the
ROLE.
GRANT CREATE TABLE TO testing;
Third, grant the role to a user.
GRANT testing TO user1;
To revoke a CREATE TABLE privilege from
testing ROLE, you can write:
REVOKE CREATE TABLE FROM testing;
The Syntax to drop a role from the database is as
below:
DROP ROLE role_name;
For example: To drop a role called developer, you can

19.
Summariz
data
method
which
only
informat
authoriz
can
encoded
a way
reae that
write:
i
DROP ROLE testing;
Explain in detail data encryption.
Data encryption is a method of protecting data by
encoding it in such a way that it can only be decrypted
or accessed by an individual who holds the correct
encryption key. When a person or entity accesses
encrypted data without permission, it appears
scrambled or unreadable.
How does data encryption work?
The data that needs to be encrypted is termed plaintext
or clear text. The plaintext needs to be passed via some
encryption algorithms, which are mathematical
calculations to be done on raw information. There are
multiple encryption algorithms, each of which differs
by application and security index.

5 1 4 3 1.6.1

Apart from the algorithms, one also needs an

encryption key. Using said key and a suitable
encryption algorithm, the plaintext is converted into the
encrypted piece of data, also known as cipher text.
Instead of sending the plaintext to the receiver, the
cipher text is sent through insecure channels of
communication.
Once the cipher text reaches the intended receiver,
he/she can use a decryption key to convert the cipher
text back to its original readable format i.e. plaintext.
This decryption key must be kept secret at all times,
and may or not be similar to the key used for
encrypting the message.
 PART – C
(2 x 10 = 20 Marks)
20. Explain the following with an appropriate query
1. Create a user (for example, HOD with administrative
privileges).
2. Create a user (for example, a student with limited
privileges).
3. Create a table with the following fields: Name,
Rollno (primary key), Gender, Department, and Mobile
Number (HOD login).
4. Put in five records
5. Delegate select, update privileges from the HOD to a
Student.
6. Revoke the student's privileges
Answer:
Create two users
1. HOD
2. Student
Connect /as sysdba;
Show user;
Create a user (for example, HOD with
administrative privileges).
Create user HOD identified by HOD;
Grant dba,resource to HOD; 10 3 3 4 1.7.1
Create a user (for example, a student with limited
privileges).
Create user student identified by student;
Grant create session to student;
Create a table with the following fields: Name,
Rollno (primary key), Gender, Department, and
Mobile Number (HOD login).
Connect HOD/HOD;
Create table student(name varchar2(15), rollno
number(4) primary key, gender varchar2(15), dept
varchar2(15), mob_no number(10));
//Insert 5 records
Insert into student values
(‘&name’,&rollno,’&gender’,’&dept’,&mob_no);
Select * from student;
//It will list all the records from the table.
Delegate select, update privileges from the HOD to a
Student.
Grant select,update on student to student;
Revoke the student's privileges
Revoke select, update on student from student;
OR
21. What is virtual private database? How can it
be implemented on oracle? What are the
policies involved?
Virtual Private Database(VPD) is the most popular 10 3 4 3 1.7.1
secured database which was introduced by Oracle
Database Enterprise. It is used when the object
privileges and database roles are inadequate to
achieve security requirements. The policies or
protocols are directly proportional to security
requirements.
When a user directly or indirectly accesses a table,
view, or synonym that is protected with an Oracle
Virtual Private Database policy, Oracle Database
dynamically modifies the SQL statement of the user.
This modification creates a WHERE condition (called a
predicate) returned by a function implementing the
security policy. Oracle Database modifies the statement
dynamically, transparently to the user, using any
condition that can be expressed in or returned by a
function. You can apply Oracle Virtual Private
Database policies to SELECT, INSERT, UPDATE,
INDEX, and DELETE statements.
For example, suppose a user performs the following
query:
SELECT * FROM OE.ORDERS;
The Oracle Virtual Private Database policy
dynamically appends the statement with a WHERE
clause. For example:
SELECT * FROM OE.ORDERS WHERE
SALES_REP_ID = 159;
In this example, the user can only view orders by Sales
Representative 159.
Advantages of VPD
✓ Higher Accessibility: Users can easily access
the data from anywhere.
✓ Flexibility: It can be easily modified without
breaking the control flow.
✓ Higher Recovery Rate: The data can be
retrieved very easily.
✓ Dynamically Secured: No need to maintain
complex roles.
✓ No back doors: The security policy is attached
to the data so no by-passing is allowed.
Dis-Advantages of VPD
• Difficult column-level security.
• Oracle account ID is required to use this
service.
• Hard to examine.
1. First we will create the users needed for our
environment
a) Owner of the schema which will have the objects
of tables
b) Security admin_user
c) Non owner user or user which will have limited
access as per data in the rows.
a) Owner of the schema which will have the objects
of tables
SQL> create user schemaowner identified by
schemaowner
default tablespace users temporary tablespace temp
quota unlimited on users;
 The schema owner represents the Oracle user that owns
all your database objects, while application users are
Oracle users that need access to those schema objects.
SQL> grant connect, resource to schemaowner;
grant succeeded.
b) Create Security admin_user
SQL> grant execute on dbms_session to sec_adm;
grant succeeded.
SQL> grant execute on dbms_rls to sec_adm;
grant succeeded.
//RLS is for Row Level Security
c) Create users with restricted access on table
SQL>create user user1 identified by user1
default tablespace users temporary tablespace temp;
user created.
SQL> grant connect,resource to user1;
grant succeeded.
SQL> create user user2 identified by user2
default tablespace users temporary tablespace temp;
user created.
SQL> grant connect,resource to user2;
grant succeeded

create or replace function vpdf1(schema

varchar2,object varchar2) return varchar2
as begin
return
'sname=sys_context("userenv","session_user")';
end;
/
exec
dbms_rls.add_policy('s_owner','emp','india','s_owner','v
pdf');
BEGIN
dbms_rls.add_policy('s_owner','emp','india','s_owner','v
pdf'); END;
22. Describe in detail the creation and application of 10 2,3 3 4 6.1.1
password policies.
Definition-Password Policies:
A password policy is a set of rules designed to enhance
computer security by encouraging users to create and
implement stronger passwords.
Why is a password policy important?
There are significant benefits to having a well-designed
password policy.
✓ Prevent Data Breaches (Safeguarding your
business’ data and customer details is
paramount.)
✓ Maintain Order (A password policy is meant
for everyone using your network, regardless of
their status. )
✓ Build Trust
✓ Cultivate Cybersecurity Culture
What are the most crucial components of a
Password policy?
1. Password Strength- Password strength refers
to the nature of your password. The more
complex your password is, the stronger it is.
2. Password Expiry- Having an expiry date for
passwords encourages users to change their
passwords regularly.
3. Password History- Program your system to
save previously used passwords in users'
password history and restrict them from reusing
those passwords.
Password Change- Users should have the freedom to
change their passwords at any time
Implementing a Password Policy
1. Identifying Settings Related to Password
Policies
There are, at the most basic level, five settings you can
configure that relate to password characteristics:
Enforce password history, Maximum password age,
Minimum password age, Minimum password length,
and Passwords must meet complexity requirements.
Enforce password history determines the number of
unique new passwords a user must use before an old
password can be reused. The value of this setting can
be between 0 and 24; if this value is set to 0, enforce
password history is disabled.
Maximum password age determines how many days a
password can be used before the user is required to
change it. The value of this is between 0 and 999; if it
is set to 0, passwords never expire. Setting this value
too low can cause frustration for your users; setting it
too high or disabling it gives potential attackers more
time to determine passwords.
Minimum password age determines how many days a
user must keep new passwords before they can change
them. This setting is designed to work with the Enforce
password history setting so that users cannot quickly
reset their passwords the required number of times and
then change back to their old passwords. The value of
this setting can be between 0 and 999; if it is set to 0,
users can immediately change new passwords.
Minimum password length determines how short
passwords can be.
Passwords must meet complexity requirements
determines whether password complexity is enforced.
If this setting is enabled, user passwords meet the
following requirements:
1. The password is at least six characters long.
2. The password contains characters from at least
three of the following four categories:
❖ English uppercase characters (A – Z)
❖ English lowercase characters (a – z)
❖ Base 10 digits (0 – 9)
 ❖ Non-alphanumeric (For example: !, $, #, or %)

Some other important factors to create and

implement password policy are
i. Limit login time
ii. Send Email notification
iii. Be impersonal
iv. Avoid repetitive or sequential characters like
111111 or abcd1234
v. Implement multi-factor authentication
vi. Prohibit login sharing
vii. Use a password generator
viii. Use an encrypted database to manage passwords
ix. Reset administrators passwords periodically
x. Use unique, randomly generated passwords

OR
23. Define the security model. Describe several database
application security model types with a clear diagram.
❖ Security models are useful tools for evaluating
and comparing security policies.
❖ Security models allow us to test security
policies for completeness and consistency.
They describe what mechanism are necessary
to implement security policy.
❖ To eliminate threats, it is necessary to define
proper security policy. Security policies are
governing principles adopted by organizations.
❖ They capture the security requirements of an
organization, specify what security properties
the system must provide and describe steps an
organization must take to achieve security.
Security models are described in terms of the
following elements:
❖ Subjects: Entities that request access to
objects. 10 1 4 4 6.1.1
❖ Objects: Entities for which access request is
being made by subjects.
❖ Access Modes: Type of operation performed
by subject on object (read, write, create etc.).
❖ Policies: Enterprise wide accepted security
rules.
❖ Authorizations: Specification of access modes
for each subject on each object.
❖ Administrative Rights: Who has rights in
system administration and what responsibilities
administrators have.
❖ Axioms: Basic working assumptions.
Access Matrix Model
It represents two main entities
1. Objects
2. Subjects
Columns represent objects and rows represent subjects.
Object can be a tables, views, procedures, database
objects.
Subjects can be a users, roles, privileges, modules.
Authorization cells- Access details on the objects
granted to the subject, access, operation, or commands

Access Modes Model

It uses objects and subjects
It specifies access modes: static and dynamic modes
Access levels: A subject has access to objects at its
level and all levels below it.
 SRM Institute of Science and Technology
Faculty of Engineering and Technology SET - D
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA - T1 Date: 06-10-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 2 Hours
Year & Sem: IV / VII Max. Marks: 50

Course Articulation Matrix: (to be placed)

Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO3 3 - - - - - - - - - - -

2 CO4 3 3 - - - - - - - - - -

PART - A
(15 x 1 = 15 Marks)
Instructions: Answer all
Q. Mark PI
Questions BL CO PO
No s Code
1. ------ software program residing on a computer that is used for 1 L1 3 1 1.6.1
data processing and for interfacing to the business logic and
database server.

a) Database server layer

b) Business logic layer
c) Web browser layer
d) Web server layer
2. In these which one is used to enable the user to connect to the 1 L1 3 1 1.6.1
database

a) GRANT SESSION TO EXTERNAL_USER

b) GRANT SESSION IDENTIFIED BY
EXTERNAL_USER
c) GRANT CREATE SESSION TO EXTERNAL_USER
d) GRANT CREATE USER TO EXTERNAL_SESSION
3. ---------------- clause of CREATE USER statement specifies the 1 L1 3 1 1.6.1
storage of the user.

a) TEMPORARY TABLESPACE
b) DEFAULT TABLESPACE
c) QUOTA
d) PROFILE
4. ---------------- clause of CREATE USER statement informs 1 L1 3 1 1.6.1
oracle of how much space a user is allowed for a specified
tablespace.

a) TEMPORARY TABLESPACE
 b) DEFAULT TABLESPACE
c) QUOTA
d) PROFILE
5. Which of the following does not specify the authentication 1 L1 3 1 1.6.1
type?
a) EXTERNAL - CREATE USER user1 IDENTIFIED
EXTERNALLY;
b) INTERNAL - CREATE
USER user1 IDENTIFIED INTERNALLY;
c) GLOBAL - CREATE USER user2 IDENTIFIED
GLOBALLY;
d) PASSWORD - CREATE USER user3 IDENTIFIED
BY user3;
6. When a user logs on to the DB through the machine where 1 L1 3 1 1.6.1
the DB is located, called as _________
a) Local user
b) Internal user
c) External user
d) Remote user
7. Which of the following is not the level of permission in 1 L1 3 1 1.6.1
SQL server?
a) System or Server level
b) Database level
c) Table (Object) level
d) Row level
8. The access mode abrogate occupies the level 1 L1 1 1 1.6.1
a) 1
b) 2
c) 3
d) 4
9. Which component is not found in Client/Server 1 L1 1 1 1.6.1
architecture?
a) User interface
b) Business Logic
c) Object Access
d) Data Access
10. Which of the software program residing on a computer that 1 L1 1 1 1.6.1
Is used for data processing?
a) Web server layer
b) Database server layer
c) Business logic layer
d) Application server layer
11. This model is flexible in implementing application 1 L1 1 1 1.6.1
security.
a) Application Role based
b) Application Function Based
c) Application Role and Function Based
d) Application Table Based
12. In this model Maintenance of application security does not 1 L1 1 1 1.6.1
require specific DB privileges

a) Application Role based

b) Application Function Based
 c) Application Role and Function Based
d) Application Table Based
13. This model uses real database user to log on
a) Database Role based
b) Application Role based
c) Application Function Based
d) Application Role and Function Based
14. The static access mode Create is at Level ______
a) 1
b) 2
c) 3
d) 4
15. This control column Contains the username that created the
record or last updated the record.

a) CTL_INS_USERS
b) CTL_INS_DTIM
c) CTL_REC_USERS
d) CTL_UPD_USERS

PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
16. List out the best practices for Administrators and Managers. 5 L3 3 1 1.6.1
▪ Follow you company ‘s procedures and
policies to create , remove or modify database users.

▪ Always change the default password and

never write it, or save it in a file that neither encrypted
nor safe.

▪ Never share the user accounts with anyone ,

especially DBA accounts.

▪ Always document and create logs for

 changes to removals of database user accounts.

▪ Never remove an account even if it is out

dated, Instead disable or revoke connections privileges
of the account.

▪ Give access permission to users only as

required and use different logins and passwords for
different applications.

▪ Educate users, developers and

administrators on user administration best practices as
well as the company policies and procedures.

▪ Keep abreast (up-to date) of database and

security technology. Should be aware of all new
vulnerabilities that may increase database security
risks.

▪ Constantly review and modify the

procedures as necessary to be in line up with the
company’s policies and procedures. Keep procedures
up to date with the dynamic nature of database and
security technology

17 Brief about the creation of an Oracle user. 5 L3 3 1 1.6.1

• User

• IDENTIFIED clause

• BY Password

• EXTERNALLY clause

• AS 'certificate_DN'

• GLOBALLY Clause

• DEFAULT TABLESPACE Clause

• TEMPORARY TABLESPACE Clause

• QUOTA Clause

• PASSWORD EXPIRE Clause

• ACCOUNT Clause

18. Briefly explain about the Access matrix model and 5 L3 4 1 1.6.1
Access Modes model.
✓ Access Matrix Model
▪ A conceptual model that specifies the
right that each subject
 ▪ possesses for each object
▪ Subjects in rows and objects in columns
Access Modes Model
✓ This model based on the Take-Grant models
✓ It uses both subject and object
✓ Object is the main security entity
✓ Access mode indicates that the subject can
perform any task or not
✓ There are two modes
▪ Static Modes
▪ Dynamic Modes

19. Briefly explain the architecture of Virtual Private 5 L3 4 1 1.6.1

Databases.

PART – C
(2 x 10 = 20 Marks)
Instructions: Answer all the Questions
20. a) Explain the architecture of security data model based on 10 L3 3 1 1.6.1
database roles.

• Control columns and description

• Architecture of a security data model based on
database roles
• Implementation in ORACLE
• Creating Application Owner
• Creating Proxy User
• Creating Application Tables
• Creating Application roles
• Assigning grants
• Add rows to the table
• Add a row for an application user
• Activate the role
• Implementation in SQL server
• Creating Application Roles using the command
line
• Dropping Application Roles

OR
L3
21 b) Elaborate on the different Application types. 10 3 1 1.6.1
• Mainframe applications
• Client / Server Applications
• Web Applications
• Data warehouse applications

22 a) Elaborate on the Granting and Revoking of User 10 L3 4 2 1.6.1

Privileges & Roles.

• System Privileges

• Object privileges

• SQL Grant

• SQL revoke

• Privileges in SQL Server

• Server privileges

• Database privileges

• Creating role with ORACLE

• Assigning role to user

 • Create Roles

• Dropping a role

OR
23.. b) Give the importance of password policies. 10 L3 4 2 1.6.1
Explain in detail about the design and
implementation of password policies.

• Importance of Password Policies

o The frontline defence of your account is

your password.

o If your password is weak, the hacker can

break in, destroy your data, and violate your
sense of security .

o For this specific reason, most of the

companies invest considerable resources to
strengthen authentication by adopting
technological measures that protect their
assets.

• Guidelines

• Password policies in oracle

• Password security parameters

• NTLM

• Kerberos
 SRM Institute of Science and Technology
Faculty of Engineering and Technology SET - A
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA3 Date: 02-11-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 1 Hour 40 Minutes
Year & Sem: IV / VII Max. Marks: 50
Course Articulation Matrix: (to be placed)
Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO4 3 2 2 - - - - - - - - -

2 CO5 2 2 2 - - - - - - - - -

PART - A
(15 x 1 = 15 Marks)
Instructions: Answer all
Q. PI
Questions Marks BL CO PO
No Code
1. Auditing is the responsibility of -------------------------- 1 1 4 4,5 1.3.1
a) Developers
b) DBA
c) Business Managers
d) All of the Above
A document that contains all activities that are being audited
2. ordered in a chronological manner
1 1 4 4,5 1.3.1
a) Auditing
b) Audit objectives
c) Audit log
d) None of the Above
A chronological record of database activities , such as shutdown,
3. start-up, logons, and data structure changes of database objects
1 1 4 4,5 1.3.1
a) Data audit
b) Database auditing
c) Audit report
d) Audit procedure
4. Identify the Components of Auditing Environment 1 1 4 3 1.3.1
a) Objectives & Procedures
b) People & Audited Entries
c) None of the Above
d) All of the Above
The first auditing model is called -------------------because it is
5. easy to understand and develop
1 1 4 3 1.3.1
a) User Friendly
b) Simple
c) Flexibility
d) Effective
The National Security Administration has given a C2 security
6. rating to
1 1 4 3 1.3.1
a) Microsoft SQL Server 2000
b) MY SQL
c) ORACLE 10G
 d) MONGO DB
The _______________ triggers will be fired before the INSERT,
7. UPDATE, or DELETE operation
1 1 4 4,5 1.3.1
a) Delete
b) Update
c) Insert
d) All of the Above
8. What is the syntax of DROP TRIGGER statement? 1 1 4 4,5 1.3.1
a) DELETE TRIGGER trigger_name;
b) REMOVE TRIGGER trigger_name;
c) DROP TRIGGER trigger_name;
d) None of the Above
9. PPDM means 1 1 5 4,5 1.3.1
a) Privacy preserving data mining
b) Privacy producing data mining
c) Privacy preserving data modeling
d) Privacy producing data modeling
10. In which method noise is added to data 1 1 5 4,5 1.3.1
a) The randomization method
b) The k-anonymity model and l-diversity
c) Distributed privacy preservation
d) Downgrading Application Effectiveness
The values across different records are swapped in order to
11 perform the privacy-preservation in
1 1 5 4,5 1.3.1
a) Data mapping
b) Data swapping
c) Data swapping and mapping
d) Data ordering
12 K-anonymity techniques uses 1 1 5 3 1.3.1
a) Generalization
b) Suppression
c) Generalization and suppression
d) Randomization
The individual records are spread out across multiple entities,
13 each of which have the same set of attributes are in
1 1 5 3 1.3.1
a) Vertically partitioned
b) Horizontally partitioned
c) Diagonally partitioned
d) Randomly partitioned
In association rule hiding if the entry for a given transaction is
14 modified to a different value then it is called as
1 1 5 4,5 1.3.1
a) Blocking
b) Aborting
c) Distortion
d) Hiding
15 Which one is not suitable fork-anonymity techniques uses in 1 1 5 3 1.3.1
a) Generalization
b) Suppression
c) Generalization and suppression
d) Randomization
 PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
Mar PI
Q. No Questions ks
BL CO PO
Code
16. Describe the need of auditing database? In what are 5 2 4 2 1.6.1
the ways it can be audited?

Auditing a database is a critical aspect of maintaining

data security, integrity, and compliance. There are
several reasons why auditing a database is necessary:

1.Data Security: Auditing helps in identifying

unauthorized access to the database. It can track
who accessed the data, what changes were
made, and when those changes occurred. This is
essential for detecting and preventing security
breaches and data leaks.
2. Compliance: Many industries and
organizations are subject to regulatory
requirements, such as HIPAA in healthcare,
GDPR in Europe, or SOX for publicly traded
companies. Auditing databases is crucial to
demonstrate compliance with these regulations
by providing an audit trail of data access and
changes.

3.Data Integrity: Auditing ensures the integrity

of the data by monitoring changes and helping
to prevent data corruption or fraudulent
activities.

4. Troubleshooting and Debugging: Auditing

can be helpful for diagnosing and resolving
issues within the database. It allows
administrators to trace problems, understand
what went wrong, and identify the responsible
parties.

5. Accountability: Database auditing promotes

accountability among users and administrators.
When individuals know that their actions are
being monitored, they are more likely to follow
best practices and adhere to security and data
management policies.

There are several ways to audit a database, depending

on the database management system (DBMS) in use
and the specific needs of the organization. Here are
 common methods for auditing databases:

1. Database Logs: Most DBMSs maintain logs

that record activities, such as login attempts,
SQL statements executed, and changes to data.
These logs can be reviewed to track database
access and modifications.

2. Database Triggers: Triggers are database

objects that can be set to automatically perform
actions, like logging changes to a separate audit
table, whenever specific events occur in the
database.

3. Database Audit Trails: Some DBMSs offer

built-in auditing features that allow you to
define and configure audit policies, specifying
which actions to audit, and where to store the
audit data.

4. Third-Party Auditing Tools: There are

specialized software tools and solutions
designed for auditing databases. These tools
often provide more advanced features and
reporting capabilities.

5. Manual Review: Database administrators and

security personnel can manually review
database logs and other records to identify
unauthorized access and changes. However, this
can be time-consuming and less efficient than
automated methods.

6. Intrusion Detection Systems (IDS): IDS can

be employed to monitor database activity and
trigger alerts when suspicious or unauthorized
activities occur.

It's important to note that the choice of auditing method

and the extent of auditing implemented should be based
on the specific security and compliance requirements of
the organization. Regularly reviewing and analyzing
the audit data is also crucial to detect and respond to
security incidents and maintain the integrity of the

17.
Summariz
data
method
which
only
informat
authoriz
can
encoded
a way
reae that
database.
i
Explain triggers in oracle? How do you create a 5 2 4 3 1.6.1
trigger using oracle?

✓ Trigger is an event driven program Executed

automatically based on event occurs
 ✓ ORACLE has six DML events also known as
trigger timings
✓ Trigger mainly used for the following purposes
✓ Performing audits (Primary use) and Preventing
invalid data from being inserted into the tables
✓ Implementing business rules (Not highly
recommended if the business rule is complex)
Generating values for columns

Trigger Syntax
CREATE [ OR REPLACE ] TRIGGER
<trigger_name>
[BEFORE | AFTER | INSTEAD OF ]
[INSERT | UPDATE | DELETE......]
ON<name of underlying object>
[FOR EACH ROW]
[WHEN<condition for trigger to get execute> ]
DECLARE <Declaration part>
BEGIN <Execution part>
EXCEPTION <Exception handling part>
END;

Example : Row level Trigger

CREATE OR REPLACE TRIGGER
customers_update_credit_trg
BEFORE UPDATE OF credit_limit
ON customers
FOR EACH ROW
WHEN (NEW.credit_limit > 0)
BEGIN
-- check the credit limit
IF :NEW.credit_limit >= 2 * :OLD.credit_limit
THEN
raise_application_error(-20101,'The new credit ' ||
:NEW.credit_limit ||
' cannot increase to more than double, the
current credit ' || :OLD.credit_limit);
END IF;
END;

18. Explain the applications of privacy preserving data 5 2 5 2 1.6.1

mining?
Privacy-preserving data mining (PPDM) is a field of
research and practice that focuses on mining useful
patterns and insights from data while protecting the
privacy of individuals or entities whose data is being
analyzed. PPDM techniques are essential in scenarios
where sensitive information needs to be kept
confidential. Here are some key applications of
privacy-preserving data mining:

1.Healthcare:

Medical Research: Researchers can analyze patient

records or genomic data while preserving patient
privacy.
Disease Surveillance: Public health agencies can
monitor disease outbreaks without revealing individual
patients' identities.
Finance:

2.Fraud Detection: Banks and financial institutions can

detect fraudulent transactions while preserving
customer privacy.
Credit Scoring: Credit agencies can assess
creditworthiness without exposing personal financial
details.
Market Research:

3.Customer Segmentation: Companies can analyze

customer behavior without disclosing individual
identities.
Targeted Advertising: Advertisers can target specific
demographics without revealing personal data.
Social Networks:

4.Privacy-Preserving Analytics: Social media platforms

can analyze user interactions and content preferences
without exposing personal details.
Anonymized User Studies: Researchers can conduct
studies on user behavior while protecting user
identities.
Government and Law Enforcement:

5.Crime Analysis: Law enforcement agencies can

analyze crime data without revealing the identities of
victims and witnesses.
Census Data: Governments can release anonymized
census data for research and policy planning.
Machine Learning:

6.Federated Learning: Organizations can collaborate on

building machine learning models without sharing
sensitive training data.
Homomorphic Encryption: Machine learning models
can be trained on encrypted data, preserving privacy
during model development.
IoT and Smart Devices:

7.Smart Home Analytics: Companies can analyze smart

home data without exposing personal activities or
habits.
Healthcare Monitoring: Remote patient monitoring can
be conducted while preserving health data privacy.
Data Sharing:

8.Secure Data Sharing: Organizations can share data

with partners or third parties while ensuring that
 sensitive information remains protected.
Data Aggregation: Data from multiple sources can be
combined without exposing individual source details.
Collaborative Research:

9.Secure Multi-party Computation: Researchers from

different organizations can collaborate on data analysis
projects while preserving data privacy.
Confidential Data Exchange: Institutions can share
research data without exposing confidential
information.
Personalization:

10.Recommender Systems: Online platforms can

provide personalized recommendations without
revealing individual preferences.
Personalized Healthcare: Healthcare providers can
tailor treatment plans without exposing patient medical
histories.

Privacy-preserving data mining techniques include

differential privacy, secure multiparty computation,
homomorphic encryption, and data anonymization
methods. These methods help strike a balance between
the need to extract valuable insights from data and the
imperative to protect individuals' privacy, particularly
in the face of increasing data collection and analysis
activities.

19. Apply the concepts of Horizontal Partitioning in 5 3 5 4 1.7.1

data mining with Example?

Horizontal partitioning in data mining involves

dividing a large dataset into smaller, more manageable
subsets based on certain criteria or attributes. This
technique can be helpful for improving data analysis,
reducing computational complexity, and managing data
storage more efficiently. Here's an example to illustrate
horizontal partitioning in data mining:

Example:
Let's consider a large e-commerce database that
contains information about millions of products,
including their attributes like product ID, name,
category, price, manufacturer, and customer reviews.
This dataset is used for various data mining tasks such
as market basket analysis, recommendation systems,
and customer segmentation.

Horizontal partitioning can be applied in the following

way:

1. Data Partitioning Criteria:

We decide to horizontally partition the dataset based on
 product categories. Each partition will contain products
belonging to a specific category. For example, we
might have partitions for "Electronics," "Clothing,"
"Home and Garden," "Toys," and so on.

2. Partition Creation:
Data mining experts create separate datasets or tables
for each category. For instance:

Electronics:
Product ID
Name
Category
Price
Manufacturer
Customer Reviews
Clothing:

Product ID
Name
Category
Price
Manufacturer
Customer Reviews
Home and Garden:

Product ID
Name
Category
Price
Manufacturer
Customer Reviews
Toys:

Product ID
Name
Category
Price
Manufacturer
Customer Reviews
3. Data Mining Analysis:
With the dataset divided into partitions, data mining
tasks can be performed more efficiently. For example,
when running a recommendation system for
electronics, the system only needs to access the
"Electronics" partition, reducing the computational load
and improving query performance. Similarly,
marketing campaigns or customer segmentation can be
applied to each partition independently.

PART – C
(2 x 10 = 20 Marks)
20. Explain in detail about how the database activities
10 2 4 2 6.2.1
are audited using oracle?
 ✓ ORACLE provides the mechanism for auditing
everything:
▪ From tracking who is creating and
modifying the structure
▪ Who is granting privileges to whom
✓ The activities are divided into two types based
on the type of SQL command statement used :
▪ Activities defined by DDL (Data
Definition Language)
▪ Activities defined by DCL (Data Control
Language)
Auditing DDL Activities
✓ ORACLE uses a SQL-based audit command
✓ The following figure presents the audit syntax
diagram ( ORACLE 10g)

Audit command syntax

AUDIT
{
{ { statement_option | ALL }
[,{statement_option | ALL }] ……
|,{syetem_privilege | ALL PRIVILEGES }
}
[BY { proxy [,proxy]…..
| user [,user]…..
]
|
{Object_option [, object_option ] …… | ALL }
ON { [ schema. ] object
|DIRECTORY directory_name
|DEFAULT
}
}
[ BY {SESSION | ACCESS } }
[WHENEVER [NOT] SUCESSFUL ] ;
Where :
Statement option – Tells ORACLE to audit the
specified DDL or DCL statement
DDL – CREATE, ALTER, DROP and TRUNCATE
DCL – GRANT , REVOKE
System privilege – Tell ORACLE to audit the specified
privilege such as SELECT, CREATE ANY, or ALTER
ANY
Object_option – Specifies the type of privileges for the
specified object to be audited
BY SESSION – Tells ORACLE to record audit data
once per session even if the audited statement issued
multiple times in session
BY ACCESS - Tells ORACLE to record audit data
every time audited statement is issued.
WHENEVER SUCCESSFUL – Tells ORACLE to
capture audit data only when the audited command is
successful
WHENEVER NOT SUCCESSFUL- Tells ORACLE to
capture audit data only when the audited command fails

DDL activities Example

Step 1 : Use any user other than SYS or SYSTEM to

create the CUSTOMER
SQL> CREATE TABLE CUSTOMER
2 (
3 ID NUMBER ,
4 NAME VARCHAR2 (20),
5 CR_LIMIT NUMBER
6 );
Table created
Step 2 : Add three rows into the CUSTOMER table
and commit changes
SQL > INSERT INTO CUSTOMER VALUES (2,
‘BMNANTHA’, 200);
1 row created
SQL > INSERT INTO CUSTOMER VALUES (3,
‘MURUGAN’, 300);
1 row created
SQL > INSERT INTO CUSTOMER VALUES (1,
‘GANESH’, 100);
1 row created
SQL > COMMIT;
Commit complete
Step 3 : Log on as SYS or SYSTEM to enable
auditing , as specified in this example
the first statement for ALTER and the next is
for DELETE
SQL > CONNECT SYSTEM @ SEC
Enter password : ******
Connected.
SQL > AUDIT ALTER ON DBSEC.CUSTOMER BY
ACCESS WHENEVER
2 SUCCESSFUL;
Audit succeeded.
SQL > AUDIT DELETE ON DBSEC.CUSTOMER
BY ACCESS WHENEVER
2 SUCCESSFUL;
Audit succeeded.
 DCL Activities Example:
✓ You are auditing the GRANT privilege issued
on a TEMP table owned by DBSEC.
✓ The following steps shows how to audit the
DCL statements audited.
✓ The same steps to be followed for all DCL
Commands.
Step 1 : Log on as SYSTEM or SYS and issue an
AUDIT statement as follows

SQL> CONN SYSTEM

Enter password : ******
Connected
SQL> DELETE SYS.AUD$;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> AUDIT GRANT ON DBSEC.TEMP;
Audit succeeded
Step 2: Log on as DBSEC and grant SELECT and
UPDATE privileges to SYSTEM on
TEMP table
SQL> CONN DBSEC
Enter password : *****
Connected.
SQL> GRANT SELECT ON TEMP TO SYSTEM;
Grant succeeded.
SQL> GRANT UPDATE ON TEMP TO SYSTEM
Grant succeeded.

OR
21. Compare and Contrast the difference between 10 2 4 2 2.2.4
oracle server and SQL server 2000 in auditing
database?
Oracle Server and SQL Server 2000 are two popular
relational database management systems (RDBMS),
and both offer auditing capabilities to track and monitor
database activities for security and compliance
purposes. However, there are significant differences
between the two in terms of how they handle database
auditing.

Auditing Model:

Oracle:

Oracle Database offers a comprehensive auditing

framework that allows fine-grained control over audit
policies and events. You can audit various types of
events, such as SELECT, INSERT, UPDATE,
DELETE, and administrative actions like user logins.
Auditing can be enabled at the database, schema, or
object level, providing a high level of granularity.
Oracle provides both standard database auditing and
unified auditing, which consolidates audit data into a
single location for easier management.
SQL Server 2000:

SQL Server 2000 provides a basic auditing mechanism

called SQL Server Profiler, which is primarily used for
performance monitoring and tracing rather than security
auditing.
It lacks the fine-grained auditing capabilities of Oracle
and doesn't offer built-in auditing for data manipulation
actions like SELECT statements.
Security Features:

Oracle:

Oracle provides robust security features, including role-

based access control, encryption, and advanced
authentication mechanisms.
Auditing in Oracle is tightly integrated with these
security features, allowing you to control who can
enable and manage auditing policies.
SQL Server 2000:

SQL Server 2000 also offers security features like user

roles and permissions, but it lacks some of the
advanced security features found in later versions of
SQL Server.
Compliance and Reporting:

Oracle:

Oracle Database provides tools like Oracle Audit Vault

and Database Firewall for centralized audit data
management and reporting.
These tools enable more comprehensive auditing for
compliance with various regulations and standards.
SQL Server 2000:

SQL Server 2000 lacks centralized audit data

management and reporting tools, making it less suitable
for compliance requirements. You would need to
develop custom solutions or rely on third-party tools
for compliance reporting.
Database Version:

SQL Server 2000 is quite outdated, and it's essential to

note that Microsoft has released several newer versions
of SQL Server with improved auditing capabilities and
enhanced security features. It is highly recommended to
upgrade to a more recent version for better auditing and
security.
Performance Impact:
 The performance impact of auditing in both systems
can vary based on the level of auditing and the database
workload. Oracle's unified auditing is designed to have
a lower performance overhead compared to traditional
auditing methods.

In summary, Oracle Database offers more robust and

fine-grained auditing capabilities compared to SQL
Server 2000, which has limited auditing features. If
you're working with SQL Server, it is strongly
recommended to consider upgrading to a more recent
version of SQL Server for improved auditing and
security features, as SQL Server 2000 is no longer
supported and lacks many of the modern security
features.
22. Illustrate the privacy preserving algorithms 10 2 5 5 6.1.1
available in data mining? Explain anyone in detail?

Privacy-preserving data mining refers to the practice of

conducting data mining and analysis on sensitive or
private data while ensuring the confidentiality and
privacy of the data subjects or information contained
within the dataset. It involves the development and
implementation of techniques and methods to extract
valuable insights, patterns, and knowledge from data,
while minimizing the risk of disclosing sensitive
information.
✓ Statistical Methods for Disclosure Control
✓ Measures of Anonymity
✓ The k-anonymity Method
✓ The Randomization Method
✓ Quantification of Privacy
✓ Utility Based Privacy-Preserving Data Mining
✓ Mining Association Rules under Privacy
Constraints
✓ Cryptographic Methods for Information Sharing
and Privacy
✓ Privacy Attacks
✓ Query Auditing and Inference Control
✓ Privacy and the Dimensionality Curse
✓ Personalized Privacy Preservation
Privacy-Preservation of Data Streams
OR
23. Justify, Why the data mining techniques preferred 10 3 5 2 1.7.1
for preserving privacy?

Data mining techniques are often preferred for

preserving privacy because they allow organizations to
extract valuable insights and patterns from data while
minimizing the risk of exposing sensitive or personally
identifiable information (PII). Here are several reasons
why data mining techniques are considered valuable for
privacy preservation:
1. Anonymization and Data Masking: Data mining
methods can be used to anonymize or mask
sensitive information. For example, by
aggregating or generalizing data, it becomes
much more challenging to identify individuals
or reveal personal details. This allows
organizations to use data for analysis without
exposing private information.

2. Differential Privacy: Differential privacy is a

mathematical framework that can be integrated
with data mining algorithms to ensure that the
inclusion or exclusion of a specific data point
does not significantly impact the results. This
technique adds noise to the data to protect
individual privacy while still providing accurate
aggregate insights.

3. Secure Multiparty Computation (SMC): SMC

techniques enable multiple parties to jointly
compute functions on their individual datasets
without revealing the underlying data to each
other. This approach allows organizations to
collaborate and mine data while maintaining the
privacy of their data sources.

4. Homomorphic Encryption: Homomorphic

encryption allows computations to be performed
on encrypted data without decrypting it first.
This enables data mining on encrypted data,
reducing the risk of data exposure during the
analysis.

5. Data Perturbation: Data perturbation techniques

introduce controlled noise or distortion to the
data before analysis, making it challenging for
adversaries to reverse engineer the original data
or identify individuals. This protects privacy
while still enabling meaningful analysis.

6. K-anonymity and L-diversity: These are

privacy-preserving techniques that ensure that
individual records in a dataset cannot be easily
distinguished from a group of at least k
individuals. This helps protect privacy by
making it more challenging to identify specific
individuals within the data.

7. Limited Data Disclosure: Data mining

techniques can be employed to extract
aggregated or summarized information from a
dataset, rather than exposing raw or granular
data. This reduces the risk of privacy breaches
 by only revealing essential insights.

8. Privacy-Preserving Machine Learning:

Techniques like federated learning and secure
multi-party computation enable machine
learning models to be trained on distributed data
sources without sharing the raw data. This
protects the privacy of the data while still
allowing model development.

9. Data Minimization: Data mining encourages

organizations to only collect and retain the data
necessary for their specific analysis or business
purposes. This reduces the volume of sensitive
data that needs protection and limits the
potential privacy risks.

10. Regulatory Compliance: Many data protection

regulations, such as the General Data Protection
Regulation (GDPR) in Europe, require
organizations to implement privacy-preserving
measures. Using data mining techniques for
privacy protection can help organizations
comply with these regulations.

In summary, data mining techniques provide a range of

methods to balance the need for data analysis and
insights with the imperative to protect individual
privacy. By applying these techniques, organizations
can derive valuable knowledge from data while
minimizing the risk of exposing sensitive information,
meeting legal requirements, and respecting individuals'
privacy rights.
 SRM Institute of Science and Technology
Faculty of Engineering and Technology SET - B
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA – T3 Date: 02-11-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 1 Hour 40 Minutes
Year & Sem: IV / VII Max. Marks: 50

Course Articulation Matrix: (to be placed)

Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO3 1 - 1 2 2 1 - - - - - -

2 CO4 1 - 3 1 1 1 - - - - - -

PART - A
(15 x 1 = 15 Marks)
Instructions: Answer all
Q. PI
Questions Marks BL CO PO
No Code
---------------- tool is used for purpose of data auditing for
1. SQL Server only? 1 1 3 4,5 1.3.1

A Apex SQL
B SQL Ninja
C SQL Audit
D Idera
----------- command used to turn off the audit?
2. 1 1 3 4,5 1.3.1
A REVOKE AUDIT
B NO AUDIT
C COMMIT Audit
D DBA_AUDIT_STATEMENT
------------ catalog view is used for SQL Server Extended
3. Events? 1 1 3 4,5 1.3.1

A sys.server_sessions
B sys.server_event_sess
C sys.server_event_session_actions
D All of the mentioned
Auditing activities conducted by the staff members of the
4. organization. 1 1 4 3 1.3.1

A Internal Auditing
B External auditing
C Security audit
D Financial audit
----------- tool provides the user interface for auditing
5. events in SQLServer 2000? 1 1 4 3 1.3.1
 A SQL profiler
B SQL Ninja
C SQL Audit
D SQL Idera
__________ , the attacker has a collection of independent
data samples from the same distribution from which the
6. 1 1 4 3 1.3.1
original data was drawn

A Known Sample Attack

B Packet sniffer
C Distributed denial of service
D Man in the middle Attack
________________ method has been proposed for
computing a k-minimal generalization with the use of
7. bottom-up aggregation along domain generalization 1 1 3 4,5 1.3.1
hierarchies.

A Incognito
B l-diversity
C l-closeness
D k-anonymity
In this case, the participants Alice and Bob are curious and
attempt to learn from the information received by them
8. during the protocol, but do not deviate from the protocol 1 1 3 4,5 1.3.1
themselves.

A Malicious
B Semi-Honest Adversaries
C Distributed denial of service
D Man in the middle Attack
In _________ the entry is not modified but is left
incomplete. Thus, unknown entry values are used to
9. 1 1 3 4,5 1.3.1
prevent discovery of association rules.

A Additive perturbation
B Multiplicative perturbation
C Blocking
D Distortion
The ___________ System was one of the earliest practical
10. applications of privacy preserving transformations. 1 1 3 4,5 1.3.1

A Datafly
B Homeland Security Applications
C Video Surveillance
D Watch list Problem
11 What is the k-anonymity method? 1 1 3 4,5 1.3.1
A A method for privacy de-identification.
B A method for measuring privacy.
C A method for privacy preservation.
D None of the above.
12 SQL Profiler tool used. 1 1 4 3 1.3.1
A User interface for auditing
 B Modification of attributes
C Change of table name
D Deleting table
Which one of the following team retested every database
13 1 1 4 3 1.3.1
application function and try to find bugs?
A Quality assurance
B Quality control
C Quality testing
D Quality manager
An audit that is conducted by a staff member of the
14 1 1 3 4,5 1.3.1
company being audited.
A External audit
B Company audit
C Internal audit
D Policy audit
In randomization technique, large volume of data analysis
15 1 2 4 3 1.3.1
can be done using
A PCA technique
B CPA technique
C APP technique
D DAA technique

PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
16. Describe the purpose of the SQL Profiler Tools

SQL Profiler is used to:

• analyze the application

• Determine the optimality of requests sent to the server

• Identify Transact-SQL commands that generate an 5 2 3 4 1.6.1

error

• Collect information about user activity over a long

period of time

• Monitor the server operation in real time

Summariz
authoriz
only
informat
which
17. List the steps for determining the location of the audit
trail records

1.Open the Audit Query widget.

2.To use an existing query, click the query selector and

5 1 4 3 1.6.1
choose one from the drop down list.

3.To create a new query, click Add Query Parameter

and select a column name from the drop down list. ...
 4.Click Search at the bottom of the widget. ...

5.Click Find to look for specific values or fields within

the search results.

18. What are top ten database auditing objectives?

▪ Data Integrity – Ensure that data is valid

and in full referential integrity

▪ Applications Users and roles – Ensures

that users are assigned roles that correspond to their
responsibilities and duties

▪ Data Confidentiality – Identify who can

read data and what data can be read

▪ Access Control – Ensures that the

application records times and duration when a user logs
onto the database or application

▪ Data changes – Create an audit trail of

all data changes

▪ Data Structure Changes – Ensures that 5 2 4 3 1.6.1

the database logs all data structure changes

▪ Database or application availability –

Record the number of occurrences and duration of
application or database shutdowns all the start-up times
. Also, record all reason for any unavailability.

▪ Change Control – Ensure that a change

control mechanism is incorporated to track necessary
and planned changes to the database or application.

▪ Physical Access – Record the physical

access to the application or the database where the
software and hardware resides.

▪ Auditing Reports – Ensure that reports

are generated on demand or automatically , showing all
auditable activities

19. What are the two kinds of attacks are possible with
some prior knowledge?

✓ Known Input-Output Attack:

5 1 3 4 1.6.1
✓ In this case, the attacker knows
some linearly independent
collection of records, and their
 corresponding perturbed version.
In such cases, linear algebra
techniques can be used to
reverse-engineer the nature of the
privacy preserving
transformation.

✓ Known Sample Attack:

✓ In this case, the attacker has a

collection of independent data
samples from the same
distribution from which the
original data was drawn. In such
cases, principal component
analysis techniques can be used
in order to reconstruct the
behaviour of the original data.

PART – C
(2 x 10 = 20 Marks)
20. Describe the distributed algorithm for k-anonymity?

In many applications, the data records are

made available by simply removing key

identifiers such as the name and social-

security numbers from personal records.

�other kinds of attributes (known as pseudo-

identifiers) can be used in order to accurately

identify the records.

10 1 3 5 6.1.1
�For example, attributes such as age,

zip-code and gender are available in

public records such as census rolls.

�When these attributes are also

available in a given data set, they can

be used to infer the identity of the

corresponding individual.

A combination of these attributes can be very

 powerful, since they can be used to narrow down the

possibilities to a small number of individuals

�k-anonymity approach can be formalized as

follows:

�Each release of the data must be such

that every combination of values of

quasi-identifiers ( are pieces of

information that are not of themselves

unique identifiers) can be

indistinguishably matched to at least

k respondents.

�The first algorithm for k-anonymity

approach uses domain generalization

hierarchies of the quasi-identifiers in

order to build k-anonymous

tables.

�The concept of k-minimal generalization has

been proposed in order to limit the level of

generalization for maintaining as much data

precision as possible for a given level of

anonymity.

OR
21. Explain in detail about advanced auditing model?

Advanced Auditing Model

✓ This Model is called “advanced” because of its

flexibility

✓ More flexible than simple models 10 1 3 4 2.2.4

✓ Used as an auditing application with a user

interface

✓ Of course the repository for tis model is more

complex than previous models
 ✓ It contains data stores to register all entities that
can be audited

Historical Data Model

✓ This model is used for applications that require

a record of the whole row when a DML
transaction is performed on the table

✓ Typically used in most financial applications

✓ With this model , the whole row is stored in the

HISTORY table, before it is changed or deleted

The following figures illustrates this model

 Auditing Application Actions Model

✓ There may be a requirement for an application

to audit specific operations or actions

✓ The following figure represents a Data Model of

a repository for auditing application actions

22. Explain l-diversity method and t-closeness model in

detail?

l-diversity method:
✓ The k-anonymity is an attractive technique
because of the simplicity of the definition and
the numerous algorithms available to perform
the anonymization.
✓ The k-anonymity is an attractive technique
because of the simplicity of the definition and
the numerous algorithms available to perform
the anonymization.
✓ Nevertheless the technique is susceptible to
many kinds of attacks especially when 10 3 4 4 6.1.1
background knowledge is available to the
attacker
✓ Some kinds of such attacks are as follows:
▪ Homogeneity Attack:
✓ In this attack, all the values for a
sensitive attribute within a group
of k records are the same.
Therefore, even though the data
is k-anonymized, the value of the
sensitive attribute for that group
of k records can be predicted
exactly.
▪ Background Knowledge Attack:
 ✓ In this attack, the adversary can
use an association between one
or more quasi-identifier
attributes with the sensitive
attribute in order to narrow down
possible values of the sensitive
field further
✓ While k-anonymity is effective in preventing
identification of a record, it may not always be
effective in preventing inference of the sensitive
values of the attributes of that record.
✓ Therefore, the technique of l-diversity was
proposed which not only maintains the
minimum group size of k, but also focuses on
maintaining the diversity of the sensitive
attributes.
✓ Therefore, the l-diversity model for privacy is
defined as follows:
▪ Let a q∗-block be a set of tuples such
that its non-sensitive values generalize
to q∗.
▪ A q∗-block is l-diverse
✓ if it contains l “well
represented” values for the
sensitive attribute S.
✓ A table is l-diverse, if every q∗-
block in it is l-diverse.
✓ when there are multiple sensitive
attributes, then the l-diversity
problem becomes especially
challenging because of the curse
of dimensionality.
t-closeness model:
• The t-closeness model is a further enhancement
on the concept of l-diversity.
• One characteristic of the l-diversity model is
that it treats all values of a given attribute in a
similar way irrespective of its distribution in the
data.
• A t-closeness model was proposed which uses
the property that the distance between the
distribution of the sensitive attribute within an
anonymized group should not be different from
the global distribution by more than a threshold
t.

OR
23. Explain different applications of privacy-preserving
data mining methods?
10 3 4 5 1.7.1
✓ Medical Databases: The Scrub and
Datafly Systems
 ✓ Bioterrorism Applications
✓ Homeland Security Applications
✓ Genomic Privacy
Medical Databases: The Scrub and Datafly Systems
Scrub :
✓ The scrub system was designed for de-
identification of clinical notes and letters which
typically occurs in the form of textual data.
✓ Clinical notes and letters are typically in the
form of text which contain references to
patients, family members, addresses, phone
numbers or providers.
✓ Traditional techniques simply use a global
search and replace procedure in order to provide
privacy.
✓ However clinical notes often contain cryptic
references in the form of abbreviations which
may only be understood either by other
providers or members of the same institution.
✓ Therefore traditional methods can identify no
more than 30-60% of the identifying
information in the data
✓ The Scrub System uses local knowledge sources
which compete with one another based on the
certainty of their findings.
✓ Such a system is able to remove more than 99%
of the identifying information from the data.
Datafly Systems:
✓ The Datafly System was one of the earliest
practical applications of privacy-preserving
transformations.
✓ This system was designed to prevent
identification of the subjects of medical records
which may be stored in multidimensional
format.
✓ The multi-dimensional information may include
directly identifying information such as the
social security number, or indirectly identifying
information such as age, sex or zip-code.
✓ The system was designed in response to the
concern that the process of removing only
directly identifying attributes such as social
security numbers was not sufficient to guarantee
privacy.
Bioterrorism Applications
✓ Often a biological agent such as anthrax
produces symptoms which are similar to other
common respiratory diseases such as the cough,
cold and the flu.
✓ In the absence of prior knowledge of such an
attack, health care providers may diagnose a
patient affected by an anthrax attack of have
symptoms from one of the more common
respiratory diseases.
 ✓ In order to identify such attacks it is necessary
to track incidences of these common diseases as
well.
✓ Therefore, the corresponding data would need
to be reported to public health agencies.
However, the common respiratory diseases are
not reportable diseases by law.
Homeland Security Applications
✓ A number of applications for homeland
security are inherently intrusive because
of the very nature of surveillance.
✓ Some examples of such applications are
as follows:
✓ Credential Validation
Problem:
✓ Trying to match the
subject of the credential
to the person presenting
the credential.
✓ For example, the theft of
social security numbers
presents a serious threat
to homeland security.
✓ Identity Theft:
✓ A related technology is to
use a more active
approach to avoid
identity theft.
✓ The identity angel system
, crawls through
cyberspace, and
determines people who
are at risk from identity
theft.
✓ This information can be
used to notify appropriate
parties.
Genomic Privacy
• Recent years have seen tremendous advances in
the science of DNA sequencing and forensic
analysis with the use of DNA.
• As result, the databases of collected DNA are
growing very fast in the both the medical and
law enforcement communities.
• DNA data is considered extremely sensitive,
since it contains almost uniquely identifying
information about an individual.
• As in the case of multi-dimensional data, simple
removal of directly identifying data such as
social security number is not sufficient to
prevent re-identification.
• It has been shown that a software called
CleanGene can determine the identifiability of
DNA entries independent of any other
 demographic or other identifiable information.
• The software relies on publicly available
medical data and knowledge of particular
diseases in order to assign identifications to
DNA entries.
• Another method for compromising the privacy
of genomic data is that of trail re-identification,
in which the uniqueness of patient visit patterns
is exploited in order to make identifications.
 SRM Institute of Science and Technology
Faculty of Engineering and Technology SET - C
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA3 Date: 02-11-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 1 Hour 40 Minutes
Year & Sem: IV / VII Max. Marks: 50
Course Articulation Matrix: (to be placed)
Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO4 3 2 2 - - - - - - - - -

2 CO5 2 2 2 - - - - - - - - -

PART - A
(15 x 1 = 15 Marks)
Instructions: Answer all
Q. PI
Questions Marks BL CO PO
No Code
1. Administration activities encompasses of 1 1 4 4,5 1.3.1
a) SQL statements issued against application tables.
b) Commands issued by the database administrators
c) Commands issued by the operators for maintenance
d) None of the Above
2. AFTER LOGON and BEFORE LOGON commands included in 1 1 4 4,5 1.3.1
a) DDL statements
b) DCL statements
c) Database events
d) SQL statements audit trail
Which one of the following tells Oracle to record audit data
3. every time the audited statement is issued
1 1 4 4,5 1.3.1
a) By Session
b) By Access
c) Whenever Successful
d) Whenever Not Successful
Selecting the _________ option can allow unaudited activity
4. which could violate your security policies
1 1 4 3 1.3.1
a) Fail
b) Shut down
c) Continue
d) Break
Grant ________ permission on the stored procedures to database
5. roles for implementing call level security
1 1 4 3 1.3.1
a) Execute
b) Exec
c) Read
d) Write
6. Trigger is special type of __________ procedure 1 1 4 3 1.3.1
a) Stored
b) Function
 c) View
d) Table
Selecting the _________ option can allow unaudited activity
7. which could violate your security policies
1 1 4 4,5 1.3.1
a) Fail
b) Shut down
c) Continue
d) Break
8. Triggers can be enabled or disabled with the ________statement 1 1 4 4,5 1.3.1
a) Alter Table Statement
b) Drop Table Statement
c) Delete Table Statement
d) Create Table Statement
In which of the Privacy preserving data mining method noise is
9. added to data
1 1 5 4,5 1.3.1
a) The randomization method
b) The k-anonymity model and l-diversity
c) Distributed privacy preservation
d) Downgrading Application Effectiveness
10. In which method noise is added to data 1 1 5 4,5 1.3.1
a) The randomization method
b) The k-anonymity model and l-diversity
c) Distributed privacy preservation
d) Downgrading Application Effectiveness
In which model we reduce the granularity of data representation with
11 the use of techniques such as generalization and suppression
1 1 5 4,5 1.3.1
a) The randomization method
b) The k-anonymity model and l-diversity
c) Distributed privacy preservation
d) Downgrading Application Effectiveness
The values across different records are swapped in order to
12 perform the privacy-preservation in
1 1 5 3 1.3.1
a) Data mapping
b) Data swapping
c) Data swapping and mapping
d) Data ordering
13 The t-closeness Model is the enhancement of 1 1 5 3 1.3.1
a) l-diversity model
b) k-anonymity
c) All of the Above
d) None of the Above
14 Which one is not suitable for k-anonymity techniques uses in 1 1 5 4,5 1.3.1
a) Generalization
b) Suppression
c) Generalization And Suppression
d) Randomization
15 Which one is not suitable fork-anonymity techniques uses in 1 1 5 3 1.3.1
a) Generalization
b) Suppression
c) Generalization and suppression
d) Randomization
 PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
Mar PI
Q. No Questions ks
BL CO PO
Code
16. Summarize the advantages and Disadvantages of 5 2 4 2 1.6.1
Auditing?

Advantages of Auditing

 Enforces company policies, government

regulations and laws
 Lowers the incidence of security
violations
 Identifies the security gaps and
vulnerabilities
 Provides an audit trail of activities
 Provides another means to observe and
evaluate operations of the audited entity
 Provides the sense or state of security
and confidence in the audited entity
 Identifies or removes doubts
 Makes the organisation being audited
more accountable
 Develops controls that can be used for
purposes other than auditing
Disadvantages of Auditing

 Performance problems due to

preoccupation with the audit instead of
the normal work activities
 Generation of many reports and
documents that may not be easily or
quickly disseminated
 Disruption to the operations of the
audited entity
 Consumption of resources, and added
costs from downtime
 Friction between operators and auditor
 From a DB perspective
• Could degrade the performance
of the system
• Also generate a massive number
of logs, reports, and that require
a system purge

17. Explain triggers in SQL? How do you create a 5 2 4 2 1.6.1

trigger using SQL?

In SQL, a trigger is a database object that defines a set

of actions to be performed automatically in response to
specific events or changes in the database. Triggers are
typically used to enforce data integrity, implement
business rules, or automate certain tasks, such as
logging changes or generating audit records. They can
be set to execute either before or after a specific event,
like an INSERT, UPDATE, DELETE, or other data
manipulation operations.

There are two main types of triggers in SQL:

Before Triggers (BEFORE

INSERT/UPDATE/DELETE): These triggers execute
before the specified data manipulation operation (e.g.,
INSERT, UPDATE, DELETE) takes place. They are
commonly used to validate data or modify values
before the change is applied to the database.

After Triggers (AFTER INSERT/UPDATE/DELETE):

These triggers execute after the specified data
manipulation operation has taken place. They are often
used to perform actions such as logging changes,
sending notifications, or maintaining audit trails.

Here's the basic syntax for creating a trigger in SQL:

CREATE [OR REPLACE] TRIGGER trigger_name

[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON table_name
FOR EACH ROW
[WHEN (condition)]
BEGIN
-- Trigger code or actions go here
END;

Let's break down the components of this syntax:

trigger_name: This is a user-defined name for the

trigger.
BEFORE or AFTER: Specifies whether the trigger
should execute before or after the specified data
manipulation operation.
INSERT, UPDATE, or DELETE: Indicates the event
that triggers the execution of the trigger.
table_name: The name of the table on which the trigger
is defined.
FOR EACH ROW: This clause indicates that the
trigger will fire once for each row affected by the
triggering event.
WHEN (condition): An optional condition that, if
specified, restricts the trigger's execution based on a
specified condition.
BEGIN...END: The block of SQL statements or actions
that the trigger should perform when it's executed.
 Here's an example of creating a simple trigger that logs
changes to a "employees" table after an update:

CREATE OR REPLACE TRIGGER

log_employee_changes
AFTER UPDATE
ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (employee_id,
change_date, changed_data)
VALUES (:OLD.employee_id, SYSDATE, 'Updated
employee data');
END;

In this example, the trigger "log_employee_changes" is

set to execute after an update on the "employees" table.
It logs the changes by inserting a record into an
"employee_audit" table with information about the
employee and the modification timestamp.

Triggers can be powerful tools, but they should be used

with caution, as they can impact database performance
and maintainability. It's important to ensure that
triggers are well-designed, and their logic is thoroughly
tested to avoid unintended side effects.
18. Illustrate the distributed privacy preserving data 5 2 5 2 1.6.1
Mining?

Distributed Privacy-Preserving Data Mining (DPDM)

is a field of research and a set of techniques that aim to
perform data mining on distributed datasets while
preserving the privacy of the individuals or entities
whose data is being analyzed. It addresses the challenge
of extracting useful insights and patterns from data
without compromising the sensitive information
contained in that data.

Here are some key aspects and techniques associated

with Distributed Privacy-Preserving Data Mining:

Privacy Concerns: The primary motivation for DPDM

is to address privacy concerns. When data is distributed
across different parties or organizations, there may be
legal, ethical, or contractual obligations to protect the
privacy of the data. DPDM ensures that data mining
operations do not reveal sensitive information about
individuals in the dataset.

Cryptographic Techniques: One of the fundamental

approaches to DPDM is the use of cryptographic
techniques, such as secure multiparty computation and
homomorphic encryption. These techniques allow
computations to be performed on encrypted data
without revealing the data in its raw form.

Differential Privacy: Differential privacy is a key

concept in DPDM. It involves adding noise to the data
or query responses in a way that makes it difficult to
distinguish the contribution of any individual data
point. This ensures that the privacy of individuals is
preserved while still enabling useful aggregate analysis.

Federated Learning: Federated learning is a technique

that allows machine learning models to be trained on
distributed datasets without sharing raw data. Instead,
model updates are exchanged between the parties, and
the model is trained collaboratively.

Secure Aggregation: Secure aggregation techniques

enable parties to compute aggregate statistics from their
data without revealing the individual data points. This
can be achieved through secure multi-party
computation.

Data Perturbation: Data perturbation involves adding

random noise to the data before analysis. This makes it
more challenging for an adversary to learn sensitive
information from the data mining results.

Trusted Third Parties: In some DPDM scenarios, a

trusted third party may be involved to coordinate the
data mining process without having access to the raw
data. This third party ensures privacy and fairness in the
data mining process.

Privacy-Preserving Data Mining Algorithms:

Researchers have developed data mining algorithms
that are designed to operate on privacy-preserving data.
These algorithms take into account the privacy
constraints and utilize techniques such as differential
privacy.

Use Cases: DPDM has applications in various domains,

including healthcare, finance, and social sciences. For
example, medical researchers can perform distributed
analysis of patient data across multiple hospitals
without exposing individual patient records.

Legal and Ethical Considerations: DPDM often

intersects with legal and ethical considerations, such as
data protection regulations (e.g., GDPR), data
ownership, and consent. Compliance with these
regulations is a critical aspect of DPDM.

DPDM is a challenging and evolving field that requires

a deep understanding of both data mining and privacy-
 preserving techniques. It enables organizations and
researchers to glean insights from distributed datasets
while respecting individuals' privacy rights.
19. Illustrate the Utility Based Privacy-Preserving Data 5 2 5 4 1.6.1
Mining

Utility-Based Privacy-Preserving Data Mining

(UBPPDM) is an approach that aims to strike a balance
between preserving the privacy of individuals' sensitive
data and extracting useful information for data analysis
or mining purposes. It addresses the challenge of
sharing data while protecting the privacy of individuals'
information. Here's an illustration of UBPPDM:

Data Collection: Consider a healthcare organization

collecting medical records from patients. These records
may include sensitive information such as diagnoses,
treatments, and personal identifiers. The organization
wants to share this data with researchers or other parties
for medical research without disclosing individual
patients' identities or specific medical details.

Privacy Concerns: Patients' privacy is a top priority.

Sharing the raw medical records as they are would
breach their privacy, violating regulations like HIPAA
in the United States. Anonymizing data by removing
identifiers is not enough because it's still possible to re-
identify individuals through auxiliary information or by
linking it with other datasets.

Data Transformation: In UBPPDM, data transformation

techniques are applied to the original data to protect
privacy. For example, differential privacy mechanisms
might be used to add random noise to the data, making
it more challenging to identify specific individuals.

Privacy Parameters: To control the level of privacy

preservation and utility, parameters are set. These
parameters determine how much noise or distortion is
added to the data. A balance must be struck to ensure
that privacy is protected while still retaining useful
patterns and insights in the data.

Data Mining Process: Data mining or analysis is

performed on the transformed data. Researchers can
extract valuable information, discover trends, and make
important insights into the medical data without
violating individual privacy. This may include
identifying disease trends, treatment effectiveness, or
risk factors.

Privacy Guarantees: UBPPDM provides privacy

guarantees based on the chosen privacy mechanisms
 and parameters. Researchers and data analysts can work
with the assurance that they are not exposing sensitive
information about individuals in the dataset.

Reporting and Visualization: The results of the data

analysis can be reported or visualized in a way that
maintains privacy. For example, aggregated statistics or
general trends can be presented instead of individual-
level details.

Iterative Process: UBPPDM can be an iterative process.

Researchers might need to fine-tune the privacy
parameters or apply more advanced privacy-preserving
techniques if the initial results are not satisfactory.

Data Sharing: The privacy-preserving data can be

safely shared with other organizations, researchers, or
the public for various purposes, such as academic
research, policy making, or public health awareness.

Continuous Improvement: UBPPDM is an evolving

field. Researchers and practitioners continually develop
new techniques and algorithms to improve the balance
between privacy and utility.

In summary, Utility-Based Privacy-Preserving Data

Mining is a crucial framework that allows organizations
to share sensitive data for analysis while protecting
individual privacy. It involves a careful balance
between adding noise to protect privacy and
maintaining the usefulness of the data for meaningful
insights and discoveries.

PART – C
(2 x 10 = 20 Marks)
20. Explain the Auditing objectives and Classification in 10 2 4 2 6.1.1
detail?

 Auditing objectives are established as a part of

the development process of the entity to be
audited
 For example , when a software application is
being coded, the developers include in their
software development design objectives the
capability to audit the application
 Auditing objectives are established and
documented for the following reasons:
 Complying – Identify all company
policies , government regulations, laws
and the industry
standards with which your company
comply.
 Informing – All policies, regulations,
laws and standards must be published
 and communicated to
all parties involved in the development
and operation of the audited
entity.
 Planning – Knowing all the objectives
enables the author to plan and document
procedures to asses the
audited entity.
 Executing – Without auditing objectives,
the person conducting the audit
cannot evaluate, verify,
or review the audited entity and cannot
determine if the
auditing objectives have been met
The top ten database auditing objectives
 Data Integrity – Ensure that data is valid
and in full referential integrity
 Applications Users and roles – Ensures
that users are assigned roles that
correspond to their responsibilities and
duties
 Data Confidentiality – Identify who can
read data and what data can be read
 Access Control – Ensures that the
application records times and duration
when a user logs onto the database or
application
 Data changes – Create an audit trail of
all data changes
 Data Structure Changes – Ensures that
the database logs all data structure
changes
 Database or application availability –
Record the number of occurrences and
duration of application or database
shutdowns all the startup times . Also,
record all reason for any unavailability.
 Change Control – Ensure that a change
control mechanism is incorporated to
track necessary and planned changes to
the database or application.
 Physical Access – Record the physical
access to the application or the database
where the software and hardware
resides.
 Auditing Reports – Ensure that reports
are generated on demand or
automatically , showing all auditable
activities

Audit Classifications
 Every industry and business sector uses
different classifications of audits.
 Definition of each classification can differ from
 business to business.
 Will discuss most generic definition of audit
classifications.
Internal Audit
 An internal audit is an audit that is conducted by
a staff member of the company being audited
 The purpose and intention of an internal audit is
to :
 Verify that all auditing objectives are
met by conducting a well-planned and
scheduled audit
 Investigate a situation that was promoted
by an internal event or incident. This
audit is random , not planned or
scheduled.
External Audit
 An external audit is conducted by a party
outside the company that is being audited.
 The purpose and intention of an External audit
is to :
 Investigate the financial or operational
state of the company . This audit is
initiated at will by the government or
promoted by suspicious activities or
accusations.
 The person conducting this audit is
usually employed and appointed by the
government.
 Verify that all objectives are met. This
audit is typically planned and scheduled.
 Ensure objectivity and accuracy.
This audit is typically performed to certify that the
company is complying with standards and regulations
Automatic Audit
 An automatic audit is promoted and
performed automatically.
 Automatic audits are mainly for systems
and DB systems.
 Some systems that employ this type of
audit to generate reports and logs.
Manual Audit
 Completely performed by humans
 The team uses various methods to
collect audit data, including interviews,
document reviews and observation.
 The auditors may even perform the
operational task of the audited entity.
Hybrid Audit
Combination of Automatic and Manual Audits
OR
21. Explain in detail about how the database activities 10 2 4 2 6.1.1
are audited using SQL Server 2000?
SQL Server 2000 is an older version of Microsoft's
relational database management system. While it's no
longer officially supported, understanding how
database activities can be audited in this version can
provide some historical context for auditing in more
recent SQL Server versions.

Auditing in SQL Server 2000 typically involves

tracking and recording various database activities to
ensure data integrity, security, and compliance. To
achieve this, you can use several methods and features
within SQL Server 2000:

SQL Profiler:
SQL Server 2000 includes SQL Profiler, a tool that
allows you to capture and analyze SQL Server events,
including SQL statements executed, login attempts,
errors, and more. To set up auditing using SQL
Profiler, follow these steps:

Launch SQL Profiler.

Create a new trace or open an existing one.
Define the events you want to capture (e.g., "T-SQL"
for SQL statements).
Specify filters to focus on specific databases, users, or
applications.
Start the trace to begin auditing.
SQL Profiler allows you to capture detailed information
about database activities and provides valuable insights
into what's happening in the system. However, it's not
the most efficient method for long-term or continuous
auditing.

Triggers:
SQL Server 2000 supports database triggers, which are
programmable actions that automatically execute when
specific events occur in the database. You can use
triggers to audit changes to data or specific actions
performed on database objects. Common triggers
include:

DML Triggers: These triggers fire in response to Data

Modification Language (DML) statements like
INSERT, UPDATE, and DELETE, allowing you to log
changes to data.
DDL Triggers: These triggers respond to Data
Definition Language (DDL) statements like CREATE,
ALTER, and DROP, enabling you to track changes to
database schema.
To implement triggers, you would write T-SQL code
that defines the trigger's behavior and attach it to a table
or the database itself. When the specified event occurs,
the trigger executes, allowing you to log relevant
information to an audit table or take other actions.
 Extended Stored Procedures:
SQL Server 2000 allows you to create custom extended
stored procedures using programming languages like
C/C++. These procedures can be used for various
purposes, including auditing. You can develop custom
stored procedures to capture and record specific
database activities in a custom audit table.

System Log (Event Logs):

SQL Server 2000 also logs certain events in the
Windows event logs. You can configure SQL Server to
log security-related events, errors, and other important
information. These logs can provide insights into login
attempts, failed authentication, and other security-
related activities.

It's essential to secure and protect the audit data by

ensuring that only authorized personnel have access to
the audit logs. You may also want to consider
periodically archiving and managing audit data to
prevent it from growing excessively.

Keep in mind that SQL Server 2000 is an outdated and

unsupported version, and it's recommended to migrate
to a more recent and supported version of SQL Server
for enhanced security and auditing capabilities. SQL
Server 2000 lacks many of the advanced auditing
features available in modern versions, like SQL Server
Audit, which provides more robust auditing and
security capabilities.
22. Explain in detail for the following. 10 2 5 1 6.1.1
A. t-closeness model

T-closeness is a privacy model used in data

anonymization and protection, particularly in the
context of publishing sensitive information while
preserving individual privacy.
T-closeness extends the ideas of k-anonymity and l-
diversity, which are also privacy models used in data
anonymization. The primary goal of t-closeness is to
ensure that the distribution of sensitive attributes in the
anonymized dataset is not too different from the
distribution of those attributes in the original dataset. In
other words, it aims to provide a more refined privacy
guarantee compared to k-anonymity and l-diversity.
Here's a brief overview of the key components of t-
closeness:

Sensitive Attribute: The t-closeness model is

primarily concerned with preserving the privacy of a
sensitive attribute. This attribute contains information
that should be protected, such as medical conditions,
income, or any other personal information.
Closeness Measure: T-closeness introduces a
closeness measure, often denoted as "t," which
quantifies the similarity between the distribution of the
sensitive attribute in the original dataset and the
anonymized dataset. The value of "t" determines the
level of privacy protection.

Anonymization: To achieve t-closeness, the dataset is

anonymized in a way that the distribution of the
sensitive attribute within each group (anonymized
record) is not significantly different from its
distribution in the original dataset. This prevents an
attacker from inferring sensitive information easily.

Grouping: Records are grouped together based on

quasi-identifiers, which are attributes that can be used
to re-identify individuals. The goal is to create groups
where the distribution of the sensitive attribute within
each group is close to the overall distribution in the
original dataset.

Global t-Closeness: This approach ensures that the

entire dataset satisfies the t-closeness property.

Local t-Closeness: This approach ensures that each

group (anonymized record) within the dataset satisfies
the t-closeness property. Local t-closeness is often
preferred when the data is highly skewed.

B. l-diversity model

L-diversity is a privacy concept and model designed to

protect sensitive information in data, particularly in the
context of data anonymization and disclosure control.
The primary goal of l-diversity is to prevent attribute
disclosure by ensuring that each group of records with
the same quasi-identifiers (attributes that can
potentially be used to re-identify individuals) contains
at least "l" distinct values for a sensitive attribute.

Here's a more detailed explanation of the l-diversity

model:

Quasi-identifiers: These are attributes in a dataset that

can be used in combination to potentially re-identify
individuals. For example, a combination of attributes
like ZIP code, age, and gender might make someone
unique in a dataset.

Sensitive attribute: This is the attribute that you want

to protect, such as medical information, income, or any
other personal and sensitive data.
 L-diversity: The number "l" in l-diversity represents
the minimum number of distinct values that must be
present for the sensitive attribute within any group of
records that share the same quasi-identifiers. In other
words, for each unique combination of quasi-
identifiers, there should be at least "l" different values
for the sensitive attribute to make it more challenging
to re-identify individuals.

The main idea behind l-diversity is to add a level of

diversity to the sensitive attribute values within each
group, making it harder for an adversary to pinpoint an
individual's sensitive information based on their quasi-
identifiers. This helps protect privacy while still
allowing useful information to be shared in a dataset.

There are various techniques to achieve l-diversity,

such as generalization and suppression of data values,
and these methods aim to balance the trade-off between
data utility and privacy. The choice of "l" value
depends on the specific privacy requirements of the
dataset and the desired level of protection.

L-diversity is just one of the many techniques used in

the field of privacy-preserving data publishing and
anonymization. It addresses the limitations of
traditional data anonymization methods, like k-
anonymity, which do not consider diversity in the
sensitive attribute values and can lead to attribute
disclosure risks.
OR
23. Justify, Why the data mining techniques preferred 10 3 5 4 1.7.1
for preserving privacy?

Data mining techniques are often preferred for

preserving privacy because they allow organizations to
extract valuable insights and patterns from data while
minimizing the risk of exposing sensitive or personally
identifiable information (PII). Here are several reasons
why data mining techniques are considered valuable for
privacy preservation:

1. Anonymization and Data Masking: Data mining

methods can be used to anonymize or mask
sensitive information. For example, by
aggregating or generalizing data, it becomes
much more challenging to identify individuals
or reveal personal details. This allows
organizations to use data for analysis without
exposing private information.

2. Differential Privacy: Differential privacy is a

mathematical framework that can be integrated
 with data mining algorithms to ensure that the
inclusion or exclusion of a specific data point
does not significantly impact the results. This
technique adds noise to the data to protect
individual privacy while still providing accurate
aggregate insights.

3. Secure Multiparty Computation (SMC): SMC

techniques enable multiple parties to jointly
compute functions on their individual datasets
without revealing the underlying data to each
other. This approach allows organizations to
collaborate and mine data while maintaining the
privacy of their data sources.

4. Homomorphic Encryption: Homomorphic

encryption allows computations to be performed
on encrypted data without decrypting it first.
This enables data mining on encrypted data,
reducing the risk of data exposure during the
analysis.

5. Data Perturbation: Data perturbation techniques

introduce controlled noise or distortion to the
data before analysis, making it challenging for
adversaries to reverse engineer the original data
or identify individuals. This protects privacy
while still enabling meaningful analysis.

6. K-anonymity and L-diversity: These are

privacy-preserving techniques that ensure that
individual records in a dataset cannot be easily
distinguished from a group of at least k
individuals. This helps protect privacy by
making it more challenging to identify specific
individuals within the data.

7. Limited Data Disclosure: Data mining

techniques can be employed to extract
aggregated or summarized information from a
dataset, rather than exposing raw or granular
data. This reduces the risk of privacy breaches
by only revealing essential insights.

8. Privacy-Preserving Machine Learning:

Techniques like federated learning and secure
multi-party computation enable machine
learning models to be trained on distributed data
sources without sharing the raw data. This
protects the privacy of the data while still
allowing model development.

9. Data Minimization: Data mining encourages

organizations to only collect and retain the data
 necessary for their specific analysis or business
purposes. This reduces the volume of sensitive
data that needs protection and limits the
potential privacy risks.

10. Regulatory Compliance: Many data protection

regulations, such as the General Data Protection
Regulation (GDPR) in Europe, require
organizations to implement privacy-preserving
measures. Using data mining techniques for
privacy protection can help organizations
comply with these regulations.

In summary, data mining techniques provide a range of

methods to balance the need for data analysis and
insights with the imperative to protect individual
privacy. By applying these techniques, organizations
can derive valuable knowledge from data while
minimizing the risk of exposing sensitive information,
meeting legal requirements, and respecting individuals'
privacy rights.
 SRM Institute of Science and Technology
Faculty of Engineering and Technology SET - D
School of Computing
DEPARTMENT OF COMPUTING TECHNOLOGIES
SRM Nagar, Kattankulathur – 603203, Chengalpattu District, Tamilnadu
Academic Year: 2023 - 2024 (ODD)

Test: CLA – T3 Date: 02-11-2023

Course Code & Title: 18CSE455T -Database Security and Privacy Duration: 1 Hour 40 Minutes
Year & Sem: IV / VII Max. Marks: 50

Course Articulation Matrix: (to be placed)

Course
S.No. PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
Outcome
1 CO3 1 - 1 2 2 1 - - - - - -

2 CO4 1 - 3 1 1 1 - - - - - -

PART - A
(15 x 1 = 15 Marks)
Instructions: Answer all
Q. PI
Questions Marks BL CO PO
No Code
Expected to provide the resources needed and select staff
1. 1 1 3 4,5 1.3.1
members to accompany the auditors
A auditor
B client
C Internal auditor
D auditee
The values across different records are swapped in order to
2. 1 1 3 4,5 1.3.1
perform the privacy-preservation is _____________ .
A Data Encryption
B Data Swapping
C Data Hiding
D Data masking
The document that contains all activities that are being
3. 1 1 3 4,5 1.3.1
audited ------- ordered in a chronological manner.
A Audit log
B Audit Profile
C Audit File
D Audit Document
Selecting the _________ option can allow unaudited
4. 1 1 4 3 1.3.1
activity which could violate your security policies.
A Fail
B Shut Down
C Continue
D Break
5. An audit which is compulsory by the law is __________. 1 1 4 3 1.3.1
A Government Audit
B Internal Audit
C Cost Audit
D Statutory Audit
__________ , the attacker has a collection of independent
6. 1 1 4 3 1.3.1
data samples from the same distribution from which the
 original data was drawn
A Known Sample Attack
B Packet sniffer
C Distributed denial of service
D Man in the middle Attack
Kind of partitioning is used for the data sets across multiple
7. 1 1 3 4,5 1.3.1
entities which same set of attributes?
A Horizontal
B Vertical
C Hash
D Key
A method based on chance alone by which study
8. participants are assigned to a treatment group is 1 1 3 4,5 1.3.1
__________________ .
A k-anonymity
B l-diversity
C t-closeness
D Randomization
The _________ model was designed to handle some
9. 1 1 3 4,5 1.3.1
weaknesses in the k-anonymity model
A k-anonymity
B l-diversity
C incognito
D Data Swapping
The Oracle _______ Log is another method of
10. 1 1 3 4,5 1.3.1
auditing database activities.
A ALERT
B REVOKE
C COMMIT
D CHECK
Bioterrorism-application, the data analyzed for privacy-
11 1 1 3 4,5 1.3.1
preserving data mining purposes is
A medical data
B Statistical data
C Spatio temporal data
D Timestamped data
The Method for compromising the privacy of genomic
12 1 1 4 3 1.3.1
data
A trail re-identification
B Prediction
C Masking
D Decoding
Which one of the following team retested every database
13 1 1 4 3 1.3.1
application function and try to find bugs?
A Quality assurance
B Quality control
C Quality testing
D Quality manager
In _________ the entry is not modified, but is left
14 incomplete. Thus, unknown entry values are used to 1 1 3 4,5 1.3.1
prevent discovery of association rules.
A Additive perturbation
B Multiplicative perturbation
 C Blocking
D Distortion
---------------- function returns Boolean value in
15 1 2 4 3 1.3.1
PKG_APP_AUDIT?
A AUDIT_CHECK
B AUDIT_REVOKE

C AUDIT_COMMIT

D AUDIT_ALERT

PART – B
(3 x 5 = 15 Marks)
Instructions: Answer any 3 Questions
Describe the following terms in few lines
Auditing

Audit log

Audit objectives

Audit trail

External auditing

 Auditing - The process of examining

and validating documents, data,
processes, systems, or other activities to
ensure that the audited entity complies
with its objective

16.  Audit log – A document that contains all 5 2 3 4 1.6.1

activities that are being audited ordered
in a chronological manner.

 Audit objectives – A set of business

rules, system controls, government
regulations or
security policies against which the
audited entity is measured to determine
compliance

 Audit trail – A chronological record of

document changes, data changes, system
activities, or operational events

 External auditing - Auditing activities

conducted by the staff members outside
of the organization.
 Summariz
authoriz
only
informat
which
Explain any one of the auditing model with neat
diagram.
 Before auditing models, it is more important
that , understand how audit is processed for data
and DB activities

 The flowchart presents data auditing

 The flowchart shows what happens when a user

perform an action to a DB object

 Specific checks occur to verify if the action , the

user or the object are registered in auditing
repository

 If they are registered the followings are

recorded

 State the object before the action was

taken along with the time of action

 Description of the action that was

performed
17. 5 1 4 3 1.6.1
 Name of the user or userid who
performed the action

Explain Data Swapping?

18. 5 2 4 3 1.6.1
 Noise addition or multiplication is not the only
 technique which can be used to perturb the data.

 A related method is that of data swapping, in

which the values across different records are
swapped in order to perform the privacy-
preservation

 One advantage of this technique is that the

lower order marginal totals of the data are
completely preserved and are not perturbed at
all.

 Therefore certain kinds of aggregate

computations can be exactly performed
without violating the privacy of the data.

 This technique does not follow the

general principle in randomization
which allows the value of a record to be
perturbed independently of the other
records.

 Therefore, this technique can be used in

combination with other frameworks
such as k-anonymity, as long as the
swapping process is designed to
preserve the definitions of privacy for
that model.

Write about mining association rules under privacy

constraints.
Mining Association Rules under Privacy
Constraints

 Since association rule mining is one of the

important problems in data mining

 There are two aspects to the privacy preserving

association rule mining problem
19. 5 1 3 4 1.6.1
1. When the input to the data is perturbed,
it is a challenging problem to accurately
determine the association rules on the
perturbed data.

2. A different issue is that of output

association rule privacy.

In this case, to ensure that none of the

association rules in the output result in leakage of
 sensitive data.

This problem is referred to as association rule

hiding by the

database community, and that of contingency

table privacy-

preservation by the statistical community.

PART – C
(2 x 10 = 20 Marks)
20. Explain auditing project case study for payroll.
Case : Taking Care of Payroll
 Acme Payroll Systems is a small payroll
services company that has been in business for
two years and has had only one major customer
 Suddenly, it lands a contract with another large
corporation
 If the company hired you as Database
consultant to design and implement a virtual
private database for the existing payroll
application.
 The main objective of the virtual private
database feature is allow each client to
administer his own payroll data without
violating the privacy of other clients.
10 1 3 5 6.1.1
The given figure represents the payroll application
model for case

OR
21. Explain auditing process? Differences in QA , Auditing
and Performance Monitoring processes? Illustrates the
auditing process flow with the neat diagram?
10 1 3 4 2.2.4
 Database applications widely used by major
corporate companies, mostly large financial and
online trading companies.
  The Quality Assurance (QA) team retested
every database application function and try to
find bugs.
 This type of auditing resembles QA or even
performance monitoring
 The purpose of QA process in software
engineering to make sure that the system is bug
free and that the system is functioning
according to its specification.
 The auditing process ensures that the system is
working and complies with the policies,
standards, regulations or laws set forth by
organization, industry or government.
 Another way to distinguish between QA and
Auditing Process is by examining the timing of
each
 QA – during development phase, before the
implementation of the system.
 Auditing Process – After the system is
implemented and in production.
 Auditing is also not the same as performance
monitoring
 Auditing objectives are totally different
 Performance Monitoring is to observe the
degradation in performance
 Auditing validates compliance to policy not
performance

22. Elaborate privacy preserving data mining in detail. 10 3 4 4 6.1.1

  Statistical Methods for Disclosure Control
 Measures of Anonymity
 The k-anonymity Method
 The Randomization Method
 Quantification of Privacy
 Utility Based Privacy-Preserving Data Mining
 Mining Association Rules under Privacy
Constraints
 Cryptographic Methods for Information Sharing
and Privacy
 Privacy Attacks
 Query Auditing and Inference Control
 Privacy and the Dimensionality Curse
 Personalized Privacy Preservation
 Privacy-Preservation of Data Streams

Note: Explain all the above headings in detail.

OR
23. Explain Randomization method in detail?

 The randomization method is a technique for

privacy-preserving data mining in which noise
is added to the data in order to mask the
attribute values of records.
 The noise added is sufficiently large so that
individual record values cannot be recovered.
 Therefore, techniques are designed to derive
aggregate distributions from the perturbed
records.
 Subsequently, data mining techniques can be
developed in order to work with these aggregate
distributions.
The method of randomization can be described as
follows.
 Consider a set of data records denoted by X =
{x1 . . .xN}
10 3 4 5 1.7.1
 For record xi ∈ X
 we add a noise component which is drawn from
the
probability distribution fY (y).
 These noise components are drawn
independently, and are denoted y1 . . . yN.
 Thus, the new set of distorted records are
denoted by
x1 +y1 . . .xN +yN.
 We denote this new set of records by
z1 . . . zN.
 In general, it is assumed that the variance of the
added noise is large enough, so that the original
record values cannot be easily guessed from the
distorted data.
 Thus, the original records cannot be recovered,
but the distribution of the original records can
be recovered.
 Thus, if X be the random variable denoting the
data distribution for the original record
 Y be the random variable describing the noise
distribution
 Z be the random variable denoting the final
record
We have:
Z=X+Y
X=Z−Y
 Now, we note that N instantiations of the
probability distribution Z are known, whereas
the distribution Y is known publicly.
 For a large enough number of values of N, the
distribution Z can be approximated closely by
using a variety of methods such as kernel
density estimation.
 By subtracting Y from the approximated
distribution of Z, it is possible to approximate
the original probability distribution X
 One key advantage of the randomization
method is that it is relatively simple, and does
not require knowledge of the distribution of
other records in the data.
 This is not true of other methods such as k-
anonymity which require the knowledge of
other records in the data.
 Therefore, the randomization method can be
implemented at data collection time, and does
not require the use of a trusted server containing
all the original records in order to perform the
anonymization process.
 While this is a strength of the randomization
method, it also leads to some weaknesses, since
it treats all records equally irrespective of their
local density.