Sauron: SQL table descriptions

Sauron Database Format version 1.0

Tables

• common_fields
• settings
• servers
  • zones    (zones.server -> servers.id)
    • hosts    (hosts.zone -> zones.id)
      • dhcp_entries    (dhcp_entries.ref -> hosts.id)
      • mx_entries    (mx_entries.ref -> hosts.id)
      • ns_entries    (ns_entries.ref -> hosts.id)
      • printer_entries    (printer_entries.ref -> hosts.id)
      • a_entries    (a_entries.host -> hosts.id)
      • txt_entries    (txt_entries.ref -> hosts.id)
      • srv_entries    (srv_entries.ref -> hosts.id)
      • wks_entries    (wks_entries.ref -> hosts.id)
      • arec_entries    (arec_entries.host -> hosts.id)
      • arec_entries    (arec_entries.arec -> hosts.id)
    • cidr_entries    (cidr_entries.ref -> zones.id)
    • dhcp_entries    (dhcp_entries.ref -> zones.id)
    • mx_entries    (mx_entries.ref -> zones.id)
    • mx_templates    (mx_templates.zone -> zones.id)
      • hosts    (hosts.mx -> mx_templates.id)
    • ns_entries    (ns_entries.ref -> zones.id)
    • txt_entries    (txt_entries.ref -> zones.id)
  • groups    (groups.server -> servers.id)
    • hosts    (hosts.grp -> groups.id)
    • dhcp_entries    (dhcp_entries.ref -> groups.id)
    • printer_entries    (printer_entries.ref -> groups.id)
  • nets    (nets.server -> servers.id)
    • dhcp_entries    (dhcp_entries.ref -> nets.id)
  • cidr_entries    (cidr_entries.ref -> servers.id)
  • dhcp_entries    (dhcp_entries.ref -> servers.id)
  • txt_entries    (txt_entries.ref -> servers.id)
  • wks_templates    (wks_templates.server -> servers.id)
    • hosts    (hosts.wks -> wks_templates.id)
    • wks_entries    (wks_entries.ref -> wks_templates.id)
  • root_servers    (root_servers.server -> servers.id)
  • vlans    (vlans.server -> servers.id)
    • nets    (nets.vlan -> vlans.id)
• ether_info
• printer_classes
  • printer_entries    (printer_entries.ref -> printer_classes.id)
• users
  • user_rights    (user_rights.ref -> users.id)
  • utmp    (utmp.uid -> users.id)
• user_groups
  • users    (users.gid -> user_groups.id)
  • user_rights    (user_rights.ref -> user_groups.id)
  • utmp    (utmp.gid -> user_groups.id)
• hinfo_templates
• history
• lastlog
• news

Table Descriptions

Table: common_fields

field	type	comments
cdate	INT4	creation date
cuser	CHAR(8) DEFAULT 'unknown'	creating user
mdate	INT4	modification date
muser	CHAR(8) DEFAULT 'unknown'	last changed by this user
expiration	INT4	expiration date

Table: settings

field	type	comments
key	TEXT NOT NULL CHECK(key <> '')	name os setting tuple
value	TEXT	string value of setting
ivalue	INT4	interger value of setting
CONSTRAINT	global_key PRIMARY KEY (key)

Table: servers

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
name	TEXT NOT NULL CHECK(name <> '')	server name
zones_only	BOOL DEFAULT false	if true, generate named.zones file otherwise generate complete named.conf
no_roots	BOOL DEFAULT false	if true, no root server (hint) zone entry is generated
dhcp_mode	INT DEFAULT 1	DHCP subnet map creation mode: 0 = use VLANs, 1 = use networks
dhcp_flags	INT DEFAULT 0	DHCP option flags: 0x01 = auto-generate domainnames 0x02 = enable failover protocol
named_flags	INT DEFAULT 0	named option flags: 0x01 = access control from master (slave only) 0x02 = include also slave zones from master (slave only) 0x04 = do NOT generate HINFO records 0x08 = do NOT generate WKS records
masterserver	INT DEFAULT -1	dynamically add slave zones for all zones in master server
		named.conf options...more to be added as needed...
version	TEXT	version string to display (optional)
directory	TEXT	base directory for named (optional)
pid_file	TEXT	pid-file pathname (optional)
dump_file	TEXT	dump-file pathname (optiona)
named_xfer	TEXT	named-xfer pathname (optional)
stats_file	TEXT	statistics-file pathname (optional)
memstats_file	TEXT	memstatistics-file pathname (optional)
named_ca	TEXT DEFAULT 'named.ca'	root servers filename
pzone_path	TEXT DEFAULT ''	relative path for master zone files
szone_path	TEXT DEFAULT 'NS2/'	relative path for slave zone files
query_src_ip	TEXT	query source ip (optional) (ip | '*')
query_src_port	TEXT	query source port (optional) (port | '*')
listen_on_port	TEXT	listen on port (optional)
transfer_source	INET	transfer-source (optional)
forward	CHAR(1) DEFAULT 'D'	forward: D=default O=only, F=first
		check-names: D=default, W=warn, F=fail, I=ignore
checknames_m	CHAR(1) DEFAULT 'D'	check-names master
checknames_s	CHAR(1) DEFAULT 'D'	check-names slave
checknames_r	CHAR(1) DEFAULT 'D'	check-names response
		boolean flags: D=default, Y=yes, N=no
nnotify	CHAR(1) DEFAULT 'D'	notify
recursion	CHAR(1) DEFAULT 'D'	recursion
authnxdomain	CHAR(1) DEFAULT 'D'	auth-nxdomain
dialup	CHAR(1) DEFAULT 'D'	dialup
multiple_cnames	CHAR(1) DEFAULT 'D'	multiple-cnames
rfc2308_type1	CHAR(1) DEFAULT 'D'	rfc2308-type1
		default TTLs
ttl	INT4 DEFAULT 86400	default TTL for RR records
refresh	INT4 DEFAULT 43200	default SOA refresh
retry	INT4 DEFAULT 3600	default SOA retry
expire	INT4 DEFAULT 2419200	default SOA expire
minimum	INT4 DEFAULT 86400	default SOA minimum (negative caching ttl)
		IPv6
ipv6	TEXT	reserved
		DHCP failover
df_port	INT DEFAULT 519	listen port
df_max_delay	INT DEFAULT 60	max-response-delay
df_max_uupdates	INT DEFAULT 10	max-unacked-updates
df_mclt	INT DEFAULT 3600	mlct
df_split	INT DEFAULT 128	split
df_loadbalmax	INT DEFAULT 3	load balance max seconds
		defaults to use in zones
hostname	TEXT	primary servername for sibling zone SOAs
hostaddr	INET	primary server IP address
hostmaster	TEXT	hostmaster name for sibling zone SOAs unless overided in zone
comment	TEXT
CONSTRAINT	servers_name_key UNIQUE(name)

Table: zones

field	type	comments
id	SERIAL	unique ID
server	INT4 NOT NULL	ptr to a record in servers table -->servers.id
active	BOOL DEFAULT true	zone active flag (only active zones are included in named configuration)
dummy	BOOL DEFAULT false	dummy zone flag
type	CHAR(1) NOT NULL	zone type: (H)int, (M)aster, (S)lave, (F)orward
reverse	BOOL DEFAULT false	true for reverse (arpa) zones
noreverse	BOOL DEFAULT false	if true, zone not used in reverse map generation
flags	INT DEFAULT 0	zone option flags: 0x01 = generate TXT records from user,dept,location,info fields
forward	CHAR(1) DEFAULT 'D'	forward: D=default, O=only, F=first
nnotify	CHAR(1) DEFAULT 'D'	notify: D=default, Y=yes, N=no
chknames	CHAR(1) DEFAULT 'D'	check-names: D=default, W=warn, F=fail, I=ignore
class	CHAR(2) DEFAULT 'in'	zone class (IN)
name	TEXT NOT NULL CHECK (name <> '')	zone name
hostmaster	TEXT	hostmaster (email) (optional; if not defined value from server table is used instead)
serial	CHAR(10) DEFAULT '1999123001'	zone serial number (automagically updated)
serial_date	INT4 DEFAULT 0	zone serial last update date
refresh	INT4	zone SOA refresh time
retry	INT4	zone SOA retry time
expire	INT4	zone SOA expire time
minimum	INT4	zone SOA minimum (negative caching) time
ttl	INT4	default TTL for RRs in this zone (if not defined, value from servers record is used instead)
zone_ttl	INT4	unused
comment	TEXT
reversenet	CIDR	contains CIDR of the reverse zone (if applicaple)
parent	INT4 DEFAULT -1	unused
CONSTRAINT	zones_key PRIMARY KEY (name,server)

Table: hosts

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
zone	INT4 NOT NULL	ptr to a zone table record -->zones.id
type	INT4 DEFAULT 0	host type: 0=misc, 1=host, 2=subdomain (delegation), 3=mx entry, 4=alias (cname), 5=printer, 6=glue record, 7=alias (arec), 8=srv entry, 9=dhcp only, 10=zone, 101=host reservation
domain	TEXT NOT NULL CHECK(domain <> '')	host domain name
ttl	INT4	TTL for host records, default if NULL
class	CHAR(2) DEFAULT 'IN'	class (IN)
grp	INT4 DEFAULT -1	ptr to group -->groups.id
alias	INT4 DEFAULT -1	ptr to another host record (for CNAME alias)
cname_txt	TEXT	CNAME value for out-of-zone alias
hinfo_hw	TEXT	HINFO hardware
hinfo_sw	TEXT	HINFO software
loc	TEXT	LOC record value
wks	INT4 DEFAULT -1	ptr to wks_templates table entry -->wks_templates.id
mx	INT4 DEFAULT -1	ptr to mx_templates table entry -->mx_templates.id
rp_mbox	TEXT DEFAULT '.'	RP mbox
rp_txt	TEXT DEFAULT '.'	RP txt
router	INT4 DEFAULT 0	router if > 0, also router priority (1 being highest priority)
prn	BOOL DEFAULT false	true for virtual printer entries
flags	INT4 DEFAULT 0	reserved
ether	CHAR(12)	Ethernet address (MAC)
ether_alias	INT4 DEFAULT -1	ptr to another host record (for ETHER address)
dhcp_date	INT4	last time host requested IP
dhcp_info	TEXT	reserved
info	TEXT	Host info (appears as TXT record)
location	TEXT	Host location info
dept	TEXT	Department name
huser	TEXT	User info
model	TEXT	host model info
serial	TEXT	serial number
misc	TEXT	misc info
asset_id	TEXT	asset ID
comment	TEXT	comment
CONSTRAINT	hostname_key UNIQUE (domain,zone),
CONSTRAINT	ether_key UNIQUE(ether,zone),
CONSTRAINT	asset_key UNIQUE(asset_id,zone)

Table: groups

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
server	INT4 NOT NULL	ptr to a servers table record -->servers.id
name	TEXT NOT NULL CHECK(name <> '')	group name
type	INT NOT NULL	group type: 1 = normal group, 2 = dynamic address pool, 3 = DHCP client class
alevel	INT4 DEFAULT 0	required authorization level
comment	TEXT
CONSTRAINT	groups_key UNIQUE(name,server)

Table: nets

field	type	comments
id	SERIAL	unique ID
server	INT4 NOT NULL	ptr to a servers table record -->servers.id
netname	TEXT	(sub)net name
name	TEXT	descriptive name of the (sub)net
net	CIDR NOT NULL	net CIDR
subnet	BOOL DEFAULT true	subnet flag
vlan	INT4 DEFAULT -1	ptr to vlans table record -->vlans.id
alevel	INT4 DEFAULT 0	required authorization level
type	INT4 DEFAULT 0	network type/option flags: 0x01 = private (hidden from browser)
ipv6	TEXT	reserved
rp_mbox	TEXT DEFAULT '.'	RP mbox
rp_txt	TEXT DEFAULT '.'	RP txt
no_dhcp	BOOL DEFAULT false	no-DHCP flag
range_start	INET	auto assign address range start
range_end	INET	auto assign address range end
comment	TEXT	comment
CONSTRAINT	nets_key PRIMARY KEY (net,server)

Table: cidr_entries

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
type	INT4 NOT NULL	type: 1=server (allow-transfer) 2=zone (allow-update) 3=zone (masters) 4=zone (allow-query) 5=zone (allow-transfer) 6=zone (also-notify) 7=server (allow-query) 8=server (allow-recursion) 9=server (blackhole) 10=server (listen-on) 11=server (forwarders) 12=zone (forwarders)
ref	INT4 NOT NULL	ptr to table speciefied by type field -->servers.id -->zones.id
ip	CIDR	CIDR value
comment	TEXT

Table: dhcp_entries

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
type	INT4 NOT NULL	type: 1=server, 2=zone, 3=host, 4=net, 5=group 6=vlan (shared-network)
ref	INT4 NOT NULL	ptr to table speciefied by type field -->servers.id -->zones.id -->hosts.id -->nets.id -->groups.id
dhcp	TEXT	DHCP entry value (without trailing ';')
comment	TEXT

Table: ether_info

field	type	comments
ea	CHAR(6) PRIMARY KEY	manufacturer code (6 bytes in hex)
info	TEXT	manufacturer name & info

Table: mx_entries

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
type	INT4 NOT NULL	type: 1=zone (not used anymore!), 2=host, 3=mx_templates
ref	INT4 NOT NULL	ptr to table speciefied by type field -->zones.id -->hosts.id -->mx_templates
pri	INT4 NOT NULL CHECK (pri >= 0)	MX priority
mx	TEXT	MX domain (FQDN)
comment	TEXT

Table: mx_templates

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
zone	INT4 NOT NULL	ptr to a zone table record -->zones.id
alevel	INT4 DEFAULT 0	required authorization level
name	TEXT	template name
comment	TEXT

Table: ns_entries

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
type	INT4 NOT NULL	type: 1=zone (not used anymore!), 2=host
ref	INT4 NOT NULL	ptr to table speciefied by type field -->zones.id -->hosts.id
ns	TEXT	value of NS record (FQDN)
comment	TEXT

Table: printer_classes

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
name	TEXT UNIQUE NOT NULL CHECK(name <> '')	class name
comment	TEXT

Table: printer_entries

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
type	INT4 NOT NULL	type: 1=group, 2=host, 3=printer_class
ref	INT4 NOT NULL	ptr to table speciefied by type field -->groups.id -->hosts.id -->printer_classes.id
printer	TEXT	printcap entry
comment	TEXT

Table: a_entries

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
host	INT4 NOT NULL	ptr to hosts table id -->hosts.id
ip	INET	IP number
ipv6	TEXT	reserved
type	INT4 DEFAULT 0	reserved
reverse	BOOL DEFAULT true	generate reverse (PTR) record flag
forward	BOOL DEFAULT true	generate (A) record flag
comment	CHAR(20)

Table: txt_entries

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
type	INT4 NOT NULL	type: 1=zone (not used anymore!), 2=host, 3=server 10=server (BIND logging entry)
ref	INT4 NOT NULL	ptr to table speciefied by type field -->zones.id -->hosts.id -->servers.id
txt	TEXT	value of TXT record
comment	TEXT	comments

Table: srv_entries

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
type	INT4 NOT NULL	type: 1=host
ref	INT4 NOT NULL	ptr to table speciefied by type field -->hosts.id
pri	INT4 NOT NULL CHECK (pri >= 0)	priority
weight	INT4 NOT NULL CHECK (weight >= 0)	weight
port	INT4 NOT NULL CHECK (port >= 0)	port
target	TEXT NOT NULL DEFAULT '.'	target
comment	TEXT	comment

Table: users

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
gid	INT4 DEFAULT -1	ptr to user group -->user_groups.id
username	TEXT NOT NULL CHECK(username <> '')	login name
password	TEXT	encrypted password (MD5 or Crypt)
name	TEXT	long user name
email	TEXT	user email address
superuser	BOOL DEFAULT false	superuser flag
server	INT4 DEFAULT -1	default server id
zone	INT4 DEFAULT -1	default zone id
last	INT4 DEFAULT 0	last login time
last_pwd	INT4 DEFAULT 0	last password change time
last_from	TEXT	last login host
search_opts	TEXT	default search options
flags	INT4 DEFAULT 0	user account flasgs: 0x01 = email notifications on
comment	TEXT
CONSTRAINT	username_key UNIQUE(username)

Table: user_rights

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
type	INT NOT NULL	type: 1=user_group 2=users
ref	INT NOT NULL	ptr to users table specified by type -->user_groups.id -->users.id
rtype	INT NOT NULL	type: 1=server, 2=zone, 3=net, 4=hostnamemask 5=IP mask 6=authorization level 7=host expiration limit (days) 8=default for dept
rref	INT NOT NULL	ptr to table specified by type field
rule	CHAR(40)	R,RW,RWS or regexp

Table: user_groups

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
name	TEXT NOT NULL CHECK (name <> '')	group name
comment	TEXT	comments
CONSTRAINT	user_groups_name_key UNIQUE(name)

Table: wks_entries

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
type	INT4 NOT NULL	type: 1=host, 2=wks_template
ref	INT4 NOT NULL	ptr to table speciefied by type field -->hosts.id -->wks_templates.id
proto	CHAR(10)	protocol (tcp,udp)
services	TEXT	services (ftp,telnet,smtp,http,...)
comment	TEXT

Table: wks_templates

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
server	INT4 NOT NULL	ptr to a server table record -->servers.id
alevel	INT4 DEFAULT 0	required authorization level
name	TEXT	template name
comment	TEXT

Table: utmp

field	type	comments
cookie	CHAR(32) PRIMARY KEY	session id cookie (MD5)
uid	INT4	ptr to users table record -->users.id
gid	INT4	ptr to user_groups table record -->user_groups.id
sid	INT4	session ID
uname	TEXT	username
addr	CIDR	user's IP address
superuser	BOOL DEFAULT false	superuser flag
auth	BOOL DEFAULT false	user authenticated flag
mode	INT4	current status of user
w	TEXT	last command user excecuted
serverid	INT4 DEFAULT -1	current server id
server	TEXT	current server name
zoneid	INT4 DEFAULT -1	current zone id
zone	TEXT	current zone name
login	INT4 DEFAULT 0	login time
last	INT4 DEFAULT 0	last activity time
searchopts	TEXT	current search options
searchdomain	TEXT	current search domain
searchpattern	TEXT	current search pattern

Table: hinfo_templates

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
hinfo	TEXT NOT NULL CHECK(hinfo <> '') UNIQUE	HINFO value
type	INT4 DEFAULT 0	type: 0=hardware, 1=software
pri	INT4 DEFAULT 100	priority (defines the order in which entries are displayed in user interfaces)

Table: arec_entries

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
host	INT4 NOT NULL	ptr to hosts table id -->hosts.id
arec	INT4 NOT NULL	ptr to aliased host id -->hosts.id

Table: root_servers

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
server	INT4 NOT NULL	ptr to server table id -->servers.id
ttl	INT4 DEFAULT 3600000
domain	TEXT NOT NULL	domainname
type	TEXT NOT NULL	A,NS,...
value	TEXT NOT NULL	value

Table: history

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
sid	INT NOT NULL	session ID
uid	INT NOT NULL	user ID
date	INT NOT NULL	date of record
type	INT NOT NULL	record type: 1=hosts table modification, 2=zones 3=servers 4=nets 5=users
ref	INT	optional reference
action	CHAR(25)	operation performed
info	CHAR(80)	extra info

Table: lastlog

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
sid	INT NOT NULL	session ID
uid	INT NOT NULL	user ID
date	INT NOT NULL	date of record
state	INT NOT NULL	record type: 1=logged in 2=logged out 3=idle timeout 4=reconnect
ldate	INT DEFAULT -1	logout date
ip	INET	remote IP
host	CHAR(40)	remote host

Table: news

field	type	comments
id	SERIAL PRIMARY KEY	unique ID
server	INT DEFAULT -1	ptr to server or -1 for global news messages
info	TEXT NOT NULL	news/motd message

Table: vlans

field	type	comments
id	SERIAL	unique ID
server	INT4 NOT NULL	ptr to a servers table record -->servers.id
name	TEXT NOT NULL CHECK(name <> '')	name of vlan
description	TEXT	long name
comment	TEXT	comments
CONSTRAINT	vlans_key PRIMARY KEY (name,server)