Administrator > Security

Fortifying Security With SQL

The IBM* i community has believed for years that the i platform is the most securable computer system on the market. The power of native security is undeniable. However, even shops that leverage native i security may be unaware of the SQL security features that can help extend native security.

In this article, I’ll focus on SQL-based security (aka privileges) and explain how they can help you become confident in your security.

SQL Object Security

The SQL GRANT and REVOKE statements operate on SQL functions, SQL packages, SQL procedures, distinct types, sequences, tables, views, and the individual columns of tables and views. Furthermore, SQL GRANT and REVOKE statements only control private and public authorities.

For the purposes of this article, I’ll focus on table, view and individual column privilege options.

Keen readers have probably noticed that GRANT and REVOKE don’t support group profiles, authorization lists and supplemental groups (but all of these security concepts are inherently honored), nor can they support objects like commands and programs. These security concepts aren’t part of SQL standards, so when you find yourself needing to apply i extensions you’ll have to use native commands like Edit Object Authority (EDTOBJAUT), Grant Object Authority (GRTOBJAUT) and Revoke Object Authority (RVKOBJAUT). You can interface with them via SQL by leveraging the Execute Command (QCMDEXC) stored procedure.

When reviewing documentation on SQL security concepts, you’ll often see a reference to an authorization ID. For all practical purposes, authorization ID is equivalent to an i user profile. A privilege granted to an authorization ID is considered a private privilege. In contrast to private privileges, privileges granted to SQL PUBLIC group user are equivalent to the *PUBLIC authority. Keep in mind that in the following examples, anywhere I use a private user (i.e., Betty), you could substitute special PUBLIC group user. One word of advice when mixing private and PUBLIC privileges is to ensure that any granted private privileges are a superset of any existing PUBLIC privileges as private privileges override PUBLIC privileges.

It’s important to note that an object’s owner can grant any and all privileges to any other user. Due to the widely adopted practice of shops running i to assign *ALLOBJ authority to users, it’s even more important to note that any such user can also grant and revoke privileges from SQL objects. In other words, special authority *ALLOBJ equates to administrative authority in SQL terminology.

DB2* for i supports nine privileges with GRANT and REVOKE SQL commands:

• SELECT
• INSERT
• UPDATE (ALL and column specific)
• DELETE
• INDEX
• REFERENCES (ALL and column specific)
• ALTER

These privileges apply to both tables and views, although views have some special restrictions (i.e., inherently nonupdatable view negates any granted UPDATE privileges). Most of the privileges are self-explanatory so I’ll just briefly touch upon the ones that may not be. The INDEX privilege implies that user can build indexes over a specified table. A REFERENCES privilege implies that user can add a referential constraint specifying a listed table or tables as a parent. The ALTER privilege implies the user can add or drop fields to the table, add or drop triggers to it and add or drop COMMENTs and LABELs on the table. The object owner can grant all privileges to other users by using ALL or ALL PRIVILEGES keywords instead of listing them individually.

Here’s a simple example that illustrates how to use SQL security at an object level:

GRANT SELECT, INSERT ON tableA TO Betty;

This lets Betty query and insert new rows into table A, but not update existing values or delete rows. A variation would be:

GRANT SELECT, INSERT ON tableA TO Betty WITH GRANT OPTION;

The WITH GRANT OPTION clause implies that Betty can now pass the SELECT and INSERT privileges on to other users. One caveat: There’s no CASCADE option on the REVOKE statement. This means you could remove previously granted privileges from Betty, but any users she’s granted privileges to in the meantime would preserve them.

Here’s an example of how to revoke existing authorities from a specific user:

REVOKE ALL PRIVILEGES ON tableA FROM Betty;

You can, of course, be selective about which privileges to revoke.

Next page: >>