Administration. Data administration functions

Сентябрь 4, 2022

Главная
Информатика
Administration. Data administration functions

Содержание

2. Data administration functions Data policies, procedures, standards Planning Data conflict (ownership) resolution Internal marketing of DA
3. New role, coming with the growth in data warehouses Similar to DA/DBA roles Emphasis on integration
4. Database security Protection of the data against accidental or intentional loss, destruction, or misuse Increased difficulty
5. Possible locations of data security threats
6. Accidental losses - attributable to Human error, software or hardware failure – tackle using procedures on
7. Data management software should have these security features: Views or subschemas which restrict user views of
8. Views and integrity controls Views - subset of the database that is presented to one or
9. Integrity controls CREATE DOMAIN PriceChange AS DECIMAL CHECK(VALUE BETWEEN .001 and 0.15); Then, in a pricing
10. Assertions are constraints enforcing desirable database conditions – they are checked automatically by the DBMS when
11. Triggers (which include an event, a condition and an action) can be used for security purposes)
12. Authorization rules Are controls incorporated in the data management system that restrict access to data and
13. Authorisation tables Many DBMS do not implement authorisation matrices but used simplified versions – two types
14. Authentication Schemes Goal – obtain a positive identification of the user Passwords are flawed: Users share
15. Database Recovery Mechanism for restoring a database quickly and accurately after loss or damage Recovery facilities:
16. Backup Facilities Automatic dump facility that produces backup copy of the entire database Periodic backup (e.g.
17. Journalizing Facilities Audit trail of transactions and database updates Transaction log – record of essential data
18. Database audit trail
19. SQL Server System Databases Master Purpose - Core system database to manage the SQL Server instance.
20. Resource Purpose - The Resource database is responsible for physically storing all of the SQL Server
21. TempDB Purpose - Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work
22. Model Purpose - Template database for all user defined databases Prominent Functionality Objects Columns Users Additional
23. MSDB Purpose - Primary database to manage the SQL Server Agent configurations Prominent Functionality SQL Server
24. Distribution Purpose - Primary data to support SQL Server replication Prominent Functionality Database responsible for the
25. ReportServer Purpose - Primary database for Reporting Services to store the meta data and object definitions
26. ReportServerTempDB Purpose - Temporary storage for Reporting Services Prominent Functionality Session information Cache Additional Information MSSQLTips
27. System Databases Do's and Don'ts Data Access - Based on the version of SQL Server query
28. Sneaking a Peak - Up to this point, all of the T-SQL code for the tables,
30. Скачать презентацию

Слайд 2

Data administration functions
Data policies, procedures, standards
Planning
Data conflict (ownership) resolution
Internal marketing

of DA concepts
Managing the data repository
Selection of hardware and software
Installing/upgrading DBMS
Tuning database performance
Improving query processing performance
Managing data security, privacy, and integrity
Data backup and recovery

Слайд 3

New role, coming with the growth in data warehouses
Similar to DA/DBA

roles
Emphasis on integration and coordination of metadata/data across many data sources
Specific roles:
Support decision –support applications
Manage data warehouse growth
Establish service level agreements regarding data warehouses and data marts

Data warehouse administration

Слайд 4

Database security
Protection of the data against accidental or intentional loss, destruction,

or misuse
Increased difficulty due to Internet access and client/server technologies

Слайд 5

Possible locations of data security threats

Слайд 6

Accidental losses - attributable to Human error, software or hardware failure

– tackle using procedures on user authorization, uniform software installation procedures, hardware maintenance schedules
Theft and fraud – attention should be focussed on all the locations in the previous Fig. (control of physical access, firewalls etc.)
Loss of privacy (personal data) and loss of confidentiality (corporate data)
Loss of data integrity – invalid/corrupt data – need established backup/recovery procedures
Loss of availability (through, e.g. sabotage)

Threats to data security can come from:

Слайд 7

Data management software should have these security features:
Views or subschemas which

restrict user views of the database (discussed previously)
Integrity controls which are enforced by the DBMS during querying and updating
Authorization rules identifying users and restricting actions they can take
User-defined procedures defining additional constraints
Encryption procedures for encoding data in an unrecognisable form
Backup, journalizing, and checkpointing capabilities which facilitate recovery procedures

Слайд 8

Views and integrity controls
Views - subset of the database that is

presented to one or more users -user can be given access privilege to view without allowing access privilege to underlying tables
Integrity Controls - protect data from unauthorized use
One type = Domain – create a user-defined data type and set allowable values. E.g. the PriceChangeDomain can be used as the datatype in any database field (such as PriceIncrease and PriceDiscount) to limit the amount prices can be changed in one transaction:

Слайд 9

Integrity controls
CREATE DOMAIN PriceChange AS DECIMAL
CHECK(VALUE BETWEEN .001 and 0.15);
Then, in

a pricing transaction table, we could have:
PriceIncrease PriceChange NOT NULL
One advantage of a domain is that if it ever has to change, it can be cahnged in one place (the domain definition) – if instead we used CHECK then we would have to go to every instance of CHECK and change it

Слайд 10

Assertions are constraints enforcing desirable database conditions – they are checked

automatically by the DBMS when transactions are run involving tables or fields on which assertions exist. E.g., assume that an EMPLOYEE table has fields of Emp_ID, Emp_Name, Supervisor_ID and Spouse_ID, and that a company rule is that no employee may supervise his or her spouse:
CREATE ASSERTION SpousalSupervision
CHECK (SupervisorID < > SpouseID)

Слайд 11

Triggers (which include an event, a condition and an action) can

be used for security purposes)
The powerful benefit of a trigger (also domains) is that that the DBMS enforces these controls for all users and all database activities – the control does not have to be coded into each query or program – so individual users and programs cannot circumvent the necessary controls

Слайд 12

Authorization rules
Are controls incorporated in the data management system that restrict

access to data and actions that people can take on data
Authorization matrix includes subjects, objects, actions and constraints
Each row of the table indicates that a particular subject is authorised to take a certain action on an object in the database (perhaps subject to some constraint)
Following fig shows authorisation matrix where anyone in the Sales Department can insert a new customer record into the database, providing the credit limit does not exceed $5000

Слайд 13

Authorisation tables
Many DBMS do not implement authorisation matrices but used simplified

versions – two types – authorisation tables for subjects and authorisation tables for objects.
In the table for subjects, we can see that salespersons are allowed to modify customer records but not delete those records
In the table for objects, we can see that users in Order Entry or Accounting can modify order records, but salespersons cannot

Слайд 14

Authentication Schemes
Goal – obtain a positive identification of the user
Passwords are

flawed:
Users share them with each other
They get written down, could be copied
Automatic logon scripts remove need to explicitly type them in
Unencrypted passwords travel the Internet
Possible solutions:
Biometric devices – use of fingerprints, retinal scans, etc. for positive ID
Third-party authentication – using secret keys, digital certificates

Слайд 15

Database Recovery
Mechanism for restoring a database quickly and accurately after loss

or damage
Recovery facilities:
Backup Facilities
Journalizing Facilities
Checkpoint Facility
Recovery Manager

Слайд 16

Backup Facilities
Automatic dump facility that produces backup copy of the entire

database
Periodic backup (e.g. nightly, weekly)
Cold backup – database is shut down during backup
Hot backup – selected portion is shut down and backed up at a given time
Backups stored in secure, off-site location

Слайд 17

Journalizing Facilities
Audit trail of transactions and database updates
Transaction log – record

of essential data for each transaction processed against the database
Database change log – images of updated data
Before-image – copy before modification
After-image – copy after modification

Слайд 18

Database audit trail

Слайд 19

SQL Server System Databases
Master
Purpose - Core system database to

manage the SQL Server instance.
In SQL Server the Master database is the logical repository for the system objects residing in the sys schema.
Prominent Functionality
Per instance configurations
Databases residing on the instance
Files for each database
Logins
Linked\Remote servers
Endpoints

Слайд 20

Resource
Purpose - The Resource database is responsible for physically

storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
Prominent Functionality
System object definition
Additional Information
Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
Prior to SQL Server 2005 the system related data was stored in the master database
Read-only database that is not accessible via the SQL Server 2005 tool set
The Resource database does not have an entry in master.sys.databases

Слайд 21

TempDB
Purpose - Temporary database to store temporary tables (#temptable or

##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
Prominent Functionality
Manage temporary objects listed in the purpose above
Additional Information
Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state

Слайд 22

Model
Purpose - Template database for all user defined databases
Prominent

Functionality
Objects
Columns
Users
Additional Information
User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
The database configurations such as the recovery model for the Model database are applied to future user defined databases

Слайд 23

MSDB
Purpose - Primary database to manage the SQL Server Agent

configurations
Prominent Functionality
SQL Server Agent Jobs, Operators and Alerts
SSIS Package storage in SQL Server 2005
Additional Information
Provides some of the configurations for the SQL Server Agent service
For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database
Missing SQL Server Agent History
MSSQLTips Category – SQL Server Agent

Слайд 24

Distribution
Purpose - Primary data to support SQL Server replication
Prominent

Functionality
Database responsible for the replication meta data
Supports the data for transaction replication between the publisher and subscriber(s)
Additional Information
MSSQLTips Category - Replication

Слайд 25

ReportServer
Purpose - Primary database for Reporting Services to store the

meta data and object definitions
Prominent Functionality
Reports security
Job schedules and running jobs
Report notifications
Report execution history
Additional Information
MSSQLTips Category – Reporting Services

Слайд 26

ReportServerTempDB
Purpose - Temporary storage for Reporting Services
Prominent Functionality
Session

information
Cache
Additional Information
MSSQLTips Category – Reporting Services

Слайд 27

System Databases Do's and Don'ts
Data Access - Based on the

version of SQL Server query only the recommended objects. In general the system database objects are being deprecated to a set of views, so be sure all of your scripts are accessing the right objects. If not, you are going to have a big project in the future to convert all of your scripts.
Changing Objects - Do not change system objects. In SQL Server 2005 all of the database objects have been moved to the Resource database which stores the definition for the system objects and can be updated via new SQL Server releases independent of the data.
New Objects - Creating objects in the system databases is not recommended. If you have objects that are needed for the instance i.e. administrative items, just create a separate DBA database to store these objects.

Слайд 28

Sneaking a Peak - Up to this point, all of the

T-SQL code for the tables, views, stored procedures, functions, etc. has been clear text. So you can review the objects and learn from the techniques used by Microsoft.
Dropping Objects - The most prominent reason to drop system objects are for specific types of lock downs and auditing in particular industries. Although some of those practices are well documented, be sure you understand the ramifications related to administering and developing applications once those restrictions are in place.

Administration. Data administration functions

Содержание

Data administration functionsData policies, procedures, standardsPlanningData conflict (ownership) resolutionInternal marketing

New role, coming with the growth in data warehousesSimilar to DA/DBA

Database security Protection of the data against accidental or intentional loss, destruction,

Possible locations of data security threats

Accidental losses - attributable to Human error, software or hardware failure

Data management software should have these security features: Views or subschemas which

Views and integrity controlsViews - subset of the database that is

Integrity controlsCREATE DOMAIN PriceChange AS DECIMALCHECK(VALUE BETWEEN .001 and 0.15);Then, in

Assertions are constraints enforcing desirable database conditions – they are checked

Triggers (which include an event, a condition and an action) can

Authorization rulesAre controls incorporated in the data management system that restrict

Authorisation tablesMany DBMS do not implement authorisation matrices but used simplified

Authentication SchemesGoal – obtain a positive identification of the userPasswords are

Database RecoveryMechanism for restoring a database quickly and accurately after loss

Backup FacilitiesAutomatic dump facility that produces backup copy of the entire

Journalizing FacilitiesAudit trail of transactions and database updatesTransaction log – record

Database audit trail

SQL Server System DatabasesMaster Purpose - Core system database to

Resource Purpose - The Resource database is responsible for physically

TempDB Purpose - Temporary database to store temporary tables (#temptable or

Model Purpose - Template database for all user defined databases Prominent

MSDB Purpose - Primary database to manage the SQL Server Agent

Distribution Purpose - Primary data to support SQL Server replication Prominent

ReportServer Purpose - Primary database for Reporting Services to store the

ReportServerTempDB Purpose - Temporary storage for Reporting Services Prominent Functionality Session

System Databases Do's and Don'ts Data Access - Based on the