Transcript lesson12
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. – 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. 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. Identifying Risk 1. 2. 3. 4. Asset identification Risk assessment Threat identification Vulnerability assessment Understanding Forensic Requirements • Evidence collection • Evidence preservation • Chain of custody • Jurisdiction Implementing Physical Security Measures • Buildings and grounds • Devices • Communications • Storage media Planning and Education • Creating a Business Continuity Plan (BCP) • Creating a disaster recovery plan (DRP) • Educating Users 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). 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 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. 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). Principals Qualified Name • Every object in a database has a unique four part fully qualified name in the form InstanceName.DatabaseName.SchemaNam e.ObjectName. – This implies a server level, database level, schema level, and object level hierarchy. 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. Securables Permissions • Within each of these scopes exist permissions. • Permissions complete the security architecture model. • The permissions available for data manipulation language (DML) securables. Permissions Applicable to DML Securables 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. Summary • SQL Server has a sophisticated security system that allows you to carefully implement your security plan. Summary for Certification Examination • Understand SQL Server’s security architecture. – Understand the concepts of permissions, securables and principles. 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.