Db2

Jan Nelken IBM Champion posted Fri March 14, 2025 07:18 AM

Hi Mark,

Can you verify that you have:

Authorization

Mark Gillis IBM Champion posted Fri March 14, 2025 07:30 AM

Hi Jan

yes DBADM authority and CONTROL on table referred to in the AFTER clause.

I did try extending my authority by granting SCHEMADM but you cannot grant a privilege or authority to yourself.

Jan Nelken IBM Champion posted Fri March 14, 2025 07:59 AM

Did you try to add schemaadm to authorization id X while logged on as Y - who has SECADM/DBADM?

I still think that CREATEIN and/or IMPLICITSCHEMA may be needed depending on whether you use all fully qualified object names in CREATE TRIGGER and body of the trigger statements.

Do you have simple repro script?

Mark Gillis IBM Champion posted Fri March 14, 2025 09:19 AM

I did try that Jan, but the results were even worse. When logged in as my "second" user (mark.gillis), which has DBADM and SECADM, I get this:

db2 grant schemaadm on schema logbook to user db2admin
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0551N  The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation.  Authorization
ID: "MARK.GILLIS".  Operation: "GRANT". Object: "LOGBOOK".  SQLSTATE=42501

I have tried to simplify the script(s) as there is a bunch of stuff in there that will confuse the issue. I'm attaching that test script which does reproduce the issue.

Thanks for looking into this for me

Regards

Mark

Mark Gillis IBM Champion posted Fri March 14, 2025 09:20 AM

Unfortunately I couldn't get the attachment link to work. Here are the script contents:

note that most of the 'real' processing is now commented out

Jan Nelken IBM Champion posted Fri March 14, 2025 09:47 AM

Mark,

As I suspected - for real Db2 (V12.1.2) and real OS (Ubuntu) - it works fine - as instance owner :-)

db2inst1@DESKTOP-F5VOQKN:~$ db2 -td# -vf mark.db2
CREATE TABLE Aircraft_Test ( Registration CHAR(6) NOT NULL , Make CHAR(25) NOT NULL , Model CHAR(25) , Type CHAR(25) NOT NULL , Most_Recently_Used TIMESTAMP , COMMENTS VARCHAR (255) , CONSTRAINT Aircraft_PK PRIMARY KEY (Registration) )
DB20000I  The SQL command completed successfully.

CREATE TRIGGER MOST_RECENT_INSERT
    AFTER INSERT ON Aircraft_Test
    REFERENCING NEW AS New_id
    FOR EACH ROW
    SECURED
--  MODE DB2SQL
BEGIN ATOMIC
    -- update airfield set MOST_RECENTLY_USED =
        -- (select max(brakes_off)
           -- from logbook
          -- where From_Airfield = Code)
      -- where From_Airfield = New_Id.From_Airfield
    -- ;
    -- update aircraft a set MOST_RECENTLY_USED =
        -- (select max(brakes_off)
           -- from logbook b
          -- where a.registration = b.registration )
     -- where  a.Registration = New_Id.Registration
    -- ;
-- select tabschema, tabname, owner from syscat.tables where tabschema='LOGBOOK' ;
END
DB20000I  The SQL command completed successfully.

I need to rebuild virtual machine with BGV (Bill Gates Virus) to try your example - stay tuned ...

Mark Barinstein posted Fri March 14, 2025 01:51 PM

DB2 may think, that you don't have DBADM.

What's the result of the following query?

SELECT *
FROM TABLE(AUTH_LIST_AUTHORITIES_FOR_AUTHID('DB2ADMIN', 'U'))
WHERE AUTHORITY IN ('DBADM', 'DATAACCESS')

Jan Nelken IBM Champion posted Fri March 14, 2025 05:44 PM

Mark!

Standard Help Desk answer: Works for me  :-)

Environment:
Windows 11
Db2 V11.5.9 SB50441
Running as db2admin (equivalent of Linux instance owner):

User db2admin holds following privileges:


There are no special settings for groups in DBM:


and user db2admin is such:

Mark Barinstein posted Sat March 15, 2025 05:05 AM

If the SECURED option is specified, the privileges held by the authorization ID of the statement must additionally include SECADM or CREATE_SECURE_OBJECT authority (SQLSTATE 42501).

Your DB2ADMIN user has to have SECADM or CREATE_SECURE_OBJECT database authority to create a SECURED trigger in addition to DBADM (with DATAACCESS to avoid other object privileges check). Does it really have one of the authorities above?

SELECT AUTHORITY, D_USER, ROLE_USER
FROM TABLE(AUTH_LIST_AUTHORITIES_FOR_AUTHID('DB2ADMIN', 'U'))
WHERE AUTHORITY IN ('DBADM', 'DATAACCESS', 'SECADM', 'CREATE_SECURE_OBJECT')

Mark Gillis IBM Champion posted Mon March 17, 2025 05:56 AM

Thanks Jan and Mark for digging into this for me. It was the SECURED option and the consequent requirement for SECADM authority that was causing the issue in the end. I'll be honest; I don't remember why the SECURED option was added but this is largely an R&D database so simply commenting that out has allowed me to create these triggers and the dependent objects.

Thanks again for your help chaps; this was driving me up the wall!

Regards

Mark Gillis