Urgent - security issue with SQL SP and UDF

[peteh]

We are running DB2 PE (AIX) 8.1.5 and have built a SQL stored proc that
uses the SNAPSHOT_APPL_INFO udf to return to the caller info about
current db2 connections.

The SP was created by a user id that belongs to the SYSADM_GROUP and
the SYSADM_GROUP. This user can run the SP and get results. When a
GRANT EXECUTE (by the definer/creator of the SP) is done to a user not
in these groups, that user gets what appears to be an authorization
error:
---------------------------------------------------------------
SQL0443N Routine "*PPL_INFO" (specific name "") has returned an error
SQLSTATE with diagnostic text "SQL1092 Reason code or token: SPPCH
". SQLSTATE=38553
----------------------------------------------------------------

In the doc, it states:
----------------------------------------------------------------
Authorization Considerations for Static SQL
To use static SQL, the user running the application only needs the
EXECUTE privilege on the package. No privileges are required for each
of the statements that make up the package. The EXECUTE privilege may
be granted to the user's authorization ID, to any group of which the
user is a member, or to PUBLIC.
-----------------------------------------------------------------

Does this have something to do with the DYNAMICRULES RUN vs
DYNAMICRULES BIND settings (the default is DYNAMICRULES RUN and I don't
know how to change it). Can anyone shed light on this? I admit security
is not one of my strengths in this environment, but I've read and tried
everything I can think of.

Pete H

[Ian]

peteh wrote:

We are running DB2 PE (AIX) 8.1.5 and have built a SQL stored proc that
uses the SNAPSHOT_APPL_INFO udf to return to the caller info about
current db2 connections.

The SP was created by a user id that belongs to the SYSADM_GROUP and
the SYSADM_GROUP. This user can run the SP and get results. When a
GRANT EXECUTE (by the definer/creator of the SP) is done to a user not
in these groups, that user gets what appears to be an authorization
error:

Prior to Fixpack 7, DB2 V8 has a registry variable DB2_SNAPSHOT_NOAUTH
which will allow non-SYSADM users to use the snapshot facility.

Fixpack 7 introduced a new group SYSMON_GROUP which replaces this
registry variable.

[peteh]

Thanks Ian;
I will give the DB2_SNAPSHOT_NOAUTH setting a try. However, if we want
to expose other administrative functions via SPs, I need to better
understand why this is necessary give the documentation references
above. Is t

For example if I (as SYSADM) write a C stored proc that uses the DB2
API function "sqlefrce" to "force" a specified AgentID, it seems that I
should be able to GRANT EXECUTE on this SP to a non-SYSADM/SYSCTRL
user? Is this not so, and if not, where in the doc can I go to get a
thorough understanding of how SP/routine security works? I have spent
most of this week looking at what Package Owners, Routine Definers, and
Routine Invokers can do. It seems pretty unambiguous in the Programming
Server Applications doc:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
The routine definer's role is to encapsulate under one authorization
ID, the privileges of running the packages associated with a routine
and the privilege of granting EXECUTE privilege on the routine to
PUBLIC or to specific users that need to invoke the routine. Note: For
SQL routines the routine definer is also implicitly the package owner.
Therefore the definer will have EXECUTE WITH GRANT OPTION on both the
routine and on the routine package upon execution of the CREATE
statement for the routine.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Do you see where my confusion lies? And thanks again for your prompt
response!

Pete H

[Serge Rielau]

peteh wrote:

Thanks Ian;
I will give the DB2_SNAPSHOT_NOAUTH setting a try. However, if we want
to expose other administrative functions via SPs, I need to better
understand why this is necessary give the documentation references
above. Is t

For example if I (as SYSADM) write a C stored proc that uses the DB2
API function "sqlefrce" to "force" a specified AgentID, it seems that I
should be able to GRANT EXECUTE on this SP to a non-SYSADM/SYSCTRL
user? Is this not so, and if not, where in the doc can I go to get a
thorough understanding of how SP/routine security works? I have spent
most of this week looking at what Package Owners, Routine Definers, and
Routine Invokers can do. It seems pretty unambiguous in the Programming
Server Applications doc:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
The routine definer's role is to encapsulate under one authorization
ID, the privileges of running the packages associated with a routine
and the privilege of granting EXECUTE privilege on the routine to
PUBLIC or to specific users that need to invoke the routine. Note: For
SQL routines the routine definer is also implicitly the package owner.
Therefore the definer will have EXECUTE WITH GRANT OPTION on both the
routine and on the routine package upon execution of the CREATE
statement for the routine.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Do you see where my confusion lies? And thanks again for your prompt
response!

Pete H

There is a bit of a clash between the SQL semantics and the APIs.
A lot of the APIs look at the current authid (you could say they behave
like dynamic SQL -ish).
As an SQL person I have of my optinions on whether this is right or not,
but it is the way it is ;-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[peteh]

Hi Serge;
Based on the above, can answer 2 questions for me?
1) If I change the DYNAMICRULES bind option to RUN (from its default of
BIND), will I be able to create a C SP (as SYSADM)that calls the
sqlefrce API and grant execute to an authid that DOES NOT have SYSCTRL
or SYSADM auth?
2) How can I change this bind parameter? I am running DB2 PE 8.1.5
(AIX) and it looks like the SET_ROUTINE_OPTS() procedure and "db2set
DB2_SQLROUTINE_PREPOPTS" are not available until we upgrade to
Stinger...

Bottom line, can I do what I want to do at my current release level?
Thanks again for any help.

Pete H

[Serge Rielau]

peteh wrote:

Hi Serge;
Based on the above, can answer 2 questions for me?
1) If I change the DYNAMICRULES bind option to RUN (from its default of
BIND), will I be able to create a C SP (as SYSADM)that calls the
sqlefrce API and grant execute to an authid that DOES NOT have SYSCTRL
or SYSADM auth? Sorry... I'm shaky on the subject of DYNAMICRULES myself.
Maybe Andrew or Liu can comment?
2) How can I change this bind parameter? I am running DB2 PE 8.1.5
(AIX) and it looks like the SET_ROUTINE_OPTS() procedure and "db2set
DB2_SQLROUTINE_PREPOPTS" are not available until we upgrade to
Stinger... DB2_SQLROYINE_PREPOPTS should be available since DB2 V7.1.
The difference between it and SET_ROUTINE_OPTS() is that the later does
not require a restart and is also session specific.
Bottom line, can I do what I want to do at my current release level?
Thanks again for any help.

There is good reason why SYSMON was invented. What about the db2set
variable referred to by others.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab