MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2012

Re: Security for SQL functions



fixed

Hi, Michael:

Take a look at:
DSPOBJAUT QSYS2/SYSROUTINE *FILE

Then you could use EDTOBJAUT or GRTOBJAUT or RVKOBJAUT to alter the *PUBLIC authority and only grant *CHANGE authority to those whom you want to be able to do those operations on this table. You may want to create a group profile or authorization list to control who is able to update this table.

(OS/400 object security applies to all objects, including database tables.)

Hope that helps,

Mark S. Waterbury

> On 9/14/2012 11:21 AM, Michael Naughton wrote:
Thanks, Mark - I understand that, but the OP asked, "How do I secure my production functions so that only certain people can drop them?" As I understand it, dropping a function only removes the record from SYSROUTINES -- it doesn't affect the *PGM or
*SRVPGM. Securing those objects would ensure that they wouldn't be deleted, but couldn't some who wasn't authorized to them still DROP the functions that use them?

Just wondering ....

Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> writes:
Hi, Michael:

You are correct that the _definition_ of SQL functions and procedures
resides in QSYS2/SYSROUTINE.

Run this SQL query:

select * from QSYS2/SYSROUTINE

and take a look at the contents of the column EXTERNAL_NAME. This
identifies the object (*PGM or *SRVPGM) that _implements_ the function
or procedure.

The actual code that implements such a function or procedure must
necessarily reside in a *PGM or *SRVPGM.

DB2/400 is unique among IBM's DB2 implementations in that you do not
necessarily have to define a *PGM to use it as a "stored procedure" --
any *PGM can be CALLed via the SQL CALL interface. But, if you want to
define it as a FUNCTION, or define the parameter style, etc., then you
need to define it via the SQL CREATE PROCEDURE or CREATE FUNCTION statement.

Hope that helps,

Mark S. Waterbury

Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA 01376
413-676-3144
Internal: x 444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that any use is
strictly prohibited. If you have received this e-mail in error, please notify us immediately by replying to it and then delete it from your computer.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact