How to prevent distributed user from connecting to DB2

Donna Domovic

How to prevent distributed user from connecting to DB2

Hello,

This is going to sound like a strange question but I'm hoping someone can help.

In RACF, we currently have the DIST profile in the DSNR class set up so that everyone can connect to DB2 from PCs, servers, etc.  We do want to keep it this way because of the large number of users and applications we have.  The ids are required to have passwords and be authorized to the data they're accessing so this handles our security.

The problem is that we have situation where the password for the id used to connect expires or the script is not updated when it changes which results in the id being revoked  This would normally not be a problem but many of our applications don't put in error handling (I know this should be a requirement) which means the application continues to run and we et 1000's of DSNL030I messages (usually every few seconds) which flood the console and the DB2 MSTR started task.

The question I have is, is there a way prevent certain ids from connecting while allowing all other ids to connect?

We are trying to work with the applications to get the required error checking added but I have very little faith that that will actually happen.

Hopefully this question makes sense.  If you have any questions, please let me know.

Thanks,

Donna Domovic

Tony Saul

How to prevent distributed user from connecting to DB2
(in response to Donna Domovic)
Donna,I haven't tried this, but could you use the DSNRLMT01 resource limit table?
If the DSNRLMT01 could be used, maybe you could have automation that will update the DSNRLMT01 table based on receiving the DSNL030I error. Then when the particular server has the password updated, the entry in DSNRLMT01 can be deleted.
Regards, Tony

On Thursday, 19 April 2018, 11:56:41 am ACST, Donna Domovic <[login to unmask email]> wrote:


Hello,

This is going to sound like a strange question but I'm hoping someone can help.

In RACF, we currently have the DIST profile in the DSNR class set up so that everyone can connect to DB2 from PCs, servers, etc.  We do want to keep it this way because of the large number of users and applications we have.  The ids are required to have passwords and be authorized to the data they're accessing so this handles our security.

The problem is that we have situation where the password for the id used to connect expires or the script is not updated when it changes which results in the id being revoked  This would normally not be a problem but many of our applications don't put in error handling (I know this should be a requirement) which means the application continues to run and we et 1000's of DSNL030I messages (usually every few seconds) which flood the console and the DB2 MSTR started task.

The question I have is, is there a way prevent certain ids from connecting while allowing all other ids to connect?

We are trying to work with the applications to get the required error checking added but I have very little faith that that will actually happen.

Hopefully this question makes sense.  If you have any questions, please let me know.

Thanks,

Donna Domovic

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** 2018 IDUG Tech Conference in Philadelphia, Pennsylvania, USA ** ** **
---> Philadelphia, Pennsylvania, April 29 - May 03, 2018 <---
http://www.idug.org/na2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Tony Saul

How to prevent distributed user from connecting to DB2
(in response to Donna Domovic)
Donna,I haven't tried this, but could you use the DSNRLMT01 resource limit table?
If the DSNRLMT01 could be used, maybe you could have automation that will update the DSNRLMT01 table based on receiving the DSNL030I error. Then when the particular server has the password updated, the entry in DSNRLMT01 can be deleted.
Regards, Tony

On Thursday, 19 April 2018, 11:56:41 am ACST, Donna Domovic <[login to unmask email]> wrote:


Hello,

This is going to sound like a strange question but I'm hoping someone can help.

In RACF, we currently have the DIST profile in the DSNR class set up so that everyone can connect to DB2 from PCs, servers, etc.  We do want to keep it this way because of the large number of users and applications we have.  The ids are required to have passwords and be authorized to the data they're accessing so this handles our security.

The problem is that we have situation where the password for the id used to connect expires or the script is not updated when it changes which results in the id being revoked  This would normally not be a problem but many of our applications don't put in error handling (I know this should be a requirement) which means the application continues to run and we et 1000's of DSNL030I messages (usually every few seconds) which flood the console and the DB2 MSTR started task.

The question I have is, is there a way prevent certain ids from connecting while allowing all other ids to connect?

We are trying to work with the applications to get the required error checking added but I have very little faith that that will actually happen.

Hopefully this question makes sense.  If you have any questions, please let me know.

Thanks,

Donna Domovic

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** 2018 IDUG Tech Conference in Philadelphia, Pennsylvania, USA ** ** **
---> Philadelphia, Pennsylvania, April 29 - May 03, 2018 <---
http://www.idug.org/na2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Daniel Luksetich

How to prevent distributed user from connecting to DB2
(in response to Tony Saul)
This is a connect authorization issue so it’s the very first thing that Db2 is doing, and so anything related to Db2 security or resource limit will not work. I think you would have to modify the authorization exit to reject these users prior to Db2 calling RACF to authenticate them. We did, long age, use a modified authorization exit at your site for SYSADM auth id’s. That was for TSO connections. You will have to modify the exit for DDF connections. Then, I am not sure about this, but you would have to toggle the exit. Maybe someone can confirm if this would actually require a bounce of the subsystems.



Not a lot of work, and you might be able to do it quickly. I’m just not sure about whether you need to bounce Db2. Of course you’d have to toggle back to the default routine when they fix the authid in question.



Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Tony Saul <[login to unmask email]>
Sent: Wednesday, April 18, 2018 10:37 PM
To: [login to unmask email]; Donna Domovic <[login to unmask email]>
Subject: [DB2-L] - RE: How to prevent distributed user from connecting to DB2



Donna,

I haven't tried this, but could you use the DSNRLMT01 resource limit table?



If the DSNRLMT01 could be used, maybe you could have automation that will update the DSNRLMT01 table based on receiving the DSNL030I error. Then when the particular server has the password updated, the entry in DSNRLMT01 can be deleted.



Regards, Tony





On Thursday, 19 April 2018, 11:56:41 am ACST, Donna Domovic <[login to unmask email] <mailto:[login to unmask email]> > wrote:





Hello,

This is going to sound like a strange question but I'm hoping someone can help.

In RACF, we currently have the DIST profile in the DSNR class set up so that everyone can connect to DB2 from PCs, servers, etc. We do want to keep it this way because of the large number of users and applications we have. The ids are required to have passwords and be authorized to the data they're accessing so this handles our security.

The problem is that we have situation where the password for the id used to connect expires or the script is not updated when it changes which results in the id being revoked This would normally not be a problem but many of our applications don't put in error handling (I know this should be a requirement) which means the application continues to run and we et 1000's of DSNL030I messages (usually every few seconds) which flood the console and the DB2 MSTR started task.

The question I have is, is there a way prevent certain ids from connecting while allowing all other ids to connect?

We are trying to work with the applications to get the required error checking added but I have very little faith that that will actually happen.

Hopefully this question makes sense. If you have any questions, please let me know.

Thanks,

Donna Domovic



-----End Original Message-----



-----End Original Message-----

Donna Domovic

RE: How to prevent distributed user from connecting to DB2
(in response to Daniel Luksetich)

I think any change to the exit would require DB2 to be bounced.  Since there are multiple ids that use DB2 and only 1 may be causing the issue I don't think we want to go the exit route.  I was hoping we could do something with the DSNR DIST RACF profile.

Since we have UACC currently set to READ, do you think adding a specific entry for ids having this issue with an access of NONE would prevent the id from connecting?

I'm honestly not even sure we want to go this route since I think numerous RACF failure messages would still be issued but looking at options.

Thanks,

Donna Domovic

Philip Sevetson

How to prevent distributed user from connecting to DB2
(in response to Donna Domovic)
Donna,

If you have a console monitor process (and it sends email or SMS), you could set it to notify the application owner when you get a lockout. And repeat for every time a message appears on the console. That would rapidly lead to design changes and a more durable solution.

If you _don’t_ have console automation, you might want to politely suggest to management that there is a large class of problems which are better solved by automation than by relying on eyeballs. I know that won’t necessarily sell, but raising awareness is probably to the benefit of your managers and the bottom line (or, at least to-the-benefit-of the service level agreements and customer satisfaction). A lot of console notifications should be pushed to places other than the operator.

--Phil


From: Donna Domovic [mailto:[login to unmask email]
Sent: Thursday, April 19, 2018 11:09 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: How to prevent distributed user from connecting to DB2


I think any change to the exit would require DB2 to be bounced. Since there are multiple ids that use DB2 and only 1 may be causing the issue I don't think we want to go the exit route. I was hoping we could do something with the DSNR DIST RACF profile.

Since we have UACC currently set to READ, do you think adding a specific entry for ids having this issue with an access of NONE would prevent the id from connecting?

I'm honestly not even sure we want to go this route since I think numerous RACF failure messages would still be issued but looking at options.

Thanks,

Donna Domovic

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Jan Moeyersons

RE: How to prevent distributed user from connecting to DB2
(in response to Donna Domovic)

Consider yourself lucky... We see some applications retry at a rate of hundreds of times a second :-(

Jantje.

In Reply to Donna Domovic:

1000's of DSNL030I messages (usually every few seconds) which flood the console and the DB2 MSTR started task.

James Campbell

How to prevent distributed user from connecting to DB2
(in response to Donna Domovic)
I think, to disable a naughty userid from accessing a resource, you'll need
RALTER ... UACC(NONE) ,
PE ... ID(*) ACCESS(READ),
PE .. ID(naughty) ACCESS(NONE)

but I suspect this is checked after password checking. That is, the ACEE is built (which
includes groups), and then access to the DSNR ssid.DIST resource is checked (which might
require use of those groups) and then the Db2 authorisation exit is called (which is handed
the address of the ACEE to build the list of secondary auth-ids).

The only way I know of to get rid of unwanted messages is the Message Processing Facility
https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.1.0/com.ibm.zos.v2r1.ieag300
/mcmpf.htm .

James Campbell


On 19 Apr 2018 at 8:08, Donna Domovic wrote:

>
> I think any change to the exit would require DB2 to be bounced.  Since there are multiple ids that
> use DB2 and only 1 may be causing the issue I don't think we want to go the exit route.  I was
> hoping we could do something with the DSNR DIST RACF profile.
> Since we have UACC currently set to READ, do you think adding a specific entry for ids having
> this issue with an access of NONE would prevent the id from connecting?
> I'm honestly not even sure we want to go this route since I think numerous RACF failure
> messages would still be issued but looking at options.
> Thanks,
> Donna Domovic
>

Lizette Koehler

How to prevent distributed user from connecting to DB2
(in response to James Campbell)
So I had a bad user like this. Had a automated script to logon thru DDF and had
a revoked password.

The biggest impact was to my RACF Admins, there were 100s of thousands of RACF
violation records for the one user. This was over many days, but still, this is
the impact of this type of issue.

Not to DB2 so much, but RACF and SMF Collections.

Lizette


> -----Original Message-----
> From: James Campbell <[login to unmask email]>
> Sent: Saturday, April 21, 2018 10:00 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: How to prevent distributed user from connecting to DB2
>
> I think, to disable a naughty userid from accessing a resource, you'll need
> RALTER ... UACC(NONE) , PE ... ID(*) ACCESS(READ), PE .. ID(naughty)
> ACCESS(NONE)
>
> but I suspect this is checked after password checking. That is, the ACEE is
> built (which includes groups), and then access to the DSNR ssid.DIST resource
> is checked (which might require use of those groups) and then the Db2
> authorisation exit is called (which is handed the address of the ACEE to
> build the list of secondary auth-ids).
>
> The only way I know of to get rid of unwanted messages is the Message
> Processing Facility
> https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.1.0/com.ibm.zos.v2r1.
> ieag300
> /mcmpf.htm .
>
> James Campbell
>
>
> On 19 Apr 2018 at 8:08, Donna Domovic wrote:
>
> >
> > I think any change to the exit would require DB2 to be bounced.  Since
> > there are multiple ids that use DB2 and only 1 may be causing the
> > issue I don't think we want to go the exit route.  I was hoping we could do
> something with the DSNR DIST RACF profile.
> > Since we have UACC currently set to READ, do you think adding a
> > specific entry for ids having this issue with an access of NONE would
> prevent the id from connecting?
> > I'm honestly not even sure we want to go this route since I think
> > numerous RACF failure messages would still be issued but looking at
> options.
> > Thanks,
> > Donna Domovic
> >
>
> -----End Original Message-----

J&#248;rn Thyssen

RE: How to prevent distributed user from connecting to DB2
(in response to Donna Domovic)

Hi Donna,

While I like the idea of sending emails to the application people for every failed logon until they fix the problem, a better solution could be to ask your z/OS TCPIP people to block the application's IP address(es).

In Reply to Donna Domovic:

Hello,

This is going to sound like a strange question but I'm hoping someone can help.

In RACF, we currently have the DIST profile in the DSNR class set up so that everyone can connect to DB2 from PCs, servers, etc.  We do want to keep it this way because of the large number of users and applications we have.  The ids are required to have passwords and be authorized to the data they're accessing so this handles our security.

The problem is that we have situation where the password for the id used to connect expires or the script is not updated when it changes which results in the id being revoked  This would normally not be a problem but many of our applications don't put in error handling (I know this should be a requirement) which means the application continues to run and we et 1000's of DSNL030I messages (usually every few seconds) which flood the console and the DB2 MSTR started task.

The question I have is, is there a way prevent certain ids from connecting while allowing all other ids to connect?

We are trying to work with the applications to get the required error checking added but I have very little faith that that will actually happen.

Hopefully this question makes sense.  If you have any questions, please let me know.

Thanks,

Donna Domovic



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

Javier Estrada Benavides

RE: How to prevent distributed user from connecting to DB2
(in response to Jørn Thyssen)

Hi there:

   Ok, I think it's a little too late now to respond, but anyway..

I believe working with RLF as some suggested would not work at all since RLF works only after the user has logged on and there's a specific entry on the tables for such IP, so you would still get all those messages.

Denying access to DSNR <ssid>.DIST wouldn't work either because that would block the entire authid including those who log in with correct userid and password, so that's not an option either, but you might want to look into SERVAUTH profiles for such IP addresses provided that you identify which are the misbehaving IPs. The downside is that your RACF admin would have to work quite a bit every time this situation happens.

It also depends on your organization, I think it's customary that apps on web containers use authids with no expiration interval for their passwords and are also IP controlled by either SERVAUTH or some other TCP/IP firewall with an alarm when a different IP tries to use your app authids, and on the other side, testers or app departments use other authids with their own identified sets of IPs, so that if you see a ICH408I message associated with the app user it will always be someone trying to steal the app user.

Hope that helps a bit

 

Regards,

Javier Estrada Benavides, Mexico

IBM Champion

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Database Administrator - DB2 11 DBA for z/OS