Conference orarep::nomahs::dec_data_distributor

282.0. "SQL$_NO_PRIV for my own transfert" by CHSR36::JSUBRI (Focus on Open/Rdb++) Mon Apr 28 1997 05:27

Rdb V6.1 or V7.0-01, ROR V7.0

The doc "handbook" says the owner of the transfert don't needs special priv 
to run is own tranfert. granting priv on ddal$tr_db solve/workaround this
problem. I would like to know if this behavior is the expected one.

Thanks
/Jean-Luc

------------- creator of the transfert jsubri -----------
SQL> crea transfer test_security type replic move  table jobs
cont> to existing file chsr36"jsubri"::rdb$root2:[70]DDAL70.rdb
cont> log filename is rdb$root2:[70]test_security.log;
SQL> start transfert test_security now;

------------ $TYPE TEST_SECURITY.LOG  ------------------
Image name:
    $2$DKA300:[SYS0.SYSCOMMON.][SYSEXE]DDAL$COPY_PROCESS.EXE

Transfer database = SYS$COMMON:[SYSEXE]DDAL$TR_DB
Transfer name     = TEST_SECURITY
Transfer option   = I (Initial replication transfer)

09:48:21  %DDAL-I-READTRDEF, reading the transfer definition from the transfer
database
----- 28-APR-1997 09:48:29.42 ----- Error           -------------------------
%DDAL-E-SQLCODERR, status code SQL$_NO_PRIV received, indicating an error in
 accessing transfer database                      

$ LOGOUT
  JSUBRI       job terminated at 28-APR-1997 09:48:29.83

  Accounting information:
  Buffered I/O count:                454      Peak working set size:      14512
  Direct I/O count:                  373      Peak virtual size:         205344
  Page faults:                      1833      Mounted volumes:                0
  Charged CPU time:        0 00:00:03.03      Elapsed time:       0 00:00:28.60

------------ prot on ddal$tr_db ---------------
SQL> att 'f sys$system:ddal$tr_db';
SQL> sho prot on database rdb$dbhandle;
Protection on Alias RDB$DBHANDLE
    (IDENTIFIER=[SYSTEM],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+
      DROP+DBCTRL+OPERATOR+DBADM+SECURITY+DISTRIBTRAN)
    (IDENTIFIER=[*,*],ACCESS=NONE)


----    4.5 Preparations at the Transfer Definition Site -------

          When you define a transfer, Replication Option associates
          your OpenVMS user identification code (UIC) and account
          name with that transfer. When the transfer is due to be
          executed, Replication Option creates an OpenVMS process
          under your account name. The copy process image runs within
          that process.

          The copy process runs on your behalf with whatever
          privileges, quotas, and defaults your account affords. 
      >>> As a transfer definer, you automatically have the privileges
      >>> needed to run, alter, or drop the transfer. 
          You can also
          grant these privileges to other users so that they can run,
          alter, or drop a transfer you have defined (including the
          schedule definition). See Section 9.7 for more information
          on granting privileges.
    

    
    Jean-Luc,
    
    No, this is not expected behavior.  Looks like something we need to
    fix.
    
    Claude

    Jean-claude,
    
    	I submited bugdb#484638
    
    Thanks
    /Jean-Luc

Looks like the problem is strictly limited to the copy process being unable
to read from the transfer database. Therefore, a simple workaround is like
this:

- create a rights identifier, for example:

  UAF> add/id transfer_user

- grant that identifier to all users that are expected to start transfers

  UAF> grand/id transfer_user user1
  UAF> grand/id transfer_user user2
  UAF> grand/id transfer_user user3
  
- grant the SELECT privilege against the transfer database and all tables in
  the database:

  SQL> grant select on database rdb$dbhandle   to transfer_user;
  SQL> grant select on      DDAL_COMMENT       to transfer_user;
  SQL> grant select on      DDAL_DEST_DPB      to transfer_user;
  SQL> grant select on      DDAL_INVOKE_TIME   to transfer_user;
  SQL> grant select on      DDAL_MOVE_CLAUSES  to transfer_user;
  SQL> grant select on      DDAL_SCHED_OFFSETS to transfer_user;
  SQL> grant select on      DDAL_SOURCE_DBS    to transfer_user;
  SQL> grant select on      DDAL_TNSF_COLUMNS  to transfer_user;
  SQL> grant select on      DDAL_TNSF_PRIVS    to transfer_user;
  SQL> grant select on      DDAL_TNSF_SCHEDULE to transfer_user;
  SQL> grant select on      DDAL_TNSF_SEL_EXPR to transfer_user;
  SQL> grant select on      DDAL_TNSF_STATUS   to transfer_user;
  SQL> grant select on      DDAL_TNSF_TABLES   to transfer_user;
  SQL> grant select on      DDAL_TNSF_VIEWS    to transfer_user;
  SQL> grant select on      DDAL_TRANSFERS     to transfer_user;
  SQL> grant select on      DDAL_TRANSFER_DB   to transfer_user;

This is only so the copy process can read the transfer database. The side
effect is to let those users read from the transfer database (but they are not
able to update the database other than via the CREATE/ALTER/DROP TRANSFER
commands). Note that transfer security is still enforced. In particular,
it is still not possible for a user to start a transfer created by another
user (assuming that other user did not grant any specific privs on that
transfer to anyone else). That check is performed upfront (not in the
copy process):

  SQL> start transfer test_security now;
  %SQL-F-DDALERR, Replication Option for Rdb fatal error
  -DDAL-E-NO_PRIV, no privilege to access this transfer

Access to the source and target databases is still controlled  by normal
privileges.
               
Another possibility (simpler) would be to grant SELECT to PUBLIC on all tables.

/albert

    
    The security problem accessing a transfer database in Replication
    Option 7.0 has been found and corrected.  The correction will appear in
    the next official product release.