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:
..."
midrange.com
