Thanks for the group permissions on EXPLAIN_ procedures. I encounted another problem with trusted context in both 10.5 and 11.1 latest build (and before I guess).
1) Catalog DB in TCPIP mode for demo purpose
2) Create ROLE to promote db2fenc1 to DBADM if connected using IP 192.168.11.133 in this example
CREATE ROLE SSE_ROLE; GRANT DBADM WITHOUT ACCESSCTRL ON DATABASE TO ROLE "SSE_ROLE" ;
CREATE TRUSTED CONTEXT JMB_CTX BASED UPON CONNECTION USING SYSTEM AUTHID db2fenc1 DEFAULT ROLE SSE_ROLE ENABLE ATTRIBUTES (ADDRESS '192.168.11.133');
3) db2 connect to BLU user db2fenc1 (no trusted connection)
[db2inst1@ibmtest ~]$ db2 -tvf getauth | sed "s/ N / /g" | sed "s/ \* / /g" SELECT char(AUTHORITY,30) AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID (USER, 'U') ) AS T ORDER BY AUTHORITY
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE ------------------------------ ------ ------- -------- --------- ---------- ----------- ------ ACCESSCTRL BINDADD Y CONNECT Y CREATETAB Y CREATE_EXTERNAL_ROUTINE CREATE_NOT_FENCED_ROUTINE CREATE_SECURE_OBJECT DATAACCESS DBADM EXPLAIN IMPLICIT_SCHEMA Y LOAD QUIESCE_CONNECT SECADM SQLADM SYSADM SYSCTRL SYSMAINT SYSMON WLMADM
20 record(s) selected.
4) connect to remote DB (implicit trusted connection):
System Auth ID : DB2FENC1 Coordinator EDU ID : 21 Coordinator Member : 0 Registered Agents : 1 Active Agents : 1 Locks timeout value : NotSet Locks Escalation : No Workload ID : 1 Workload Occurrence ID : 12 Trusted Context : JMB_CTX Connection Trust Type : implicit trusted connection Role Inherited : SSE_ROLE Application Status : UOW-Waiting Application Name : db2bp Application ID : 192.168.11.133.59215.170508130335
Question 1: Why do I get the same output below as with non implicit trusted connexion, i.e user is "not seen" as DBADM.
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE ------------------------------ ------ ------- -------- --------- ---------- ----------- ------ ACCESSCTRL BINDADD Y CONNECT Y CREATETAB Y CREATE_EXTERNAL_ROUTINE CREATE_NOT_FENCED_ROUTINE CREATE_SECURE_OBJECT DATAACCESS DBADM
Question 2: I have created a table with db2inst1, inserted 1 row and exported to a file.
Why do IMPORT ... INSERT fails and IMPORT .... ALLOW WRITE ACCESS INSERT succeeds ?
For me, the user should have all success because he is DBADM thru implicit trusted connection.
[db2inst1@ibmtest ~]$ db2 "import from blu.jmb.del of del allow write access insert into blu.jmb" SQL3109N The utility is beginning to load data from file "blu.jmb.del".
SQL3110N The utility has completed processing. "1" rows were read from the input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "1" rows were processed from the input file. "1" rows were successfully inserted into the table. "0" rows were rejected.
Number of rows read = 1 Number of rows skipped = 0 Number of rows inserted = 1 Number of rows updated = 0 Number of rows rejected = 0 Number of rows committed = 1
[db2inst1@ibmtest ~]$ db2 "import from blu.jmb.del of del insert into blu.jmb" SQL3015N An SQL error "-551" occurred during processing.
SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "DB2FENC1". Operation: "LOCK". Object: "BLU.JMB". SQLSTATE=42501
SQL3021N The user does not have the authority to run the specified IMPORT command on table "BLU.JMB".
Same issue if we try to create triggers for example.
PMR 68455 664 706 has been created since 1 month with no progress on this problem that is blocking us. We want to provide DBADM access to users only when accessing from certain IP addresses (SIEBEL application servers).
Thanks & Best regards,
For AUTH_LIST_AUTHORITIES_FOR_AUTHID (and the other AUTH_LIST routines), the issue is not that we could not include the authorities/privileges inherited through the trusted context today but rather that, if we did, it would potentially cause more confusion since it would not be obvious which ones they were or how you got them. So, they need to be called somehow either as a new column or as a new value in existing columns (e.g. 'I' to indicate inherited in addition to the current Y/N/* options). Either way, new code :)
My memory is that the ban on DDL came about for two reasons:
So, one could argue that the other non-creating DDL statements could safely be allowed... which would be new code ;)
The issue with groups and creation of objects is similar to a degree but different in that we don't know when or if you lose the membership in the group and so don't know when or if objects created using the group privileges need to be invalidated because the owner can no longer sustain their existence (from a security perspective).
To be clear, I am not arguing against the usage you envision and would probably support RFEs to lift/improve things, just explaining the rationale for why the logic is the way it is today (and why it is not a defect :).
Hope this makes sense,