Db2

Hi,

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

Repro:

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.

[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                                                        

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,

JMB

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:

1. the original use case for trusted context was for middleware servers providing connection multi-plexing for mutliple end-user connections running queries/reports
2. the semantic issues involved with having an owner of a created object that does not have the privileges to maintain/sustain the object's existence (i.e. you were able to create the object due to inherited privileges but outside of the trusted context, you do not and the object should be invalidated).

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,

Paul