Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2015
    Provided Answers: 2

    Unanswered: db2 federation security

    So I'm trying to secure the link between the DBs by read only user.
    The description is as following:

    OLTP DB users:
    coreadm - main user
    core_ro - read only user, have only select privs on coreadm tables

    ARCH DB users:
    coreadm - only user, have link to core_ro@OLTP

    Desired end result:
    connect to ARCH with coreadm and be able to select (and nothing else) from coreadm@OLTP tables.


    OLTP Server :
    create database OLTP on /db2/data
    db2 connect to OLTP
    db2 grant dbadm on database to coreadm
    db2 connect to OLTP user coreadm
    db2 "create table oltp_tab(name varchar(100))"
    db2 "insert into oltp_tab values('asd')"
    db2 "grant select on oltp_tab to core_ro"
    db2 connect to OLTP user core_ro 
    db2 "select * from coreadm.oltp_tab"
      1 record(s) selected.
    db2 connect to OLTP user coreadm
    db2 "revoke select on oltp_tab from core_ro"
    db2 connect to OLTP user core_ro
    db2 "select * from coreadm.oltp_tab"
    SQL0551N  The statement failed because the authorization ID does not have the
    required authorization or privilege to perform the operation.  Authorization
    ID: "CORE_RO".  Operation: "SELECT". Object: "COREADM.OLTP_TAB".
    ARCH server:
    db2 create database ARCH on /db2/data
    db2 catalog tcpip node OLTPN remote rmtserver server 50000
    db2 catalog db OLTP as OLTPF at node OLTPN
    db2 connect to ARCH
    db2 "create wrapper drda"
    db2 "create server COREOLTP type db2/udb version '10.5' wrapper drda authorization \"core_ro\" password \"core_ro\" options (DBNAME 'OLTPF', fold_pw 'N' )"
    db2 "create user mapping for USER server COREOLTP options (remote_authid 'core_ro', remote_password 'core_ro')"
    db2 connect to ARCH user coreadm
    db2 "select * from coreOLTP.coreadm.oltp_tab"
      1 record(s) selected.
    As you can see from OLTP@core_ro I'm unable to select (because of the revoke), but from ARCH@coreadm I am able to select by the federation although it suppose to go through core_ro user which does NOT have select priv on that table.
    What am I missing? Or doing wrong..?
    Is that the mapping for USER command?

    On DB2 LUW 10.5

    Thanks guys
    Last edited by AntiLopa; 02-01-16 at 05:20.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts