The Informix Logical Layout

Each server instance can host multiple databases. Two default databases are sysmaster and sysutils; on Linux there is a third called sysusers. The sysmaster database contains a table called sysdatabases. This table holds the details of all the other databases on the instance. Connecting to the sysmaster database and issuing the following query will list all of the databases:

 select name from sysdatabases 

On choosing a database of interest you'll want to dump a list of the tables in the database; you can do this with the following query:

 select tabname from systables 

You'll notice that each database has some metatables; these metatables hold data about the database itself. The metatables are

systables

syscolumns

sysindices

systabauth

syscolauth

sysviews

sysusers

sysdepend

syssynonyms

syssyntable

sysconstraints

sysreferences

syschecks

sysdefaults

syscoldepend

sysprocedures

sysprocbody

sysprocplan

sysprocauth

sysblobs

sysopclstr

systriggers

systrigbody

sysdistrib

sysfragments

sysobjstate

sysviolations

sysfragauth

sysroleauth

sysxtdtypes

sysattrtypes

sysxtddesc

sysinherits

syscolattribs

syslogmap

syscasts

sysxtdtypeauth

sysroutinelangs

syslangauth

sysams

systabamdata

sysopclasses

syserrors

systraceclasses

systracemsgs

sysaggregates

syssequences

sysdomains

sysindexes

Of interest are the %AUTH% tables because they describe who has what permissions or privileges. One of the major shortcomings of the Informix database is that it is not possible to revoke the public select permission from these AUTH tables. Attempting to execute

 revoke select on sysmaster:informix.systabauth from public 

results in an error: "511: Cannot modify system catalog (systabauth)." This means that anyone can go poking around looking at authorizations. Just as frustrating is the fact that you can't protect the sysusers table either. This table lists the users that have been given explicit access for a given database. Let's delve into users further.

Understanding Authentication and Authorization

Like the other IBM database, DB2, Informix uses the operating system authentication for authentication purposes. That said, there is a sysusers table. This table stores the usernames of those people that have been given access to the database. Like most database servers there's a special user called public. Everyone enjoys the privileges that are given to public. There are three main groupings of privileges under Informix: Connect, Resource, and DBA. To connect to the database server you need a minimum of Connect privileges. If public has been given the Connect privilege, anyone with a valid OS username and password can connect to the database server. (Although this is true on Linux, this is not fully the case on Windows. If the user hasn't been given direct access, he gets access only via public if he's in the administrators group .) A secure server should not give public the connect privilege, but note that, by default, public is granted Connect. Going back to the sysusers table, the usertype column describes a user 's privilege level. A "C" indicates Connect, an "R" indicates Resource and, you guessed it, a "D" indicates DBA. Let's quickly examine what each privilege level has.

Connect

Users with the Connect privilege can run SELECT, INSERT, UPDATE, and DELETE queries as long as they have the appropriate table-level privileges. They can execute procedures ”again providing they have the privilege to do so. They can create views provided they have the relevant privileges on the underlying table. Finally, they can also create temporary tables and indexes on temporary tables.

Resource

Users with the Resource privilege can do everything that Connect can do; they can also create new database objects such as tables, procedures, and so on.

DBA

DBAs are basically God as far as the server is concerned . Well, maybe not quite that powerful. As you'll see later there are a number of frustrating things a DBA can't do.

Object Privileges

Privileges that can be granted on tables include SELECT, UPDATE, INSERT, DELETE, INDEX, ALTER, and REFERENCE. If a user has been granted privileges on a table, the details of the grant will be listed in the systabauth table. A grant of every privilege would be listed as su-idxar. If the letters are in uppercase, then the user has the WITH GRANT option that indicates he can grant the privilege to others. The dash in su-idxar indicates column-level privileges.

There is only one routine-level privilege ”EXECUTE. Details on routine grants are stored in the sysprocauth table.

Privileges and Creating Procedures

One area that deserves special attention is privileges where creating procedures is concerned. Procedures can be written in languages such as C and Java but to do so a user needs to have been granted the "usage" of the language in question. For example, you can write a procedure in C only if you've been given usage on the C language. This can be granted with the following:

 GRANT USAGE ON LANGUAGE C TO USERNAME 

When a user has been granted usage on a language this grant is stored in the syslangauth table. The langid column from the following table relates to the langid column from the sysroutinelangs table.

langid	langname
	builtin
1	C
2	SPL
3	Java
4	Client

Incidentally the sysroutinelangs table presents an interesting location for a backdoor into a compromised Informix server. The langpath column holds the path to a Dynamic Link Library or shared object that the language uses, which is loaded to facilitate it. The langinitfunc column holds the name of the function to be called to do this. This table can be updated, for example replacing the library for java:

 update sysroutinelangs set langpath='foo.dll' where langid = 3