DMA Chapter No5

Amrutvahini Polytechnic Sangamner
Program – IF Class – IF4I
Course – Database Management Code – 22416
Staff – Navale N.D

Chapter No 5
Database Security and Transaction Processing (08)
Database security encompasses a range of

security controls designed to protect the
Database Management System (DBMS). The
types of database security measures your
business should use include protecting the
underlying infrastructure that houses the
database such as the network and servers),
securely configuring the DBMS, and the access
to the data itself.
Goals
• The goal of database security is to prevent
unauthorized or accidental access to data.
• Because the database environment has
become more complex and more
decentralized, management of data security
and integrity has become a more complex and
time consuming job for data administrators.
Database Users
• Application Programmers – They are the developers who interact with the database by means of DML
queries. These DML queries are written in the application programs like C, C++, JAVA, Pascal etc. These
queries are converted into object code to communicate with the database. For example, writing a C
program to generate the report of employees who are working in particular department will involve a
query to fetch the data from database. It will include a embedded SQL query in the C Program.
• Sophisticated Users – They are database developers, who write SQL queries to
select/insert/delete/update data. They do not use any application or programs to request the database.
They directly interact with the database by means of query language like SQL. These users will be
scientists, engineers, analysts who thoroughly study SQL and DBMS to apply the concepts in their
requirement. In short, we can say this category includes designers and developers of DBMS and SQL.
• Specialized Users – These are also sophisticated users, but they write special database application
programs. They are the developers who develop the complex programs to the requirement.
• Stand-alone Users – These users will have stand –alone database for their personal use. These kinds of
database will have readymade database packages which will have menus and graphical interfaces.
• Native Users – these are the users who use the existing application to interact with the database. For
example, online library system, ticket booking systems, ATMs etc which has existing application and
users use them to interact with the database to fulfill their requests.
Database Administrators
• Installing and upgrading the DBMS Servers:

• Design and implementation: –
• Performance tuning: –
• Migrate database servers: – Sometimes, users using oracle would like to shift to SQL server or
Netezza. It is the responsibility of DBA to make sure that migration happens without any
failure, and there is no data loss.
• Backup and Recovery: – Proper backup and recovery programs needs to be developed by DBA
and has to be maintained him. This is one of the main responsibilities of DBA. Data/objects
should be backed up regularly so that if there is any crash, it should be recovered without
much effort and data loss.
• Security: – DBA is responsible for creating various database users and roles, and giving them
different levels of access rights.
• Documentation: – DBA should be properly documenting all his activities so that if he quits or
any new DBA comes in, he should be able to understand the database without any effort. He
should basically maintain all his installation, backup, recovery, security methods. He should
keep various reports about database performance.
• Create user user_name identified by
externally;
• Create user user_name identified by
password;
• Update user user_name identified by
password;
• DROP user user_name;
Database Privilege
• A privilege is a right to execute a particular
type of SQL statement or to access another
user's object. Some examples of privileges
include the right to:
• Connect to the database (create a session)
• Create a table
• Select rows from another user's table
• Execute another user's stored procedure
• You grant privileges to users so these users can
accomplish tasks required for their job. You should
grant a privilege only to a user who absolutely
requires the privilege to accomplish necessary
work. Excessive granting of unnecessary privileges
can compromise security. A user can receive a
privilege in two different ways:
• System privileges
• Schema object privileges
System Privileges
• A system privilege is the right to perform a particular

action, or to perform an action on any schema objects
of a particular type. For example, the privileges to
create tablespaces and to delete the rows of any table
in a database are system privileges. There are over 60
distinct system privileges.
• Grant and Revoke System Privileges
• You can grant or revoke system privileges to users and
roles
Grant/Revoke
• Grant – Assign permission to user.
Grant privilege_name on table_name to user;
Example:
Grant select on student to user1;
Revoke : Cancel assign permission from user;

Revoke select on student from user1;
Schema Object Privileges
• A schema object privilege is a privilege or right to

perform a particular action on a specific schema object:
• Table
• View
• Sequence
• Procedure
• Function
• Package
Transaction Property(ACID)
• ACID Properties
• A transaction is a very small unit of a program and it may contain several
low level tasks. A transaction in a database system must
maintain Atomicity, Consistency, Isolation, and Durability − commonly
known as ACID properties − in order to ensure accuracy, completeness,
and data integrity.
• Atomicity − This property states that a transaction must be treated as an
atomic unit, that is, either all of its operations are executed or none. There
must be no state in a database where a transaction is left partially
completed.
• Consistency − The database must remain in a consistent state after any
transaction. If the database was in a consistent state before the execution
of a transaction, it must remain consistent after the execution of the
transaction as well.
• Durability − The database should be durable enough to hold all its
latest updates even if the system fails or restarts. If a transaction
updates a chunk of data in a database and commits, then the
database will hold the modified data. If a transaction commits but
the system fails before the data could be written on to the disk,
then that data will be updated once the system springs back into
action.
• Isolation − In a database system where more than one transaction
are being executed simultaneously and in parallel, the property of
isolation states that all the transactions will be carried out and
executed as if it is the only transaction in the system. No
transaction will affect the existence of any other transaction.
States of Transactions
• Active − In this state, the transaction is being executed. This is the initial state of every
transaction.
• Partially Committed − When a transaction executes its final operation, it is said to be in a
partially committed state.
• Failed − A transaction is said to be in a failed state if any of the checks made by the
database recovery system fails. A failed transaction can no longer proceed further.
• Aborted − If any of the checks fails and the transaction has reached a failed state, then the
recovery manager rolls back all its write operations on the database to bring the database
back to its original state where it was prior to the execution of the transaction.
Transactions in this state are called aborted. The database recovery module can select one
of the two operations after a transaction aborts −
– Re-start the transaction
– Kill the transaction
• Committed − If a transaction executes all its operations successfully, it is said to be
committed. All its effects are now permanently established on the database system.

DMA Chapter No5

Uploaded by

Copyright:

Available Formats

DMA Chapter No5

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DMA Chapter No5

Uploaded by

Copyright:

Available Formats

Amrutvahini Polytechnic Sangamner

Program – IF Class – IF4I

Course – Database Management Code – 22416

Staff – Navale N.D

Database security encompasses a range of

• Installing and upgrading the DBMS Servers:

• A system privilege is the right to perform a particular

Revoke : Cancel assign permission from user;

• A schema object privilege is a privilege or right to

You might also like