database management system - aiktc › wp-content › uploads › 2019 › ... · integrity...
TRANSCRIPT
IntegrityandSecurityinDatabases
Database Management System
AIKTC
Prof Muhammed Salman Shamsi
Disclaimer• Allthematerialsusedinthispresentationbelongstotherespectiveauthorsmentionedinreferencesection.
• ThispresentationistoonlyhelpthestudentscommunityofMumbaiUniversityforthesubjectofDatabaseManagementSystemandisforprivatecirculationonly.
• Ineitherclaimthismaterialoruseitforcommercialpurpose.
• Thispresentationispurelyforeducationpurpose.
IntegrityConstraints
• Integrityconstraintsguardagainstaccidentaldamagetothedatabase,byensuringthatauthorizedchangestothedatabasedonotresultinalossofdataconsistency.
• Itisthemechanismtopreventinvaliddataentryintothetable.
• Henceintegrityconstraintsarelimitationsorsetofrulesimposedondataofdatabaseinordertokeepdatabaseinconsistentorcorrectstate.
• DomainConstraints&ReferentialIntegrityConstraintsarethetypesofIntegrityConstraints.
DomainConstraints• Domainconstraintsarethemostelementryformofintegrityconstraints.
• Theytestthevaluesinsertedinthedatabase,andtestqueriestoensurethatthecomparisionmakesense.
• Newdomainscanbecreatedfromtheexistingdatatypes:• createdomain<new_domain_name>as<new_data_type>
createdomain Dollarsasnumeric(12,2)createdomain Poundsasnumeric(12,2)
• Note:wecannotassignorcompareavalueoftypeDollarstoavalueoftypePounds.Howeverwecanconverttypeasbelow:
(cast r.Aas Pounds)
ReferentialIntegrity• Ensuresthatavaluethatappearsinonerelationforagivensetofattributesalsoappearsforacertainsetofattributesinanotherrelation.
• LetAbeasetofattributes.LetRandSbetworelationsthatcontainattributesAandwhereAistheprimarykeyofS.Aissaidtobeaforeignkey ofRifforanyvaluesofAappearinginRthesevaluesalsoappearinS.RandSarenotnecessarilydistinct.
• IfaforeignkeyFinatableRreferstoandmatchestheprimarykeyPoftableStheneveryvalueofFmusteitherbeequaltovalueofPorwhollyNULL.
•
CascadingActionsinReferentialIntegrity• createtablecourse(
course_id char(5)primarykey,titlevarchar(20),dept_name varchar(20)referencesdepartment
)• createtablecourse(
…dept_name varchar(20),foreignkey(dept_name) referencesdepartment
ondeletecascadeonupdatecascade,
...)
• alternativeactionstocascade:setnull,setdefault
7
ColumnConstraintsandTableConstraints
Iftheconstraintsaredefined along withthe acolumndefinitionofatable,than they arecalledcolumnconstraints.Theseconstraintsinvolveonlyoneattribute.Ifmorethanoneattributeisinvolvedthetableconstraintmustbeused.Acolumnconstraintwillnotbecheckedifvaluesinothercolumnsarebeingupdated.Ifthedataconstraintsattachedtoaspecificcolumninatablereferencesthecontentsofaanothercolumninthetablethentheyarecalledastableconstraints.
ExamplesofdifferentConstraints
• NotNullconstraint• PrimaryKeyconstraint• UniqueConstraint• DefaultvalueConstraint• ForeignKeyConstraint• CheckIntegrityConstraints
PKasaColumnConstraintA column constraint is usually used when the PK is a single attribute.
Constraint: Data entered in the column must be unique and not null.
CREATE TABLE Match(MatchID INT PRIMARY KEY, Team1 CHAR(15), Team2 CHAR(15), Ground CHAR(20), Date CHAR(10), Result CHAR(10));
PKasaTableConstraint
AtableconstraintisusuallyusedwhenthePKismorethanasingleattribute.
CREATETABLEBowling(MID INT,PID INT,NOvers INT,Maidens INT,NRuns INT,NWickets INT,PRIMARYKEY(MID,PID));
FKasaColumnConstraint
AcolumnconstraintisusuallyusedwhentheFKisasingleattribute.
CREATETABLEEmployee(EmpID NUMERIC(6)PRIMARYKEY,Name CHAR(20),Dept CHAR(10),REFERENCESDepartment (DeptID),Address CHAR(50)Position CHAR(20));
FKasaTableConstraintAtableconstraintisusuallyrequiredwhentheFKismorethanasingleattribute.
CREATETABLEBowling(MatchID INT,PID INTEGER,NOvers INT,Maidens INT,NRuns INT,NWickets INT,PRIMARYKEY(MID,PID)FOREIGNKEY(MatchID)REFERENCESMatch,FOREIGNKEY(PID)REFERENCESPlayer);
NULLasaColumnConstraint
CREATE TABLE Match(MatchID INT PRIMARY KEY, Team1 CHAR(15) NOT NULL,Team2 CHAR(15) NOT NULL,Ground CHAR(20), Date CHAR(10), Result CHAR(10));
DEFAULTasaColumnConstraint
CREATETABLEMatch(MatchID INTPRIMARYKEYTeam1 CHAR(15)DEFAULT‘India’,Team2 CHAR(15),Ground CHAR(20),Date CHAR(10),Result CHAR(10));
UNIQUEasaColumnConstraint
AcolumnconstraintisusuallyusedwhenUNIQUEisasingleattribute.
CREATETABLEEmployee(EmpID NUMBER(6)PRIMARYKEY,Name CHAR(20),DeptID CHAR(10),Telephone INTUNIQUE,Address CHAR(50),Position CHAR(20);
UNIQUEasaTableConstraint
AtableconstraintisusuallyrequiredwhenUNIQUEismorethanasingleattribute.
CREATETABLEPlayer(PlayerID INTPRIMARYKEY,LName CHAR(15),FName CHAR(15),Country CHAR(20),YBorn INT,BPlace CHAR(20)FTest INT,UNIQUE(LName,FName));
CHECKConstraint
Possible conditions in the CHECK clause
1 attribute A > value v
2 attribute A between value v1 and value v2
3 attribute A IN (list of values)
4 Attribute A IN subquery
5 attribute A condition C1 OR condition C2
6 attribute A condition C1 AND condition C2
CHECKasaColumnConstraint
CREATETABLEPlayer(PlayerID INTPRIMARYKEY,LName CHAR(15),FName CHAR(15),Country CHAR(20),YBorn INTCHECK(YBorn >1950),BPlace CHAR(20),FTest INT);
CHECKasaTableConstraintAtableconstraintisusedwhentheCHECKconstrainthasmorethanasingleattribute.
CREATETABLEPlayer(PlayerID INTPRIMARYKEY,LName CHAR(15)NOTNULL,FName CHAR(1)NOTNULL,Country CHAR(20),YBorn INT,BPlace CHAR(20),FTest INT,CHECK(FTest >YBorn +15));
Alternativewaystocreateconstraints
• Syntax:constraint[<constraint_name>]constraint_definition;
• IncreatecommandcreatetableStudent(sidvarchar(20),mobilenovarchar(10),....................................,constraintstud_pkprimarykey(sid),constraintm_uniqueunique(mobileno));
• InAltercommandAltertableStudentADDCONSTRAINTcheck_ageCHECK(age>16);
• DroppingaconstraintAltertableStudentDROPCONSTRAINTcheck_age;
ComplexCheckClauses• Complexcheckconditionscanbeusefulwhenwewanttoensureintegrityofdata,butmaybecostlytotest.check (timeslot_idin (select timeslot_idfrom timeslot))
• Forexample,thepredicateinthecheckclausewouldnotonlyhavetobeevaluatedwhenamodificationismadetothesectionrelation,butmayhavetobecheckedifamodificationismadetothetimeslotrelationbecausethatrelationisreferencedinthesubquery.
• Unfortunately:subqueryincheckclausenotsupportedbyprettymuchanydatabase
Assertion• Anassertion isapredicateexpressingaconditionthatwewishthedatabasealwaystosatisfy.
• Domainconstraints andreferential-integrityconstraints arespecialformsofassertions.
• createassertion<assertion-name>check<predicate>;• Alsorarely supportedbyanyone
• Twoexamplesofsuchconstraintsare:•Foreachtupleinthestudentrelation,thevalueoftheattributetot_credmustequalthesumofcreditsofcoursesthatthestudenthascompletedsuccessfully.• Aninstructorcannotteachintwodifferentclassroomsinasemesterinthesametimeslot.
AssertionExample
createassertion credits_earned constraintcheck(notexists(select ID
from studentwhere tot_cred<>(select sum(credits)from takesnatural join coursewhere student.ID=takes.ID
and gradeisnot nulland grade<>’F’);
Triggers• Atrigger isastatementthatisexecutedautomaticallybythesystemasasideeffectofamodificationtothedatabase.
• Todesignatriggermechanism,wemust:• Specifytheconditionsunderwhichthetriggeristobeexecuted.
• Specifytheactionstobetakenwhenthetriggerexecutes.
• Theabovemodeloftriggersisreferredtoastheevent-condition-action modelfortrigger.
NeedforTriggers
• TriggerscanbeusedtoimplementcertainintegrityconstraintsthatcannotbespecifiedusingtheconstraintmechanismofSQL.
• Triggersarealsousefulmechanismsforalertinghumansorforstartingcertaintasksautomaticallywhencertainconditionsaremet.
• Asanillustration,wecoulddesignatriggerthat,wheneveratupleisinsertedintothetakesrelation,updatesthetupleinthestudentrelationforthestudenttakingthecoursebyaddingthenumberofcreditsforthecoursetothestudent’stotalcredits.
TriggerSyntax[MySQL]
CREATE[DEFINER ={user|CURRENT_USER }]TRIGGER trigger_nametrigger_timetrigger_eventON tbl_nameFOREACHROWtrigger_body
trigger_time:{BEFORE |AFTER }trigger_event:{INSERT |UPDATE |DELETE }
TriggeringEventsandActionsinSQLTriggeringeventcanbeinsert,delete orupdateTriggersonupdatecanberestrictedtospecificattributesFor example, after update of takes on gradeValuesofattributesbeforeandafteranupdatecanbereferencedreferencing old row as : for deletes and updatesreferencing new row as : for inserts and updatesTriggerscanbeactivatedbeforeanevent,whichcanserveasextraconstraints.Forexample,convertblankgradestonull.
TriggerExampleIBMDB2
createtrigger setnullbeforeupdateon takesreferencingnewrowas nrowforeachrowwhen (nrow.grade=’’)beginatomic
set nrow.grade=null;end;
TriggerExampleMySQL
createtrigger setnullbeforeupdateontakesforeachrowbegin
ifnew.grade=''thensetnew.grade=null;
endif;end;
createtrigger credits_earnedafterupdateof takeson (grade)referencingnewrowas nrowreferencingoldrowas orowforeachrowwhen nrow.grade<>’F’and nrow.gradeisnotnulland (orow.grade=’F’or orow.gradeisnull)beginatomicupdate studentset tot_cred=tot_cred+(select creditsfrom coursewhere course.course_id=
nrow.course_id)where student.id=nrow.id;
end;
Triggersformaintainingreferentialintegrity
createtrigger timeslot_check1afterinserton sectionreferencingnewrowas nrowforeachrowwhen (nrow.time_slot_idnotin (
select time_slot_idfrom time_slot))/*timeslotidnot
presentintimeslot*/begin
rollbackend;
WhenNottoUseTriggers• Noneedto implementtheondeletecascadefeatureofaforeign-key
constraintbyusingatrigger,insteadofuse thecascadefeature.• Thereisnoneedtowritetriggercodeformaintainingmaterializedviews.• Moderndatabasesystems,providebuilt-infacilitiesfordatabase
replication,makingtriggersunnecessaryforreplicationinmostcases.• Triggersshouldbewrittenwithgreatcare,sinceatriggererrordetectedat
runtimecausesthefailureoftheactionstatementthatsetoffthetrigger.Furthermore,theactionofonetriggercansetoffanothertrigger.Intheworstcase,thiscouldevenleadtoaninfinitechainoftriggering.
• Manytriggerapplicationscanbesubstitutedbyappropriateuseofstoredprocedures
Security
• Securityisaprotectionfrommaliciousattemptstostealormodifydata.Thesecurityshouldbeprovidedatfollowinglevels:
• 1)Databasesystemlevel.(useracessonlyrequireddata)• 2)Operatingsystemlevel.(superuser)• 3)Networklevel.
(encryption,eavesdropping,masquerading)• 4)Physicallevel.• 5)Humanlevel.(usertraining)
Authorization
Formsofauthorizationonpartsofthedatabase:
Read - allowsreading,butnotmodificationofdata.Insert - allowsinsertionofnewdata,butnotmodificationofexistingdata.Update - allowsmodification,butnotdeletionofdata.Delete - allowsdeletionofdata.
Formsofauthorizationtomodifythedatabaseschema
Index - allowscreationanddeletionofindices.Resources - allowscreationofnewrelations.Alteration - allowsadditionordeletionofattributesinarelation.Drop - allowsdeletionofrelations.
AuthorizationSpecificationinSQL
Thegrant statementisusedtoconferauthorization
grant <privilegelist>on<relationnameorviewname>to <userlist><user list> is: a user-idpublic, which allows all valid users the privilege grantedA role (more on this later)
Grantingaprivilegeonaviewdoesnotimplygrantinganyprivilegesontheunderlyingrelations.Thegrantoroftheprivilegemustalreadyholdthe
PrivilegesinSQLselect: allowsreadaccesstorelation,ortheabilitytoqueryusingtheview
Example: grant users U1, U2, and U3 select authorization on the instructor relation:
grantselectoninstructortoU1,U2,U3
insert:theabilitytoinserttuplesupdate:theabilitytoupdateusingtheSQLupdatestatementdelete:theabilitytodeletetuples.all privileges:usedasashortformforalltheallowableprivileges
RevokingAuthorizationinSQLTherevoke statementisusedtorevokeauthorization.
revoke <privilege list>on <relation name or view name> from <user list>
Example:
revoke select on branch from U1, U2, U3<privilege-list>maybealltorevokeallprivilegestherevokeemayhold.If<revokee-list>includespublic,alluserslosetheprivilegeexceptthosegranteditexplicitly.Ifthesameprivilegewasgrantedtwicetothesameuserbydifferentgrantees,theusermayretaintheprivilegeaftertherevocation.Allprivilegesthatdependontheprivilegebeingrevokedarealsorevoked.
Roles• createrole instructor;• grant instructor toAmit;• Privilegescanbegrantedtoroles:
• grant select on takes to instructor;• Rolescanbegrantedtousers,aswellastootherroles
• create role teaching_assistant• grant teaching_assistant to instructor;
• Instructor inheritsallprivilegesofteaching_assistant• Chainofroles
• create role dean;• grant instructor to dean;• grant dean to Satoshi;
LimitationsofSQLAuthorization• SQLdoesnotsupportauthorizationatatuplelevel.• Allendusersofanapplicationmaybemappedtoasingledatabaseuser.
• Thetaskofauthorizationinabovecasesfallsontheapplicationprogram,withnosupportfromSQL:
Benefits:Finegrainedauthorizationsimplementedbyapplications
Drawback:Authorizationloopholesarecreatedwhichbecomesdifficulttofindduetolargeamount
ofapplicationcode
References
• DatabaseManagementSystem,G.KGupta,TataMcGrawHill• DatabaseSystemConcepts,Korth,Sudarshan et.al.,TataMcGrawHill