itadmin — Manage your IT using a MySQL/MariaDB database

Synopsis

itadmin

Description

Using itadmin you can keep your IT information in a database. The following files can be generated from the database contents:

• Configuration files /etc/hosts, /etc/ethers (in two variants: IP address first or MAC address first).
• LDIF files hosts.ldif, ethers.ldif and netgroup.ldif to import information into an LDAP server.
• A dhcpd.conf file for use with the ISC DHCP server.
• A network documentation file netdocu.tex.
• A license report file licenses.tex.

Building all these files from the same database ensures consistency.

You can run

pdflatex netdocu && pdflatex netdocu && pdflatex netdocu
pdflatex licenses && pdflatex licenses && pdflatex licenses

to produce netdocu.pdf and licenses.pdf
Use "pdflatex -interaction=batchmode" instead of "pdflatex" to run the PDF creation less noisy.

Options

The program produces configuration files and license report if neither -c nor -l is specified.

Exit status

0 on success, all other status codes indicate an error.

Required / Recommended other software

Database (required)
A database server is required to store the IT database.
At the momemt only MariaDB or MySQL databases can be used.

GUI program for database access (recommended)
Typically you don't like to edit the database using a command line tool. So you should install the MariaDB/MySQL ODBC connector and use programs from an office suite to access the database via GUI from your desktop.

LDAP server (optional)
The itadmin program produces both configuration files (hosts/ethers) and LDIF files for data import on an LDAP server. If you plan to use netgroups, you need an LDAP server.

DHCP server (optional)
If you want to provide dynamic IP address configuration, you need a DHCP server to do so. Itadmin writes a dhcpd.conf file for use with the ISC DHCP server.

LaTeX distribution (optional)
Don't be afraid, you don't need to learn LaTeX syntax to use itadmin.
Itadmin creates the files netdocu.tex and licenses.tex for you.
You just run

pdflatex netdocu
pdflatex licenses

three times to produce the network documentation netdocu.pdf and the license report licenses.pdf from them.

Configuration

The program uses configuration files named "itadmin.conf". Run

itadmin --/log/stderr/level=debug

to see which files are attempted and processed in which order.

Each line in the configuration file is a key=value entry. The following keys can be used:

Database

Installing the initial database

SQL files to install the initial database are copied to /usr/share/dktools/itadmin or /usr/local/share/dktools/itadmin during installation.

Use the mysql command to install it:

mysql -u root -p < /.../dktools/itadmin/it-latin1.sql

You can use the it-latin1.sql file to create the initial database structure. German users can use it-de.sql instead.
The it-utf8.sql file is experimental and untested. Someone from regions where UTF-8 encoding is needed because the latin1 character set ist not sufficient should make tests with it and give me feedback.

Database contents

General information

Table "users"

The users table contains information about persons. There is no need to list all your organization's people, just list those in charge for computers and licensing.
Each record contains the following fields:

Table "buildings"

The buildings table lists buildings where computers can be placed.

Physical network

Table "ddspeed"

The ddspeed table lists possible speed settings for ethernet connections.

Table "vlans"

The vlans table lists the virtual LANS (VLANS) built on top of the physical network.

Table "patches"

The patches table lists the ethernet sockets in the rooms.

IP networking

Table "networks"

The networks table lists IP networks.

Netgroups

Netgroups overview

Netgroups are a mechanism to assign permissions to computers. The same permissions can be applied to a group of computers.
If you specify computer permissions for — i.e. Samba, NFS and tcpd — directly in the configuration files you have to restart the services after making changes.
Using netgroups avoids these restarts, simply assign permissions to the netgroups in the configuration files. You can add/remove hosts to/from netgroups without having to restart daemons.
In NIS netgroups can be nested. If netgroup A is a member of netgroup B all member hosts of A are automatically members of netgroup B.
With itadmin you can use nested netgroups with LDAP too, the itadmin program resolves nested netgroups to plain host lists before writing the netgroups.ldap file.

Netgroup flattening in itadmin

In the IT database you can use a nested netgroup structure.
The itadmin program resolves dependencies of nested netgroups before creating the netgroup.ldif file. So in netgroup.ldif each netgroup only contains hosts.
This flattening is done for two reasons:

Performance
If a NIS/LDAP client needs to know whether or not a host is in a netgroup it asks the NIS/LDAP server whether or not the client is a direct member of the netgroup. If it isn't, the client asks for a list of all member subgroups of the netgroup. For all these subgroups the client needs to check again whether the host is in the subgroup … recursively. This might result in a larger number of requests and responses.
If the server has the flattened information the client can stop after requesting the the subgroups of the initial netgroup as this request returns an empty list.

Problems with some nss implemtations in the past
Previously I had problems with clients not doing the recursive check correctly.

Table "netgroups"

The netgroups table lists the netgroups.

Table "ngdeps"

The ngdeps table configures the netgroup dependencies.

All member hosts of the nd_c netgroup are automatically members of the nd_p netgroup.

Computers

Table "computers"

The computers table lists the computers.

The co_gu field is used for licensing responsibility.

DHCP

DHCP overview

A DHCP service allows client computers to obtain an IP address. When a DHCP client is booted it sends a broadcast request to obtain IP configuration data.
The DHCP server processes the request. Depending on the data sent by the client (i.e. the clients ethernet address, host name…) it chooses an appropriate IP address and sends a response containing IP address, net mask, gateway and optionally other information back to the client.

The IP address can be either an address from an address pool or a fixed address statically assigned to the client.
For addresses from a pool the client is allowed to use the address for a limited time (lease time). If the client doesn't renew the lease (request the IP address for yet another period) the server assumes the IP address is no longer used by the client.

In a network there might be different types of computers (i.e. in a university faculty there are staff members and students). You might decide to use different address pools for different types of computers, so you have to assign a DHCP class to computers. Use different DHCP class names for different types of computers obtaining IP addresses from different address pools.

DHCP options (additional information sent in the response, i.e. boot file and boot server) can be assigned to the server, a VLAN, an IP network, a DHCP address pool, a DHCP group or a host.

Table "dhcpclasses"

The dhcpclasses table lists the available DHCP classes:

Table "dhcpgroups"

The dhcpgroups table lists DHCP groups (groups of hosts sharing the same DHCP attributes).

Table "dhcppools"

The dhcppools table lists the available DHCP address pools.

To grant general access to a pool, even for unknown clients:
dp_al=1, dp_dn=0, dc_s=undefined.

To restrict access to a pool, allow only clients from a specified DHCP class:
dp_al=0, dp_dn=1, dc_s=class.

Table "dhcpoptions"

The dhcpoptions table can be used to assign DHCP options to the entire server, a VLAN an IP network, a DHCP address pool, a DHCP group, or a single host.

Licensing

Several tables are involved in license management:

• The "swmanufacturers" table lists software manufacturers.
   
• The "swproducts" table lists software products, each entry contains a reference to a software manufacturer.
   
• The "swresellers" table lists software resellers, this table is not used when creating the license report.
   
• The "licensetypes" table lists different license types.
   
• The "licenses" table lists the purchased licenses. Each entry contains a reference to a software product and a license type.

Table "swmanufacturers"

The swmanufacturers table lists software manufacturers:

Table "swproducts"

The swproducts table lists products you can purchase from software manufactures: program licenses (full versions), upgrades, software insurances…:

Enter the full name of the software product including version number.
If the product is an upgrade license requiring a previous product from the same product line, include the word UPGRADE here.
If the product is an "Upgrade Advantage" allowing an upgrade from previous versions and lower product lines, include "UPGRADE from …" here and list the products allowing the upgrade.
If the product is a software insurance, enter "SA from … until 2034-08-31" here while in the software insurance period. Change it to "SA from … up to version x.y" after the software insurance period.
Example:
"SA from Desktop 3.5 until 2034-08-31" while the software insurance is running, "SA from Desktop 3.5 up to Desktop 25.8" after 2034-08-31.
Note: For all updates and insurances the full name must explain which product version(s) can be used as upgrade base and which version is the upgrade destination.

Table "swresellers"

The swresellers table is currently not used by itadmin:

Table "licensetypes"

The licensestypes table lists the different license types:

Table "licenses"

The licenses table lists the licenses purchased by the organizational unit or purchased by the organization and assigned to the organizational unit:

Restrictions

At this time only the MariaDB / MySQL database server is supported.

The program is not available on Windows platforms.

Notes

This program uses DK libraries version 3.

FAQ

Where can I obtain MySQL?

MySQL is available at http://www.mysql.com. The community edition server is available free of charge.
Depending on the office suite you use on your desktop you most likely need the Connector/ODBC. On Linux systems your package management should allow you to install server, client software, and connector.

OpenOffice: Should I use the Java connector or the ODBC connector?

I prefer the ODBC connector, it is significantly faster on my computer.

Linux: How do I create an ODBC data source for use with oobase?

Make sure you have installed the "mysql-connector-odbc" and "unixODBC" packages.

The /etc/odbcinst.ini file should contain a section like:

# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

If the section doesn't exist - add it. The file names of the shared libraries may change, i.e. if there is a new version of MySQL.

In /etc/odbc.ini create a section:

[it]
Description     =       IT database
Driver          =       /usr/lib/libmyodbc5.so
Server          =       127.0.0.1
Database        =       it
Port            =       3306
Socket          =       /var/lib/mysql/mysql.sock
Option          =       3
ReadOnly        =       No
ServerType      =       MySQL
charset         =       utf8

It depends on your system whether or not you need the final "charset" line and which setting to choose.
Check the LANG environment variable. If it ends on ".UTF-8" or ".utf8" the "utf8" setting is correct. If the LANG environment variable ends on ".latin1" you must set the charset to "latin1". In general the "charset" setting specifies the character set used by the application processing the data, not the character set used in the database tables.

Now you should be able to open the database in oobase as ODBC database.

Where can I obtain the ISC DHCP server?

Visit http://www.isc.org/.

How do I run itadmin?

Normally you do not run itadmin alone, you combine it with other steps in a shell script.

Change into a special directory.

cd /root/scripts/itadmin

Note: Do not use the /etc directory!

Clean up the directory
Remove the files "hosts", "ethers-by-ip", "ethers-by-mac", "hosts.ldif", "ethers.ldif", "netgroup.ldif", "dhcpd.conf", "netdocu.tex", "licenses.tex", "netdocu.pdf", and "licenses.pdf".

Run itadmin and check the result
Continue with the following steps only if itadmin succeeded and returned exit status 0.

itadmin

Copy hosts and ethers to the /etc directory.

cp hosts /etc/hosts
cp ethers-by-mac /etc/ethers

It depends on your system whether to copy ethers-by-mac or ethers-by-ip to /etc/ethers. On Linux use ethers-by-mac, on Solaris use ethers-by-ip. Check your systems documentation to choose the correct file.

If you are running a DHCP server: Bring the new dhcpd.conf file in place.

/etc/rc2.d/Sxxdhcp stop
sleep 2
cp dhcpd.conf /etc/dhcpd.conf
/etc/rc2.d/Sxxdhcp start

You need to customize the above commands.

If you run an LDAP service: Update LDAP data.

Create a list of LDAP objects to delete (hosts, ethers and netgroup objects).

itacldap.pl

You can run the itacldap.pl script if you have Perl available on your system. Save the script as /usr/local/bin/itacldap.pl, the script must be customized before it can be used. It creates a file "objects-to-delete".

Delete LDAP objects.

ldapdelete -f objects-to-delete

Further command line options are necessary to specify an LDAP bind DN, a password or password file, a host or URI.

Enter new LDAP objects.

Run either

ldapaddent -f hosts hosts
ldapaddent -f ethers ethers
ldapadd -f netgroup.ldif

or

ldapadd -f hosts.ldif
ldapadd -f ethers.ldif
ldapadd -f netgroup.ldif

Create network documentation and license report.

PDFLATEX="pdflatex -interaction=batchmode"
$PDFLATEX netdocu && $PDFLATEX netdocu && $PDFLATEX netdocu
$PDFLATEX licenses && $PDFLATEX licenses && $PDFLATEX licenses

Copy network documentation, license report, and probably other files from the server to your desktop PC.
You might want to copy all the files from the current directory from the server to your desktop PC.
The files are needed the most if there are problems with network and/or file server, so make sure to have them accessable when the server is unreachable.

What's the "database credentials file"?

When connecting to a database service, a user name and a password is needed. This user name/password combination is not written directly to the itadmin.conf file, a reference to a credentials file containing user name and password is used instead. Only the credentials file needs protection against illegal access — set permissions to 600 so nobody else can even read it. For the itadmin.conf file you can use relaxed permissions 644, other users can read it as it does not contain confidential data.

A database credentials file looks like:

[client]
user=...
password=...

Why should I use netgroups?

Netgroups are an elegant method to administer host-based permissions.
Without using netgroups you have to enter each host manually into access lists of daemons (i.e. Samba, ssh, tcpd…) and restart the service to apply the changes.
When using netgroups you configure the services to grant a group of hosts access to the service. The group name is written into the service configuration files. When adding a new computer to or removing a computer from a netgroup no service restart is necessary as the service configuration file was not changed and the group membership is checked at runtime when a client attempts to use a service.

What name services are required to use netgroups?

You need an LDAP service to use netgroups.
If you are still using NIS, use the hostsadm script from dktools 2.x.x to produce an input file for NIS service. And plan to migrate to LDAP as soon as possible.

How should I organize the netgroups?

Netgroups can be nested, one netgroup A can be a member of another netgroup B. This means all member hosts of A are automatically member hosts of B too.
I suggest a two-level netgroup scheme: On the lower level hosts are grouped by type, i.e. desktop PCs, student PCs, bring-your-own-device, guest laptops… On the higher level there are groups with access to resources, the lower level groups are members of the higher level groups.
When purchasing a new PC you only have to add it to the appropriate lower level group, that's it.

Why should I run a DHCP server?

• When re-installing PCs the correct TCP/IP setup is found automatically.
   
• Guests in the network can obtain a temporary IP address from a dedicated address pool.
   
• Minimize waste of IP addresses. If some PCs are not used contiguously, assigning a fixed IP address would be waste of IP addresses.
   
• After cloning PCs — i.e. to install a PC classroom — you can boot all PCs at same time and they obtain different IP addresses. If the cloning source (master PC for installation) was configured to use a fixed IP address, all cloned PCs use the same IP. So without DHCP you could boot exactly one cloned PC at a time.

How can I grant access to DHCP address pools?

When using DHCP pools in itadmin there are two variants to configure a DHCP pool:

• Only members of a dedicated DHCP class can obtain addresses from the pool.
  Access for unknown clients is not allowed.
  Access for unknown clients is denied.
  A DHCP class name is specified.
   
• Unknown clients are allowed to obtain an address from the pool.
  Access for unknown clients is allowed.
  Access for unknown clients is not denied.

When using itadmin, membership in DHCP classes is based on the MAC address.
To allow a computer to obtain an IP address dynamically, leave the "co_ip" field in the computers table empty. Set the "dc_s" field to the name of DHCP class the computer belongs to. Set the "co_mc" field to the MAC address of the computer.
You can obtain the MAC address by running "ifconfig -a" (*x) or "ipconfig /ALL" (Windows).

How many DHCP pools should I create?

I recommend to have at least two pools:

• One DHCP pool for all computers not (yet) registered. The pool should provide public IP addresses granting neither Internet access nor any access to resources.
   
• One DHCP pool (or multiple DHCP pools) for registered hosts. The computers are listed in the database (not fixed IP address but MAC address and DHCP class specified).

The purpose of these different pools is that every guest, laptop owner… must visit the network administrator to register the computer for DHCP use. Depending on your organizations policies the user has to sign a contract or is simply tought about usage policies…