Database Administration And Security

The below post is notes prepared by me by studying the book "Database Systems Design, Implementation and Management" by Peter Rob and Carlos Coronel

Content, examples and diagrams are taken from that book.

Unit –V Chapter-II Database Administration And Security

Q: Explain the need for and role of database in an organization?

Ans: The DBMS helps a organization in many ways:

Interpretation and presentation of data

Distribution of data and information to the right people

Data preservation and monitoring the data usage

Control over data duplication and use

At the top management level, the database role is:

Provide the information necessary for strategic decision making.

Provide access to external and internal data

Provide a framework for defining and enforcing organization policies

At the middle management level, the database role is:

Deliver the data necessary for tactical decision and planning

Monitor and control the allocation and use of company resources.

Providing a framework for enforcing and ensuring the security and privacy of the data in the database.

At the operation management level, the database role is:

Represent and support the company operations as closely as possible.

Produce query results with in specified performance levels.

Enhance the company’s short term operational ability.

Q: The evolution of the database administration function?

Ans: The cost of data  and managerial duplication in decentralized and old file system gave rise to  centralized data administration function known as electronic data processing (EDP) or data processing (DP) department.

DP resolves data conflicts created by the duplication and/or misuse of data.

The advent of the DBMS and its shared view of data produced a new level of data management  and led the DP department to evolve into information systems (IS) department.

The responsibilities of IS department are

A service function to provide end user with active data management support.

A production function to provide end users with specific solutions for their information needs.

As the number of databases grew, data management became increasingly complex, thus leading to the development of database administration function.

The person responsible for the control of the centralized and shared database became known as the database administrator (DBA).

Devise administration Strategy        Has responsibility and authority to plan, define,

No authority to enforce it               implement  and enforce the policies, standards and

No authority to resolve conflicts                            procedures used in data administration activity.

The fast-paced changes in DBMS technology dictate changing organization styles. For example

Distributed Databases can force to decentralize the data administration function

Internet accessible data and growing number of data warehousing applications are likely to add to the DBA’s data modeling and design activities.

The new microcomputer environment required  the DBA to develop a new set of technical and managerial skills.

Functions of DBA

DBA function by dividing the DBA operations according to DBLC phases

DBA function requires personnel to cover the following activities.

Several different and incompatible DBMSs installed to support different operations.

There may also be variety of microcomputer DBMSs installed in different departments. In such an environment, the company might have one DBA assigned for each DBMS. The general coordinator of all DBAs is known as System administrator.

Differentiate between the responsibilities of data administrator (DA) and Database Administrator (DBA)?

Ans: The DA is responsible for controlling the overall corporate data resource, both computerized and manual.  Thus the DA’s job description covers a larger area of operations than that of the DBA because the DA is in charge for controlling not only the computerized data, but also the data outside the scope of the DBMS.

Data Administrator(DA)	Database Administrator (DBA)
1. Does strategic planning	controls and supervises
2. Sets long-term goals	Executes plans to reach goals
3. Sets policies and standards	Enforces policies and procedures Enforces programming standards
Is broad in scope	Is narrow in scope
Focuses on the long term	Focuses on the short term (daily operations)
Has a managerial orientation	Has a technical orientation
Is DBMS-independent	Is DBMS-specific

Q: What are desired  DBA skills? OR  Discuss the abilities and responsibilities of DBA

Ans: The DBA skills can be divided into two categories managerial and technical and summarized in the following table.

Managerial	Technical
Broad Business understanding	Broad data-processing background
Coordination skills	Systems development life cycle knowledge
Analytical skills	Structured Methodologies: Data flow diagrams Structures, Charts Programming languages
Conflict resolution skills	Database life cycle knowledge
Communication skills (oral and written)	Database modeling and design skills Conceptual Logical Physical
Negotiation Skills	Operational skills: database implementation, data dictionary management, security and so on
Experience : 10 years in a large DP department

Responsibilities (roles of DBA)

The DBA’s Managerial Role:

The DBA delivers services such as

End-User Support: These include

• Gathering User Requirements
• Building end-user confidence
• Resolving conflicts and problems
• Finding solutions to information needs
• Ensuring quality and integrity of data and applications
• Managing the training and support of DBMS user

Policies, Procedures and Standards: The DBA must define, document, and communicate the policies, procedures and standards before they can be enforced.

Policies are general statements or action

Example: All users must have passwords

      Passwords must be changed every six months.

Standards are rules that are used to evaluate the quality of the activity.

Example: A password must have a minimum of five characters.

      A password must have a maximum of twelve characters.

Procedures are written instructions that describe a series of steps to be followed during the performance of a given activity

Example: to create a user account

1. the user sends a written request to DBA.
2. the DBA approves the request and forwards it to computer operator
3. the operator creates the account and assigns a temporary password and sends to the user
4. a copy is sent to DBA
5. user changes temporary password to permanent one.

The DBA must define, communicate and enforce procedures that cover areas such as

1. End-user database requirements gathering
2. database design and modeling
3. documentation and naming conventions
4. design, coding and testing of database application programs
5. database software selection
6. database security and integrity
7. database backup and recovery
8. database maintenance and operation
9. end-user training

Data Security, privacy and integrity DBA must use security mechanisms provided by DBMS and also must team up with internet security experts to safeguard data from possible attacks or unauthorized access.

Data Backup and Recovery: The Backup and recovery measures must include at least:

• Periodic data and application backups
• Proper backup identification
• Convenient and Safe backup storage.
• Physical protection of both hardware and software
• Personal access control to software of a database installation
• Insurance coverage for the data in the database.

Data distribution and Use: The DBA is responsible for ensuring that the data are distributed to the right people at right time and in right format.

The DBA’s technical role

The DBA’s technical role requires a broad understanding of DBMS’s functions, configuration, programming languages, data modeling and design methodologies and so on.

The technical aspects of the DBA’s job

• Evaluating, selecting and installing the DBMS and related utilities:
  To match DBMS capability to organization’s needs, the DBA must check the following features in DBMS
      DBMS model                    DBMS storage capacity            Application development support
     Security and integrity         Back up and Recovery              Concurrency Control
     Performance                       Database administration tools   Interoperability and data distribution
     Portablity and standards     Hardware                                   Data dictionary
     Vendor training & support Available third-party tools         Cost
• Designing and implementing Databases and Applications: The DBA has to review the database applications design to ensure that transaction are
     Correct: the transaction mirror real world events
     Efficient: the transaction do not overload the DBMS.
     Compliant: complies with integrity rules and standards.           
• Testing and evaluating databases and applications: The evaluation process should cover all technical aspects of both the applications and the database. This process has to enforce all data validation rules.
• Operating the DBMS utilities and applications: DBMS operations are divided into four main areas:
            System support
            Performance monitoring and tuning
            Back up and recovery
            Security auditing and monitoring.
• Training and supporting users: Training people to use the DBMS and its tools is included in DBA’s technical activities.
• Maintaining the DBMS utilities and applications: periodic DBMS maintenance includes management of the physical or secondary storage devices. Maintenance activities also include upgrading the DBMS and utility software.

The DBA’s role as an arbitrator between data and users.

The DBA also verifies that programmer and end-user access meets the required quality and security standards.

Data base users might be classified by the

Type of decision making support required (operational, tactical or strategic)

Degree of computer knowledge (novice, proficient or export)

Frequency of access (casual, periodic or frequent)

The DBA must be able to interact with all of those people and understand their needs.

Q: What are the various database administration tools? Explain.

Ans:

Data dictionary: Data dictionary is defined as “a DBMS component that stores definition of data characteristics and relationships”. The data dictionary resembles an x-ray of the company’s entire dataset, and it is a crucial element in data administration.

Two main types of data dictionaries exists: integrated and standalone.

An integrated data dictionary is included with the DBMS. The DBA may use third party standalone data dictionary.

Data dictionaries can also be classified as active or passive.

An active data dictionary is automatically updated by DBMS with every database access, thereby keeping its access information up to date. A passive data dictionary is not updated automatically and usually requires running a batch process.

The DBA can use the data dictionary to support data analysis and design.

For ex, the DBA can create a report that lists all data elements to be used in a particular application, a list of all users who access a particular program, a report that checks data redundancies.

CASE tools: CASE is acronym for computer aided systems engineering. A CASE tool provides an automated framework for the SDLC.

Uses structured methodologies and graphical interfaces.

CASE tools are usually classified according to the extent of support they provide for the SDLC.

For ex: Front-end CASE tools provide support for planning, analysis and design phases.

Back-End CASE tools provide support for the coding and implementation phases.

Following are the benefits of CASE tools.

1. A reduction in development time and costs.
2. Automation of the SDLC
3. Standardization of systems development methodologies.
4. Easier maintenance of application system developed with CASE tools.

A typical CASE tool provides five components:

1. Graphics designed to produce structured diagrams such as data flow diagrams, ER diagrams, class diagrams etc.

2. Screen painters and report generators.

3. An integrated repository for storing and cross referencing the system design data.

4. An analysis segment to provide fully automated check on systems consistency, syntax and completeness.

5. A program documentation generator.

Q: Explain the usage of  ORACLE for database administration?

Ans: To perform any administrative task, you must connect to the database using a username with administrative (DBA) privileges. By default ORACLE automatically created SYSTEM and SYS user id that have administrative privileges with every new database you create.

Creating tablespaces and data files:

In ORACLE a database is logically composed of one or more tablespaces. A tablespace is a logical storage space.

The tablespace data are physically stored in one or more datafiles. ORACLE automatically creates the tablespace and data files.

The following are examples.

1. the SYSTEM tablespace is used to store the data dictionary data.
2. the USERS tablespace is used to store the table data created by the end users.
3. the TEMP tablespace is used to store the temporary tables and indexes created during the execution of SQL statements.
4. the UNDOTBS1 tablespace is used to store database transaction recovery information.

Managing the database objects: tables, views, triggers and procesures

The ORACLE enterprise manager gives the DBA a graphical user interface to create, edit, view and delete database objects in the database. A database object is basically any object created by end users.

Managing users and establishing security:

One of the most common database administration activities is creating and managing database users.

The security section of the ORACLE enterprise manager’s administration page enables the DBA to create users, roles and profiles.

1. A user is a uniquely identifiable object that allow a given person to login to the database
2. A role is a named collection of database access privileges that authorize a user to connect to the database and use the database system resources.
3. A profile is a named collection of settings that control how much of the database resource a given user can use.

Customizing the database initialization parameters:

Fine tuning a database is another important DBA task. This task usually requires the modification of database configuration parameters. Some of which can be changed in real time using SQL commands.

Each database has an associated database initialization file that stores its run-time configuration parameters. The initialization file is read at instance start up and is used to set the working environment for the database.

Creating a new database:

Using the ORACLE database configuration assistant, it is simple to create a database. The DBA uses a wizard interface to answer a series of questions to establish the parameters for the database to be created. This process creates the database structure, including the necessary data dictionary tables, the administrator, users accounts and other supporting process required by the DBMS to manage the database.

Responsibilities of DBA: The DBA is responsible for:

Designing the logical and physical schemas, as well as widely-used portions of the

external schema.

Security and authorization.

Data availability and recovery from failures.

Database tuning: The DBA is responsible for evolving the database, in particular

the conceptual and physical schemas, to ensure adequate performance as user

requirements change.

A DBA needs to understand query optimization even if s/he is not interested in running his or her own queries because some of these responsibilities (database design

and tuning) are related to query optimization. Unless the DBA understands the per-formance needs of widely used queries, and how the DBMS will optimize and execute these queries, good design and tuning decisions cannot be made  

What is data warehouse? Discuss about the properties of a data warehouse?

Ans: Data warehouse is an integrated, subject oriented, time variant, non volatile collection of data that provides support for decision making.

The following are important properties of a data warehouse.

Integrated:

The data warehouse is a centralized, consolidated database that integrates data derived from the entire organization and from multiple sources with diverse formats.

Data integration implies that all business entities, data elements, data characteristics and business metrics are described in the same way throughout the enterprise.

Subject oriented:

Data warehouse data are arranged and optimized to provide answer to questions coming from diverse functional areas within a company.

Data warehouse data are organized and summarized by topic. .

Instead of storing a INVOICE table, data warehouse stores its “sales by product” and “sales by customer” components.

Time variant:

Warehouse data represent the flow of data through time.

Once data are periodically uploaded to the data warehouse, all time dependent aggregations are recomputed.

For ex: once data for previous weekly sales are uploaded, the weekly, monthly, yearly and other time dependent aggregates for products, customers, stores and other variables are also updated.

Once the data enters the data warehouse, the time ID assigned to the data cannot be changed.

Non Volatile: Once data enter the data warehouse, they are never removed. Because data are never deleted and new data are continually added the data warehouse is always growing.