Abstract

System administration and security are more intertwined than many people think. Those who attack systems and networks are generally the first to recognize that neglected or "orphan" systems make the easiest targets. What many people do not realize, however, is that the sheer effort required to manage many systems often leads to problems such as faulty account and group administration that can also create easy avenues of attack. Effective system administration is thus conducive to security.

I work at Rensselaer Polytechnic Institute (RPI), where we have been automating many aspects of our Unix systems administration using an Oracle-based package called "Simon." This effort has started with management of the actual Unix userids [Fin92a][Fin93], Unix groups, printer configuration [Fin94], postmaster (/etc/aliases), hostmaster[Fin92c] and many other kinds of things. This project, ongoing for over eight years, has provided many "lessons learned," both good and bad, about what to do and how to do it. We even have had opportunities to redo some things based on what we have learned.

We are not alone in these efforts. I have talked with system administrators at many other

sites who are working on similar projects, including the University of Alberta, Simon Frasier

University, SUNY Albany, and the University of Connecticut. Efforts such as the one described in this paper are not limited to educational sites, however. I have also spoken with people at Cisco Systems and Collective Technologies about similar projects. A quick glance at the last few LISA proceedings show a number of similar projects including Accountworks [Arn98], NFS Configuration Management [dSdCF+ 98], Unix Host Administration[TSO+ 96], Aurora[GMR95], Exu[RG95], and others.

I will give a very brief introduction to relational database use and simple views, then will cover complex views, stored procedures, database triggers and advanced packages, all cast in terms of systems administration. Although these examples are drawn directly from our system running under Oracle 8, many of the facilities are available in other databases, or at least similar features may be available. Note that definitions of field names (e.g., "Gecos") are contained in the tables throughout this paper. All of the table and PL/SQL definitions are available on the web (see the "References and Availability" section for details).

The very base of our system is the LOGINS table (see Table 1), which contains much of the information available in /etc/passwd as well as other information, such as who owns the account, budget numbers, email handling, etc. We use this table to generate our /etc/passwd files distributed via NIS (Network Information System) and other means. You may note some missing fields--we use Kerberos for authentication, so we do not store a Unix password entry here (although we used to). We also don't specify a shell--that is derived from the SOURCE field. The home directory is generated based on the username and unixuid, so we don't need to include it here. If needed, it would be trivial to add these fields.

Table 1

Logins Oracle Table Definition

When we set up an account, we also set up a matching Oracle account. This enables us to use many of the tricks and techniques described in this paper. Our users are largely unaware that they have an Oracle account; they never interact directly (via SQL) with it. Oracle offers a number of ways to handle authentication. Initially, we created a custom network protocol to talk to the server, but many alternatives (including Kerboros, RADIUS, SecurID, and others) are available. The Oracle account, along with the view My__Logins (see Figure 1), enables users to change their own Gecos field in the database (and it is then replicated to all of the systems). In the same way, users can also change their email forwarding.

Figure 1

MY__LOGINS View Definitions

The USER value in the My__Logins view is the current Oracle user. If my Username is finkej, my Oracle account name is OPS$finkej. This view is then granted to everyone, along with the right to update two of the fields. The really superb thing here is that I can only see MY information (and change those two particular fields).

The power of a relational database comes from its ability to handle relations between data

in tables. Let’s look at how we might handle the user's shell in the LOGINS table. We want the shell to be based on the type of account, which we can determine from the LOGINS.SOURCE column. Let’s create a table called SOURCE__TYPES (see Table 2).

Table 2

Source_Types Oracle Table Definition

We can connect the LOGINS table with the SOURCE_TYPES table (or, in database terms, "join" them) by equating the two columns in a select statement (refer to Figure 2). This will return a list of usernames, unixuids and shells for each entry in the LOGINS table that has a corresponding entry in the SOURCE_TYPES table. Beware, however--if there is no matching source value in the SOURCE__TYPES table for a given row in the LOGINS table, that row will not be returned. On the other hand, if there is more than one entry in the SOURCE__TYPES table that matches, each combination will be returned. Since you can create a view of just about anything you can select, you could create a view of the LOGINS table that returns the passwd file (see Figure 3)!

Figure 2

Join Example

Figure 3

ETC__PASSWD View Definitions

We normally determine the Unix GID of an account based on the type of account. Students are in one group and staff are in another. However, we want the ability to individually override any group, so we use the NVL function to return the LOGINS.UNIXGID value if it is not null. Otherwise, we return the value from the SOURCE__TYPES table. We also do some string concatenation to build up the home directory path from the username.

A more complicated problem is enabling specific individuals within each department to do certain administrative tasks for the students within their department. We need to designate more than one person for a department, allow someone to service more than one department, and have this happen as automatically as possible. To start, we have the STUDENTS table, which is maintained by other programs using information from the registrar (see Table 3).

Table 3

STUDENTS Oracle Table Definition (partial)

We need to create a table to associate departmental administrators with departments. This gives us the Dept__Admin table (see Table 4)

Table 4

Dept_Admin Oracle Table Definition

The first step is to create a view My__Admin__Depts (refer to Figure 4), which will be all of the departments for which a person can maintain data.

Figure 4

My Admin Depts view definitions

We then build on this to create the view My__Admin__People (see Figure 5), the list of all of the people who are in that department.

Figure 5

My__Admin__People View Definitions

We want the departmental administrators to be able to change email forwarding

(Mail_Delivery) for their students. We thus need one more view of the logins table called My__Admin__Logins (see Figure 6).

Figure 6:

My__Admin__Logins View Definitions

Finally, we have a view that allows departmental administrators to view and update mail forwarding for their students. All access control is enforced by the database,. We don't need to rely on the application for security. But what if we want to allow entries to be inserted (as opposed to record updates) on behalf of someone else? For the same example, let’s consider the case in which we need to request that a billing statement be mailed to the address of record. We start with the table Statement__Request (see Table 5).

Table_5

Statement_Request Oracle Table Definition

We can give insert access to the individual via the My__Statement__Req__Ins view (see Figure 7). Note that in order for this view to "select" anything, not only must the person_id of the request be the current person, but the time and date of the request must also be the current time and date. Unless you are very quick (to the resolution of the Oracle clock), you will never get any rows out of this view. You can insert a new row, however, since at that moment in time you will match the selection constraints and the insertion will be allowed. We can take this concept a bit further and enable department administrators to make requests with the My__Admin__Req__Ins view (refer to Figure 8).

Figure 7

MY__Statement__Req__Ins View Definitions

Figure 8

My__Admin__Req__Ins View Definitions

You may eventually need to go beyond the capabilities of views, applying more complex business rules (such as only allowing administrative password changes during business hours), enabling users to request more complex tasks, or providing immediate feedback. The key to these additional capabilities is stored procedures.

We currently use Kerberos for authentication to all of user accounts (student, faculty

and alumni). Users need to have their passwords reset from time to time, necessitating that our help desk staff can reset passwords. However, we did not want our student consultants to be able to change faculty passwords. To make life even more interesting, we wanted our Alumni Relations staff to be able to change the passwords of Alumni accounts, but not any of the others. We also wanted the ability to do this without giving each of the people a Kerberos administrative account.

Although we could change passwords with something such as SysCtl[DL93], we wanted some finer-grained control, based on administrative switches such as "student" or "alumnus." So we created an Oracle table to hold the requests (see Table 6).

Table 6

Passwd_Change_Queue Oracle Table Definition

Requests are put in this table. Another process running on a secure machine (with

proper credentials) periodically looks in this table for entries that have not been processed

(i.e., Date__Processed is null), makes a few sanity checks, changes the password, and marks it as done. (A later section will discuss how to do this in almost real time).

For our basic procedure, the only access check we want to make is to ensure that the target

username is not in a list of special users, generally system administrators and people who need special handling. To do this, we create a stored procedure, Queue__Passwd__Change (see Figure 9).

Figure 9

Queue__Passwd__Change Procedure Definition

Now that we have a way for the full time help desk staff to make requests, we need to allow the alumni relations staff to change passwords on alumni accounts. We thus create a second stored procedure, Queue__Alumni__Passwd__Change (see Figure 10).

Figure 10

Queue__Alumni__Passwd__Change Procedure Definition

This allows anyone who has been granted access to the role Simon__Req__Alum__Pw__Change the ability to request a new password for an alumni account. Requests to change some other type of account, however, are rejected. Similarly, we can set up a stored procedure for our student employees to change student passwords. When an account changes from student to alumnus, the set of people who can "administer" it changes automatically!

Oracle includes a number of packages that can be used in program development. One of these is called DBMS__ALERT[ABF+ 92], which allows Oracle applications to register interest in a particular signal, wait for them (with optional timeouts), and signal other waiting applications. Since all the signal processing is taking place on the database server, any Oracle application on any platform (that support Oracle of course) can wait for or signal processes on other machines.

Referring to the earlier example of the password change queue, suppose we make a change to the PW change daemon. Now after it starts up and processes any outstanding requests, instead of exiting, it registers for a signal and then starts waiting for it. When the wait terminates, (without an error status, of course), it checks for outstanding requests, processes them and goes back into the loop again. We also add the following lines to the Queue__Passwd__Change procedure:

If a password changing daemon is running, it will be signaled and the password will be

changed, generally in under a second. If there isn't a daemon running, the change request

is queued until a daemon is started; it will then be processed. What makes this even easier is that since the procedure is stored in the database, we were able to add this functionality to all of the password changing programs without modifying their source code or even re-compiling them. We simply updated the stored procedure and all the applications started using them!

Some of the packages supplied with Oracle, and possibly some custom written ones, may be so powerful than you may not want to release them to generic users. For example, we do not make the dbms_alert package generally available. Rather than granting access to everyone (or even specific developers), you can instead wrap the routines to provide a more restrictive operating environment, such as with the PWChange__Wait__Signal procedure (shown in Figure 11). We can in this way grant execute privileges to the PWChange__Wait__Signal routine, and users (or roles) can execute this routine only when a particular signal occurs.

Figure 11

PWChange__Wait__Signal Procedure Definitions

Database triggers are a very powerful tool. Stored procedures that are executed when anyone inserts, deletes or changes a row in a given table can be set up. Since these are part of the central database, there is no need to change applications to call these procedures. Additionally, the external applications cannot bypass these triggers.

The Gecos change was one of the earliest Simon applications. Part of our normal daily

procedures was to regenerate the password file and update it on disk and in the NIS maps.

Last year, we started providing /etc/passwd services to some of our machines via DCE.

Unlike the NIS passwd image, the DCE registry started with a snapshot of our password file, which then needed to be kept in sync with Simon. Although we were able to modify our account creation and expiration processing to queue requests to create and expire user accounts, this did not handle Gecos changes. We thus created the trigger Logins__Update (refer to Figure 12). This trigger was fired whenever the Gecos or Source field in the LOGINS table was changed in some way. If the Gecos field was changed, it would call a stored procedure that handles Gecos changes (and queues a request to update things in DCE). In the same way, it looked for changes in the source field (account type) and called another procedure to take appropriate action.

Figure 12

Logins__Update Trigger Definitions

An Oracle (or other relational) database can be a very powerful tool in administering systems. You can do many things with the basic tools (queries, simple joins, etc). However, if you start using some of the more advanced features, you can do some really amazing things with fine-grained access control, enforcement of business rules, change tracking. These facilities are well worth exploring, not just for the sake of efficiency, but also for the sake of security. As stated earlier, good, efficient system administration goes hand-in-hand with good security.

All source code for the Simon system is available on the web (or via AFS). See

http://www.rpi.edu/campus/rpi/simon/README.simon

for details. In addition, all of the Oracle table definitions as well as PL/SQL package source

are available at

http://www.rpi.edu/campus/rpi/simon/misc/Tables/simon.Index.html

A number of papers on the Simon system, as well as the slides to go with this paper are available at

http://www.rpi.edu/"finkej/Papers