Hi all,
"Administrator Guide Implementation" DB2 8.2, chapter 7, section
"Indirect privileges through a package" states:

(highlight >>>>!!!<<<<)
"Privileges granted to individuals binding the package and to PUBLIC
are used for authorization checking when static SQL is bound.
Privileges granted through groups are >>>>not!!!!<<<< used for
authorization checking when static SQL is bound. The user with a valid
authID who binds a package must either have been explicitly granted all
the privileges required to execute the static SQL statements in the
package or have been implicitly granted the necessary privileges
through PUBLIC unless VALIDATE RUN was specified when binding the
package. If VALIDATE RUN was specified at BIND time, all authorization
failures for any static SQL statements within this package will not
cause the BIND to fail, and those SQL statements are revalidated at run
time. PUBLIC, group, and user privileges are >>>>all!!!<<<< used when
checking to ensure the user has the appropriate authorization (BIND or
BINDADD privilege) to bind the package."

Why such a restriction when checkin privileges for sql statements? I've
just grouped users accessing the database to avoid maintenance problems
with new or abandonig users...
Does VALIDATE RUN mean that group privileges are taken in account at
run time?

One more question: with sql/pl are .bnd file produced by the server?
(when creating sql stored procedures with "create procedure"
statements)
My problem is: if I have to grant privileges to specific users to let
them work with stored procedures, how will I manage the situation of a
users who goes away from the project? His userID has to be downgraded
to a lower priviledge level, will SPs continue work? My experience says
no... Either I have all SP created by a dbadm (bottleneck) hoping he
never leaves, or I rebind packages assigning them to another userID...

thank you all in advance
virgilio

virgilio wrote:
> Hi all,
> "Administrator Guide Implementation" DB2 8.2, chapter 7, section
> "Indirect privileges through a package" states:
>
> (highlight >>>>!!!<<<<)
> "Privileges granted to individuals binding the package and to PUBLIC
> are used for authorization checking when static SQL is bound.
> Privileges granted through groups are >>>>not!!!!<<<< used for
> authorization checking when static SQL is bound. The user with a valid
> authID who binds a package must either have been explicitly granted all
> the privileges required to execute the static SQL statements in the
> package or have been implicitly granted the necessary privileges
> through PUBLIC unless VALIDATE RUN was specified when binding the
> package. If VALIDATE RUN was specified at BIND time, all authorization
> failures for any static SQL statements within this package will not
> cause the BIND to fail, and those SQL statements are revalidated at run
> time. PUBLIC, group, and user privileges are >>>>all!!!<<<< used when
> checking to ensure the user has the appropriate authorization (BIND or
> BINDADD privilege) to bind the package."
>
> Why such a restriction when checkin privileges for sql statements? I've
> just grouped users accessing the database to avoid maintenance problems
> with new or abandonig users...
> Does VALIDATE RUN mean that group privileges are taken in account at
> run time?
>
> One more question: with sql/pl are .bnd file produced by the server?
> (when creating sql stored procedures with "create procedure"
> statements)
> My problem is: if I have to grant privileges to specific users to let
> them work with stored procedures, how will I manage the situation of a
> users who goes away from the project? His userID has to be downgraded
> to a lower priviledge level, will SPs continue work? My experience says
> no... Either I have all SP created by a dbadm (bottleneck) hoping he
> never leaves, or I rebind packages assigning them to another userID...
>
> thank you all in advance
> virgilio
>
While SQL Procedures use packages under the covers you are rarely
exposed to the,.
Typically you use the EXECUTE privilege on teh proceurd to control access.

The reason why DB2 is picky about group privileges is that it doesn't
know when they change. If I revoke a privilege from you on a table that
is needed for a procedure you created, DB2 will invalidate the package.
If I take you out of a group DB2 willnever knwo and thus cannot
invalidate the package.
It is interesting to not that soem other DBMS which us database internal
groups behave quite similar. So the teh problem is not merely rooted
with OS group managing.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Thank you Serge, I understand the reasons for such a behaviour. But
still I'd like to find a way to work with SPs having little impact on
DBAs activity.
It seems to me that "VALIDATE RUN" will solve the problem IF (AND ONLY
IF) at runtime db2 takes into account group membership for collecting
privileges. An appropriate test would be:
- grant only SELECT privilege on a table to PUBLIC
- login as a normal user
- CALL SYSPROC.SET_ROUTINE_OPTS('DB2_SQLROUTINE_PREPOPTS= VALIDATE
RUN')
- CREATE PROCEDURE xxx containing and UPDATE clause on that table
- ignore warnings on privileges
- CALL the procedure, hoping it works!

unfortunately the above workflow doesn't work, I receive fatal errors
when creating the SP due to the missing UPDATE privilege on the table.
(no problems related to bindadd or similar)

What's wrong with it? Is there any chance that this is the right
approach?
Otherwise, would the OWNER parameter of BIND command solve the problem?
I mean: a temporary DBADM creates all the SP (validate bind semantic)
assigning them to the instance owner ID; when the user is subsequently
revoked the DBADM privilege, will everithing continue running?

cheers
virgilio