DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501 and group permissions

Revised 13 Mar 2014

The Situation

A Groovy script that executes some SQL against a DB2 for LUW 9.7 database throws an exception:

com.ibm.db2.jcc.a.bn: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=USERID;EXECUTE;SCHEMA.GET_FACE_AMOUNT, DRIVER=3.51.90

IBM states that SQLCODE -551 means:

authorization-ID does not have the required authorization or privilege to perform operation operation on object object-name.

Initially, this seems to be a pretty straightforward problem. This is a database that was recently copied from another system. It is not unlikely that when it was restored, permissions were not granted to USERID to execute SCHEMA.GET_FACE_AMOUNT.

However, once the database administrator demonstrates that he can execute the GET_FACE_AMOUNT function while logged on as USERID, we’re left with a puzzle.

What We Learned

Our DB2 permissions were applied to user groups. The executing userid is a member of the ‘rw’ group, which should have read, write and execute permissions to the GET_FACE_AMOUNT function. As it turns out, however, DB2 does not apply group permissions to user defined functions.

It’s a much better practice to manage database permissions using roles. I’m not a DB2 DBA, so I can’t comment on exactly what the differences between groups and roles are. However, it seems like roles are a more robust way of defining permissions for a collection of users.

Advertisements

#db2, #db2-sql-error, #sql, #sql0551n, #sqlcode-551, #sqlstate-42501

Reading from a DB2 CLOB into a String with Groovy 1.56

Groovy makes everything easier, so I expected that there would be a simple, Groovy way to read text out of a CLOB field in my DB2 database. So far, however, I haven’t found any reference to one.

The java.sql.Clob API Guide describes three different methods that can be used to read from a CLOB: getAsciiStream(), getCharacterStream() and getSubString(). I worked out a few different uses from Sun’s suggestions:

Using getAsciiStream():

sql.eachRow("select clob_field from table_x") {row ->
java.sql.Clob clob = (java.sql.Clob) row["clob_field"]
String valueFromClob = clob.getAsciiStream().getText()
}

Using getSubString():

sql.eachRow("select clob_field from table_x") {row ->
java.sql.Clob clob = (java.sql.Clob) row["clob_field"]
String valueFromClob = clob.getSubString(1, clob.length().intValue())
}

Using getCharacterStream():

sql.eachRow("select clob_field from table_x") {row ->
java.sql.Clob clob = (java.sql.Clob) row["clob_field"]
List lines = clob.getCharacterStream().readLines()
StringBuffer contents = new StringBuffer()
lines.each {
contents.append(it)
}
String valueFromClob = contents.toString()
}

I worked those into a handy utility class:


import java.sql.Clob
import org.apache.log4j.Logger

public class ClobUtil {

static String getAsString(Object rsField) {
Logger log = Logger.getLogger("ClobUtil")
int tooBig = (64 * 1024 * 1024)

Clob clob = (Clob) rsField
/*
* In practice, we have found that the JVM has a 64K limit on Strings and will
* throw a java.lang.ClassFormatError when trying to process a longer String
* value. See http://jira.codehaus.org/browse/GROOVY-2382.
*/
int contentSize = clob.length().intValue()
if (clob.length() > tooBig) {
log.warn("Truncating contents at 64K characters. Use getAsCharArray() to read larger values.")
contentSize = tooBig
}

return clob.getSubString(1, contentSize)
}

static char[] getAsCharArray(Object rsField) {
Clob clob = (Clob) rsField
Reader reader = clob.getCharacterStream()
List contents = []
for (i in 1..clob.length().intValue()) {
contents << reader.read()
}
return (char[]) contents.toArray()
}
}

#clob, #db2, #groovy, #groovy-sql

Which environment is this?

Some context:  I’m working on a Grails web application that integrates with our internal business administration systems.  It runs on a Tomcat web server and it connects to a DB2 UDB database. 

Now, like any proper enterprise development effort, we’ve set up multiple environments to facilitate development and testing separately from our stable production systems.  In each environment, we’ve got a copy of the DB2 database, an instance of the business admin system’s executables, an instance of Tomcat running some build of our web app, and a copy of any other systems or objects that are part of the whole ecosystem. 

In addition, in some environments, we’ve actually got more than one instance of Tomcat.  In production, we run the web-facing applications on a separate instance from the the web applications used internally, for security.  And we’ve got an extra Tomcat instance exposed to the internet that runs against the user acceptance testing database, on which we can demo a beta of our web app for a select group of customers.

Problem:  So, given that we’ve got all of these different copies of our website, even if you know which URL and port you’ve navigated to, it can still be pretty easy to get confused about which system you’re interacting with at any given moment.  Except for the address, the screens look identical.  Imagine having one session open into the production system where you’re helping an end user and another one where you’re currently testing code.  And then you enter some test data into the production system by accident.  D’oh!

Before we even had the web app in production, users started requesting a visible indication on each page of the website showing which environment they were looking at.

Solution:  We really had two platforms that we needed to identify for users: which instance of the database they were connected to and which instance of Tomcat and build of the web application they were logged into.

On DB2 UDB, you can retrieve the database name using this statement: values current server.

So it was easy enough to code up a method in Groovy to fetch the db name:


static String getEnvironmentName() {
def sql = your connection string here
String env = new String()
try {
env = (String) sql.firstRow("values current server")[0]
} catch (SQLException e) {
// This is a db2 statement. May fail if run on other platforms.
env = null
}
return env
}

To identify the Tomcat instance, we had to be a bit more creative.  Most of our Tomcat instances run on the same host, and coding up some kind of matrix that would correlate a port number to an instance was something I had no inclination to have to maintain.  And there’s nothing that I know of built into Tomcat that applies a name to a server, other than a hostname.  Tomcat does, however, allow you to add your own environment variables in its conf/context.xml file.  So I decided upon a convention of adding an environment variable to each Tomcat instance specifying its name, and got our sysadmins to agree to it.


<Context>
...
<Environment name="appServerInstance" value="DEVELOPMENT" type="java.lang.String" over
ride="false"/>
...
</Context>

With an appServerInstance value assigned in each instance, we could use the following code to retrieve the instance name. At our sysadmin’s suggestion, we also decided not to assign a name to the production instance, and I wrote my code so that if no appServerInstance value is found, to show nothing on the web page. So, on every instance except the one our end users see, we’ve got an environment name at the top of the page. Our end users see nothing, since the system name wouldn’t be relevant to them anyway.


import javax.naming.InitialContext
import javax.naming.Context
import javax.naming.NameNotFoundException

...

static String getAppServerInstanceName() {
Context env = (Context) new InitialContext().lookup("java:comp/env")
String name = new String()
try {
name = (String) env.lookup("appServerInstance")
} catch (NameNotFoundException e) {
/* If the appServerInstance property has not been set in
Tomcat, then fail gracefully. */
name = null
}
return name
}

Now, to make the solution complete, I added some lazy initialization methods to our login controller that cache both names as session variables.


import javax.servlet.http.HttpSession
import org.springframework.web.context.request.RequestContextHolder

class SysUtil {

/**
* Retrieves the database environment name and caches the
* value on the session context.
*/
static String getEnvironmentName() {
String env = new String()
HttpSession session =
RequestContextHolder.currentRequestAttributes().getSession()
if (!session.environment) {
env = Sys.getEnvironmentName()
session.environment = env
} else {
env = session.environment
}
return env
}

/**
* Retrieves the app server instance name and caches the
* value on the session context.
*/
static String getAppServerInstanceName() {
String env = new String()
HttpSession session =
RequestContextHolder.currentRequestAttributes().getSession()
if (!session.appServer) {
env = Sys.appServerInstanceName()
session.appServer = env
} else {
env = session.appServer
}
return env
}

}

All that was left was to show the names somewhere in the main web page template. In a Grails application, that’s easily accomplished by editing grails-app/views/layouts/main.gsp. At a co-worker’s suggestion (thanks, Andy), I added the names to the page’s <title> tag, so that the environment name would show up in the taskbar button name of a minimized window. I also added it to a <div> at the top of the page:


<%@ page import="myPackage.SysUtil" />
...
<head>
<g:set var="environment" value="${SysUtil.getEnvironmentName()}" />
<g:set var="appServer" value="${SysUtil.getAppServerInstanceName()}" />
...
<title>
<g:if test="${appServer}">${appServer}: </g:if>
<g:layoutTitle default="Grails" />
</title>
...
</head>
<body>
<g:if test="${appServer}">
<div class="env">App Server: ${appServer} / Environment: ${environment}</div>
</g:if>
...
</body>

#best-practices, #db2, #grails, #groovy, #tomcat