UNIT 5:
DATABASE SECURITY
[10 Marks]
Alfiya Mulla
Lecturer, Computer Engg. Dept.
AI ARKP, Panvel.
• What is Database Security? [4M]
• Functions of Database Administrator. [MImp] [4-6M]
• Define Transaction. [2m]
• Types of Database Users. [Imp (Viva)]
• What do you understand by the term database privileges ? [Viva]
• Queries on Grant and Revoke [4m]
• ACID Properties [M M IMP] [4M] [viva & ESE]
CONTENT
• Introduction to Database Security (W16)
• Protecting the Data within the Database
• Transaction: Concept, Properties and States of Transaction (MMIMP)
• Database Backup
• Database Recovery
Importance of Data
• Bank/Demat accounts
• Credit card, Salary, Income tax data
• University admissions, marks/grades
• Land records, licenses
• Data = crown jewels for organizations
• Recent headlines:
• Personal information of millions of credit card users stolen
• Laws on privacy in the US
• Theft of US data in India
• Criminal gangs get into identity theft
• Earlier this year in Mumbai
• Hackers steal credit card data using card reader and make fraudulent purchases
• Hacker creates fake Web site to phish for credit card information
• Auto-rickshaw license fraud in New Delhi
4
Database Security
• Database Security - protection from malicious attempts to steal
(view) or modify data.
5
Database Security
• Database security refers to the collective measures used to protect and
secure a database or database management software from illegal use
and malicious threats and attacks.
• Database security covers and enforces security on all aspects and
components of databases like Data stored in database, Database server,
DBMS
Data Security Requirements
Data Security Requirements:
• Authentication: System verifies a user's identity.
• Authorization: Which database operations that user may perform (like read,
update, drop etc.) and which data objects that user may access.
• Secure Storage of Sensitive Data: Once confidential data has been entered, its
integrity and privacy must be protected on the databases and servers wherein it
resides.
• Integrity: Data integrate means that data is protected from deletion and corruption
• Availability: A secure system makes data available to authorized users, without
delay.
• Confidentiality: A secure system ensures the confidentiality of data. This means
that it allows individuals to see only the data they are supposed to see.
Need of Data Security
To protect the database, we must take security measures at several levels:
• Physical: The sites containing the computer systems must be secured against armed or
surreptitious entry by intruders.
• Human: Users must be authorized carefully to reduce the chance of any such user giving
access to an intruder in exchange for a bribe or other favors .
• Operating System: No matter how secure the database system is, weakness in operating
system security may serve as a means of unauthorized access to the database.
• Network: Since almost all database systems allow remote access through terminals or
networks, software-level security within the network software is as important as physical
security, both on the Internet and in networks private to an enterprise.
• Database System: Some database-system users may be authorized to access only a
limited portion of the database. Other users may be allowed to issue queries, but may be
forbidden to modify the data. It is responsibility of the database system to ensure that
these authorization restrictions are not violated.
Types of Database Users
• Database Administrator (DBA):
• Sophisticated Users:
• Application Programmers:
• Naïve Users
• Specialized User
Types of Users
• Native users − The native users need not be aware of the presence of
the database system. They are end users of the database who works
through a menu driven application programs, where the type and range
of response is always indicated to the user
• Sophisticated Users − They are those users who interact with the
system without writing the program instead they form their request in
database query language. They are the SQL programmers, who are
going to deal directly with the database. They write queries to delete or
select or insert and update the database.
Types of Users
• Specialized Users − Specialized users who write specialized database
applications that do not fit into the fractional database processing
framework.
• Application Programmer − The application programmer users who are
responsible for developing the application programs or user interface. The
application programs could be written in high level language. For example
− Java, .net, php etc,
• Database Administrator (DBA) − It is a person or the group in charge of
implementing the database system within the organization. The DBA has all
the privileges allowed by the DBMS and can assign or remove the
privileges from the users.
Functions of DBA
1. Schema Definition The Database Administrator creates the database schema by
executing DDL statements. Schema includes the logical structure of database
table (Relation) like data types of attributes, length of attributes, integrity
constraints etc.
2. Storage structure and access method definition The DBA creates appropriate
storage structures and access methods by writing a set of definitions which is
translated by data storage and DDL compiler.
3. Schema and physical organization modification DBA writes set of definitions
to modify the database schema or description of physical storage organization.
Functions of DBA
4. Granting authorization for data access The DBA provides different access rights to the users
according to their level. Ordinary users might have highly restricted access to data, while you go up
in the hierarchy to the administrator, you will get more access rights. Integrity constraints
specifications: Integrity constraints are written by DBA and they are stored in a special file which is
accessed by database manager while updating data.
5. Routine Maintenance some of the routine maintenance activities of a DBA is given below.
(i) Taking backup of database periodically
(ii) Ensuring enough disk space is available all the time.
(iii) Monitoring jobs running on the database.
(iv) Ensure that performance is not degraded by some expensive task submitted by some users.
6. Integrity- constraint specification: Integrity constraints are written by DBA and they are stored
in a special file, which is accessed by database manager, while updating the data.
Privileges and Roles
• Confidentiality, integrity, and availability are the stamps of database security.
Authorization is the allowance to the user or process to access the set of objects. The type
of access granted can be any like, read-only, read, and write. Privilege means
different Data Manipulation Language(DML) operations which can be performed by the
user on data like INSERT, UPDATE, SELECT and DELETE, etc.
• There are two methods by which access control is performed is done by using the
following.
1.Privileges
2.Roles
Privileges :
The authority or permission to access a named object as advised
manner, for example, permission to access a table. Privileges can allow
permitting a particular user to connect to the database. In, other words
privileges are the allowance to the database by the database object.
Privileges
• Database privileges :
A privilege is permission to execute one particular type of SQL statement or
access a second persons’ object. Database privilege controls the use of computing
resources. Database privilege does not apply to the Database administrator of the
database.
• System privileges:
A system privilege is the right to perform an activity on a specific type of object.
for example, the privilege to delete rows of any table in a database is system
privilege. There are a total of 60 different system privileges. System privileges
allow users to CREATE, ALTER, or DROP the database objects.
Object privilege
An object privilege is a privilege to perform a specific action on a
particular table, function, or package. For example, the right to delete
rows from a table is an object privilege. For example, let us consider a
row of table AIARKP that contains the name of the employee who is no
longer a part of the organization, then deleting that row is considered as
an object privilege. Object privilege allows the user to INSERT,
DELETE, UPDATE, or SELECT the data in the database object.
ACID properties in DBMS
States of Transaction
1. Active State –
When the instructions of the transaction are running then the transaction is in active state. If all the ‘read and write’
operations are performed without any error then it goes to the “partially committed state”; if any instruction fails, it goes to
the “failed state”.
2. Partially Committed –
After completion of all the read and write operation the changes are made in main memory or local buffer. If the changes are
made permanent on the DataBase then the state will change to “committed state” and in case of failure it will go to the
“failed state”.
3. Failed State –
When any instruction of the transaction fails, it goes to the “failed state” or if failure occurs in making a permanent change
of data on Data Base.
4. Aborted State –
After having any type of failure the transaction goes from “failed state” to “aborted state” and since in previous states, the
changes are only made to local buffer or main memory and hence these changes are deleted or rolled-back.
5. Committed State –
It is the state when the changes are made permanent on the Data Base and the transaction is complete and therefore
terminated in the “terminated state”.
6. Terminated State –
If there isn’t any roll-back or the transaction comes from the “committed state”, then the system is consistent and ready for
new transaction and the old transaction is terminated.
