New V12 functionality - UNLOAD authority! Why? What is the point?

By Brian Laube posted Feb 21, 2020 02:02 AM


Db2 for zOS V12 introduces a new UNLOAD authority permission for tables.   This is “beside” the good-old-fashioned classic table authority permissions for SELECTAUTH, INSERTAUTH, DELETEAUTH, UPDATEAUTH, etc. etc.

This UNLOAD authority controls the ability to use the UNLOAD utility on the table.

  • In V11, anyone could use the UNLOAD utility… but you needed SELECTAUTH on the actual table. That seemed reasonable.  Life was simple! 


In addition, Db2 V12 introduced a new ZPARM to control if UNLOADAUTH is followed by Db2!

It is obscurely called AUTH_COMPATABILITY.  

  • One might think the new ZPARM name might have “UNLOAD” somewhere in the name to make the purpose of the ZPARM a bit more obvious. But it does not. 

The default value of this ZPARM when moving to V12 is blank. 

The only other possible value for this ZPARM is SELECT_FOR_UNLOAD


How does the ZPARM affects your UNLOAD behavior?

When the ZPARM value is blank then Db2 requires that any attempt to use UNLOAD utility requires that the user has the explicit UNLOADUATH on the table.

If the ZPARM value is “SELECT_FOR_UNLOAD” then Db2 follows the good old-fashioned V11 rules where you simply require SELECTAUTH on the table.


So what is the point of all this subtle complexity to UNLOAD authority?

The auditors and security folks of the world requested this enhancement from IBM! 

Why?  Because UNLOAD utility allows you to extract the contents of the whole table.

BUT the table itself could have column masks or row permissions that restrict access to certain rows.  This subtle table control might be important to someone!

As a result, if you wanted to be a clever and tricky end-user, you could use UNLOAD utility to extract data from a table that you might not have permission to access via SELECT!

This new V12 UNLOAD authority to control permission to use UNLOAD on a table closes that gap and makes auditors happy.  It allows separation between being able to SELECT on a table vs being to UNLOAD a table!


Of course… the auditor probably only cares if you ALREADY have this type of sophisticated permissions to control access to columns or rows of a table

If you do not have that type of sophisticated table access control, then UNLOAD authority does not really matter.

So as always for all things Db2 … the best value for this ZPARM depends upon your application world!


What was my experience with this new V12 functionality? 

In my world, my system DBAS kept upgrading Db2 subsystems to V12R1M500 and Db2 began to look at the new ZPARM with default value of blank.  Soon afterwards, some user or application “broke” when running UNLOAD and I had to scratch my head and embarrassingly remember… oh yes… I should request to set this ZPARM value be explicitly be set to SELECT_FOR_UNLOAD so we continue with the V11 authority behaviour for UNLOAD!

I could have granted UNLOADAUTH on the table (or all tables) to an appropriate group of end-users.  But that seemed overkill considering my application tables and their existing security controls. 

I decided to use the ZPARM and be done with the problem very quickly. 

I will continue to use the ZPARM with SELECT_FOR_UNLOAD, at least until the day that we implement more “subtle” permission controls on our important Db2 tables. 

Currently, we have simple SELECT controls on most of our tables.  If you have permission to look at the table… you can look at everything in the table!   We just make sure the group of people with access to SELECT is reviewed and approved.  


What about DSNTIAUL? 

To be clear, I like to think of DSNTIAUL as the “unload program”, not a utility.  It is handy “sample program” given to us by IBM.  Everyone in the world uses DSNTIAUL because it is super useful for producing simple flat file output of SELECT result sets!  But DSNTIAUL is a “program” that basically issues dynamic SQL on your behalf.  It is not a utility! 

The input to DSNTIAUL is basically SQL SELECT and so DSNTIAUL just follows normal SELECT permission rules!

DSNTIAUL does not check if the user has UNLOADAUTH on the table (because it ain’t UNLOAD!!!).  And to state the obvious, the new ZPARM has no impact to DSNTIAUL!


Closing thoughts

The new ZPARM and permission to use UNLOAD is a new thing in V12.  I wanted to be clear that there is a good reason about why it was introduced. 

If you miss this subtle ZPARM when you move to V12 and encounter this surprise, you may quickly set the ZPARM to SELECT_FOR_UNLOAD and be done with the problem.  Which is fine.  But you may want to remember WHY UNLOAD authority is now a thing… and maybe you want to reset the ZPARM later.

Also, if you think about it… you might want different values for this ZPARM in test-Db2 and dev-Db2 and prod-Db2… it depends!  There are always pros and cons.