Integrity

DATA SECURITY & INTEGRITY IN DATABASES

Lecture Series By :

Er. Kanwalvir Singh Dhindsa Deptt. of CSE & IT

DBMS by Er. K.S.Dhindsa © 2006

DATA SECURITY

 Data Security is the protection of the database against intentional and unintensional threats that may be computerbased or non-computer based  Database security encompasses hardware, software, infrastructure, people and data of the organization

DBMS by Er. K.S.Dhindsa © 2006

DATA SECURITY ISSUES  Data Security Risks  Data Tampering  Data Theft  Falsifying User Identities  Password-Related Threats  Unauthorized access to tables & columns DBMS by Er. K.S.Dhindsa © 2006

DATA SECURITY ISSUES

 Unauthorized Access to Data Rows  Lack of Accountability  Complex User management Requirements

DBMS by Er. K.S.Dhindsa © 2006

SECURITY LEVELS

 PHYSICAL  HUMAN  OPERATING SYSTEM  NETWORK  DATABASE SYSTEMS DBMS by Er. K.S.Dhindsa © 2006

DATA SECURITY ISSUES & REQUIREMENTS

 Confidentiality  Privacy of communications  Secure Storage of Sensitive Data  Authentication

DBMS by Er. K.S.Dhindsa © 2006

DATA SECURITY ISSUES & REQUIREMENTS

Authorization 

Read Authorization



Insert Authorization



Update Authorization



Delete Authorization



Alteration Authorization



Drop Authorization DBMS by Er. K.S.Dhindsa © 2006

DATA INTEGRITY

Data Integrity means that data is protected from deletion and corruption,both while it resides within the database,and while it is being transmitted over the network.

DBMS by Er. K.S.Dhindsa © 2006

Role of Views : In Security & Integrity  All operations performed on view affect the base table of the view from which it is created  Dropping the base table destroys the view of the table Views can be used to SELECT,INSERT or UPDATE data All changes will be actually made in the base table DBMS by Er. K.S.Dhindsa © 2006

VIEWS Syntax : CREATE VIEW view-name As sub-query 5) Create View MGR as SELECT * from emp where job=‘MGR’;  All the changes made in the view affect the base table from which it is created DBMS by Er. K.S.Dhindsa © 2006

Protecting the Data within the Database

Methods by which data access control is done are by using : 

PRIVILEGE



ROLE

DBMS by Er. K.S.Dhindsa © 2006

PRIVILEGES A Privilege is permission to access a named object in a prescribed manner.

I.

Database Privileges :



Right to connect to the database(create a session) Right to create a table Right to select rows from another user’s table

 

DBMS by Er. K.S.Dhindsa © 2006

PRIVILEGES I.

System Privileges : Right to perform a particular action, or to perform action on a particular type of object



Right to delete the rows of any table



Give the system privileges for creating tables and views

DBMS by Er. K.S.Dhindsa © 2006

PRIVILEGES III. Object Privileges : Right to perform a particular action on a specific table, view, sequence, procedure, function, or package 

Privilege to delete(drop) the table



Privilege to delete(drop) the view of a table

DBMS by Er. K.S.Dhindsa © 2006

ROLES A Role is a mechanism that can be used to provide authorization. A single person or a group of people can be granted a role or group of roles  Roles are named group of related privileges that you grant to users or other roles (*Easy & *Controlled Privilege Management)  Designed to ease the administration of end-user system and object privileges DBMS by Er. K.S.Dhindsa © 2006

GRANTING & REVOKING – Privileges & Roles GRANT COMMAND ::

 GIVES Users Privileges to base tables & views. Syntax :: GRANT privilege ON object to USERS

 Grant the SELECT authority on the EMP table to all users GRANT SELECT ON EMP TO PUBLIC; DBMS by Er. K.S.Dhindsa © 2006

GRANTING & REVOKING – Privileges & Roles  Grant

all privileges on EMP table to user ‘DEEP’ GRANT ALL ON EMP TO DEEP;

 Give the system privileges for creating tables and views to ‘AJAY’ GRANT CREATE TABLE,CREATE VIEW TO AJAY; DBMS by Er. K.S.Dhindsa © 2006

GRANTING & REVOKING – Privileges & Roles  Grant SELECT,DELETE and UPDATE authority on DEPT TABLE to user ‘AJAY’ GRANT SELECT,DELETE,UPDATE ON DEPT TO AJAY;  Grant UPDATE authority on the SAL column of the EMP to user ‘AJAY’ GRANT UPDATE (SAL) ON EMP TO AJAY;

DBMS by Er. K.S.Dhindsa © 2006

REVOKE COMMAND Revoke command that allows the withdrawal of privileges SYNTAX :

REVOKE privileges On object From users

i) Revoke the system privileges for creating table from ‘Ajay’ REVOKE Create Table FROM Ajay; ii) Remove Delete and Update Authority on SAL and JOB columns of the EMP table from user ‘AJAY REVOKE delete, update (Sal, Job) ON EMP FROM Ajay; DBMS by Er. K.S.Dhindsa © 2006

REVOKE COMMAND



Revoke the SELECT privileges on EMP table from ‘Ajay’ REVOKE SELECT ON EMP FROM Ajay;



Remove all the privileges on EMP table from user ‘AJAY’ REVOKE ALL ON EMP FROM AJAY; DBMS by Er. K.S.Dhindsa © 2006

DATA SECURITY & INTEGRITY IN DATABASES

Lecture Series By :

Er. Kanwalvir Singh Dhindsa Deptt. of CSE & IT

DBMS by Er. K.S.Dhindsa © 2006