data integrity
DESCRIPTION
Data Integrity. Lesson 12. Skills Matrix. Maintaining Data Integrity. Maintaining data integrity is your most important responsibility. Performing backups of all data on a regular schedule as your first layer of defense. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/1.jpg)
Data IntegrityData IntegrityLesson 12
![Page 2: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/2.jpg)
Skills MatrixSkills Matrix
![Page 3: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/3.jpg)
Maintaining Data IntegrityMaintaining Data Integrity• Maintaining data integrity is your most
important responsibility. – Performing backups of all data on a regular
schedule as your first layer of defense. – The time between the last backup and the
moment of disaster could involve some data loss.
– You must work with your management to find the acceptable loss rate, combined with the investment rate to avoid the anticipated loss, to maintain this goal.
![Page 4: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/4.jpg)
Enforcing Organizational Security Enforcing Organizational Security PoliciesPolicies• The most important phase of
maintaining data integrity, and the effort demanding your greatest attention, is the “watchdog” phase.
• You must constantly run baselines to check for performance degradation and potential data losses.
• You must constantly encourage users to use safe practices.
![Page 5: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/5.jpg)
Identifying RiskIdentifying Risk1. Asset identification2. Risk assessment3. Threat identification4. Vulnerability assessment
![Page 6: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/6.jpg)
Understanding Forensic RequirementsUnderstanding Forensic Requirements• Evidence collection• Evidence preservation• Chain of custody• Jurisdiction
![Page 7: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/7.jpg)
Implementing Physical Security Implementing Physical Security MeasuresMeasures• Buildings and grounds• Devices• Communications• Storage media
![Page 8: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/8.jpg)
Planning and EducationPlanning and Education• Creating a Business Continuity Plan
(BCP)• Creating a disaster recovery plan
(DRP)• Educating Users
![Page 9: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/9.jpg)
Backup, Backup, BackupBackup, Backup, Backup• A backup and restoration process creates
your first line of defense against data loss.
• A backup copies your data to a store somewhere other than on the mass storage devices supporting your database, usually on some type of tape media.– You can also store a backup on a hard
drive on another computer connected over a local area network (LAN).
![Page 10: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/10.jpg)
Security PlanSecurity Plan• The first step in safeguarding any type of
system must be a good security plan:– Type of users– Fixed server roles– Database access– Type of access– Group permissions– Object creation– Auditing– Public role permissions– Guest access
![Page 11: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/11.jpg)
SQL Server’s Security ArchitectureSQL Server’s Security Architecture• Starting with SQL Server 2005, Microsoft
introduced the concepts of working with principles, securables and permissions.– Principle: An entity (such as a login, user,
group, or role) that can be granted access to a securable resource.
– Securable: Entities that can be secured with permissions.
– Permissions: A rule associated with an object to regulate which users can gain access to the object and in what manner.
![Page 12: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/12.jpg)
Maintaining Data IntegrityMaintaining Data Integrity• Principals are entities that can request
SQL Server resources. • Principals are arranged in a hierarchy. • The scope of influence of a principal
depends on the scope of the definition of the principal: Windows, Server, Database, or whether the principal is indivisible or a collection
• Every principal has a security identifier (SID).
![Page 13: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/13.jpg)
PrincipalsPrincipals
![Page 14: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/14.jpg)
Qualified NameQualified Name• Every object in a database has a
unique four part fully qualified name in the form InstanceName.DatabaseName.SchemaName.ObjectName. – This implies a server level, database
level, schema level, and object level hierarchy.
![Page 15: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/15.jpg)
Qualified NameQualified Name• The four part SQL Server security architecture
begins with the InstanceName. • SQL Server can be installed into multiple instances
on a single Windows operating system (OS) and hardware platform.
• The default SQL Server installation assumes the name of the OS platform and thus this four part naming convention has been traditionally expressed as
ServerName.DatabaseName.SchemaName.ObjectName
• Within each of these four scopes exist securables.
![Page 16: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/16.jpg)
SecurablesSecurables
![Page 17: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/17.jpg)
PermissionsPermissions• Within each of these scopes exist
permissions. • Permissions complete the security
architecture model. • The permissions available for data
manipulation language (DML) securables.
![Page 18: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/18.jpg)
Permissions Applicable to DML Permissions Applicable to DML SecurablesSecurables
![Page 19: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/19.jpg)
![Page 20: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/20.jpg)
SummarySummary• This lesson examined the forethought
and planning that must be undertaken to maintain data integrity. – Don’t forget anything. Can a water pipe
rupturing flood your computer room? – Test your recovery plan for every
possible disaster. – Practice a database restore (to a
different spindle) to assure your procedures actually work.
![Page 21: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/21.jpg)
SummarySummary• SQL Server has a sophisticated
security system that allows you to carefully implement your security plan.
![Page 22: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/22.jpg)
Summary for Certification ExaminationSummary for Certification Examination• Understand SQL Server’s security
architecture. – Understand the concepts of
permissions, securables and principles.
![Page 23: Data Integrity](https://reader034.vdocuments.us/reader034/viewer/2022051402/56815d4a550346895dcb5216/html5/thumbnails/23.jpg)
SummarySummary• You can partition views in the same
way you can partition tables and for the same reasons: store parts of your views on different spindles or even different servers.