data integrity lesson 12. skills matrix maintaining data integrity maintaining data integrity is...
TRANSCRIPT
![Page 1: Data Integrity Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/html5/thumbnails/1.jpg)
Data IntegrityData IntegrityLesson 12
![Page 2: Data Integrity Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/html5/thumbnails/2.jpg)
Skills MatrixSkills Matrix
![Page 3: Data Integrity Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/html5/thumbnails/5.jpg)
Identifying RiskIdentifying Risk
1. Asset identification2. Risk assessment3. Threat identification4. Vulnerability assessment
![Page 6: Data Integrity Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/html5/thumbnails/6.jpg)
Understanding Forensic RequirementsUnderstanding Forensic Requirements
• Evidence collection• Evidence preservation• Chain of custody• Jurisdiction
![Page 7: Data Integrity Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/html5/thumbnails/7.jpg)
Implementing Physical Security Implementing Physical Security MeasuresMeasures
• Buildings and grounds• Devices• Communications• Storage media
![Page 8: Data Integrity Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/html5/thumbnails/13.jpg)
PrincipalsPrincipals
![Page 14: Data Integrity Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/html5/thumbnails/16.jpg)
SecurablesSecurables
![Page 17: Data Integrity Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/html5/thumbnails/18.jpg)
Permissions Applicable to DML Permissions Applicable to DML SecurablesSecurables
![Page 19: Data Integrity Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/html5/thumbnails/19.jpg)
![Page 20: Data Integrity Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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 Lesson 12. Skills Matrix Maintaining Data Integrity Maintaining data integrity is your most important responsibility. –Performing backups](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649ea15503460f94ba52a7/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.