how to block update command when using STRSQL ( db2/400)?

20 pts.
Tags:
AS/400
AS/400 DB2
DB2
STRSQL
Hi People , I got several analysts that they must only use Select command through STRSQL prompt, I found I can block update command changing the object authority but I have loads and load of tables to change, is there any easier way to do that?
ASKED: July 20, 2009  4:46 PM
UPDATED: July 24, 2009  3:07 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

First do:

Display Object Description (DSPOBJD)

Type choices, press Enter.

Object . . . . . . . . . . . . . > *ALL Name, generic*, *ALLUSR…
Library . . . . . . . . . . . > YOURLIB Name, *LIBL, *USRLIBL…
Object type . . . . . . . . . . > *FILE *ALL, *ALRTBL, *AUTL…
+ for more values
Detail . . . . . . . . . . . . . *BASIC *BASIC, *FULL, *SERVICE
ASP device:
Device . . . . . . . . . . . . * Name, *, *ALLAVL…
Search type . . . . . . . . . *ASP, *ASPGRP
Output . . . . . . . . . . . . . > *OUTFILE *, *PRINT, *OUTFILE
File to receive output . . . . . dspobjd Name
Library . . . . . . . . . . . yourwklib Name, *LIBL, *CURLIB
Output member options:
Member to receive output . . . *FIRST Name, *FIRST
Replace or add records . . . . *REPLACE *REPLACE, *ADD

Then create or locate a file with the authority you want to use on all of the files.

Then compile this pgm and run it.

PGM

*/
DCLF FILE(YOURWKLIB/DSPOBJD)

MONMSG MSGID(CPF2208)

RCVF
MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(END))

GRTOBJAUT OBJ(&ODLBNM/&ODOBNM) OBJTYPE(&ODOBTP) +
REFOBJ(YOURWKLIB/YOUREFOBJ) REFOBJTYPE(*FILE)

GOTO CMDLBL(READ)

END: ENDPGM

//——————————————–
Wouldn’t this be a good case for an authorization list?
Phil
//——————————————–

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • mcl
    Authorization list would be a great thing to use. You can assign the authorization list to all the file objects in a lib, then assign authorities through the list by groups. Set for public *EXCLUDE. Put your analysts in a group, give that group *USE access. etc, etc.. Works fine, but like most things, think about what you want before you go playing with the settings... Regards Mike
    2,740 pointsBadges:
    report
  • DanD
    Like Mike says, this would be a great use of autl. I always use them when possible because you can fix an authority problem without allocating the object so its a much safer way to change object access. Here is the CL I use for that. I hacked the first pgm from this: PGM PARM(&USERNAME &AUTHLIST) DCL VAR(&USERNAME) TYPE(*CHAR) LEN(10) DCL VAR(&AUTHLIST) TYPE(*CHAR) LEN(10) /* */ DCLF FILE(SECDATA/CHGLIBOWN) MONMSG MSGID(CPF2208) READ: RCVF MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(END)) CHGOBJOWN OBJ(&ODLBNM/&ODOBNM) OBJTYPE(&ODOBTP) + NEWOWN(&USERNAME) GRTOBJAUT OBJ(&ODLBNM/&ODOBNM) OBJTYPE(&ODOBTP) + AUTL(&AUTHLIST) GRTOBJAUT OBJ(&ODLBNM/&ODOBNM) OBJTYPE(&ODOBTP) + USER(*PUBLIC) AUT(*AUTL) GOTO CMDLBL(READ) END: ENDPGM
    2,865 pointsBadges:
    report
  • TomLiotta2
    First, if you don't want SQL statements to be used, then don't give access to them. A simple RUNQRY command can be used if all that is needed is a query from a basic SELECT statement. STRSQL is intended for SQL manipulation of databases. Also, STRSQL is just the most convenient access to SQL. The RUNSQLSTM command can run UPDATEs or DELETEs (or DROP TABLE or most other SQL statements) as well. And STRQM is even more powerful than STRSQL. Further, ODBC (and OLEDB and ADO.NET) is an additional concern. The Navigator component of System i Access can open as much access as anyone needs. Simple REXX procedures can even run dynamic SQL statements. The simple act of restricting UPDATE in STRSQL won't be much of a guarantee of anything. The real answer must always be to assign proper authorities to users and objects. There is no good substitute. However, if you really want to do nothing more than to give access to STRSQL while allowing only SELECT statements, then create a CL program that contains these statements: STRDBG UPDPROD(*NO) STRSQL ENDDBG Compile the program with USRPRF(*OWNER) and have it be owned by a profile that has authority to the STRSQL command. Revoke authority to STRSQL from your analysts and let them call your program instead. If your data libraries are created as TYPE(*PROD), then tables in those libraries are protected as long as debug is active, i.e., until the STRSQL command completes. It's a poor substitute for proper object authority, but it's what you asked for.
    25 pointsBadges:
    report
  • Altino
    thanks everbody!
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following