Re: SQL and Data security

["Simon Coulter" <shc@xxxxxxxxxxxxxxxxx>]

a
Hello RIchard,

You wrote:
>During a pre-audit, a flag was raised about programmers have access to data 
>altering utilities on the production AS400.  DFU, EDTF, DBU, WRKDBF, etc. 
>are easy enough to restict.  However, the problem comes with SQL. How can a 
>user be limited in the execution of SQL?  We need to be able to allow the 
>programmers SQL SELECT, but prevent UPDATE or DELETE.  These rules should 
>only be in place when SQL is executed from a command line, but allowed 
>within RPG or CL programs since the application uses embedded SQL.  Any 
>thoughts?

I assume by "command line" you mean Interactive SQL?   If so, revoke authority 
to the 
STRSQL command but allow them to use Query Manager (STRQM).  You can restrict 
the 
alllowed SQL statements by working with the user's profile from within QM 
(option 10 if I 
recall correctly).

If you really mean the AS/400 command line (especially since you say CL 
program) then you 
aren't using the SQL product but rather some 3rd-party tool like ASC Sequel.  
In that 
case you can change the respective commands using CHGCMD and ensure the ALLOW 
keyword is 
*IPGM, *BPGM, *IMOD, *BMOD, *IREXX, and *BREXX.

NOTE!!!! That may satisfy the immediate audit requirement but it doesn't solve 
the real 
problem.  If the programmer can run SQL in a program but not from the command 
line then 
they can write a program to issue the SQL statement (or indeed an SQL program 
to issue 
any SQL statement) to trash production data.

The real problem is allowing your programmer's update rights to production data 
which is 
simply a stupid idea regardless of how small your shop is.  The tool is not at 
fault, 
rather the entire security mechanism.

Regards,
Simon Coulter.

«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»
«» FlyByNight Software         AS/400 Technical Specialists       «»
«» Eclipse the competition - run your business on an IBM AS/400.  «»
«»                                                                «»
«» Phone: +61 3 9419 0175      Mobile: +61 0411 091 400           «»
«» Fax:   +61 3 9419 0175      mailto: shc@flybynight.com.au      «»
«»                                                                «»
«» Windoze should not be open at Warp speed.                      «»
«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---