Advanced Oracle Tricks in Supporting Systems Administration

Jon Finke

Abstract

Many organizations (including the one for which I work) use Oracle or other relational database management systems to help manage their user account space, as well as other aspects of their operation. Over the years, we have developed a number of techniques using advanced features of Oracle to assist in this process. Since many of the people who are implementing these systems are systems administrators rather than database application developers, I have written this paper to explain how to increase the level of automation, provide better access control, and simply explore some of the superb features and power of Oracle. All the techniques described in this paper, which picks up where my earlier Oracle Tools[Fin92b] paper leaves off, can substantially improve systems security.

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).

Starting Points and Views

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.

Name

Type

Size

Description

Owner_Id

Number

9

The People.Id of the person who owns the userid

Username

Varchar2

8

The Unix username

UnixUid

Number

16

The Unix UID for the account

UnixGid

Number

16

The Unix group ID, if not the default

Gecos

Varchar2

64

The Gecos of finger information field for the password fileóinitially, the userís "real name"

Source

Varchar2

16

The type of account and its current status, such as "PRIMARY_STUDENT" or "EXPIRED-EMP"

Budget

Varchar2

32

The budget number, either a 9-digit student number or a 4-part finance ID number

Expire_Date

Date

 

When this account expires (or makes the next transition)

Mail_Delivery

Varchar2

128

Optional email forward information for username@rpi.edu

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.

Create View My`Logins

as Select Username,Gecos,Mail`Delivery,Expire`Date

from Logins

where Username=Substr(USER,5)

and Substr(USER,1,4) = 'OPS"$'

with check option;

Grant Select,update(Gecos,Mail`Delivery)

on My`Logins to PUBLIC;

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).

Simple Relations

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).

Name

Type

Size

Description

Source

Varchar2

16

The type of account and its current status, such as "PRIMARY_STUDENT" or "EXPIRED-EMP"

Shell

Varchar2

128

The shell to be used for this type of account

Unixgid

Number

12

The default Unix group id for this type of account

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)!

Select Username,Unixuid,Shell

from Logins, Source`Types

where Logins.Source = Source`Types.Source;

each entry in the LOGINS table that has a corresponding source entry in the SOURCE__TYPES

Figure 2

Join Example

Create View Etc`Passwd

as Select Username, Pwhash, Unixuid,

nvl(Logins.Unixgid,Source`Type.Unixgid),

Gecos, Shell, '/home/' __ Username

from Logins, Source`Types

where Logins.Source = Source`Types.Source;

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.

Complex Views

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).

Name

Type

Size

Description

Person_Id

Number

12

The Person.Id of this student

Department

Varchar2

4

The department code for this student

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)

Name

Type

Size

Description

Unixuid

Number

12

The Unixuid of the account being authorized to operate on students in this department

Username

Varchar2

4

The department code for department user can maintain

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.

Create View My`Admin`Depts

as Select Departments

from My`Logins, Dept`Admins

where My`Logins.Unixuid = Dept`Admins.Unixuid;

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.

Create View My`Admin`People

as Select Person`Id

from Students, My`Admin`Dept

where Students.Department = My`Admin`Dept.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).

Create View My`Admin`Logins

as Select Username, Mail`Delivery

from Logins

where Owner in (Select Person`Id

from My`Admin`People)

with check option;

Grant select,update(Mail`Delivery) on My`Admin`Logins to Public;

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).

Name

Type

Size

Description

Person_Id

Number

12

The People.Id of the person who wants a statement

Request_Data

Date

The time and date the statement was requested

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).

Create View My`Statement`Req`Ins as

Select Person`Id, Request`Date

from Statement`Request

where Person`Id in (Select Owner

From My`Logins)

and Request`Date = Sysdate

with Check Option;

Grant Select,Insert on My`Statement`Req`Ins to Public;

Figure 7

MY__Statement__Req__Ins View Definitions

Create View My`Admin`Req`Ins as

Select Person`Id, Request`Date

from Statement`Request

where Person`Id in (Select Owner

From My`Admin`People)

and Request`Date = Sysdate

with Check Option;

Grant Select,Insert on My`Admin`Req`Ins to public;

Figure 8

My__Admin__Req__Ins View Definitions

Stored Procedures

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.

Access Control

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.

Password Change Management

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).

Name

Type

Size

Description

Unixuid

Number

16

The Unix uid of the ID to be changed

New_Passwd

Varchar2

32

The new password encrypted with double ROT-13

Request_Date

Date

The time and date when the request was made

Processed_Date

Date

The time and date when this was processed

Processed_Result

Varchar2

8

A flag indicating what happened

Clerk_Uid

Number

16

The Unix uid of the person entering the request

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).

procedure queue`change( target`unixuid IN Passwd`Change`Queue.Unixuid%Type,

target`username IN Logins.Username%Type,

new`passwd IN Passwd`Change`Queue.New`Passwd%Type,

target`disable IN Logins.Disabled%Type,

RetVal OUT Passwd`Change`Queue.Process`Result%Type)

is

rows Number;

BEGIN

--

-- check to see if the username/uid is on the reject list.

Select count(*),max(nvl(Reason,'ExclList'))

into Rows,RetVal

from passwd`change`exceptions PCE

where ( PCE.Unixuid = Target`Unixuid

or PCE.Username = Target`Username )

and when`marked`for`delete is null

if ( Rows > 0 )

then

return;

end if;

--

-- We passed the test, insert the record

--

Insert into passwd`change`queue

(unixuid, new`passwd, request`date, clerk`id)

values (target`unixuid, new`passwd, sysdate, user);

Retval := 'InsertOk';

end Queue`Passwd`Change;

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).

Procedure queue`alumni`passwd`change

( target`unixuid IN Passwd`Change`Queue.Unixuid"%Type,

new`passwd IN Passwd`Change`Queue.New`Passwd"%Type,

RetVal OUT Passwd`Change`Queue.Process`Result"%Type)

is

target`source Logins.Source"%Type;

target`username Logins.Username"%Type;

BEGIN

Select Username,Source

into Target`Username,Target`source

from Logins

where unixuid=target`unixuid;

if target`source != 'PRIMARY-ALUMNI'

then

RetVal := 'NonAlum';

return;

end if;

--

-- it appears to be an alumnus, pass this down.

queue`passwd`change(target`unixuid, target`username, new`passwd,

'', RetVal);

return;

end queue`alumni`passwd`change;

grant execute on queue`alumni`passwd`change

to Simon`Req`Alum`Pw`Change;

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 Signals and Pipes

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:

-- Signal any waiters

--

dbms`alert.signal('PASSWORD`CHANGE`PENDING',NULL);

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!

Package Access Control

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.

procedure pwchange`wait`signal( Message OUT varchar2,

Status OUT integer,

Timeout IN number)

IS

BEGIN

dbms`alert.waitone('PASSWORD`CHANGE`PENDING',Message, Status, Timeout);

end Wait`Signal;

Figure 11

PWChange__Wait__Signal Procedure Definitions

Database Triggers

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.

create or replace trigger logins`update

before update of gecos,source

on logins

for each row

declare

begin

--

-- Look for Gecos changes

if :new.GECOS != :old.GECOS

then

Login`triggers.Gecos`Change(:Old.Username, :new.Gecos);

end if; -- GECOS

-- Source changes

if :new.source != :old.source

then

Login`triggers.Source`Change(:Old.Unixuid, :Old.Source, :New.Source);

end if;

end;

Figure 12

Logins__Update Trigger Definitions

Conclusion

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.

References

[ABF+ 92] Eric Armstrong, Steve Bobrowski, John Frazzini, Brian Linden, and Maria Pratt. Oracle 7 Server Application Developer's Guide, chapter Appendix A, pages A15-A20. Oracle Corporation, Dec 1992.

[Arn98] Bob Arnold. Accountworks: User create account on sql, notes, nt and unix. In The Twelfth Systems Administration Conference (LISA 98) Proceedings, pages 49-61. Sybase Inc, USENIX, December 1998.

[DL93] Salvator DeSimone and Christine Lombardi. Sysctl: A distributed systems control package. In USENIX Systems Administration (LISA VII) Conference Proceedings, pages 131-144, USENIX, November 1993.

[dSdCF+ 98] Fabio Q. B. da Silva, Juliana Silva da Cunha, Danielle M. Franklin, Luciana S. Varejao, and Rosalie Belian. An nfs configuration management system and its underlying object-oriented model. In The Twelfth Systems Administration Conference (LISA 98) Proceedings, pages 121-130. USENIX, December 1998.

[Fin92a] Jon Finke. Automated userid management. In Proceedings of Community Workshop '92, Troy, NY, June 1992. Paper 3-5.

[Fin92b] Jon Finke. Oracle tools. In Proceedings of Community Workshop '92, Troy, NY, June 1992. Paper 3-1.

[Fin92c] Jon Finke. Simon system management: Hostmaster and beyond. In Proceedings of Community Workshop '92, Troy, NY, June 1992. . Paper 3-7.

[Fin93] Jon Finke. Relational database + automated sysadmin = simon. Invited Talk, July 1993. Sun Users Group - East Conference.

[Fin94] Jon Finke. Automating printing configuration. In USENIX Systems Administration (LISA VIII) Conference Proceedings, pages 175-184. Rensselaer Polytechnic Institute, USENIX, September 1994.

[GMR95] Xev Gittler, W. Phillip Moore, and J. Rambhasker. Morgan Stanley's aurora system: Designing a next generation global production Unix environment. In Ninth Systems Administration Conference (LISA '95), pages 47-58. USENIX, September 1995.

[RG95] Karl Ramm and Michael Grubb. Exu - a system for secure delegation of authority on an insecure network. In Ninth Systems Administration Conference (LISA '95), pages 89-93. USENIX, September 1995.

[TSO+ 96] Gregory S. Thomas, James O. Schroeder, Merrilee E. Orcutt, Desiree C. Johnson, Jeffrey T. Simmelink, and John P. Moore. Unix host administration in a heterogeneous distributed computing environment. In The Tenth Systems Administration Conference (LISA 96) Proceedings, pages 43-50, USENIX, October 1996.

Additional Information and Resources

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