1 / 23

Data Integrity

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.

quant
Download Presentation

Data Integrity

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Integrity Lesson 12

  2. Skills Matrix

  3. 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. • 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.

  4. Enforcing Organizational Security Policies • 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.

  5. Identifying Risk • Asset identification • Risk assessment • Threat identification • Vulnerability assessment

  6. Understanding Forensic Requirements • Evidence collection • Evidence preservation • Chain of custody • Jurisdiction

  7. Implementing Physical Security Measures • Buildings and grounds • Devices • Communications • Storage media

  8. Planning and Education • Creating a Business Continuity Plan (BCP) • Creating a disaster recovery plan (DRP) • Educating Users

  9. Backup, 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).

  10. Security 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

  11. SQL 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.

  12. Maintaining 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).

  13. Principals

  14. Qualified 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.

  15. Qualified 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.

  16. Securables

  17. Permissions • Within each of these scopes exist permissions. • Permissions complete the security architecture model. • The permissions available for data manipulation language (DML) securables.

  18. Permissions Applicable to DML Securables

  19. Summary • 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.

  20. Summary • SQL Server has a sophisticated security system that allows you to carefully implement your security plan.

  21. Summary for Certification Examination • Understand SQL Server’s security architecture. • Understand the concepts of permissions, securables and principles.

  22. Summary • 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.

More Related