Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your developerWorks profile is displayed to the public, but you may edit the information at any time. Your first name, last name (unless you choose to hide them), and display name will accompany the content that you post.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

DB2 9 Fundamentals exam 730 prep, Part 2: Security

Graham G. Milne (gmilne@ca.ibm.com), I/T Specialist DB2 UDB, IBM Canada
Graham Milne, HBSc. - Computer Science, is a DB2 Certified Advance Technical Expert and has been working with DB2 since 1998. Currently Graham is a Premium Support Manager for DB2 supporting large premium customers. Previous to this, he was the senior advanced service consultant for DB2 support based out of the IBM Toronto Software Lab.

Summary:  This tutorial introduces the concepts of authentication, authorization, and privileges as they relate to DB2® 9. It is the second in a series of seven tutorials designed to help you prepare for the DB2 9 Fundamentals Certification Exam (730). You should have basic knowledge of database concepts and operating system security. This is the second in a series of seven tutorials to help you prepare for the DB2 9 for Linux®, UNIX®, and Windows® Fundamentals exam 730.

View more content in this series

Date:  20 Jul 2006
Level:  Intermediate PDF:  A4 and Letter (505 KB | 32 pages)Get Adobe® Reader®

Comments:  

DB2 authorities

Introduction to authorities

DB2 authorities control the following aspects of a database security plan:

  • The authority level that a user is granted
  • The commands that a user is allowed to run
  • The data that a user is allowed to read and/or alter
  • The database objects a user is allowed to create, alter, and/or drop

Authorities are made up of groups of privileges and higher-level database manager (instance-level) maintenance and utility operations. Of the five authorities available in DB2, SYSADM, SYSCTRL, SYSMAINT, and SYSMON are instance-level authorities. That means that their scope includes instance-level commands as well as commands against all the databases within the instance. These authorities can only be assigned to a group; you can do so through the DBM CFG file.

The DBADM, LOAD, and SECADM authorities are assigned to a user or group for a particular database. This can be done explicitly using the GRANT command.

The following sections describe how each authority is assigned and what commands users with that authority are allowed to perform. Note that any reference to group membership implies that the user and group names have already been defined at the operating system level.

Users can determine what authorities and database-level privileges they have by issuing the following command:

db2 get authorizations


Obtaining SYSADM authority

SYSADM authority in DB2 is comparable to root authority on UNIX or Administrator authority on Windows. Users with SYSADM authority for a DB2 instance are able to issue any DB2 commands against that instance, any databases within the instance, and any objects within those databases. They also have the ability to access data within the databases and grant or revoke privileges and authorities. SYSADM users are the only users allowed to update the DBM CFG file.

SYSADM authority is controlled in the DBM CFG file via the SYSADM_GROUP parameter. When the instance is created, this parameter is set to Administrator on Windows (although it appears blank if you issue the command db2 get dbm cfg ). On UNIX, it is set to the primary group of the user who created the instance.

Since SYSADM users are the only users allowed to update the DBM CFG, they are also the only ones allowed to grant any of the SYS* authorities to other groups. The following example illustrates how to grant SYSADM authority to the group db2grp1:

 db2 update dbm cfg using SYSADM_GROUP db2grp1

Remember, this change will not take effect until the instance is stopped and then restarted. Also, keep in mind that if you are not currently logged in as a member of db2grp1, you may not have authority to restart the instance! You would have to log out and log back in with an ID in the correct group, or add your current ID to db2grp1.


Obtaining SYSCTRL authority

Users with SYSCTRL authority can perform all administrative and maintenance commands within the instance. However, unlike SYSADM users, they cannot access any data within the databases unless they are granted the privileges required to do so. Examples of commands a SYSCTRL user can perform against any database in the instance are:

  • db2start/db2stop
  • db2 create/drop database
  • db2 create/drop tablespace
  • db2 backup/restore/rollforward database
  • db2 runstats (against any table)
  • db2 update db cfg for database dbname

A user with SYSADM authority can assign SYSCTRL to a group using the following command:

db2 update dbm cfg using SYSCTRL_GROUP group name
                


Obtaining SYSMAINT authority

The commands that a user with SYSMAINT authority can issue are a subset of those allowed to users with SYSCTRL authority. SYSMAINT users can only perform tasks related to maintenance, such as:

  • db2start/db2stop
  • db2 backup/restore/rollforward database
  • db2 runstats (against any table)
  • db2 update db cfg for database dbname

Notice that users with SYSMAINT cannot create or drop databases or tablespaces. They also cannot access any data within the databases unless they are granted the explicit privileges required to do so.

If you have SYSADM authority, you can assign SYSMAINT authority to a group using the following command:

db2 update dbm cfg using SYSMAINT_GROUP group name
                


Obtaining SYSMON authority

SYSMON authority provides the ability to take database system monitor snapshots of a database manager instance or its databases. SYSMON authority is assigned to the group specified by the sysmon_group configuration parameter. If a group is specified, membership in that group is controlled outside the database manager through the security facility used on your platform.

SYSMON authority enables the user to run the following commands:

  • GET DATABASE MANAGER MONITOR SWITCHES
  • GET MONITOR SWITCHES
  • GET SNAPSHOT
  • LIST ACTIVE DATABASES
  • LIST APPLICATIONS
  • LIST DCS APPLICATIONS
  • RESET MONITOR
  • UPDATE MONITOR SWITCHES

SYSMON authority enables the user to use the following APIs:

  • db2GetSnapshot - Get snapshot
  • db2GetSnapshotSize - Estimate size required for db2GetSnapshot() output buffer
  • db2MonitorSwitches - Get/update monitor switches
  • db2ResetMonitor - Reset monitor

SYSMON authority enables the user to use all snapshot SQL table functions without previously running SYSPROC.SNAP_WRITE_FILE. SYSPROC.SNAP_WRITE_FILE takes a snapshot and saves its content into a file. If any snapshot table functions are called with null input parameters, the file content is returned, instead of a real-time system snapshot.

Users with the SYSADM, SYSCTRL, or SYSMAINT authority level also possess SYSMON authority.

A user with SYSADM authority can assign SYSMON to a group using the following command:

db2 update dbm cfg using SYSMON_GROUP group name


Obtaining DBADM authority

DBADM authority is a database-level authority rather than an instance-level authority. In summary, DBADM users have complete control over a database -- almost. DBADM users cannot perform such maintenance or administrative tasks as:

  • drop database
  • drop/create tablespace
  • backup/restore database
  • update db cfg for database db name

However, they can perform the following tasks:

  • db2 create/drop table
  • db2 grant/revoke (any privilege)
  • db2 runstats (any table)

DBADM users are also automatically granted all privileges to the database objects and their contents. Since DBADM authority is a database-level authority, it can be assigned to both users and groups. The following commands illustrate different ways in which you can give DBADM authority.

  • db2 create database test

    This command gives implicit DBADM authority on the database named test to the user who issued the command.

  • db2 connect to sample
    db2 grant dbadm on database to user tst1

    This command can only be issued by SYSADM users; it issues DBADM authority to the user tst1 on the sample database. Note that the issuing user must be connected to the sample database before granting DBADM authority.

  • db2 grant dbadm on database to group db2grp1

    This command grants DBADM authority to everyone in the group db2grp1. Again, only SYSADM users can issue this command.


Obtaining LOAD authority

LOAD authority is also considered a database-level authority, and can therefore be granted to both users and groups. As the name implies, LOAD authority allows users to issue the LOAD command against a table. The LOAD command is typically used as a faster alternative to insert or import commands when populating a table with large amounts of data. Depending on the type of LOAD you wish to perform, having LOAD authority alone may not be sufficient. Specific privileges on the table may also be required.

The following commands can be run by users with LOAD authority:

  • db2 quiesce tablespaces for table
  • db2 list tablespaces
  • db2 runstats (any table)
  • db2 load insert (must have insert privilege on table)
  • db2 load restart/terminate after load insert (must have insert privilege on table)
  • db2 load replace (must have insert and delete privilege on table)
  • db2 load restart/terminate after load replace (must have insert and delete privilege on table)

Only users with either SYSADM or DBADM authority are permitted to grant or revoke LOAD authority to users or groups. The following examples illustrate how LOAD authority can allow our user to LOAD data into a table called sales. Assume that the command db2 connect to sample has already been issued.

  • db2 grant load on database to user tst1
    db2 grant insert on table sales to user tst1

    With LOAD authority and insert privilege, tst1 could issue a LOAD INSERT or a LOAD RESTART, or TERMINATE after a LOAD INSERT against the sales table.

  • db2 grant load on database to group grp1
    db2 grant delete on table sales to group grp1
    db2 grant insert on table sales to group grp1

    With LOAD authority, as well as delete and insert privileges, any member of grp1 could issue a LOAD REPLACE or a LOAD RESTART, or TERMINATE after a LOAD REPLACE against the sales table.


Obtaining SECADM authority

SECADM authority is considered a database-level authority, but can only be granted to a specific user by a SYSADM user. A user with SECADM can perform the following:

  • Create and drop security label components
  • Create and drop security policies
  • Create and drop security labels
  • Grant and revoke security labels
  • Grant and revoke LBAC rule exemptions
  • Grant and revoke setsessionuser privileges
  • Execute the SQL statement TRANSFER OWNERSHIP on objects that you do not own

No other user can perform these functions, not even the SYSADM, unless SECADM was explicitly granted to that SYSADM user. This is important because these security abilities are very powerful and should only be granted to a user who is defined as a security administrator. See the "Label-based access control" section for more information on this security feature new to DB2 V9.

4 of 8 | Previous | Next

Comments



Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=147859
TutorialTitle=DB2 9 Fundamentals exam 730 prep, Part 2: Security
publish-date=07202006
author1-email=gmilne@ca.ibm.com
author1-email-cc=

Tags

Help
Use the search field to find all types of content in My developerWorks with that tag.

Use the slider bar to see more or fewer tags.

Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).

My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).

Use the search field to find all types of content in My developerWorks with that tag. Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere). My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).