DB2-RACF package security problem

Ivan Losada

DB2-RACF package security problem
Hello world,
We have a problem with RACF and DB2. In our DB2 we have modified exit
[login to unmask email] to check object security in RACF instead in DB2. We have permited
in RACF to use the QMF plan to CONTROL-M user.

When we run in our test environment a QMF Proc in batch with the CONTROL-M
user its run correctly. We trace the RACF calls that it does and it verifys
the following resources:
CLASS(MDSNPN) RES(DB21.DB210QM72.EXECUTE) The QMF plan.
CLASS(MDSNTB) RES(-----) All the tables that the QMF PROC uses.

But when we run this QMF proc in our production environment, and only
sometimes, it fails with a ICH408I telling us that the CONTROL-M user has
not privilege over the CLASS(MDSNPK) RES(DB22.Q.DSQDRDB2.EXECUTE) that is
one of the QMF packages. The message is quite confusing, because the racf
message says that is the CONTROL-M user the one who hasnt execute privilege
over this package, but the QMF, in DSQBEDUG says that is the binder the one
who hasnt privilege to execute the package (the binder is defined as SYSADM)

I think that the normal DB2 behaviour is that the binder must have execute
privilege, but the CONTROL-M user(the user who executes the plan) dont need
to have that privilege (only execute plan privilege)

Why only sometimes it verify the privilege to execute a package when in
normal situations you only need to have the privilege to execute the plan?

Thanks

Regards, Ivan

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Rob --- Sr. Database Administrator --- CFS Crane

Re: DB2-RACF package security problem
(in response to Ivan Losada)
The execute privilege on a package allows the authid with that privilege to include the package in the PKLIST of the plan bind, etc. I believe the only time DB2 checks the execute privilege at runtime on a package is for packages executed outside a plan (triggers, stored procedures, drda connections that can access a package without a plan).

Have you been able to see what they are doing in that QMF package when you get the error?

-Rob
FedEx Freight
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Ivan Losada
Sent: Tuesday, December 07, 2004 3:15 AM
To: [login to unmask email]
Subject: DB2-RACF package security problem

Hello world,
We have a problem with RACF and DB2. In our DB2 we have modified exit
[login to unmask email] to check object security in RACF instead in DB2. We have permited
in RACF to use the QMF plan to CONTROL-M user.

When we run in our test environment a QMF Proc in batch with the CONTROL-M
user its run correctly. We trace the RACF calls that it does and it verifys
the following resources:
CLASS(MDSNPN) RES(DB21.DB210QM72.EXECUTE) The QMF plan.
CLASS(MDSNTB) RES(-----) All the tables that the QMF PROC uses.

But when we run this QMF proc in our production environment, and only
sometimes, it fails with a ICH408I telling us that the CONTROL-M user has
not privilege over the CLASS(MDSNPK) RES(DB22.Q.DSQDRDB2.EXECUTE) that is
one of the QMF packages. The message is quite confusing, because the racf
message says that is the CONTROL-M user the one who hasnt execute privilege
over this package, but the QMF, in DSQBEDUG says that is the binder the one
who hasnt privilege to execute the package (the binder is defined as SYSADM)

I think that the normal DB2 behaviour is that the binder must have execute
privilege, but the CONTROL-M user(the user who executes the plan) dont need
to have that privilege (only execute plan privilege)

Why only sometimes it verify the privilege to execute a package when in
normal situations you only need to have the privilege to execute the plan?

Thanks

Regards, Ivan

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




*******************************************************
This message contains information that is confidential
and proprietary to FedEx Freight or its affiliates.
It is intended only for the recipient named and for
the express purpose(s) described therein.
Any other use is prohibited.
*******************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ivan Losada

Re: DB2-RACF package security problem
(in response to Rob --- Sr. Database Administrator --- CFS Crane)
The QMF batch runs a QMF proc that executes queries to extract data from
the catalog. It seems to do anything because the DD DSQDEBUG (QMF trace) is
empty. The job calls QMF plan, so i think it doesn´t all any package
directly.

Thanks,
Ivan

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Pedro Luz Cunha

Re: DB2-RACF package security problem
(in response to Ivan Losada)
Ivan,

I assume your are using the sample RACF-DB2 exit. IF yes, what is its service level? I know there were some issues with releases below 'SERVICELEVEL 'OW57299' Release/APAR number'.

[]s
+-------------------------------------------------------+
Pedro Luz Cunha
http://www.pedroluz.com
Alamy at http://tinyurl.com/4t5wu
+-------------------------------------------------------+



----- Original Message -----
From: Ivan Losada
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Thursday, December 09, 2004 1:33 PM
Subject: Re: DB2-RACF package security problem


The QMF batch runs a QMF proc that executes queries to extract data from
the catalog. It seems to do anything because the DD DSQDEBUG (QMF trace) is
empty. The job calls QMF plan, so i think it doesn´t all any package
directly.

Thanks,
Ivan

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ivan Losada

Re: DB2-RACF package security problem
(in response to Pedro Luz Cunha)
Hi,
we are using the RACF exit with servicelevel OW57299, thanks anyway.

In the last week we couldnt reproduce the problem, because it works fine
all the times. The problem is that it only fails sometimes, executing the
same job and we cant find what is the thing that change from one execution
to each other.

We are going to trace [login to unmask email] calls (ifcid 314) and see is there are
something wrong in this. We will tell you about this.

Thanks
regards, ivan

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Davies

Db2 Security
(in response to Ivan Losada)
Dear List: I have a problem that you might be able to help me with. We have a system that is written in Visual Basic that hits DB2 V7 on the mainframe. Against my recommendation it was written with dynamic calls to the database instead of using stored procedures. As a result I have to grant access to the specific tables instead of access to a plan. Since we have other systems that use DB2 and CICS people also have either DB2 Connect or the Merant driver to access the tables and download them to other databases on their PC's. Since I am also the RACF administrator besides being the DBA I have been tasked with coming up with a solution besides rewriting the application. What I am looking for is a way to insure that these tables are only updated through the application and not through QMF or an OBDC driver. We use DB2 native security and not DB2 RACF security. Does anyone have any suggestions as to what another solution might be.



Thanks in advance
Walter Davies
El Dorado County

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Vaughan

Re: Db2 Security
(in response to Walter Davies)
One option would be to have the application use an access-id. In other words, a user logs into the application, but once they are authenticated, the application controls the connection to the database using a separate authid. In this case the table access is under the application access-id, and the end user does not need to have authority. This is what I most often see apps in this situation.

This is not without it's drawbacks. For one, the access-id and password needs to be stored/accessed somehow within the application in a secured fashion. You also lose some monitoring/auditing ability on the back end since all of the threads come in to the database under the same authid.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Walter Davies
Sent: Friday, December 10, 2004 10:19 AM
To: [login to unmask email]
Subject: Db2 Security


Dear List: I have a problem that you might be able to help me with. We have a system that is written in Visual Basic that hits DB2 V7 on the mainframe. Against my recommendation it was written with dynamic calls to the database instead of using stored procedures. As a result I have to grant access to the specific tables instead of access to a plan. Since we have other systems that use DB2 and CICS people also have either DB2 Connect or the Merant driver to access the tables and download them to other databases on their PC's. Since I am also the RACF administrator besides being the DBA I have been tasked with coming up with a solution besides rewriting the application. What I am looking for is a way to insure that these tables are only updated through the application and not through QMF or an OBDC driver. We use DB2 native security and not DB2 RACF security. Does anyone have any suggestions as to what another solution might be.



Thanks in advance
Walter Davies
El Dorado County

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Roger Miller

Re: DB2 Security
(in response to Mike Vaughan)
Some other typical options to provide more solid authorization would be to
use static SQL and its authorization model, to use DYNAMICRULES(BIND), or
to separate table access into a stored procedure (using static SQL or
DYNAMICRULES(BIND).

Roger Miller

Date: Fri, 10 Dec 2004 16:50:12 -0600
Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]
L.ORG>
Sender: DB2 Data Base Discussion List <[login to unmask email]>
From: "Vaughan, Mike" <[login to unmask email]>
Subject: Re: Db2 Security
Content-Type: multipart/mixed;

One option would be to have the application use an access-id. In other
words, a user logs into the application, but once they are authenticated,
the application controls the connection to the database using a separate
authid. In this case the table access is under the application access-id,
and the end user does not need to have authority. This is what I most
often see apps in this situation.

This is not without it's drawbacks. For one, the access-id and
password needs to be stored/accessed somehow within the application in a
secured fashion. You also lose some monitoring/auditing ability on the
back end since all of the threads come in to the database under the same
authid.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Walter Davies
Sent: Friday, December 10, 2004 10:19 AM
To: [login to unmask email]
Subject: Db2 Security


Dear List: I have a problem that you might be able to help me with. We
have a system that is written in Visual Basic that hits DB2 V7 on the
mainframe. Against my recommendation it was written with dynamic calls to
the database instead of using stored procedures. As a result I have to
grant access to the specific tables instead of access to a plan. Since we
have other systems that use DB2 and CICS people also have either DB2
Connect or the Merant driver to access the tables and download them to
other databases on their PC’s. Since I am also the RACF administrator
besides being the DBA I have been tasked with coming up with a solution
besides rewriting the application. What I am looking for is a way to
insure that these tables are only updated through the application and not
through QMF or an OBDC driver. We use DB2 native security and not DB2 RACF
security. Does anyone have any suggestions as to what another solution
might be.


Thanks in advance
Walter Davies
El Dorado County

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: DB2 Security
(in response to Roger Miller)
Walter,

The excellent suggestions Mike & Roger have made are summarized in table
13-1 of the redbook SG24-6418. Depending on whether or not free-form SQL is
allowed and use of dynamicrules we provide the security implications and
recommendations.

The static SQL model is clearly the best. Dynamicrule(bind) in an ODBC/JDBC
environment sometimes creates more issues than it solves.

Bottom line is this:

Generic interfaces like ODBC/JDBC using free-form SQL in a 2-tier
environment: there is no 100% secure implementation.

One additional option (may not be possible for you) is to use an app server
(3-tier). Alternatively, you could bind different ODBC packages (one per
application) with just the right authority. This assume, for example, that
anyone authorized for payroll can do all of payroll etc.

Thanks,
Suresh

>From: Roger Miller <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: DB2 Security
>Date: Tue, 14 Dec 2004 19:05:56 -0600
>
>Some other typical options to provide more solid authorization would be to
>use static SQL and its authorization model, to use DYNAMICRULES(BIND), or
>to separate table access into a stored procedure (using static SQL or
>DYNAMICRULES(BIND).
>
>Roger Miller
>
>Date: Fri, 10 Dec 2004 16:50:12 -0600
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]
>L.ORG>
>Sender: DB2 Data Base Discussion List <[login to unmask email]>
>From: "Vaughan, Mike" <[login to unmask email]>
>Subject: Re: Db2 Security
>Content-Type: multipart/mixed;
>
> One option would be to have the application use an access-id. In other
>words, a user logs into the application, but once they are authenticated,
>the application controls the connection to the database using a separate
>authid. In this case the table access is under the application access-id,
>and the end user does not need to have authority. This is what I most
>often see apps in this situation.
>
> This is not without it's drawbacks. For one, the access-id and
>password needs to be stored/accessed somehow within the application in a
>secured fashion. You also lose some monitoring/auditing ability on the
>back end since all of the threads come in to the database under the same
>authid.
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
>Behalf Of Walter Davies
>Sent: Friday, December 10, 2004 10:19 AM
>To: [login to unmask email]
>Subject: Db2 Security
>
>
> Dear List: I have a problem that you might be able to help me with. We
>have a system that is written in Visual Basic that hits DB2 V7 on the
>mainframe. Against my recommendation it was written with dynamic calls to
>the database instead of using stored procedures. As a result I have to
>grant access to the specific tables instead of access to a plan. Since we
>have other systems that use DB2 and CICS people also have either DB2
>Connect or the Merant driver to access the tables and download them to
>other databases on their PC’s. Since I am also the RACF administrator
>besides being the DBA I have been tasked with coming up with a solution
>besides rewriting the application. What I am looking for is a way to
>insure that these tables are only updated through the application and not
>through QMF or an OBDC driver. We use DB2 native security and not DB2 RACF
>security. Does anyone have any suggestions as to what another solution
>might be.
>
>
>Thanks in advance
>Walter Davies
>El Dorado County
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm