|
On an IBM i 7.2 lpar I did the following
CRTUSRPRF DUMMY (only those parameters)
Signed on as DUMMY.
STRSQL
CREATE PROCEDURE ROB/DELETEME NOT DETERMINISTIC CONTAINS SQLEXTERNAL PARAMETER STYLE GENERAL
Procedure DELETEME was created in ROB.
drop procedure rob/deletemeDROP PROCEDURE statement complete.
F3
DSPOBJAUT OBJ(SYSPARMS) OBJTYPE(*FILE)
*PUBLIC is *CHANGE
But you have to understand NO ONE but an IBM API can update or change this
table!
DSPFD SYSPARMS
Allow read operation . . . . . . . . . . . : Yes
Allow write operation . . . . . . . . . . . : No
Allow update operation . . . . . . . . . . : ALWUPD *NO
Allow delete operation . . . . . . . . . . : ALWDLT *NO
They do the same trick on the history table for a temporal table.
So when you do the CREATE PROCEDURE or DROP PROCEDURE it is an IBM API
which updates this table.
Someone got into your system and just totally buggered it up without
knowing what they were doing.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Joe Wood <wood3875@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 04/20/2016 11:53 AM
Subject: SQL0552 Not authorized to DROP PROCEDURE.
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
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)
System error message states -- DROP PROCEDURE or DROP FUNCTION requires
*OBJOPR and *DLT authority to the catalog table SYSPARMS in QSYS2.
The developer's user profile is a member of group security and
supplemental
groups. User profile name & Both group names have been granted via
EDTOBJAUT OBJ(QSYS2/SYSPARMS) OBJTYPE(*FILE) as having *ALL authority.
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
We have also explored CHGFCNUSG / WRKFCNUSG for QIBM_DB_SQLADM and
QIBM_DB_SECADM - adding the user as *ALLOWED - no success.
Developer's are still Not authorized to DROP PROCEDURE or CREATE
PROCEDURE.
(User profiles with *SECOFR *ALLOBJ authorities can make the SQL DROP /
CREATE without issue)
Any ideas on extending this capability to developers ?
--
----------------
Joe Wood
----------------
E: wood3875@xxxxxxxxx
L: http://www.linkedin.com/in/josephgwood
--
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.
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
