CIS 2210 - Database Management and Design

Chapter 16: Database Administration and Security


This lesson discusses material from chapter 16. Objectives important to this lesson:

  1. Data as an asset
  2. Establishing a role for your DBMS
  3. DBA roles
  4. Human elements
  5. Security for data, the database, and the organization
  6. Tools and strategies

Chapter 16

Data as an Asset

The chapter begins with a not very convincing argument that data is valuable because it can provide your company with competitive advantages. To have value, there must be something you can do with the information that can be derived from the data. In the graphic on page 723, the text illustrates a concept it calls the data-information-decision cycle.

  1. A body of data is analyzed by a human and/or software to become information. Are the decisions the company has made, and the actions it has taken producing revenue for the company?
  2. Various kinds of information are combined to become a related body of knowledge.
  3. The knowledge is used to make decisions about the business.
  4. Actions are taken based on the decisions.
  5. More data is gathered from and about the actions. This takes us back to step 1, where this data is reviewed in the next cycle.

On the next page, the text explores some causes of dirty data, data that is incorrect.

  • failure to enforce entity integrity and referential integrity
  • human error in data entry
  • failure to stick with a chosen vocabulary, resulting in queries that are missing records they should have found
  • tables that are different from one project to another, from one system to another due to different perceptions of the data

Some of the errors above result in problems across the enterprise. Standardization becomes more important the larger the organization becomes. You can't just start out with good intentions and trust that everything will go well. Data assets need to be examined and changed to meet standards, which includes keeping them current. The text recommends setting up master copies of tables that are used in multiple system. Updates should be made to the master copy, then the other necessary copies should update from the master. This controls redundancy and sets a standard that other tables should follow.

Establishing a Role for Your DBMS

Even though we use a DBMS in each department of our organization, we can be using different data spaces with different rules and different versions of what should be the same tables. This makes our database look more like an electronic version of an outdated paper file system. Central control and management can fix this problem.

The text stresses that an enterprise database is a planning tool. This is a special database that tracks the activity of the company that uses it. It is there to support managers making decisions, and to support the three standards goals of security: Confidentiality, Integrity, and Availability.

The text presents a common model of management:

  • top level - concerned with the big picture; strategic, long term plans are made here, as are company-wide policies; sometimes plans are made for a year at a time
  • middle level - tactical level concerns are the focus here; plans are made for an organizational unit, perhaps separated from others by geography or by job function; plans may be made here for a month at a time
  • lower level - operational level concerns are more a day to day thing, often handling the problems of first line staff

The text presents several bullets for each category above, detailing the planning concerns at that level. It should be remarked that management at any level may need to access data for concerns at a higher or lower level as well. Sometimes everyone is concerned with the emergency of the day. The general idea is that the enterprise database will contain information that concerned parties in the enterprise will use to carry out their jobs. It is unlikely that any single database will contain all the information or tools a person will need, but it should provide access to everything it has that a person needs.

The text also discusses three problem areas that are typically encountered when an organization installs a management tool/enterprise database such as it has been proposing:

  • technological - installation and operation of the tool
  • managerial - using the tool to perform management functions
  • cultural - helping staff and managers accept the new system

Note that the third area concerns what we usually call soft skills. People need time to accept change. They often need to be convinced that there are good reasons for that change. This aspect of the text is not pertinent to your skills in designing tables or linking them to each other. We will move on.

DBA Roles

The text provides some history about:

  • the creation of data processing departments in companies that mostly did data entry and reports
  • the evolution of data processing into information systems departments that wrote programs and handled data
  • the continued development of IT/IS departments to have specialized teams, including database administrators

The text explains that database administrators (DBAs) often fall into one of two organization chart locations. They may be in staff positions. In that case, they advise other IT staff, they design database structures, but they don't do general policy creation or enforcement. A DBA in a line position does all of those things, including the enforcement of policies. The text goes on to say that these two organization chart examples are not the only possibilities, but they are common. A DBA may be attached to any part of an IT chart that maintains services on servers.

On page 729, the text presents another view of a Database Life Cycle (DBLC). It tells us that the DBA needs to be involved in all the steps listed there. I am a bit confused by the next illustration, which shows each of the functions in this version of the DBLC as a functional organization chart for the DBA position. They are all duties the DBA would perform. I don't think putting each of them on a branch of the DBA's activity tree helps us much. The second org chart fragment on page 730 is clearer. It shows a specialist DBA for each of five kinds of systems, and all of them work for a systems administrator, who oversees all of their work. At the bottom of that page, the author adds another job title, data administrator (DA). This role is mentioned again in the next section. It is a higher management position that reports to the upper layers of the organization. Think of it as a vice president over all the database functions of the organization.

Human Elements

This section has an odd start. It begins with the assumption that your organization has DBAs and at least one DA over them. If this is so, the chart on page 731 will probably apply, with the higher level, managerial functions performed by the DA and the lower level, more technical functions performed by one or more DBAs. The part that feels odd to me is the idea that the DBA may have to do all of these functions is there is no DA involved. This is not entirely so. There may be some form of governance over IT functions that would have to be satisfied before new systems are brought up, new policies are enabled, and new job functions are added to a person or to a team.

On page 733, there is a table of skills that a good DBA should have, divided into two categories: managerial and technical. The managerial skills are needed by any IT person working as a liaison between a system and its users, whether building or maintaining that system. The text continues the managerial section for a few pages (to page 738), and it seems that the author is assuming that the DBA is either a team leader or a manager with regard to the allocation of human resources mentioned on pages 733 and 734.

It is more enlightening to read through the bulleted items on the pages in this section, noting each of them as a step in the professional development of a system. In the section on End-User Support, you will see six topics that make the system better and the users happier. In the next section, Policies, Procedures, and Standards, the text offers a short example of each of those terms. In the context of this chapter, a policy is a rule that tells you what to do. A standard is a clarifying rule that tells you specific features your compliance must have. For example, there is a policy that you must have a password. There are two standards that state the minimum and maximum lengths of a password. A procedure is a longer document that tells you the steps that must be followed, and by whom, to do something, such as enabling a user to sign in for the first time and create a new password. The text provides an example of a procedure describing this process.

As an aside, I will note that these definitions of standard and procedure are not universal. Some organizations might consider a standard to be a modification of a procedure to be used in particular circumstances which vary from one location to another. The example in the text is simply a more detailed instruction, not linked to a "problem" environment. It should also be noted that managers are not the only ones who write procedures, but they are typically the source/conduit of rules. Most people will resist a rule unless they hear about it from their immediate manager, the person who approves or disapproves their work. Some IT departments, and some upper management people, forget about this, thinking they can just make a rule that people will blindly follow.

This section of the chapter goes on to consider other duties that the DBA or analysts working with the DBA would probably carry out. Look them over and discuss any you have questions about.

On page 736, the text returns to the idea of security, which is more integral to the work a DBA does. Security policies that apply to a database may exist in the DBMS or in the network software, or in both. I think the author is padding this chapter, adding the section that follows about backup, recovery, and disaster management. These are topics that apply no more to a particular database than to any other IT asset of the organization. The system that the DBA manages should be part of an overall backup and recovery plan. It you have never read a discussion of these concepts, please read through them. If you have, scan this page and the next.

On page 738, the text mentions data distribution as the last management concern of a DBA. The discussion is pretty theoretical, except for the caution that administrators must take more precautions to protect data that can be accessed outside their own networks. On the same page, the text begins a list of DBA duties that are more technical than administrative.

  • Evaluating, selecting, and installing the DBMS and related utilities - It would be unusual to have a free hand to choose anything at all. It is more likely that you would use an already entrenched product chosen for use in your organization. The material on pages 739 and 740 are useful material for planning a project in a new organization, in which choices can be made. The DBMS features described in this section make a good shopping list.
  • Designing and implementing databases and applications - This is the actual work of a DBA, designing tables and relationships in the data, making sure the functions needed by the requester are performed properly. Note that the list of objectives include being compliant with the security needs of the users and the organization.
  • Testing and evaluating databases and applications - Some developers put the least effort in this step, which is a mistake. There are sometimes errors that will occur only when the product encounters live, full tables of data and real users, well meaning or not.
  • Operating the DBMS, utilities, and applications - The system must run, and it must continue to run as needed. It should be checked for ongoing performance, particularly from the point of view of the users. Regular backups should be scheduled and tested. Audits are a fact of life, and it is necessary to learn to read an audit report, and learn to respond to it with updates to your system.
  • Training and supporting users - Often, training is left to the users themselves, but that can be facilitated by creating good training material that is made available on demand, such as procedure documents and video lessons placed on an organization's internal web server.
  • Maintaining the DBMS, utilities, and applications - Maintaining a system should include periodic evaluation of the need for changes to it.

Page 745 takes us to the discussion of security. We usually have this topic in several courses, so it should be no surprise to find it in this text. The author begins with the classic three elements of computer security: Confidentiality, Integrity, and Availability. The system should only open itself for authorized users, it should not be changed except by those allowed to make changes, and it should be available to authorized users when it is needed.

  • Security policies are organizational rules that relate to security concerns. Typically, a large organization will have its own security personnel, who will have guidelines that the DBAs must follow. Policies often are created in response to new threats and vulnerabilities.
  • Security vulnerabilities are aspects of a system that are easier to attack than other aspects. A vulnerability may come from a software or hardware flaw (technical), from lack of training (educational/managerial), from bad habits (cultural), from lack of security in operations (procedural), or through willingness to believe or help (social engineering).
  • A security threat is often defined as a potential form of loss or damage. It can be an event or an attack that could occur, and could cause the full or partial loss of an asset.
  • A security exploit is a method of attack, typically taking advantage of a vulnerability.
  • A security breach is an actual damage to or loss of the confidentiality, integrity, or availability of an asset, typically as a result of a successful attack. A successful attack may have theft, change, or destruction as its goal. In the case of theft, you may not know it has happened.

Table 16.4 on page 747 presents a list of system components, vulnerabilities that may apply to them, and security measures that should be taken to reduce vulnerability. You should review this chart to learn about vulnerabilities you don't already know about.

Security for Data, the Database, and the Organization

The text makes some recommendations about the security of databases. Several apply to networks, servers, and computer equipment in general.

  • Change default account names and passwords - this is a common vulnerability that exists on most devices. If you know what a piece of hardware is (brand and model), or what software it is running, you can probably get administrative access to it through the accounts the manufacturer or publisher created as defaults. Many breaches have taken place because accounts were not removed/renamed and passwords were never changed.
  • Change default installation folders
  • Apply patches
  • Secure installation folders (and other folders that need it) so only authenticated, authorized users can access them
  • Remove services you do not use from servers
  • Use audit logs
  • Use session logs and require encryption to hide the data in hijacked logs

Tools and Strategies

This section recommends obtaining and using several administration tools. It also recommends reading the information in the data dictionary, which is also called the information resource dictionary on page 751. The text recommends querying the SYSTABLES table, which is part of the data dictionary in DB2. In MySQL, you would examine several different, but similarly named tables. The online manual lists them on this page.

List of INNODB System Tables

You may recall that the InnoDB database engine is the current default engine for MySQL. Follow the link above for a description of what is stored in each of those tables.

The text mentions CASE (Computer Aided Systems Engineering) tools, and recommends them for building large systems. They often provide documentation tools, help with complying to the flow of SDLC phases, and a known interface once you learn it. It goes off on a tangent to discuss creating an ERD in Visio, which is a fine tool for that purpose, but it has nothing to do with creating a database system.

The chapter moves on to discuss creating a strategy for database management on page 755. In addition to to database concerns, it lists issues that pertain to any IT project, so making them standard concerns in all of your projects is a good idea.

  • management commitment - projects should be approved from the top of the organization and all the way down to succeed
  • analysis of the current situation - know where the organization is before you try to change it
  • end-user involvement in the project - get agreement on the system from typical users at each phase of the project, and get their ideas as you go along
  • defined standards - use common, standard techniques so your developers will understand each other's work
  • training - this includes training for your technical staff as well as for managers and users
  • implement starting with a pilot project - changing the entire organization to the new system at once can be tempting, because it looks like it is less costly, but there is nothing more costly than changing to a new system whose fatal flaw has not yet been found and fixed

On page 756, the text begins a section on the DBA's role in a cloud-based system. The DBA still has to manage the database, just not so much of the physical server. The server is a virtual server on someone else's computer, so some of the headaches are simplified. As the text points out, the connection to the remote server becomes more critical in this situation. No connectivity to the Internet, and specifically to your vendor's cloud means no value from your database while that is so. The text makes an argument against going with this option. The vendor on the cloud is benefited by the system running slower and using more processing power, which will cost you more in the long run than you might be saving by not owning the hardware. This should be monitored to determine what your best option is.

The chapter ends with a section on using Oracle and the management tools that are part of it.

Okay, that is enough theory from chapter 16. Let's take a look at some problems using the bookstore database. First off, let's assume you want to want to list the title of each book written by a particular author. For this problem, we will assume it is author 20. What tables do you need? Take a look at this unfinished ERD for that database.

The titles only occur in the book table, so we need that. There are no author numbers in the book table, so we need to relate to another table. How about the author table? It has author numbers, but it does not relate to books. The book table also has book codes for each book, and those codes are linked to author numbers in the wrote table.

So, let's assume we need the from clause to say:

FROM wrote, book

The requirement for the problem says we only need the book title, but that doesn't lead to good troubleshooting. How about outputting the title, the book code, and the author number? Why would I want to do that? To make sure I am getting the right selections. So the select clause might say this:

SELECT wrote.book_code, title, wrote.author_num

Why did I pull two fields from the wrote table? It is a bit easier to do that since we are sure we can get both in that table, and that they will be matching pairs.

That takes us to a where clause, which is more complex for multiple table queries. How can we select the right rows from the wrote table? That's pretty easy, since we already know which author number we want. The where clause can start like this:

WHERE wrote.author_num = '20'

That's not enough. We also need to pull the right book titles. The phrase above tells the database to read the rows for the right author. Now, we need to match the book numbers in those particular rows against titles in the book table.

and wrote.book_code = book.book_code

To make it nicer, let's sort the output by title.

select wrote.book_code, title, wrote.author_num
from wrote, book
where wrote.author_num = '20' and wrote.book_code = book.book_code
order by title;

Here is a screen capture of doing it in the MySQL Workbench:

We can walk through more in class if we can spark some interest in doing that.