DB2 Version 10.1 for Linux, UNIX, and Windows

Using roles after migrating from IBM Informix Dynamic Server

If you have migrated from IBM® Informix® Dynamic Server to the DB2® database system and are using roles there are a few things you need to be aware of.

The Informix Dynamic Server (IDS) SQL statement, GRANT ROLE, provides the clause WITH GRANT OPTION. The DB2 database system GRANT ROLE statement provides the clause WITH ADMIN OPTION (this conforms to the SQL standard) that provides the same functionality. During an IDS to DB2 database system migration, after the dbschema tool generates CREATE ROLE and GRANT ROLE statements, the dbschema tool replaces any occurrences of WITH GRANT OPTION with WITH ADMIN OPTION.

In an IDS database system, the SET ROLE statement activates a particular role. The DB2 database system supports the SET ROLE statement, but only to provide compatibility with other products using that SQL statement. The SET ROLE statement checks whether the session user is a member of the role and returns an error if they are not.

Example dbschema output

Assume that an IDS database contains the roles DEVELOPER, TESTER and SALES. Users BOB, ALICE, and TOM have different roles granted to each of them; the role DEVELOPER is granted to BOB, the role TESTER granted to ALICE, and the roles TESTER and SALES granted to TOM. To migrate to the DB2 database system, use the dbschema tool to generate the CREATE ROLE and GRANT ROLE statements for the database:

CREATE ROLE DEVELOPER
CREATE ROLE TESTER 
CREATE ROLE SALES 

GRANT  DEVELOPER TO BOB 
GRANT  TESTER TO ALICE, TOM 
GRANT  SALES TO TOM

You must create the database in the DB2 database system, and then you can run the preceding statements in that database to re-create the roles and assignment of the roles.