Grant or revoke access to a cobol/db2 program manually

sathyaraj · New User Joined: 28 Sep 2007 Posts: 71 Location: India.

Hi,

I know this question is going to sound totally absurd for most of the senior folks here, but I really couldn't find a way around this.

Is it possible to grant or revoke access to a cobol/db2 program manually by running Grant/Revoke queries? I know the entries are created in Systabauth when a bind is done, but what if at a later stage, I want to revoke the update access to a few tables without touching the program?

Is that possible? If I didnt make myself clear.. please let me know..

Cheers,

enrico-sorichetti · Posted: Mon Feb 27, 2012 5:07 pm

and what are You going to do when the program will face a SQL return code for lack of authority ???

sathyaraj · New User Joined: 28 Sep 2007 Posts: 71 Location: India.

Enrico,

I think I will get a -438, but that should be fine. We are making the system completely read only and so the users will be advised not to update anything. The problem with controlling this at a RACF level is that I need the users to use one particular update transaction alone. So couldnt think of any other idea other than revoking the access the programs have...

Thanks

sushanth bobby · Posted: Mon Feb 27, 2012 6:02 pm

Hi Sathyaraj,

Bind package with two different owners and store the packages in different collections.
Owner one has all the acess.
Owner two has only select access(you read-only package)

And i don't think you will get -438 for lack of authority.

Thanks,
Sushanth

sathyaraj · New User Joined: 28 Sep 2007 Posts: 71 Location: India.

Thanks Sushanth,

Okay. say I have 10 programs out of which I want to have 9 as read only and one as read/write. So I will bind 9 programs with a owner ID which is read only and 1 program with a different owner ID which can read write.

But will the bind of the 9 programs not thow any error? i.e binding a DBRM with update statements to a owner ID that has only read access?

Cheers,

sushanth bobby · Posted: Tue Feb 28, 2012 10:28 am

Sathyaraj,

VALIDATion should be done during RUNtime. Moreover, read-only programs and updatable programs should not be mixed, it causes confusion. Please discuss with your DBA.

Regards,
Sushanth

dbzTHEdinosauer · Posted: Tue Feb 28, 2012 11:14 am

Sushanth,

what you say is correct,
but the TS does not want to create/modify/write any modules.

The TS is attempting to use the flags in db2 sysibm tables to make-up
for a poorly thought-out plan.

Sathyaraj,

yes, i imagine you are going to have bind errors.

what you have is a requirement to modify a system on the cheap.

i would suggest the cleanest solution to your problem
would be to employ triggers to inhibit the updates/inserts/deletions of rows based on user/group id.

your solution of 'modifying sysibmtabauth would be a nightmare,
as well as it probably will not work.

using triggers would allow you to keep everything as it is (the code)
yet provide a clean, controllable method of inhibiting users from modifying the database.

sathyaraj · New User Joined: 28 Sep 2007 Posts: 71 Location: India.

Thanks Brenholtz,

Trigger sounds good. But I am not sure of the work involved. creating a trigger for each table..

Ok will try to arrive at some numbers and see if that is viable.

Thanks,

dbzTHEdinosauer · Posted: Tue Feb 28, 2012 8:56 pm

instead of thinking of viability
you should be thinking of the lesser evil

when someone says all the work involved,
all i see is someone who uses stone wheels and knives.

create one, test it and if it works, write a rexx script to generate all the other CREATE Trigger's

enrico-sorichetti · Posted: Tue Feb 28, 2012 9:07 pm

if the DB2/CICS/IMS/<whatever> environment has been setup properly all the authorization/privileges checking will be done on the USERID/GROUP running the transaction

for batch USER of the JOB card ( real,surrogate,propagated )
for TSO USER logged on ( with allowance for some surrogate processing )
for CICS/IMS the USER logged/signed in thu the proper CICS/IMS sign in process

so fiddling around with programs and friends is pretty useless

but naturally the security setup should have been done properly

sathyaraj · New User Joined: 28 Sep 2007 Posts: 71 Location: India.

Brenholtz,

I totally agree that trigger was a paradigm shift from what I was thinking and it is a less complex solution. It took me some time to digest the approach and now like you said, can think about ways to quickly do it.

Enrico,

our applications have access levels hard coded and am trying to find a way to control the application without touching it. Gonna test some SQL PL, and see how it goes..

Cheers,

enrico-sorichetti · Posted: Tue Feb 28, 2012 9:34 pm