Database Roles

If you are familiar with Oracle Database, you will be happy to know that DB2 9.5 has introduced few new features that you may already be familiar with in Oracle. First of all, there are several SQL related compatibility features (e.g. support for dual, decode etc.). Check the developerWorks article DB2 Viper 2 compatibility features for more details. These compatibility features will greatly help the migration from Oracle to DB2 database.

Database Roles, another new feature in DB2 9.5, was also available in Oracle since long. A role has a certain set of privileges and a user who is granted membership in this role inherits those privileges. Roles are also available in Informix, SQL Server, Sybase and may be some other RDBMS’s.

DB2 provided an alternative to role in the form of group based authorization. You may grant one or more privileges to user groups and all the users in that group will automatically have those privileges. You may add a user to that group (equivalent to granting a role to a user) or remove a user from the group (equivalent to revoking a role from a user). However, there are two major limitations with group based authorization

1. Privileges and authorities granted to groups are not considered when a user is creating views, materialized query tables (MQTs), SQL routines, triggers and packages containing static SQL. This is because groups and users are controlled externally from the DB2 database system, for example, by an operating system or an LDAP server. So if a user will be removed from a group, database may not be aware of it and would not update the validity of created objects mentioned above.
2. A role can be granted to a role and so a role hierarchy is possible, which is not possible with group based authorization.

To overcome the above limitations, DB2 9.5 introduced roles in addition to group based authorization. In DB2, a role is a database object that groups together one or more privileges and can be assigned to users, groups, PUBLIC, or other roles by using a GRANT statement. For more details, check the Roles at DB2 Information Center.