Db2 User Group | October 2026
Safeguarding Your Data:
A Comprehensive Guide to Db2 for z/OS Security Exit Routines
This session explores Db2 for z/OS Security Exit Routines and their role in strengthening enterprise data protection and access control strategies. Attendees will gain insight into how security exit routines can be used to enhance auditing, authorization, and security governance within Db2 for z/OS environments.
The session will examine practical considerations for implementing and managing security exits as part of broader cybersecurity and compliance initiatives on the mainframe.
Read the Transcription
[00:00:10] – Amanda Hendley
Welcome to today’s Mainframe Virtual User Group. We’re here today talking about Db2, so I’m excited to have you. Before we get started, I want to thank our partner for this session, DataKinetics. You can learn more about their products and services on their website, or I’m happy to provide you that information, but I want to thank them for their partnership today. Thank you, Sarah. So we’re here today for Safeguarding Your Data, a Comprehensive Guide to Db2 for z/OS Security Exit Routines. And in a minute, I’ll tell you why we’re talking so much about security. But for now, I’d love to find out where some of you are from and what you do. If you want to drop that into chat, it’ll also let me know that you can hear me and no one’s having any issues with the system. So we’d love to see where you’re from. I’m in Atlanta, actually, where we’ve been waking up to 43 degrees. Degrees, which is quite cool for Atlanta. Not fun for me, um, because I am steadfastly keeping my thermostat very low right now, just avoiding the heat kick-on. So, um, but I’d love to find out where you’re from as well.
[00:01:20] – Amanda Hendley
Uh, for today, we, uh, are looking for additional sessions. So if you have a desire for a session or want to share something with the Db2 community, Let me know. You can reach me at ahenley@planetmainframe.com. Look at that, Lawrenceville, Georgia. That is so close. I love it. I’m in East Cobb, Marietta, so you’re just like a stone’s throw away. And then we have someone in Belgium, not quite a stone’s throw away. Awesome. Some security expertise coming in, Emil. Great. So today, now that I know you know how to use chat, for any questions you have today during our session, you can drop those into chat. Anil will try to take care of them during the session, but if it’s something that requires a lengthier conversation or answer, or maybe we need to get you off mute, we’ll hold those to the end of our presentation today. Awesome. Got a great audience today. All right. So I teased as to why we’re talking security so much this month or in this session, but that’s because it is Security Month at Planet Mainframe. We’ve been publishing on security topics now for the whole of the month. So I encourage you to go to planetmainframe.com and check out any of our recently published things.
[00:02:49] – Amanda Hendley
Make sure you’re subscribed to our newsletter. And you’ve registered for this session, so you will definitely get updates about Db2. And to a couple of the questions I’m seeing in, yes, we will be sharing the recording and hopefully the presentation, Emil, if you’re willing to share it after the fact. Thank you. So we will get you taken care of. It usually takes us about a week to get that recording turned around, and you’ll get a notice about it. And then we usually also write a recap Article that we publish on Planet Mainframe. The last thing I’m going to plug before we get started is our our use mainframe Arcady Navigator. So this is a research report that we do. We’ll be releasing the research study link in the next week or so. But for now, if you go to our website, you can claim your free directory listing. So any companies out there can get a directory listing in the research report. And then we also have sponsorship opportunities. So you can go to planetmainframe.com and look at the top bar, and there’ll be an Arcady Navigator link. So we’re looking forward to sharing this with you when it is, uh, when it’s ready.
[00:04:08] – Amanda Hendley
We value all of your opinions in our research study. So with that, let me stop my share so that Emil can start his. And Emil is an experienced principal software architect with a demonstrated history of working in the computer software industry. He’s got skills in IBM Db2, agile methodologies, and mainframe, and he’s definitely got the credentials with a PhD in mathematical engineering. And I know Emil has presented at a lot of shows, in particular like the IDUC show. Were you just— did you just present at the one a couple weeks ago?
[00:04:51] – Emil Kotrc
Yeah, this one was presented 2 years ago, I think, at IDAC. So maybe some of you have seen it. So it’s not like a recent one, but it still applies. So
[00:05:01] – Amanda Hendley
yeah, we’re looking forward to it. All right, you can share your screen when ready.
[00:05:05] – Emil Kotrc
Hopefully you can see my full slide deck.
[00:05:12] – Amanda Hendley
There it is.
[00:05:15] – Emil Kotrc
Okay, ready to go?
[00:05:17] – Amanda Hendley
Ready to go. Thanks.
[00:05:18] – Emil Kotrc
Thank you. So hello, good morning, good afternoon, everybody. I’m Emil. I’m with Broadcom. I work as an architect in the Db2 tools development, and I’m based in Prague, Czech Republic. So for me, it’s half past 5, so it’s getting late. So it will be the last call of the day. So I’m excited to be here, and thank you for having me, and thank you for joining. So this presentation will be about Db2 connection sign-on and authorization authentication exits, which I think is sometimes overlooked topic. And I think it’s important to understand the details, what these exits do, what are the, why are they used, when Db2 uses them, and so on. So that’s the purpose of this meeting, of this session. I did it, as I mentioned, I presented this with my colleague, Vid Gotwald, 2 years ago at IDAC. So Vid back then helped me with some parts that involved VS Code high-level assembler plugin, which can help you to understand those plugins. So you will see it later. I will just walk you through that very quickly, and then I will focus on those internals. And if you don’t mind, I will close my camera so that I focus on the text because I don’t enjoy watching myself speaking.
[00:06:44] – Emil Kotrc
So if you don’t mind, I will close that and we’ll focus on those slides. So let me move. So this will be my agenda today. So at the beginning, very quick intro about the Db2 security in a nutshell with the focus on those exits, so where they apply and what they do. Then I will speak about the, like, very briefly about the VS Code high-level assembler extension because that’s one thing that can, that can help you. I mean, this presentation was done pre-AI agentic development, so it’s still like old-school type of presentation. These days you can definitely use AI to help you to navigate through them, but I think all these things still apply. And then the second half or the major part of the presentation will be the security exits deep dive. So it consists of 2 parts. The first will be about the connection Connection and sign-on exits, and the second one will be about access control authorization exit. So let’s start with the Db2 security in a nutshell. And, you know, Db2 security is very complex, and what I like to show is this chart and the link on top because this link goes to the IBM documentation And compared to like PDFs, this is interactive.
[00:08:04] – Emil Kotrc
So you can move your mouse and it will, it will tell you what’s there and what, what, what those individual dots mean and, and do. So that’s why I like this, this chart. So if you go there, move your mouse and, and read about, about, about all the security in Db2 in high level. And what applies to our session here are those red circles that I added. So we will speak about authentication, which means the user identification verification and also assigning the secondary IDs and so on. And that’s what those connection and sign-on exits do. So they are on the top right, connection and sign-on. The second type of exit takes place for the user authorization, which means whenever A user requests access to Db2 resource, which means like select from a table or creating a table, tablespace, those types of resources. So that’s where the second type of exit can take place, is the authorization of user to access the Db2 resources. Also mentioned about Db2 native versus external security. So At the beginning, there was only a native security with Db2. And later, IBM added also the external security, which is controlled by the External Security Manager.
[00:09:30] – Emil Kotrc
So I will explain that a bit more later. So again, from this slide, follow the link on top if you haven’t before and play with that site. So some basic terms that we’ll use across this session. And sometimes people confuse those 2, authentication and authorization. So authentication means really the identification verification of the user, which means like, hi Db2, I’m Emil, I want to talk to you. So that’s the authentication. Db2 verifies, okay, this is Emil, he provides me the credentials, I can verify them using the ESM and so on. So that’s the authentication. And Db2 has different methods of doing that. Typical, like you just log on to TSO and then you are authenticated. So you can talk to Db2, but there are some other options, but I will not talk about these. The second term is authorization, which is basically permitting or rejecting the access to the resources, which I mentioned at the beginning, typically select from a table or inserting some rows, updating the table rows in a table, and so on. By authorization, I also mean including Db2 itself. So that’s where Db2 authorizes you to— do you have access to Db2 at all?
[00:10:47] – Emil Kotrc
So it’s, it already authenticated you. So it’s now it’s Emil. And now Db2 wants to see if I have access to that Db2. So that’s the authorization. So it will check with the security manager. So hopefully this is all clear, authentication versus authorization. The next 3 items I have on this slide is primary auth ID, secondary auth ID, and SQL ID. And why this matters is because those IDs, especially secondary and SQL IDs, are those IDs that can be manipulated by those sign-on and connection exits. So primary By primary auth ID, I mean the ID that typically identifies the process. So it’s, if you are in a TSO session, it’s your user ID. If you submit the job, that’s the user in the job card. If you connect through DDF, that’s how you authenticate it to Db2 and so on. Secondary ID, it’s typically a set of groups that can be associated with your auth ID. They are typically defined in the ESM, in the external security manager such as RACF. So you have some groups associated with you which can hold additional privileges that your user ID doesn’t have. So maybe ML doesn’t have all the privileges, but he’s part of the Db2 group and he can do some selects and inserts into certain tables and so on.
[00:12:07] – Emil Kotrc
So that’s the secondary auth ID. Then we have SQL ID, and that’s Db2 term already. And that’s a user ID that can provide some privileges that are checked for certain dynamic SQLs. For example, it’s also a default schema if you don’t provide it. So it can have different semantics depending on the context and SQL use. And this can be one of either my primary ID or any of the secondary IDs. And in the next section, I will show you how this gets assigned. So, and now about those exits. So there are 2 types. The first type on top is Db2 connection and identification exit. So there are 2: connection identification and sign-on. And in the parents, I have those exit names. So basically what they do, they assign values to primary, secondary, and SQL ID. So basically This is the crucial information that Db2 uses to provide authorization if the user is authorized to access those resources. So this is very important. The process depends on the authentic environment. What I mean by this is, is it a TSO, CICS, IMS environment, and so on? And one thing here to mention is that if you want to use secondary user IDs, you must replace the default routines that come with Db2 with those sample routines.
[00:13:33] – Emil Kotrc
So we need to compile them and run. Because that’s where, where this, this is implemented, where those exits can provide or copy those groups back to, back to Db2. And I will show you how that works in a minute. So that’s connection and sign-on. The second part is external access control exit, and that’s basically used if you are using external security manager for authorizations of access to Db2 objects. So we can use either internal or external. So this applies only if you are using external. Db2 ships with a default exit that disables this external security. So by default, we are using internal security, and I will mention the differences in the next slide. But if you are— if you want to switch to external, this is where this exit takes place. That’s where those authorization checks happen. So basically, it means that Db2 use this exit to permit or rejecting the access to resources if you, if you use this external security. So now back to those connection and sign-on exits. So I mentioned there are different environments, so I grouped them into those 3 categories on this slide. So you can see that there is this in the middle column, you have connection exit, and the right column has the sign-on exit.
[00:14:55] – Emil Kotrc
And I have these 3 groups of environments. So you see different colors. So, and what, why I did it, because I wanted to highlight which exit takes place in different environments. So for example, on top you see the typical, like, TSO environment. So you will go into TSO and you run Spoofy, and that’s where the connection exits take place. And as you can see, the sign-on exit is not used here at all. On the other hand, the second group is a typical candidate can be CICS, or I like to remind RRSAF or RDF, where both takes can take place. For example, in case of RRSAF, the connection exit takes place during the identification call where you identify. So Db2 will assign the primary, secondary, and so on. But with RRSAF, you can also do authorized sign-on. So Even that you were authenticated before, you can still sign on as a different user if you are authorized. And that’s where the sign-on exit takes place, for example, as well. So that’s why I have these 2. And the 3rd category only uses the sign-on exit. And that’s, for example, if you have like a CICS subtask.
[00:16:05] – Emil Kotrc
So because that can sign on you as a different user, so that’s where the sign-on exit applies. The second types of exit I mentioned is access control authorization exit. And I mentioned it’s only used if you are external security. So there is these 3 categories. You either use Db2 internal, external, or a combination of those, or you use no security. Most likely no one uses no security. There is this DS and z/parm auths. Most likely everyone has that unless you have some test system. And so the major difference is who manages the security. So if you have internal, that’s a database admin. Basically, if you use grant and revoke statements, that’s where you are using internal security, internal Db2 security. On the other hand, if you are— if you typically ask your security admin to grant you access and so on, that’s where you use the external security because it’s typically managed by the security administrators. These days I see a lot of shift from internal to external to consolidate all the security under the security admin umbrella, but it depends on the site. So internal security, also called Db2 native, basically the privileges and roles are tracked in the Db2 catalog.
[00:17:27] – Emil Kotrc
So there are those auth tables, sys_user_auth and so on. SysTap auth and so on where Db2 tracks all these internal security rules. And again, you are using grant and revoke commands to manage that. On the other hand, if you are using external security, Db2 calls the external security manager RACF Top Secret ACF2 to check the privileges. Access, that’s the second exit I mentioned, access control authorization exit routine. And as I mentioned, the Db2 ships with a default routine which basically disables. The important thing here to understand is that this internal and external can be combined. Most likely you don’t want to do that. So in that case, you need to provide more controls in the external security to, for example, to reject the access by default. But if you don’t, and ESM cannot decide, then it returns like return code 4, which means unable to determine. And in this case, internal security can take place. So the problem might be that you might have some outdated definitions in the internal tables while you are using the external security. So make sure that you close the gap. And most likely, you want to just use the external if you switch to that.
[00:18:51] – Emil Kotrc
And here is basically a summary. So I have internal on the left, external on the right. So internal is managed by database admin typically. External is typically managed by security admin or coordination combination of those 2 authorities. In the case of internal security, all the information about these security controls are stored in a Db2 catalog. I mentioned those sysauth tables. With external, it’s in the security database. How do you control it? With internal, you use grant and revoke. In external, you use the control statements of the ESM such as permit. What objects do you have? So in case of internal, you grant to tables, packages, tablespaces, and so on. In external, you use the standard resource classes and security resource entities. Privileges using select, execute, and so on, while in external you have profile names. And I will mention it later in the session in the second part when I will speak about that authorization exit. So I will show you how you can navigate through that exit to understand that. So that was the quick intro into the Db2 security. I mean, it was really super quick. I don’t know, 5 minutes or so.
[00:20:12] – Emil Kotrc
We could spend hours here, but I wanted to make sure about that we are all on the same page with those basic terms, especially primary, secondary, and SQL IDs. So now let’s go a bit deeper about those security exits and see some basics. So I think the most important thing here is on top. That an exit routine runs as an extension of Db2, which possesses some great responsibility. So it can behave such as part of Db2. So you need to be careful. And that’s why I also mentioned this, or that’s why I also built this session, because some users do not understand that those exits really have high privileges because they run as extension of Db2. Most likely, or what I have seen most, that Users use those samples that are shipped with Db2, but I have seen even some customized versions. And in that case, you need to be careful who has access, what this exit contains, and so on. So make sure you, you have full control. Part of it is that it also— those exits reside in authorized program library, typically SDSN exit. So they must be APF authorized. Can only be modified when Db2 is down.
[00:21:33] – Emil Kotrc
It’s not that correct. They can be modified anytime, but they will take place only when you restart Db2. So Db2 will reload them, right? But you can of course edit them anytime, but then you need to recycle your Db2 to take place. So exit routine has all the privileges of Db2. That’s important. And the last item as well, they can expose the integrity of z/OS because they run from the APF authorized library. So they are really critical. And that’s what I remind everybody. Make sure that you understand what you use for this exit. If you have any customizers, make sure it’s all version controlled and you know who has access to those. Now, some coding requirements. So they are written in assembler. And that’s why I will also mention how to navigate through that a bit. There are some requirements. I think I can skip that. Maybe those that are at the bottom that The exit must not invoke any Db2 services nor any SVCs or ISAI routines. And that’s because it runs under inside of Db2. So if you call SQL, it would like, can cause some trouble. So it’s not allowed. Also because SVC services and ISAI routines, because Db2 already provides functional recovery routines.
[00:22:49] – Emil Kotrc
So, and it would be like a different recovery environment. So you must use FRRs if needed. Samples are shipped with SDS and SAMP. So all that you will see in this session, you can go yourself and check in your SDS and SAMP and read those exits. I will speak about those, only about those samples that are shipped. I will not speak about any customizations or anything like that. Now, execution environment. So they follow the standard z/OS or NVS conventions. So the standard call interface. They require 31-bit A mode, and they run in supervisor key 7 because Db2 itself runs in supervisor key 7. And they may run in cross-memory mode, so that you must keep in mind as well because Db2 typically has like multi-address spaces, and you have allied address spaces that talk to Db2. So typically they can run in this cross-memory mode. So that’s just in case you developed your own. Not sure if that’s your use case, but again, like, even if you look into those samples, you will see all these things there. So now that will be just a very quick, because how do you read assembler? I mean, you can still use the ISPF editor and read them, but just like a short teaser about this extension, which can be very helpful to navigate through that.
[00:24:20] – Emil Kotrc
So those of you who don’t know, so mainframers love 3270. I love it as well because I have my muscle memory, so I know how to navigate quickly. But, you know, colleagues and maybe your new colleagues love VS Code more because that’s what they used in the college and so on. So now there are many, many extensions that you can use, and one of them is this High-Level Assembler. So I just have this list so you can go to the marketplace and see what is available. Most likely you have already seen it, so there are some highlights here, but on the top left you see the assembler, High-Level Assembler VS Code plugin. So, and that can be pretty useful, especially in this context, because you can use whatever you use on your PC scrolling. It provides the minimap, search using regular expressions, and so on. It also has some nice navigation features, and I will show some of them as I will mention some or will show some examples from those exits. It can help you with the code analysis, syntax highlighting, of course, and syntax checking, branch indicators. That’s what I like, and I will showing.
[00:25:41] – Emil Kotrc
So if you don’t use it already, maybe give it a try. It’s available for no charge. You can just use VS Code and download these and enjoy your assembler in VS Code. And my colleague Witek was able to speak about it for a longer period doing a demo. So I’m sorry, I just, I just will do this quick intro and focus on the next part. Oh, yeah, and I think just, and gotcha that he was, because when he was experimenting, when Vitek was experimenting with, with this plugin using Db2 exits, he figured out that some macro was placed in a different library than expected. So just like if you do it, make sure that you have all the macros available. Hopefully that was all clear. So we know the basics of security. We understand those different Db2 security exits, sign-on, and connection and authorization. And now we can go deeper. That’s why I have my microscope here, right? So let’s start with the first one, and that’s the connection exit that is shipped with Db2. So if you want to To find it in your sample library, that’s the member name on top right, right?
[00:27:02] – Emil Kotrc
So the DSM3SATH. So that’s the authorization or connection exit. So this is the CSEC. And what are the input values to this exit? So the primary input is the initial primary auth ID. So that’s what Db2 assigns as a primary auth ID. Typically, it comes from the ASXB_USER8, which is part of your, of the address space. So there is, for each address space, there is a control block that contains control information about the address space, and one of those is user ID. So in the TSO, it’s typically your, your logon ID. In job submission, that’s, that’s the user in the job card or, or yourself, whoever submitted that, that job. And what are the output values? So as I mentioned at the beginning, these types of exits are responsible for providing primary ID, SQL ID, and list of secondary IDs. Again, warning, these are not returned from the default exit. So if you want to have them returned, you need to recompile that sample exit that is shipped with Db2. And there can be up to 1,000-something secondary IDs. And interestingly, I have seen a customer who said that they reached that limit. So apparently they had like a very big number of secondary IDs.
[00:28:24] – Emil Kotrc
So this exit has 3 sections that I will be speaking about. In fact, it has 4 sections, but I will not mention the 4th section, which is setting the entries in the session variable array. I will not talk about it, so I will focus on those 3 sections here that are important in this context. So let’s start with the first section. So basically the first section determines the primary auth ID. So I have this flowchart to navigate through that code. So basically, as I mentioned, Db2 sets the default primary auth ID as ASXBUSER8. But in some situations when it cannot, it can be blank. So if it’s non-blank, then it goes to section 2 because primary auth ID is already set. But if it’s blank, then it goes to the decision on the right. So is it TSO foreground? If yes, then take logon ID. If no, then take job user. Typically, this is used in TSO, so Db2 is already able to assign that user ID. So here is a snippet from that VS Code extension. So as you can see, it provides the coloring, syntax highlights, and so on. And what I like here, I don’t know if you see my cursor actually here.
[00:29:51] – Emil Kotrc
Anyway, so in the middle next to the B&H, you might see this little green arrow pointing down, which is a branch indicator. And in this case, it can tell you, it can help you to understand that this is something like an if statement, right? Because it goes down. So it’s some condition. If something equals to something, go next. So this is like if-else condition. So like this small navigation can help to, to go through, to navigate through that exit. So now we are in section 2. Section 2 determines the list of secondary auth IDs. So again, this is from that sample exit that is shipped, not by that default. So now it, this exit checks whether the RACF is active, and if yes, it checks if there are any groups, and if there are, then it will copy the groups to the output. Okay, so that’s the major point of this section, to get the list of secondary Secondary auth IDs. Again, I have this sample, and maybe one thing I would like to highlight. If you look at the very bottom, almost the second row at the bottom, it has the row upwards, like you see the blue arrow pointing up, and this is the indication that this is sort of loop.
[00:31:15] – Emil Kotrc
So in the loop here, what does it do? It basically copies It goes through those individual groups, secondary user IDs, and copies them to the output. So this is an indicator of a loop. Again, some small hint that can help you to navigate through those exits. And the last section of the Db2 connection exit is determine the SQL ID. And again, a reminder, this is important in certain Dynamic SQLs and like a default schema name if you create objects and so on. So it starts with the initialization. So the SQL ID is initialized to primary auth ID, which came already from the section 1. And now what is interesting here is that if you are in— if you are not in TSO, Then this is done. But if you are in TSO, it goes next and checks that do we have the TSO profile found. So there is this profile TSO command where you can define your prefix, dataset prefix. So it goes next. If the TSO dataset prefix exists on top right, then it checks whether this prefix matches your secondary user IDs. And if yes, it can check, change your supply to TSO dataset prefix.
[00:32:39] – Emil Kotrc
And that’s the thing I have seen in one customer case. A customer was a bit confused why the SQL ID doesn’t equal to the primary auth ID. And the reason was exactly that, that they had this TSO data, a dataset prefix. And this exit assigned a SQL ID to this TSO dataset prefix. So might be confusing, especially if this is different than your user ID. Typically in TSO session, your dataset prefix is Equal to your to your user ID, but this is it can be different basically, and that’s what we have seen in some some cases. So make sure that you understand also this flow because the setting of SQL authorization ID matters, especially if you have some dynamic SQLs. So and those are those three major sections in the connection exit. So it assigns the primary auth ID, copies secondary IDs, and then determines the SQL ID. Again, a snippet from that VS Code sample. Hopefully, hopefully that helps if you read it. Now the second exit that takes place during the authentication, and that’s the sign-on exit. And as I mentioned, this can be used in certain environments together with connection exit. So both can apply, or in some environments only the sign-on exit.
[00:34:03] – Emil Kotrc
Takes place. So what is the input value? So again, input value is the initial primary auth ID. Now this typically comes from the attachment facility, which is CICS, RRSAF, and DDF, and so on. So that’s where the user ID comes from. Output values, this, this again, like similar to the prior exit, it sets, it may set the primary auth ID But the default doesn’t change it. So it keeps it from the connection. SQL ID set to primary and list of secondary IDs. Again, it can copy it, but only if you use this, if you recompile that sample that is shipped. So it again contains 3 sections plus the 4th section that I will not speak about. So let’s take a look at those 3 sections that are in those sample exits. So the first one is pretty simple. So primary auth ID, it’s not changed. So that’s, that’s easy. Section 2 SQL ID is set to primary auth ID. So no magic here. But the section 3 has some more logic which now determines the list of secondary IDs and copies them to the output. So we start checking whether the RACF is active. If yes, then checks whether the AC is passed.
[00:35:30] – Emil Kotrc
And now it can be yes or no depending on, on the environment. Typically in CICS, the AC is passed, so it will take this AC and from it, it will copy the groups into the output. And for those of you who don’t know what AC is, AC is basically your security context. So that’s a control block that is created by the security manager. And it contains information about yourself. About the user. It contains also the groups and some sign control information. So that’s AC. In some environments, this is not passed, such as in IMS. So this exit tries to find it because the AC can be attached to address space or the task. So it first navigates to that. Most likely it will be there. But if not, it can even create its own based on that primary auth ID. So, and once it has this AC, it can have those groups attached to it, and it will copy them and give them back to Db2, and Db2 will use them later for the authorization checking. And here is an example, like the snippet from that, from that exit. Nothing that I would highlight here.
[00:36:44] – Emil Kotrc
I mentioned those arrows already. So just for your information. So those were those 2 types of exit. Again, reminder, connection and sign-on. And they assign primary, secondary, and SQL ID. Very important. And these take place regardless whether you use internal or external security. Right. They take place basically when Db2 authenticates you, when you connect to Db2. And Db2 wants to assign those primary auth IDs, secondary auth IDs, and, and seek write. Now this, this guy is a bit different. So now we’ll speak about the external access control exit. And that’s basically used in case where you want to use external security to do the authorization checkings when Db2 is accessing, accessing the objects. Selecting tables, creating table spaces, those kind of things. So Db2 ships with a default which is already compiled and linked, so it’s available, which basically means it turns off the external security. So by default, you have no external security. There is also a sample exit that enables external security, but you need to take action if you want to use it. You need to compile it and restart your Db2. And this exit is called in 3 types of calls.
[00:38:17] – Emil Kotrc
First one is at the init call, typically at the Db2 startup. So Db2 will call this exit with the known CSEC name, so it will call it. And if the exit returns return code 2 with reason code -1, Then it means, do not call me again. I won’t be used. So use only the internal security. And that’s what the default exit that is already compiled and linked, that is shipped with Db2, does. So basically, when Db2 calls it for the first time, it says, don’t call me again. And then Db2 switches to internal security exclusively. However, if you use it for external security checking, Then Db2 will use it for authorizations at the authorization check whenever Db2 needs to check whether the user is authorized to do some action on that object. There are a few cases where this auth call can be bypassed. I have some examples here. Typically, installations is admin, installations is oper, which have— they have like more privileges and Db2 bypasses those. And also Db2, for the sake of performance, can cache the authorizations already. So if it caches that you have select privileges from this table, it will not call this exit again because it knows.
[00:39:37] – Emil Kotrc
On the other hand, there might be changes even during the lifetime of Db2 when it’s running. So Db2 also listens to those ENF signals. Whenever there is a change in the security database, it can invalidate this cache and rerun the authorization. And the 3rd call happens during the termination and the Db2 shutdown. And basically it means to do the cleanup. So it calls the exit to do the proper cleanup, free the storage it was using, and so on. So that’s the final stage of call. Now, we’re speaking about the external security exit that is shipped in the SDS and SAMP. So that’s the member name on top. And this is way more complex than those exits that I mentioned before, those connection and sign-on exits. So it has like a set of input parameters, but typically you would expect those. So it has a user ID, so someone who is the requester who wants to take the action, who is the user. So, but, so there is this field, I will call it primary, this XAPLU Primary, like primary user. There is also another user ID, the SQL authorization ID, which can be the primary or secondary or role.
[00:41:00] – Emil Kotrc
It’s this XAPLU check. So that’s the user check that Db2 can use it for the ownership check. And I will speak about it on the next slide because this can be very confusing because there are 2 fields for the user ID and only one AC, one that that security context, and it can differ depending on the settings. So I will mention that in a minute. And then, of course, so Db2 now knows the user, and then it needs to know like a Db2 SSID group, type of the object you want to access. Is it a table, tablespace, whatever? It needs to know its name, privilege being checked. You want to select or insert, right? And some additional control information. And the output from this exit tells Db2 whether this— whether access was allowed or denied. So important is return code 0, which means access is allowed. Return code 8 means access is denied. Return code 4 means ESM cannot decide. So for example, the security resource is not defined for this particular object and privilege. So then It means, okay, so I don’t know, like RACF doesn’t know, then Db2 can fall back to the internal security.
[00:42:18] – Emil Kotrc
There might be some additional return codes. I have this return code 12 listed on the screen, which means there might be some other error. Don’t call me again, and so on. And in that case, you have some options what Db2 can do, whether shut down or use internal security, for instance. So now about this confusing part. So I mentioned there are 2 user IDs, the primary user ID, that’s the requester, and the authorization ID, that user check. And those can differ. And there is one AC that specifies the user or that security context that will be used by the RACRoot FastAuth. And the RACRoot FastAuth is the basically the security, it’s the SAF call, security facility call to check the authorizations if the user that possesses this AC is able to access that object. And I have this example how bind works. So when Db2 binds a package, it does 2 types of checks. First is But do you are able to do the bind really? So that’s the bind check at the beginning. In that case, both are the same. So it’s like authorization of the binder. So Db2 checks if whoever submits that bind job is able to do the bind.
[00:43:44] – Emil Kotrc
OK, that’s fine. But then what happens next is the Db2 checks the access or resource checks in that package. So you manipulate some data. You select from tables. And so on. And now these 2 fields may differ. So the primary user ID is authorization of the binder, but the user check or the authorization ID is the authorization of the package owner, and they can be different. And now Db2 will create the AC for one of those, and depending for which one is controlled by the ZPARM setting. So there is this auth exit check. ZPAR. And by default, this is set to primary, which means that Db2 will use this primary user ID, which is in this case authorization of the binder, which might be confusing if you are migrating from internal to external. And on the next slide, I will show you a chart that hopefully explains that. So most likely what you want to do is change it from this default to auth_exit_check equals to Db2. where Db2 will pass the AC that is associated with the CHECK_USER_ID. That’s the SQL ID of the package owner, basically. The reason for this is because IBM introduced external security exit.
[00:45:06] – Emil Kotrc
I don’t know if it was version 6, maybe 5. I don’t remember exact Db2 version. But this security— this ZPAM setting was introduced later. I think it was 10 or 11. So, and of course, because of the backwards compatibility, Db2 kept that default as primary because that was the original behavior. So, and this is where I wanted to outline it a bit. So on top, you have a different zparm setting. So it can be auth check equal to primary or auth check equal to Db2. And now I mentioned there are 2 different user IDs, like the binder and package owner. You have this UPRM field and you have this UCHECK. Those are the input params to that exit. And it can be user A, user B, but there is only one AC that is passed to that exit. So in case we are using the primary, the AC points to the primary user, which is in that case it’s the binder. While if you are using, and that’s on the right hand, on the right hand from the dots, if you have auth check equal to Db2, that’s where the AC of the of the package owner is used.
[00:46:20] – Emil Kotrc
So dbtools will assign these ACs depending on this zparm setting, and that this goes later, this AC goes later to this ESM check, this RACROOT_FAST_AUTH, where the exit passes the AC. And again, like, reminder of what AC is, it’s basically the security context of that user ID. So keep this in mind, especially if you are migrating from internal to external, because I have seen some confusions about, like, how is it possible that if I bind internal, it was the owner, package owner, but if I switch to external, it’s the binder, not the package owner, that is checked for those authorizations. And it depends on this zparm settings. So make sure you understand The context. Okay, now how this exit does the auth check. So that’s the most important piece here, how this sample exit does the authorization checks, whether you are authorized to use that Db2 resource. So there are 2 methods, implicit, which is used for privileges Of the objects that you own. So if you create a table, you possess some implicit privileges, right? It’s your table, so you can use it. And there are some— the second method is that it uses RACF profiles for those Db2 resources.
[00:47:53] – Emil Kotrc
So if you are not— if those implicit do not apply. And all the logic, I mean, this exit is pretty long, so you can still read it. But if you understand how it works, it’s not that complex. So basically, the logic itself is table-driven. So it uses 3 set of tables. It uses authorization checking tables. So the first one is the privilege table, which helps you to locate or helps to locate the authority checking rules. Basically, it means if I want to do this action, what are the rules that I need to check? Okay. Then it’s associated with this resource table. So authority checking rules are associated with the resource tables, which define which resources will be checked in ESM. So now that’s the link from Db2 objects to the external security entries. So resource table defines whether to perform implicit check, which class to use, which is the resource name, and so on. And typically, it contains multiple resources. And I will show you example on the next slide. The important thing here is that the rules and resources are executed in a loop. Which means it starts typically at the beginning. It checks for the implicit privileges, then for those least privileges like the select, then it may go to database admin, sysadmin, and so on.
[00:49:14] – Emil Kotrc
So it goes from, from those least privileges to the big ones. And it tries them one by one. So it started those implicit checks first, followed by those RACRUT calls. So, and this is how you can put all these things together. So on the right hand, I have a snippet from the documentation. So if you take a look into that documentation, which is specific to this exit, so there is a special guide in Db2 PDFs in the documentation, and it can like point you to those items in that exit. On the left hand, I have snippets from that exit. So I will start on the right hand. So I want to check the select privileges I’m selecting from a table. So in the documentation I can see, okay, that’s the privilege code 50. So then it goes to that first table. So as you can see on the left-hand top, the privileges, you see that priv code 50, which is the select authority, and it applies to objects tables and views. Okay, that’s the T and V. From there it goes to that select auth authorization rules table. So it’s the left middle picture. And you see, okay, privilege 50 for object table.
[00:50:36] – Emil Kotrc
And the rules are first check the ownership. That’s the implicit privilege basically. Then it tries if you have select privilege. If you’re not, then it checks if you are DB database admin, if you are SQL admin, sysdb admin, data access, and so on. Those are those, those high privileges. ADN SysAdmin and SecAdmin. And from here, from those resources, it goes to the resource table which defines the resource names basically. And that from that left hand, it maps what is also on the right hand. If you look into the documentation and you see, so first it checks the implicit, and if not, then it goes through these resources. So at least one of these, if you have access to one of these, then you are granted to select from the table. So it first checks this resource name that is on top, .select, so it’s a select privilege, then it checks for DB admin, SQL admin, and so on. So basically whatever you see here is what you see on the left hand. So the reason why I show it here is that if you look into that exit, it sounds very complex. It has a lot of code, but in fact it’s just a set of tables that take place.
[00:51:47] – Emil Kotrc
And that, that should help you to navigate through that. And always have this, this book with you because this provides those, those information about how to— which is the privilege code and, and what are the resources. Yeah, those resource names and the class names, they, they are defined how you basically how you define your, your exit. And I think I have it. No, let’s Let’s discuss different things and I will explain that a bit later. So now Db2 knows the ACE. So it knows depending on the zparm, it knows which security context to use. And basically from that, from those different tables, it knows the resource name. So what Db2 will do, or this exit, sorry, this exit will do, it will do this RACROOT with request fast auth. That’s the authorization check. So it provides the resource that is from the table, the class that is known. This is the ACE. And important highlight here is that log equals no fail, which means Db2 will audit only the success. And now you need to have in mind that loop. So as I mentioned, Db2 will go through that loop checking all those resources. If you have one, it will log, okay, now Emil has select privilege, so I will log it.
[00:53:07] – Emil Kotrc
I will allow this access and this will be logged. Failures are not, not updated here. And I will tell you how this works on the next slide. Also on the right hand, what I have here is snippet from that VS Code extension because it can help you to see the macros, how, what are the parameters and so on. So if you just hover your mouse on the track route call, it will show you some information about how this, how this macro is structured, what, what are the input params and so on. So that can be very helpful. Now I mentioned that only the success is audited. However, if you don’t have access to any of these resources, then there is a failure. So what this exit will do if access is not granted at all, so it will go through all these resources and none of them apply. RACRUTE, the exit will rerun the first RACRUTE. Which is typically that select, for example, in our case. And it will now change it with the log as is, which means it will log, it will audit even the failures, which means it depends on your ESM setting, but typically you have this for auditing.
[00:54:21] – Emil Kotrc
So that’s the reason why if you don’t have the access, it will give you an information that the user doesn’t have the select privilege. So it will not list all of those rules. But it will stop at the first one. So in this case, it’s typically you don’t have the select privilege. So, and that’s where what will be audited as a failure. So I mentioned some options in that exit. And this is where you define what is your class name. So there are 2 options. You can have a single subsystem scope or multiple subsystem scope, which is the default. So basically the class name contains is the same for those systems. So in this case, the resource names contain or includes the SSID, Db2 SSID names. And one more thing I think I mentioned was the error option, which is what to do, what Db2 should do if that initialization fails or if there are some other issues with that exit. So what’s to do in that case? So Db2 has an option like fallback to Db2 native or stop Db2 if the exit cannot decide. So those are those options that are there documented in that Db2 documentation and also in that exit itself.
[00:55:45] – Emil Kotrc
Okay, so that was a quick run through that exit. And if time permits, I think we have We still have 1 or 2 minutes. Just 1 slide about the Broadcom ESM continuation because the behavior is a bit different if you are using Top Secret or SF2. I don’t know if there is any user of these security products in the audience, but basically I spoke about those 2 types, connection and sign-on. So they are on top. So for Top Secret, Top Secret uses the sample shipped with Db2, so no problem here. ACF2, it ships its own sample exit, so you need to use those if you want to use those secondary IDs. The authorization access to Db2 works very differently because Top Secret and ACF2, they install intercept in Db2 region, so they can work even if you don’t use that exit basically. So they can, you can use external security even if you don’t use that exit, but they still They still provide that exit because it can be used, for example, by vendor tools. It can protect Db2 even if you don’t have that ESM up and running. There can be some race conditions, whatever, some failures in that subsystem and so on.
[00:57:06] – Emil Kotrc
So there is still an exit that is shipped by both Top Secret and ACF tool. Just like a high-level difference between what Db2 ships for RACF and what’s available for Top Secret and ACF2. And with that, I think that’s all I had for you. Here are some links if you are interested. Yeah, typically it’s like IBM documentation. One session that I liked at IDAC, so that was about migrating from Internal to external. That was useful. And with that, I don’t know, I was not monitoring the chat, so I don’t know if there were any questions. So I hope you enjoyed, had fun. I know this is a complex topic. So this is a picture of our twin exits. So, so I know it’s difficult to treat the exits and twins. So hopefully you still enjoyed. If you have any questions, I think we might have some time. If not, I’m happy to reply offline if you want to send me an email. If you let me go back at the beginning, this is my email address. So feel free to reach out if you have any questions. And thank you for listening. Hopefully it was useful.
[00:58:33] – Amanda Hendley
Thank you. I am seeing some comments come in. I don’t see any direct questions, but I think you’ve definitely interested a lot of people in the topic. Let me pull up my screen real quick. So just a couple things I wanted to call out. We have a new article posted this week on Db2 Cyber Recovery that is on the website, and since I saw so many security folks on. There is a CICS security discovery question or session this week as well. So it’s actually a very busy month for the virtual user groups. This is all available on planetmainframe.com, so don’t worry about necessarily pulling out the QR codes right now. To get involved, our user group— I’m sorry, our YouTube user group page is going to be where all these videos are located in addition to living on planetmainframe.com. Then lastly, I wanted to invite you to our last Db2 session of the year with Craig Mullins doing a presentation on his all-time Db2 top 10 lists. We look forward to seeing you just in about a month for that session. With that, There’s no other questions. Emil, thank you so much for presenting with us today.
[00:59:59] – Emil Kotrc
Thank you for having me. Thank you.
Virtual Db2 Sponsor
