Re: SQL0552 Not authorized to DROP PROCEDURE.

On 20-Apr-2016 10:53 -0500, Joe Wood wrote:

SQL, Security, and Stored Procedure Experts,

Our environment is V7R2, current cumulative, groups, HIPers, TR3,
security level 50.

Within STRSQL, a developer issues: "drop procedure library.abc123"
and receives Not authorized to DROP PROCEDURE. (Same results for
CREATE PROCEDURE)

Just since upgrading, since applying maintenance, or since some change to the user profile of the developers?

System error message states -- DROP PROCEDURE or DROP FUNCTION
requires *OBJOPR and *DLT authority to the catalog table SYSPARMS in
QSYS2.

That is accurate for DROP ROUTINE. But other required authorities are documented as well. The files ship publicly authorized, so no granting should be necessary, unless the security had been customized for accessing that feature of the SQL.

The developer's user profile is a member of group security and
supplemental groups. User profile name and Both group names have
been granted via EDTOBJAUT OBJ(QSYS2/SYSPARMS) OBJTYPE(*FILE) as
having *ALL authority.

A tad bit excessive reaction to try to correct; they should be able to update the data, not be able DROP or RENAME the catalog TABLE.

Since we have *SYSBASE and an iASP, I have ensured that
QSYS2/SYSPARMS and QSYS200182/SYSPARMS both have the authority
changes. We've even extended the authority changes to these
table/files in *SYSBASE and iASP:
QSYS2.SYSPROCS
QSYS2.SYSPARMS
QSYS2.SYSROUTINE

After the aforementioned grant authority activity, did the problem then migrate to each of those files; i.e. reasoning for granting aut to those as well? Again, granting *ALL is excessive; opening the system to enabling the developer to accidentally delete the file(s), e.g. with a seemingly innocuous DLTF SYSP* for which the user was thinking of some files with that naming in their own\current library.

We have also explored CHGFCNUSG / WRKFCNUSG for QIBM_DB_SQLADM and
QIBM_DB_SECADM - adding the user as *ALLOWED - no success.

Developers are still Not authorized to DROP PROCEDURE or CREATE
PROCEDURE.

Turn on Authority Failure auditing, and review what T-AF is generated, and for what object; review the authority for that object to those users, and adjust accordingly.

(User profiles with *SECOFR *ALLOBJ authorities can make the SQL DROP
/ CREATE without issue)

Any ideas on extending this capability to developers ?

Finding to what object the non-godlike profiles are not authorized is key. If there is no preceding message or the "System error message" is no more illuminating than the (*OPR *DLT) text shown, then the audit journal entries typically will be most helpful.

[https://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_73/db2/rbafzcrtpsf.htm]
_CREATE PROCEDURE_ (SQL)
"The CREATE PROCEDURE (SQL) statement creates an SQL procedure at the current server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:
..."

[https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzdropst.htm]
_DROP_
"The DROP statement drops an object. Objects that are directly or indirectly dependent on that object may also be dropped.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization
...
To drop a procedure, the privileges held by the authorization ID of the statement must include at least one of the following:
..."