IP Register database general outline

The IP address database is built on an Oracle database. We consider Oracle to have industrial strength insofar as the basic database engine is concerned. This project does not however rely on any of Oracle's applications ware. The basic interface to the database is therefore SQL, and there is a sharp divide between the (hopefully) secure and robust relational database and the not inconsiderable icing sugar around it. If you want to go in with raw SQL then feel free: the database is supposed to be adequately robust. Most COs will not want to do this, however, and there is a whole raft of code which will normally be used on top of this. Grow your own if you don't like ours. In particular, there is a Perl applications programming interface, built on top of the Perl DBI module, and there is a Web forms interface (including batch update) built on top of all that. We anticipate that almost everyone will use the Web forms exclusively.

Interim restriction: Programmable access, though designed for and intended, is not yet (Sept 2001) available, and we are as yet uncertain exactly what route we will eventually open up for direct SQL (or Perl) access. The only currently supported mode of access is the web forms, and moreover with a Real Person behind the browser rather than a robot.

The DNS is updated automatically and daily by reference to the database. Updating the database is sufficient to ensure that the DNS will be updated consequentially.

Underlying relational database structure (more detail)

Most of the planning effort went into the underlying relational database, not least because it will be difficult to change its structure once it is operational. It contains various integrity constraints, some of which are implemented via automatically maintained tables that you don't need to know any more about.

Database tables are global and statically defined. For example, there is just one table box holding all box definitions known on the CUDN. Privacy between institutions is implemented by only allowing access to restricted "views" of these tables. The ordinary CO will not have direct access to the box table, but only to the my_box view, which dynamically "select"s those boxes within hir sphere of interest.

Spheres of interest are modelled as management zones, known as mzones. These typically coincide with dns domains, but not necessarily so. An mzone may well span more than one dns domain. An mzone will usually have one or more associated Computer Officers, identified by crsid, which is also of course the database user id, and the restricted views simply select those rows which are associated with an mzone for which the user is a CO.

An mzone is not to be confused with a lan, which also has a database representation. The purpose of mzones is to define extents of authority, whereas the purpose of lans is to help choose correct new addresses. Addresses, whether attached to machines or not, are tagged with the lan to which they pertain, so that an appropriate address can normally be picked merely by SELECTing from the appropriate lan. The management of lans is devolved along with everything else that is devolved.

The most commonly referenced tables include box and v4_address. A row in box represents pretty much what you might expect, a physical object that has a location, an owner, an end user, and a system administrator. It also has a name (fqdn). These rows (objects if you prefer that way of thinking) are created and destroyed as required. v4_address rows on the other hand are created once and for all by the CS and are flagged as being in use or not. They are also tagged with the mzone to which they pertain, so that a CO is limited in specifiable addresses. Addresses are associated with boxes by the name being a key in common. Further detail is beyond this scope of this introduction, but the important point is that the apparently simple case of a workstation with a single network interface requires specific entries in two tables, a box entry and an address entry. This caters for the much more complex arrangements that occasionally occur.

For every main table T there is also a table T_history with identical column specifications, and of course a view my_T_history. Whenever a row of T is updated a copy of the row as it was before is automatically dumped (INSERTed) into T_history. An automatic unique row identifier field called thread is provided to help pick out the history of any particular row, which may otherwise be hard to track when the primary key might get changed. Furthermore, all main tables have fields update_date, updated_by, review_date and remarks. The first two are automatically maintained; the last two are discretionary. It is hoped that review_date may prove especially useful in respect of student machines which have a strictly limited tenure.

IPv6 apparatus is simply not there at present. You might think that a v6 address is just a big v4 address, but it ain't like that. For a start, it has structure, comprising a part that is local to the CUDN (say), and a part that specifies the routing to the CUDN. It is probably not appropriate to represent the global routing part here, and certainly wrong to burn it into every address entry. Furthermore, the local part is typically partly based on MAC address, which is entirely outside our control, so the idea of pre-allocating is a non-starter. v6 address entries will have to be created and destroyed as required, but being two a penny shold not need the supply restriction we enforce for v4 addresses. It is quite clear that separate treatment will be needed, but the urgent need was to cope with the v4 addresses. Inasmuch as separate tabular apparatus will be needed, the later addition of it all should not be unduly problematic.

There is a library of utility routines that is accessible in SQL statements. Perhaps the only really useful one for ordinary use is ip.dquad_to_hex8, useful in particular under the table_ops web interface, where you could specify a predicate like address like ip.dquad_to_hex8('131.111.254.%').

The IPDB perl interface module

Although the basic underlying database is accessible, raw SQL is not the ideal interface for most COs. Nor are Oracle's application programming languages particularly convenient. They are also hideously expensive. Perl on the other hand is a widely accepted programming language that is particularly well suited to the needs and already has a number of relevant add-on modules, notably DBI and CGI. It seemed clear that a module to ease access from Perl to the IP database would be worthwhile, and some effort has been spent on that. It should be emphasised, however that this interface is only sugar. I hope it helps make life easy, but it doesn't do anything that the ordinary programmer couldn't do anyway and it does not implement security policy. Conversely, it does allow you, should you wish, to mix in your own SQL along with the SQL it generates behind the scenes.

IPDB.pm is fully documented as pod in the usual manner, and you should look there for detail. (Go something like perldoc IPDB.pm. Oh all right, here is an HTML rendering.) Only a very brief summary follows.

IPDB is built on top of the standard DBI module, and you need to start by looking at that because all of the DBI facilities remain available. In particular explicit SQL is available, and you may well wish to use it, particularly for specially crafted SELECTs. That said, however, the intent is to insulate as far as possible those who do not want to tangle with SQL, and to present a Perl-ish interface to the database. There are two main groups of (method) procedures, on one hand those which simply provide canonical mapping between Perl and SQL, and on the other hand those which parcel up some of the more commonly required operations involving multiple tables, such as the introduction of a new workstation. (See above for why this is slightly complex.)

You can use this module on CUS, where DBI can interface directly to the database. You can also use it on any remote machine, connecting to the database via the DBI Proxy mechanism. Either way, however, you should consult with the CS before investing effort in these somewhat experimental services.

Web interfaces

For those (probably the vast majority) who do not want to roll their own applications code but who just want a ready made operational interface, we have provided a number of web forms served from the database engine itself. To deflect a likely hail of rotten tomatoes it is worth a moment or two to set out the rationale and ethos of what is provided.

Firstly, it seems a possibly regretable fact of life that the web browser is far and away the most widely available user interface. It is there and ready on almost every workstation, and users are entirely familiar with it. The only other remotely possible user interface is telnet, which like it or not seems to be in terminal(!) decline. Writing and deploying our own client code for a sufficiently wide selection of client workstations is inconceivable. The web browser thus appears to be the only medium worth considering.

That said, we have to keep our feet on the ground to an extent regrettably absent in the popular e-world. We are not interested in overly fancy presentation and have in any case no resources to waste on that. We are however concerned to provide a reliable service without being unreasonably fussy as to the calibre or configuration of the browser.

Various pragmatisms follow.

  • Java may just possibly be the way of the future, but is certainly not the way of today. We avoid it.

  • Javascript is a fruitful source of security exposures, and I happen not to be fluent. We avoid it in the first instance.

  • The database engine contains much personal data, quite apart from the IP address data, and must be kept secure. The general interface used therefore insists that all access is encrypted under ssl.

  • To avoid the tedium of re-authentication and the cost of re-initialisation on every interaction we have to build some concept of an ongoing session. To this end we require cookies to be accepted (and returned) by the browser, though only "from the same server as the host being viewed".

  • A reality of life is that Netscape is very widely available, and is the commonest browser found in the constituency. Whilst trying hard to avoid being specific to the browser, we nevertheless have only tested against Netscape and don't care very much about infelicities seen on other browsers.

  • Inasmuch as this interface is peripheral sugar, albeit rather essential sugar, and given the need to get something going quickly, we have not worried overmuch about smoothness.

The result may therefore seem clunky, but I hope it is usable. It is by no means cast in stone.

Finally, it should be noted that the forms relating to the IP address database, or rather the programs that generate them, are built on (a) the IPDB module discussed above, and (b) a general mechanism for web forms access to this particular database engine. When reading the code, all you really need to know to get started is that the routine "handler" is called every time the page is accessed or the form submitted.

I generally disfavour screenshots, as being a sure sign of the triumph of artistry over usefulness, but a few may help clarify the narrative and have been included. They are actually examples of forms as actually delivered. Don't try to submit them. (Naughty! I said don't!)

So now to the forms themselves.

Common features of the forms

All the forms have a "prefix" field, normally initialised to MY_ This value feeds right down to the generated SQL as a prefix to table names, and this default value thus selects your personal view of the data. If you have sufficient privileges other views can be selected, or even the underlying tables if the field is emptied. The default will be right for most people most of the time. CS registrars automatically have the privileges of all institutional COs, but will need the ALL_ views for higher privilege.

Most of the forms have a debug button, which you can play with but which is really intended to help me rather than you.

The single_ops form (more detail)

This form provides the common operations for single simple IP hosts, hosts that is that have exactly one address and no aliases of any sort. You can create a new registration, for example, either specifying the address or leaving the system to choose one from those available to you, depending on your mood and on whether it actually matters. You can constrain the automatic choice in various ways. You do have to supply a name and the usual mandatory other data in the appropriate and obvious fields in the form.

You can rescind a registration or modify the detail on one.

You can change the name of a simple host, but though you might think that's the same as modifying detail in fact it is conceptually quite different and involves different underlying operations. The reason is that two tables are involved, as discussed above, and must be updated together without violating integrity constraints in any halfway stage. For these reasons rename is deliberately presented as a separate operation.

You can retrieve the detail of a current registration by fuzzy match on any field content. Just type sufficient detail into enough fields to pin it down and press the button. The fields are matched using the standard SQL LIKE function, so % and _ act as string and single character wildcards. Empty form fields don't count.

The fact that two tables, box and v4_address, each with its own remarks field, last_udpated field etc, are involved in a simple system is to some extent at odds with any simple presentation. In fact it is the fields from the box table that are presented here. The v4_address fields, if of interest, must be handled separately, perhaps with the table_ops form.

The list_ops form (more detail)

Many institutions currently have their own parallel databases of their particular IP address registrations, usually in the form of a spreadsheet, and at times want either to bring their databases into line with the official register or to make batch changes to the official register to bring it into line with their own. This sort of thing is particularly common among Colleges, and bulk transfers of data are essential at times such as start of term. Manual re-keying into the single_ops form would be quite hopeless for this sort of scenario, and the list_ops form is provided to enable bulk upload/download in a form compatible with many spreadsheets.

The basic data format for these bulk requests is one line of tab separated fields per request, the first line of all labelling the fields. Any one batch is all for the same type of operation, either register, rescind, modify, rename or get info, as selected by button once the (local) source file has been specified. The upload works reasonably cleanly, but the download (get info) is currently a bit messy, the results appearing in the display window rather than being delivered as a download file. For the time being you will have to save and edit somehow, but at least the info is there.

list_ops only caters for simple cases, as with single_ops.

The table_ops form (more detail)

This form is simply a means of doing explicit manual update of any of the underlying views or forms. Row creation, modification, deletion and fuzzy search (as in singleops) are offered. You could use it instead of singleops if you know what you are doing, and for the time being it is the only (forms) way of dealing with the cases that are not simple, for example where a box has multiple addresses. Note however that each interaction is a single transaction, so the form cannot be used to build up complex arrangements atomically or to defer deferable constraints.

Other forms

There are several other forms for handling less common affairs.

range_ops allows bulk assignment of network structure attributes to ranges of addresses. Remember that IPv4 addresses are never destroyed, merely flagged as being in or out of use. The main use to Institutional COs is to set LAN attachments. CS registrars can use it to set mzone attachments etc.

multihome_ops allows management of hosts that have multiple network interfaces, such as the otherwise friendlier single_ops cannot handle.

The remaining forms can be used by Institutional COs to display things, but not to create or adjust them, those activities being reserved to CS registrars.

The interim form aname_ops allows management of ANAMES, which result in A-type DNS aliases. (Note hoewver that CNAME type aliases are more commonly what is required.)

The interim form vbox_ops allows management of VBOXes, virtual boxes, that is, ones that appear to the network to be real and distinct but are in fact mimiced by some real box that has its own established existence.

The interim form maildom_ops allows manipulation of mail domains and their MTAs, which in Cambridge are subject to regulation.


Migration to this new world has been gradual, quite deliberately, but is now complete, and institutional network managers can manage their own addresses for ordinary affairs using the web forms. Some less common operations such as the creation of alias names are reserved to Computing Service registrars as a matter of policy, and need to be negotiated as before by email. The CS is still prepared to turn the handle for those departments that have weak IT support, even for day-to-day affairs, though increasingly strong encouragement to DIY is being exerted.