![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
The security administrator has a requirement to allow an end user to connect to the database only from a specific set of IP addresses. This article shows how database roles and trusted contexts can be used to address this. For illustration purposes, we will assume that the security administrator would like to ensure that user Einstein can connect to the database only from IP address 9.26.120.62.
A role is a database object that groups together one or more privileges and can be assigned to users, groups, special group PUBLIC, other roles, or a trusted context. Roles simplify the administration and management of privileges by allowing the security administrator to group authorities and privileges in a role and grant this role to the users that need those authorities and privileges to perform their jobs. Also because roles reside within the DB2 database system, they are not subject to the same restrictions as groups.
All DB2 privileges and authorities that can be granted within a database can be granted to a role. For example, a role can be granted CONNECT authority and SELECT privilege on some table.
A trusted context is a database object that defines a trust relationship for a connection between the database and an external entity, such as an application server.
The trust relationship is based upon the following set of attributes:
- System authorization ID — Represents the user that establishes a database connection
- IP address (or domain name) — Represents the host from which a database connection is established
- Data stream encryption — Represents the encryption setting (if any) for the data communication between the database server and the database client
When a user establishes a database connection, the DB2 database system checks whether the connection matches the definition of a trusted context object in the database. When a match occurs, the database connection is said to be trusted.
One capability trusted contexts provide is the ability for the user of that trusted context to inherit a database role. For example, a security administrator could choose to grant SELECT privilege on the payroll table to a role and make that role available only through a trusted context. That is to say that users will not be able to take advantage of the role (and, consequently, the SELECT privilege on the payroll table) when operating outside the scope of that trusted context.
Prior to DB2 9.7 FixPak 3, roles inherited through trusted contexts were not taken into account when checking for CONNECT authority at database connection time. This restriction is being removed in DB2 9.7 FP3. One immediate application of this enhancement is the ability to restrict where an end user might connect to the database from.
We have regular user Einstein that we want to allow to connect to the T1 database on 9.26.120.64 (hotel48) only from IP 9.26.120.62 (hotel46).
Step 1: Create the T1 database on 9.26.120.64 (hotel48)
db2start db2 create database T1 |
Verify that the database is created: db2 list db
directory.
Figure 1. System database directory
Step 2: Configure the TCPIP communication protocol
On server side 9.26.120.64 hotel48, run the following:
Listing 1. Configuring communication and configuration for TCPIP
db2set DB2COMM=TCPIP db2 update dbm cfg using SVCENAME xziskind db2stop db2start |
On client side 9.26.120.62 hotel46, run:
Listing 2. Cataloging the TCPIP node and database
db2 catalog tcpip node NT1 remote hotel48 server xziskind db2 terminate db2 catalog database T1 at node NT1 db2 terminate |
Verify that user Einstein can connect to the T1 database.
Figure 2. From 9.26.120.64 hotel48
Figure 3. From 9.26.120.62 hotel46
User Einstein can connect to the T1 database from both IP addresses.
Let's verify Einstein's authorities. For this purpose, we will make use of
the AUTH_LIST_AUTHORITIES_FOR_AUTHID table
function. This returns all authorities held by the authorization ID, found in the
database configuration file or granted to an authorization ID directly or
indirectly through a group or a role.
Listing 3. Authorities held by Einstein
db2 SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC,
ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE
FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('EINSTEIN', 'U') ) AS T
ORDER BY AUTHORITY
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL N N N N N N *
BINDADD N N Y N N N *
CONNECT N N Y N N N *
CREATETAB N N Y N N N *
CREATE_EXTERNAL_ROUTINE N N N N N N *
CREATE_NOT_FENCED_ROUTINE N N N N N N *
DATAACCESS N N N N N N *
DBADM N N N N N N *
EXPLAIN N N N N N N *
IMPLICIT_SCHEMA N N Y N N N *
LOAD N N N N N N *
QUIESCE_CONNECT N N N N N N *
SECADM N N N N N N *
SQLADM N N N N N N *
SYSADM * N * * * * *
SYSCTRL * N * * * * *
SYSMAINT * N * * * * *
SYSMON * N * * * * *
WLMADM N N N N N N *
19 record(s) selected. |
User Einstein has CONNECT authority through the special group PUBLIC.
Step 3: Revoke CONNECT authority from PUBLIC
db2 revoke connect on database from public |
Verify Einstein's authorities again:
Listing 4. Einstein's authorities after revoking CONNECT from PUBLIC
db2 SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC,
ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE
FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('EINSTEIN', 'U') ) AS T
ORDER BY AUTHORITY
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL N N N N N N *
BINDADD N N Y N N N *
CONNECT N N N N N N *
CREATETAB N N Y N N N *
CREATE_EXTERNAL_ROUTINE N N N N N N *
CREATE_NOT_FENCED_ROUTINE N N N N N N *
DATAACCESS N N N N N N *
DBADM N N N N N N *
EXPLAIN N N N N N N *
IMPLICIT_SCHEMA N N Y N N N *
LOAD N N N N N N *
QUIESCE_CONNECT N N N N N N *
SECADM N N N N N N *
SQLADM N N N N N N *
SYSADM * N * * * * *
SYSCTRL * N * * * * *
SYSMAINT * N * * * * *
SYSMON * N * * * * *
WLMADM N N N N N N *
19 record(s) selected. |
User Einstein no longer has CONNECT authority from any source.
Now try to connect again.
Figure 4. From 9.26.120.64 hotel48
Figure 5. From 9.26.120.62 hotel46
User Einstein can no longer connect from any IP address.
Step 4: Create the role and the trusted context
The following commands are run as a user with SECADM authority:
- Create the role:
db2 create role connect_role - Grant CONNECT authority to the role:
db2 grant CONNECT on database to role connect_role - Create the trusted context:
db2 create trusted context connect_tc based upon connection using system authid einstein attributes(address '9.26.120.62') default role connect_role enable
Figure 6. Creating the role, connecting, and creating trusted context
Verify that user Einstein can connect from IP 9.26.120.62 (hotel46) but cannot from IP 9.26.120.64 (hotel48).
Figure 7. From 9.26.120.62 hotel46
Figure 8. From 9.26.120.64 hotel48
By combining the capabilities of trusted contexts, roles, and CONNECT authority, we can control where a regular user connects to the database from. Note that this solution does not apply to superusers SYSADM, SYSCTRL, SYSMAINT, SYSMON, DBADM, and SECADM. Those users have implicit CONNECT authority.
Learn
- Learn more about database roles in DB2 for Linux, UNIX, and Windows.
- Get more information about Trusted contexts and trusted connections in DB2.
- Get the details about the AUTH_LIST_AUTHORITIES_FOR_AUTHID table function.
- Get an Authorities overview for DB2 for Linux, UNIX, and Windows.
- Learn more about Information Management at the developerWorks Information Management
zone. Find technical documentation,
how-to articles, education, downloads, product information, and
more.
- Stay current with
developerWorks technical events and webcasts.
- Follow developerWorks on
Twitter.
Get products and technologies
- Now you can use
DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the
community that offers the same core data features as DB2 Express Edition
and provides a solid base to build and deploy applications.
- Build your next
development project with
IBM trial software,
available for download directly from developerWorks.
- Now you can use
DB2 for free. Download DB2 Express-C, a no-charge
version of DB2 Express Edition for the community that offers the same core
data features as DB2 Express Edition and provides a solid base to build
and deploy applications.
Discuss
- Participate in the discussion forum.
- Check out the
developerWorks
blogs and get involved in the
developerWorks community.
Mihai Iacob has been working as a software developer at the IBM Toronto Lab for the past five years as part of the DB2 for Linux, UNIX, and Windows Security Development team.
Igal Ziskind has been working as a software developer at the IBM Toronto Lab for the past seven years. He is part of the DB2 for Linux, UNIX, and Windows Security Development team. Prior to his current position, he was part of the DB2 Up and Running Development team.
Walid Rjaibi is an IBM Senior Technical Staff Member and member of IBM's Security Architecture Board Steering Committee. He is the Chief Security Architect for DB2 for Linux, UNIX, and Windows (LUW) and has direct technical and management oversight over the architecture, design, development, and common criteria certification of all security capabilities in DB2 LUW. This includes identification and authentication, authorization, access control, data masking, auditing, and encryption.
Prior to his current role, Walid was a Research Staff Member at the IBM Zurich Research Lab in Switzerland where he established and led a new research program focused on database security and privacy. Walid's research results were the foundation for key security enhancements in DB2 LUW and for which he led the actual development efforts upon his return to DB2 LUW development in Toronto.
Walid's key database security achievements include leading the research and development of label-based access control, role-based access control, trusted contexts, separation of duties, and fine-grained access control. His work resulted in 20 patents and several publications in the proceedings of leading scientific conferences, such as the International Conference on Very Large Databases (VLDB), the International Conference on Data Engineering (ICDE), and the International Conference on Security and Cryptography (SECRYPT).
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
