Authorization

As far as authorization is concerned , access to database objects is controlled with what are known as authorities . Operating system accounts or groups are granted authorities and an authority describes what that user or group can or cannot do. Most information on authorities is stored in database tables, but not all; the difference being whether the authority has database- or instance-wide scope. For example, SYSADM is the highest level of administrative authority on DB2 and has instance-wide scope, and the SYSADM_GROUP configuration parameter details the operating system group that is given this authority. For example, this would be the Administrators group on Windows and the db2grp1 group on Linux. The tables, or rather views, that store relevant information about authorities can be found in the SYSCAT schema and typically end with the suffix -AUTH. We'll examine the three most important of these, namely DBAUTH, TABAUTH, and ROUTINEAUTH.

The DBAUTH View

This view (of the SYSIBM.SYSDBAUTH table) contains information about database authorities. Each authority determines a set of actions that can be performed if the authority is granted.

DBADMAUTH

If granted, this authority gives the grantee the ability to perform administrative tasks on the database server. Almost as powerful as the SYSADM authority, the DBADM authority affects a database only ”and not an instance.

CREATETABAUTH

If granted, this authority gives the grantee the ability to create tables within the database.

BINDADDAUTH

If granted, this authority gives the grantee the ability to create and bind new applications in the database server.

CONNECTAUTH

If granted, this authority gives the grantee the ability to connect to the database server.

NOFENCEAUTH

If granted, this authority gives the grantee the ability to create routines (also known as procedures) that are not fenced ”that is, the procedure can run in the address space of the database process itself.

IMPLSCHEMAAUTH

If granted, this authority gives the grantee the ability to implicitly create schemas by creating an object using a schema name that doesn't exist.

LOADAUTH

If granted, this authority gives the grantee the ability to load data into tables, for example, from the filesystem.

EXTERNALROUTINEAUTH

If granted, this authority gives the grantee the ability to create procedures that call out to the operating system.

QUIESCECONNECTAUTH

If granted, this authority gives the grantee the ability to connect to the database when it is quiesced ”inactive.

One point to note here is that, by default, the special group PUBLIC is assigned certain authorities, namely the CONNECTAUTH, CREATETABAUTH, BINDADDAUTH, and the IMPLSCHEMAAUTH.

Note  

PUBLIC has the select permission on the -AUTH tables. This means that everyone can determine security-sensitive information such as which accounts are DBAs. With knowledge of this information an attacker can concentrate his efforts on specific accounts. To help secure DB2, the SELECT permission should be revoked from these views and tables from PUBLIC.

The TABAUTH View

The TABAUTH view (of the SYSIBM.SYSTABAUTH table) holds data about who can do what to database tables. There are three options for each authority in this table. A "Y" denotes that the grantee has the authority, an "N" that the grantee doesn't, and a "G" to indicate that, not only is the authority granted, but the grantee can grant it to others as well.

CONTROLAUTH

If granted, this authority gives the grantee the ability to completely control the table and assigns all of the table privileges including drop.

ALTERAUTH

If granted, this authority gives the grantee the ability to change the table's layout, for example add or remove columns . With this authority a user can also create triggers on the table.

DELETEAUTH

If granted, this authority gives the grantee the ability to delete data from the table.

INDEXAUTH

If granted, this authority gives the grantee the ability to create an index on the table.

INSERTAUTH

If granted, this authority gives the grantee the ability to insert new rows into the table.

SELECTAUTH

If granted, this authority gives the grantee the ability to select data from the table.

REFAUTH

If granted, this authority gives the grantee the ability to create and drop foreign keys for the table for references.

UPDATEAUTH

If granted, this authority gives the grantee the ability to update data in the table.

Note  

In a default install of DB2, PUBLIC is given far too much access to tables. For a secure installation of DB2 you'll want to revoke most of this.

The ROUTINEAUTH View

The ROUTINEAUTH view (of the SYSIBM.SYSROUTINEAUTH table) has only one authority defined ”the EXECUTEAUTH authority. This denotes whether the grantee can execute the procedure or not. This is important because one of the greatest weaknesses of any bit of database server software is usually its procedures, and DB2 is no different. Many of the vulnerabilities within DB2 are due to flaws within procedures and functions, collectively known as routines.

Note  

By default, PUBLIC can execute most procedures and functions. One would think that the best way to minimize risk of server compromise would be to revoke the execute authority from PUBLIC on routines. While this is true it's not that straightforward on DB2. If the routine is in the SYSFUN or SYSIBM schema, it is impossible to revoke PUBLIC execute access for it. Hopefully, one day this will change. It's advised that, where possible, the execute authority be revoked from PUBLIC.