Db2 (On Premises and Cloud)

Expand all | Collapse all

implicit trusted context not providing appropriate permissions thru role

  • 1.  implicit trusted context not providing appropriate permissions thru role

    Posted Mon March 30, 2020 11:47 AM

    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



  • 2.  RE: implicit trusted context not providing appropriate permissions thru role

    Posted 14 days ago

    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