Hour 21. Working with the System Catalog
What You’ll Learn in This Hour:
• What the system catalog is
• How the system catalog is created
• What data is contained in the system catalog
• Examples of system catalog tables
• Querying the system catalog
• Updating the system catalog
In this hour, you learn about the system catalog, commonly referred to as the data dictionary in some relational database implementations. By the end of this hour, you will understand the purpose and contents of the system catalog and will be able to query it to find information about the database based on commands that you have learned in previous hours. Each major implementation has some form of a system catalog that stores information about the database. This hour shows examples of the elements contained in a few of the different system catalogs for the implementations discussed in this book.
What Is the System Catalog?
The system catalog is a collection of tables and views that contain important information about a database. A system catalog is available for each database. Information in the system catalog defines the structure of the database and information on the data contained therein. For example, the Data Definition Language (DDL) for all tables in the database is stored in the system catalog. See Figure 21.1 for an example of the system catalog within the database.
Figure 21.1. The system catalog.
As you can see in Figure 21.1, the system catalog for a database is actually part of the database. Within the database are objects, such as tables, indexes, and views. The system catalog is basically a group of objects that contain information that defines other objects in the database, the structure of the database, and various other significant information.
The system catalog for your implementation might be divided into logical groups of objects to provide tables that are accessible by the Database Administrator (DBA) and any other database user. For example, a user might need to view the particular database privileges that she has been granted but doesn’t care how this is internally structured in the database. A user typically queries the system catalog to acquire information on the user’s own objects and privileges, whereas the DBA needs to be able to inquire about any structure or event within the database. In some implementations, system catalog objects are accessible only to the DBA.
The system catalog is crucial to the DBA or any other database user who needs to know about the database’s structure and nature. It is especially important in those instances in which the database user is not presented with a Graphical User Interface (GUI). The system catalog allows orders to be kept, not only by the DBA and users, but by the database server.
Did You Know?: Database System Catalogs Vary
Each implementation has its own naming conventions for the system catalog’s tables and views. The naming is not important; however, learning what the system catalog does is important, as is what it contains and how and where to retrieve the information.
How Is the System Catalog Created?
The system catalog is created either automatically with the creation of the database, or by the DBA immediately following the creation of the database. For example, a set of predefined, vendor-provided SQL scripts in Oracle is executed, which builds all the database tables and views in the system catalog that are accessible to a database user.
The system catalog tables and views are system-owned and not specific to any one schema. In Oracle, for example, the system catalog owner is a user account called SYS, which has full authority in the database. In Microsoft SQL Server, the system catalog for the SQL server is located in the master database. In MySQL the database is contained in the mysql system database. Check with your specific vendor documentation to find where the system catalogs are stored.
What Is Contained in the System Catalog?
The system catalog contains a variety of information accessible to many users and is sometimes used for different specific purposes by each of those users.
The system catalog contains information such as the following:
• User accounts and default settings
• Privileges and other security information
• Performance statistics
• Object sizing
• Object growth
• Table structure and storage
• Index structure and storage
• Information on other database objects, such as views, synonyms, triggers, and stored procedures
• Table constraints and referential integrity information
• User sessions
• Auditing information
• Locations of database files
The database server maintains the system catalog. For example, when a table is created, the database server inserts the data into the appropriate system catalog table or view. When a table’s structure is modified, appropriate objects in the data dictionary are updated. The following sections describe, by category, the types of data that are contained in the system catalog.
User Data
All information about individual users is stored in the system catalog: the system and object privileges a user has been granted, the objects a user owns, and the objects not owned by the user to which the user has access. The user tables or views are accessible to the individual to query for information. See your implementation documentation on the system catalog objects.
Security Information
The system catalog also stores security information, such as user identifications, encrypted passwords, and various privileges and groups of privileges that database users utilize to access the data. Audit tables exist in some implementations for tracking actions that occur within the database, as well as by whom, when, and so on. Database user sessions can be closely monitored through the use of the system catalog in many implementations.
Database Design Information
The system catalog contains information regarding the actual database. That information includes the database’s creation date, name, object sizing, size and location of data files, referential integrity information, indexes that exist in the database, and specific column information and column attributes for each table in the database.
Performance Statistics
Performance statistics are typically maintained in the system catalog as well. Performance statistics include information concerning the performance of SQL statements, both elapsed time and the execution method of an SQL statement taken by the optimizer. Other information for performance concerns memory allocation and usage, free space in the database, and information that allows table and index fragmentation to be controlled within the database. You can use this performance information to properly tune the database, rearrange SQL queries, and redesign methods of access to data to achieve better overall performance and SQL query response time.
System Catalog Tables by Implementation
Each implementation has several tables and views that compose the system catalog, some of which are categorized by user level, system level, and DBA level. For your particular implementation, you should query these tables and read your implementation’s documentation for more information on system catalog tables. Table 21.1 has examples of six major implementations.
Table 21.1. Major Implementation System Catalog Objects
These are just a few of the system catalog objects from the main relational database implementations that we cover in the book. Many of the system catalog objects that are similar between implementations are shown here, but this hour strives to provide some variety. Overall, each implementation is specific to the organization of the system catalog’s contents.
Querying the System Catalog
The system catalog tables or views are queried as any other table or view in the database using SQL. A user can usually query the user-related tables but might be denied access to various system tables accessible only by privileged database user accounts, such as the DBA.
You create an SQL query to retrieve data from the system catalog just as you create a query to access any other table in the database. For example, the following query returns all rows of data from the Microsoft SQL Server table SYS.TABLES:
SELECT * FROM SYS.TABLES;
GO
The following query lists all user accounts in the database and is run from the MySQL system database:
SELECT USER
FROM ALL_USER;
USER
----------------
ROOT
SYSTEM
RYAN
SCOTT
DEMO
RON
USER1
USER2
8 rows selected.
By the Way: A Word About the Following Examples
The following examples use MySQL’s system catalog. MySQL is chosen for no particular reason other than to give you some examples from one of the database implementations talked about in the book.
The following query lists all tables within our learnsql schema and is run from the Information_schema:
SELECT TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA='learnsql';
TABLE_NAME
----------------
CUSTOMER_TBL
EMPLOYEE_PAY_TBL
EMPLOYEE_TBL
PRODUCTS_TBL
ORDERS_TBL
5 rows selected.
Watch Out!: Manipulating System Catalog Tables Can Be Dangerous
Never directly manipulate tables in the system catalog in any way (only the DBA has access to manipulate system catalog tables). Doing so might compromise the database’s integrity. Remember that information concerning the structure of the database, as well as all objects in the database, is maintained in the system catalog. The system catalog is typically isolated from all other data in the database. Some implementations, such as Microsoft SQL Server, do not allow the user to manipulate the system catalog directly in order to maintain the integrity of the system.
The next query returns all the system privileges that have been granted to the database user BRANDON:
SELECT GRANTEE, PRIVILEGE_TYPE
FROM USER_PRIVILEGES
WHERE GRANTEE = 'BRANDON';
GRANTEE PRIVILEGE
---------------------- --------------------
BRANDON SELECT
BRANDON INSERT
BRANDON UPDATE
BRANDON CREATE
4 rows selected.
By the Way: These Are Just a Few of the System Catalog Tables Available
The examples shown in this section are a drop in the bucket compared to the information that you can retrieve from any system catalog. You might find it extremely helpful to dump data dictionary information using queries to a file that can be printed and used as a reference. Refer to your implementation documentation for specific system catalog tables and columns within those available tables.
Updating System Catalog Objects
The system catalog is used only for query operations—even when the DBA is using it. The database server makes updates to the system catalog automatically. For example, a table is created in the database when a database user issues a CREATE TABLE statement. The database server then places the DDL that created the table in the system catalog under the appropriate system catalog table.
There is never a need to manually update a table in the system catalog even though you might have the power to do so. The database server for each implementation performs these updates according to actions that occur within the database, as shown in Figure 21.2.
Figure 21.2. Updates to the system catalog.
Summary
You have learned about the system catalog for a relational database. The system catalog is, in a sense, a database within a database. The system catalog is essentially a database that contains all information about the database in which it resides. It is a way of maintaining the database’s overall structure, tracking events and changes that occur within the database, and providing the vast pool of information necessary for overall database management. The system catalog is only used for query operations. Database users should not make changes directly to system tables. However, changes are implicitly made each time a change is made to the database structure itself, such as the creation of a table. The database server makes these entries in the system catalog automatically.
Q&A
Q. As a database user, I realize I can find information about my objects. How can I find information about other users’ objects?
A. Users can employ sets of tables and views to query in most system catalogs. One set of these tables and views includes information on what objects you have access to. To find out about other users’ access, you need to check the system catalogs containing that information. For example, in Oracle you could check the DBA_TABLES and DBA_USERS system catalogs.
Q. If a user forgets his password, is there a table that the DBA can query to get the password?
A. Yes and no. The password is maintained in a system table, but it is typically encrypted so that even the DBA cannot read the password. The password has to be reset if the user forgets it, which the DBA can easily accomplish.
Q. How can I tell which columns are in a system catalog table?
A. You can query the system catalog tables as you query any other table. Simply query the table holding that particular information.
Workshop
The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
1. In some implementations, the system catalog is also known as what?
2. Can a regular user update the system catalog?
3. Which Microsoft SQL Server system table retrieves information about views that exist in the database?
4. Who owns the system catalog?
5. What is the difference between the Oracle system objects ALL_TABLES and DBA_TABLES?
6. Who makes modifications to the system tables?
Exercises
1. In Hour 19, “Managing Database Security,” you looked at the tables in your learnsql database. Now find some of the system tables that we discussed earlier in this chapter. Review them.
2. At the prompt, type in queries to bring up each of the following:
• Information on all the tables
• Information on all the views
• All the usernames in the database
3. Write a query using multiple system tables to retrieve all the users and their associated privileges in your learnsql database.