DB 2 V9 Z.os - is anyone using Roles as part of their security?

Robert Catterall

DB 2 V9 Z.os - is anyone using Roles as part of their security?
Sorry about the super-late response -- just now seeing this.

I don't know that roles and trusted contexts would give you what you're
looking for. When you say that you want "to give DBAs functional work
ability on the structures without data access," that's a privilege thing,
not a role thing. The introduction of roles and trusted contexts did not
introduce any new DB2 privileges. Rather, this new (with DB2 9) security
capability gave you a new way to assign privileges (roles) and to limit the
scope of the exercising of privileges (trusted contexts). So, instead of
granting to an ID a set of privileges required to execute some dynamic
SQL-issuing application, and then having a situation in which that ID might
be used for unauthorized purposes, you could grant the privileges to a role
and then associate that role with a trusted context that might (for example)
limit the use of the role's privileges to a particular application server
(identified by an IP address) and a particular "system" auth ID (supplied by
the app server when it connects to DB2), and maybe even to some IDs of
individual users of the application that runs on the designated app server
(and which uses the designated system auth ID for connecting to DB2). By
assigning privileges to a role and then substantially limiting the
circumstances in which that role can be used, you substantially reduce the
possibility of unauthorized use of the privilege set.

What you'd do to provide DBAs with the ability to do their job without
having access to data in user tables would depend on the release of DB2 for
z/OS you're running. If it were DB2 10, you could use a new option of the
GRANT statement (I'm showing a GRANT to an auth ID -- you could also grant
the privilege to a role):


If you're running a DB2 release prior to DB2 10, you could accomplish your
objective by assigning to the DBAs (directly or through a role) DBCTRL
authority plus any additional privileges needed to do the job and not
included with DBCTRL authority (e.g., ALTER), while withholding privileges
such as SELECT. Note that the privileges included with DBADM that aren't
included with DBCTRL are nicely documented in the DB2 Administration Guide
(in the DB2 9 manual -- eighth edition, December 2010 -- you find this
information in Figure 16, in the section titled "Administrative authorities"
in Chapter 5).

Your mention of "a legal requirement based on geography" may refer to a
possible application of roles and trusted contexts. If what you mean is that
only DBAs working in a certain geography are to have the "no user data
access" restriction, you could assign the needed privileges (mentioned
above) to a role, and associate that role with a trusted context that would
have as attributes a set of IP addresses of servers (presumably located in
the geographic area in which the DBAs work) that these DBAs would use in
issuing SQL statements. Alternatively, if these DBAs were restricted to
using a certain batch job for issuing SQL statements, the trusted context
could have as an attribute the associated job name.

NOTE: when a user establishes a trusted connection with a DB2 subsystem (a
connection that's in accordance with a trusted context), he or she has the
privileges of the associated role PLUS any privileges granted directly to
his or her ID. The point here is that roles and trusted contexts are
effective in limiting the exercise of privileges if those privileges are not
widely granted to users' DB2 auth IDs.

Hope this helps.


On Wed, Nov 3, 2010 at 4:54 PM, Case, Missy J <[login to unmask email]>wrote:

> All,
> I have been researching the definition of DB2 roles & trying to get a
> handle on them in pre V9 build up.
> We are looking at a legal requirement based on geography, to limit a DBA’s
> ability to access any type of true user data, in other words, to give DBA’s
> functional work ability on the structures without direct data access.
> Has anyone set up roles in a similar manner?
> Or – am I looking at this from entirely the wrong angle?
> Thanks for the help,
> *Missy Case*
> *DB2 Database Architecture and Performance***
> *Global Database Administration Services | First Data Corporation***
> *Office Phone: (402) 222-7562 ***
> ------------------------------
> * The information in this message may be proprietary and/or confidential,
> and protected from disclosure. If the reader of this message is not the
> intended recipient, or an employee or agent responsible for delivering this
> message to the intended recipient, you are hereby notified that any
> dissemination, distribution or copying of this communication is strictly
> prohibited. If you have received this communication in error, please notify
> First Data immediately by replying to this message and deleting it from your
> computer. *
> ------------------------------
> [image: Independent, not-for-profit, User Run - the IDUG difference! ] < http://www.idug.org >
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >

Robert Catterall
DB2 Specialist
IBM US - East
[login to unmask email]

* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv