explain with validate(bind)

Marcus Davage

explain with validate(bind)
Hi.

If I EXPLAIN, in batch using DSNTEP2, an UPDATE statement for which I do not
have permission to the underlying table, I get a -551, as you would expect.
The reason for this is, or so I thought, that the package/plan DSNTEP2 is
bound with has VALIDATE(RUN). If I bind the package/plan with VALIDATE(BIND)
under a sysadm ID, I still get a -551 on the EXPLAIN. Should this not occur,
when I am expecting the permission validation to be done by the sysadm ID?

Comments?

Marcus Davage
Senior Technician, Business Services, Lloyds TSB Insurance
'The views expressed are my own and do not necessarily represent the views
or policy of my employer, Lloyds TSB Bank plc.'



-----------------------------------------------------------------------------------------------------------------------
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.

evolvebank.com is a division of Lloyds TSB Bank plc.
Lloyds TSB Bank plc, 71 Lombard Street, London EC3P 3BS. Registered in
England, number 2065. Telephone No: 020 7626 1500
Lloyds TSB Scotland plc, Henry Duncan House, 120 George Street,
Edinburgh EH2 4LH. Registered in Scotland, number 95237. Telephone
No: 0131 225 4555

Lloyds TSB Bank plc and Lloyds TSB Scotland plc are regulated by the
Personal Investment Authority and represent only the Scottish Widows
and Lloyds TSB Marketing Group for life assurance, pensions and
investment business.

Members of the UK Banking Ombudsman Scheme and signatories to the UK
Banking Code.
-----------------------------------------------------------------------------------------------------------------------

Mike Holmans

Re: explain with validate(bind)
(in response to Marcus Davage)
Marcus,

You're expecting wrong. How would the bind process know what statements you
intend to PREPARE and EXECUTE? You can't really expect BIND to know what SQL
statements you or any other user of DSNTEP2 might want to submit.

In other words, dynamic SQL is always VALIDATE(RUN).

Mike Holmans
Database Consultant
BT Affinitis Computing Partners
[login to unmask email]

This post is solely the opinion of its author and does not necessarily
reflect BT's view



-----Original Message-----
From: Davage, Marcus [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2001 12:39 PM
To: [login to unmask email]
Subject: [DB2-L] explain with validate(bind)



Hi.

If I EXPLAIN, in batch using DSNTEP2, an UPDATE statement for which I do not
have permission to the underlying table, I get a -551, as you would expect.
The reason for this is, or so I thought, that the package/plan DSNTEP2 is
bound with has VALIDATE(RUN). If I bind the package/plan with VALIDATE(BIND)
under a sysadm ID, I still get a -551 on the EXPLAIN. Should this not occur,
when I am expecting the permission validation to be done by the sysadm ID?

Comments?

Marcus Davage
Senior Technician, Business Services, Lloyds TSB Insurance
'The views expressed are my own and do not necessarily represent the views
or policy of my employer, Lloyds TSB Bank plc.'



----------------------------------------------------------------------------
-------------------------------------------
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.

evolvebank.com is a division of Lloyds TSB Bank plc.
Lloyds TSB Bank plc, 71 Lombard Street, London EC3P 3BS. Registered in
England, number 2065. Telephone No: 020 7626 1500
Lloyds TSB Scotland plc, Henry Duncan House, 120 George Street,
Edinburgh EH2 4LH. Registered in Scotland, number 95237. Telephone
No: 0131 225 4555

Lloyds TSB Bank plc and Lloyds TSB Scotland plc are regulated by the
Personal Investment Authority and represent only the Scottish Widows
and Lloyds TSB Marketing Group for life assurance, pensions and
investment business.

Members of the UK Banking Ombudsman Scheme and signatories to the UK
Banking Code.
----------------------------------------------------------------------------
-------------------------------------------


Mike Turner

Re: explain with validate(bind)
(in response to Mike Holmans)
Marcus

The bind option that gives the effect you want is DYNAMICRULES(BIND).

Regards
Mike Turner
Email: [login to unmask email]
Web: www.michael-turner.ltd.uk

Message text written by DB2 Data Base Discussion List
>Hi.

If I EXPLAIN, in batch using DSNTEP2, an UPDATE statement for which I do
not
have permission to the underlying table, I get a -551, as you would expect.
The reason for this is, or so I thought, that the package/plan DSNTEP2 is
bound with has VALIDATE(RUN). If I bind the package/plan with
VALIDATE(BIND)
under a sysadm ID, I still get a -551 on the EXPLAIN. Should this not
occur,
when I am expecting the permission validation to be done by the sysadm ID?

Comments?

Marcus Davage
Senior Technician, Business Services, Lloyds TSB Insurance
<



James Campbell

Re: explain with validate(bind)
(in response to Mike Turner)
Unfortunately, there is one more step that the auditors would like:
since DYNAMICRULES(BIND) would allow _any_ sql that the bind
owner is authorised to do, you'll have to construct a special version
of DSNTEP2 that will _only_ do explains. The source for
DSNTEP2 is readily available (SDSNSAMP) and, from memory,
fairly obvious. Depending on your PLI programming skills, you
could include an appropriate SELECT .... FROM PLAN_TABLE.

Some people have written special programs to do the explain,
plan_table report etc in one step. Perhaps one of those would be
kind enough to give you a copy.

James Campbell


On 18 Dec 2001, at 8:51, Mike Turner wrote:

Date sent: Tue, 18 Dec 2001 08:51:40 -0500
Send reply to: DB2 Data Base Discussion List <[login to unmask email]>
From: Mike Turner <[login to unmask email]>
Subject: Re: [DB2-L] explain with validate(bind)
To: [login to unmask email]

> Marcus
>
> The bind option that gives the effect you want is DYNAMICRULES(BIND).
>
> Regards
> Mike Turner
> Email: [login to unmask email]
> Web: www.michael-turner.ltd.uk
>
> Message text written by DB2 Data Base Discussion List
> >Hi.
>
> If I EXPLAIN, in batch using DSNTEP2, an UPDATE statement for which I do
> not
> have permission to the underlying table, I get a -551, as you would expect.
> The reason for this is, or so I thought, that the package/plan DSNTEP2 is
> bound with has VALIDATE(RUN). If I bind the package/plan with
> VALIDATE(BIND)
> under a sysadm ID, I still get a -551 on the EXPLAIN. Should this not
> occur,
> when I am expecting the permission validation to be done by the sysadm ID?
>
> Comments?
>
> Marcus Davage
> Senior Technician, Business Services, Lloyds TSB Insurance
> <
>
>
> DB2-L webpage at htt
p://www.ryci.com/db2-l. The owners of the list can be reached at [login to unmask email]


James A Campbell



Marcus Davage

Re: explain with validate(bind)
(in response to James Campbell)
Good idea, James -

I've already tampered with DSNTEP2, so making an "EXPLAIN only" version
shouldn't be that difficult... unless one of the "some people" would kindly
hurl a copy in my general direction...

Marcus

-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: 19 December 2001 01:27
To: [login to unmask email]
Subject: Re: explain with validate(bind)


Unfortunately, there is one more step that the auditors would like:
since DYNAMICRULES(BIND) would allow _any_ sql that the bind
owner is authorised to do, you'll have to construct a special version
of DSNTEP2 that will _only_ do explains. The source for
DSNTEP2 is readily available (SDSNSAMP) and, from memory,
fairly obvious. Depending on your PLI programming skills, you
could include an appropriate SELECT .... FROM PLAN_TABLE.

Some people have written special programs to do the explain,
plan_table report etc in one step. Perhaps one of those would be
kind enough to give you a copy.

James Campbell


-----------------------------------------------------------------------------------------------------------------------
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.

evolvebank.com is a division of Lloyds TSB Bank plc.
Lloyds TSB Bank plc, 71 Lombard Street, London EC3P 3BS. Registered in
England, number 2065. Telephone No: 020 7626 1500
Lloyds TSB Scotland plc, Henry Duncan House, 120 George Street,
Edinburgh EH2 4LH. Registered in Scotland, number 95237. Telephone
No: 0131 225 4555

Lloyds TSB Bank plc and Lloyds TSB Scotland plc are regulated by the
Personal Investment Authority and represent only the Scottish Widows
and Lloyds TSB Marketing Group for life assurance, pensions and
investment business.

Members of the UK Banking Ombudsman Scheme and signatories to the UK
Banking Code.
-----------------------------------------------------------------------------------------------------------------------