Re: SQL0552 Not authorized to DROP PROCEDURE.

Chuck/Rob,

Joblog, additional SQL0552 information and answers in-line below.

ASP group set to NAM1ASP.

Job 936024/FREDJW/QPADEV001S started on 04/20/16 at 13:39:14 in subsystem

QINTER in QSYS. Job entered system on 04/20/16 at 13:39:13.

Library FRED added to library list.

Library PDFPRT added to library list.

Environment variable added.

strsql

Not authorized to DROP PROCEDURE.

dspjoblog

Bottom







Additional Message
Information



Message ID . . . . . . : SQL0552 Severity . . . . . . . :
30

Message type . . . . . : Diagnostic


Date sent . . . . . . : 04/20/16 Time sent . . . . . . : 13:39:24




Message . . . . : Not authorized to DROP PROCEDURE.


Cause . . . . . : The operation cannot be performed without the
required

authority.

-- CREATE TABLE requires *USE authority to the CRTPF
command.

-- CREATE VIEW or CREATE INDEX requires *USE authority to the
CRTLF

command.

-- CREATE ALIAS requires *USE authority to the CRTDDMF
command.

-- CREATE SCHEMA requires *USE authority to the CRTLIB
command.

-- ALTER TABLE requires *USE authority to the ADDPFCST command in order
to

add constraints, and *USE authority to the RMVPFCST command in order to
drop

constraints.


-- ALTER TRIGGER requires *USE authority to the CHGPFTRG
command.

-- CREATE PROCEDURE or CREATE FUNCTION requires *OBJOPR and *ADD
authority

to the catalog table SYSROUTINES in
QSYS2.

-- DROP PROCEDURE or DROP FUNCTION requires *OBJOPR and *DLT authority
to

the catalog table SYSPARMS in
QSYS2.

-- CREATE TYPE requires *OBJOPR and *ADD authority to the catalog
table

SYSTYPES in
QSYS2.

-- DROP TYPE requires *OBJOPR and *DLT authority to the catalog
table

SYSTYPES in
QSYS2.

-- CREATE TRIGGER requires *USE authority to the ADDPFTRG
command.

-- DROP TRIGGER requires *USE authority to the RMVPFTRG
command.

-- CREATE SEQUENCE requires *USE authority to the CRTDTAARA
command.

-- DROP SEQUENCE requires *USE authority to the DLTDTAARA
command.

-- ALTER SEQUENCE requires *USE authority to the RTVDTAARA and
CRTDTAARA

commands.

-- The COMMENT ON statements for procedures, functions, types,
triggers

and sequences require *OBJOPR, *READ, and *UPD authority to the
catalog

table associated with the
object.

-- SET SESSION AUTHORIZATION requires that the authorization ID
associated

with the statement has *ALLOBJ special
authority.

-- SET CURRENT DEGREE and the QSYS2/RESET_ENVIRONMENTAL_LIMITS
procedure

require that the authorization ID associated with the statement has
*JOBCTL

special authority or be authorized to the QIBM_DB_SQLADM
function.

-- CREATE, ALTER, DROP, LABEL ON and COMMENT ON of a MASK or
PERMISSION

requires that the authorization ID associated with the statement
be

authorized to the QIBM_DB_SECADM function. The same authorization
is

required to RENAME, DROP or delete an object that is referenced by a mask
or

permission and to CREATE, DROP, or ALTER a secure TRIGGER or FUNCTION.


Recovery . . . : Obtain authority from the security officer and try
the

operation
again.

Authorization to the QIBM_DB_SQLADM and QIBM_DB_SECADM functions can
be

handled by Application Administration in System Navigator for i. The
Change

Function Usage (CHGFCNUSG) command can also be used to allow or deny use
of

the function. For example: CHGFCNUSG FCNID(QIBM_DB_SQLADM)
USER(xxxxx)

USAGE(*ALLOWED).




On Wed, Apr 20, 2016 at 10:53 AM, CRPence <crpbottle@xxxxxxxxx> wrote:

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?

Hasn't been needed in 8-months. Was a 740 on 7.1 Sec level 40 then
replaced with 8286-42A, 7.2, Sec level 50. Not a maintenance or profile
change.

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.

I agree to the excessive reaction, but we are pulling out all the stops on
trying something.

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.

The above changes did absolutely nothing. The problem did not migrate,
those file/tables were referenced in SQL0552 relating to "other" SQL
commands so we tried them as well.

We will return these back to their original state, again, we're trying any
& everything to find the authorization that is stopping this. We've
followed the SQL0552 recommends and still it does not work as described.

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.

System Value QAUDLVL does have *AUTFAIL. I will check there...

(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.

There is no preceding message, joblog pasted above.

I will review these links and the audit log journal entries, thank you.

[
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:
..."

--
Regards, Chuck


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.