/* Server version: WI-V6.3.7.18553 Firebird 2.1 
   SQLDialect: 3. ODS: 11.1. Forced writes: On. Sweep inteval: 20000.
   Page size: 8192. Cache pages: 9500 (76000 Kb). Read-only: False. */

SET SQL DIALECT 3;

/* Alter Procedure... */
/* empty dependent procedure body */
/* Clear: RIGHTS_REGRANT for: RIGHTS_REVOKE */
SET TERM ^ ;

ALTER PROCEDURE RIGHTS_REGRANT AS
 BEGIN EXIT; END
^

/* Alter (RIGHTS_REVOKE) */
ALTER PROCEDURE RIGHTS_REVOKE AS
declare variable enterprise_mode varchar(50);
declare variable db_owner varchar(32);
declare variable app_role varchar(32);
declare variable infomatic_role varchar(32);
declare variable auditor_role varchar(32);
declare variable pos_app_user varchar(32);
declare variable public_role varchar(32);
begin
  /* enterprise_mode = 'CENTRAL', 'REMOTE', 'STANDALONE'
  || RDB$PRIVILEGE = 'S' Select, 'R' Referance, 'X' Execute, 'I' Insert, 'U' Update, 'D' Delete
  || It is not a bad idea to forbid the user REPL to log on with SYSTEM_LOG_ON !
  || It is ABSOLUTELY necessary for POS and ADMIN to log on with dbuser different from SYSDBA
  */
  db_owner = 'SYSDBA';

  app_role = 'CLOCKBS_ROLE';
  infomatic_role = 'CLOCK_HS_ROLE';
  auditor_role = 'AUDITOR_ROLE';
  public_role = 'PUBLIC';

  pos_app_user = 'POS';

  execute procedure SYSTEM_GET_VALUE(null, 'DB_TYPE')
  returning_values  :enterprise_mode;
  enterprise_mode = coalesce(:enterprise_mode, '');

  if (:enterprise_mode not in ('CENTRAL', 'REMOTE', 'STANDALONE', 'CENTRAL_V2', 'REMOTE_V2')) then exception WRONG_VALUE;

  /* ONLY REMOTE DB NEEDS THIS PRIVILEGE, SO BY DEFAULT WE MUST CLEAR IT */
  delete from RDB$USER_PRIVILEGES UP
  where  UP.RDB$USER = 'SYSTEM_POOL_TOTAL';

  /* CLEAR ALL GRANTS TO USERS OTHER THAN "SYSDBA" */
  delete from RDB$USER_PRIVILEGES
  where  RDB$USER <> :db_owner;

  insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION,
         RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
  select :public_role, :db_owner, RDB$PRIVILEGE, 0,
         RDB$RELATION_NAME, RDB$FIELD_NAME, 8, RDB$OBJECT_TYPE
  from   RDB$USER_PRIVILEGES p
  where  ((p.RDB$RELATION_NAME starting with 'EAI')
          or (p.RDB$RELATION_NAME in ('SYSTEM_LOG', 'SETTING_PROFILE', 'SETTING_PROFILE_FH',
             'SETTING_PROFILE_PRINTER', 'SETTING_PROFILE_PRINTER_CMD', 'SETTING_VALUE',
             'SYSTEM_CLIENT', 'SYSTEM_CLIENT_SECURE', 'RIGHT_LOGON_LOG', 'SYSTEM_LOCK',
             'EXPORT_LOG$ENT', 'EXCHANGE_STATUS$ENT', 'IMPORT_ERROR_LOG$ENT'))
         )
         and p.RDB$USER = :db_owner;

  if (:enterprise_mode = 'STANDALONE') then begin
    /* GIVE ALL RIGHTS THAT "SYSDBA" HAS TO "CLOCKBS_ROLE" */
    insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
    select :app_role, :db_owner , U.RDB$PRIVILEGE, 0 , U.RDB$RELATION_NAME, U.RDB$FIELD_NAME, 13, U.RDB$OBJECT_TYPE
    from   RDB$USER_PRIVILEGES U
    where  U.RDB$USER = :db_owner
           and not exists
             (select *
              from RDB$USER_PRIVILEGES U2
              where U2.RDB$USER = :app_role
                    and U2.RDB$PRIVILEGE = U.RDB$PRIVILEGE 
                    and U2.RDB$RELATION_NAME = U.RDB$RELATION_NAME);

  end else begin
    /* GRANT 'S', 'R', 'X' TO "PUBLIC", THEESE ARE RIGHTS EVERYONE MUST HAVE
    || FOR EVERY TABLE IN THE DATABASE
    */
    insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
    select :app_role, :db_owner, RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, 13, RDB$OBJECT_TYPE
    from   RDB$USER_PRIVILEGES U
    where  U.RDB$USER = :db_owner
           and U.RDB$PRIVILEGE in ('S', 'R', 'X');

    /* GRANT 'I', 'U', 'D' TO "PUBLIC", DEPENDING ON THE ENTERPRISE MODE -> DB_TYPE
    || THESE TABLE GRANTS MUST MATCH REPLICATION SCHEMA
    */
    if (:enterprise_mode in ('REMOTE', 'REMOTE_V2')) then begin
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      select :app_role, :db_owner, RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, 13, RDB$OBJECT_TYPE
      from   RDB$USER_PRIVILEGES U
      where  U.RDB$USER = :db_owner
             and U.RDB$PRIVILEGE in ('I', 'U', 'D')
             and U.RDB$RELATION_NAME in (
             /* REPLICATED TABLES FROM REMOTE TO CENTRAL DATABASE */
             'SALES_HEAD', 'SALES_LINE',
             'EAI_EFFECT_SALE', 'EAI_EFFECT_LINE', 'SUM_DAY', 'SUM_PAY',
             'SUM_VOID', 'SUM_STOCK', 'SUM_DISCOUNT', 'SUM_CONTRAGENT', 'SUM_CONTRAGENT_PAY',
             'SYSTEM_END_WORK',
             /* COMMON, LOCAL ONLY TABLES */
             'DUMMY', 'KEYBOARD_LAYOUT', 'KEYBOARD_SHORTCUT', 'RIGHT_DEFINE',
             'RIGHT_GROUP', 'RIGHT_GROUP_LINE', 'RIGHT_GROUP_REPORT', 'SYSTEM_CLIENT_SECURE',
             'RIGHT_LOGON_LOG', 'RIGHT_OVERRULE', 'RIGHT_USER', 'RIGHT_USER_GROUP',
             'SETTING_PROFILE_FH', 'SETTING_PROFILE_PRINTER', 'SETTING_VALUE', 'SYSTEM_CFG',
             'SYSTEM_CLIENT', 'SETTING_PROFILE', 'NOM_FIRM_DATA', 'CONTRAGENT_POOL_CHRONOLOGY',
             /* OPERATIONAL, LOCAL ONLY TABLES */
             'SYSTEM_END_WORK', 'SYSTEM_LOCK', 'SYSTEM_NOMERATOR',
             'ARCHIVE_LINE','ARCHIVE_HEAD','ARCHIVE_BON_INVENT', 'ARCHIVE_DISCOUNT',
             'ARCHIVE_FOOTER', 'ARCHIVE_PAYMENT', 'ARCHIVE_TRANS', 'ARCHIVE_FRONT',
             'ARCHIVE_MANAGE', 'ARCHIVE_PAYMENT_EXTERNAL', 'ARCHIVE_LINE_TEXT', 'ARCHIVE_MODIFFIER',
             'SMETKA_LINE', 'SMETKA_HEAD', 'SMETKA_BON_INVENT', 'SMETKA_DISCOUNT',
             'SMETKA_FOOTER', 'SMETKA_PAYMENT', 'SMETKA_TRANS', 'SMETKA_FRONT', 'SMETKA_STOCKOUT',
             'SMETKA_MANAGE', 'SMETKA_PAYMENT_EXTERNAL', 'SMETKA_LINE_TEXT', 'SMETKA_MODIFFIER', 'SMETKA_FISCAL_DATA',
             'NOM_PRINTER', 'INVENT_PRINTER', 'NOM_PRINTER_TYPE', 'NOM_PRINTER_COMMAND', 'SETTING_PROFILE_PRINTER_CMD',
             'REPL_LOG', 'EAI_CONTRAGENT', 'EAI_MAP_TAG', 'EAI_MAP_DATA', 'EAI_P2F_SUPPRESS_TP', 'EAI_P2F_TRANSFER_LOG', 'EAI_P2F_TRANSFER_LOG_LINES',
             'EAI_MAP', 'EAI_MAP_ALL_INCLUSIVE_PAYABLE', 'EAI_MAP_ALLINCLUSIVE', 'EAI_MAP_ALLINCLUSIVE_LOG',
             'NOM_OBJECT_SUPPRESS', 'SMETKA_PRINT_QUEUE', 'ARCHIVE_PRINT_QUEUE', 'PDA_BILL_POST', 'SMETKA_TAX', 'ARCHIVE_TAX'
             );
      /* POS2FRONT SETTINGS MUST BE REMOTELY MODIFIED */
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      values (:public_role, :db_owner, 'U', 0, 'NOM_PAYMENT_TYPE', 'EXT_INTERFACE', 8, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      values (:public_role, :db_owner, 'U', 0, 'NOM_PAYMENT_TYPE', 'EXT_INTERFACE_DATA', 8, 0);

      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      select :public_role, :db_owner, RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, 8, RDB$OBJECT_TYPE
      from   RDB$USER_PRIVILEGES U
      where  U.RDB$USER = :db_owner
             and U.RDB$PRIVILEGE in ('U')
             and U.RDB$RELATION_NAME in ('NOM_OBJECT');

      if (:enterprise_mode = 'REMOTE_V2') then begin
        insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
        select :public_role, :db_owner, RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, 8, RDB$OBJECT_TYPE
        from   RDB$USER_PRIVILEGES U
        where  U.RDB$USER = :db_owner
               and U.RDB$PRIVILEGE in ('U', 'D', 'I', 'R')
               and U.RDB$RELATION_NAME in ('NOM_VOID_REASON', 'NOM_TABLE_MAP', 'SETTING_STRUCTURE_ELEMENTS', 'SETTING_STRUCTURE_REPORTS', 'SYSTEM_CLIENT_MULTIPROFILE');
      end

      /* BILL_FINALIZE_NUM CAN NOT BE PREPARED WITHOUT WRITE ACCES TO "CONTRAGENT_POOL_HISTORY".
      || WE MUST GRANT U,D,I,R FOR 'CONTRAGENT_POOL_HISTORY'&'CONTRAGENT_POOL' TO THE
      || STORE PROCEDURE 'SYSTEM_POOL_TOTAL'.
      */
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', :db_owner, 'U', 0, 'CONTRAGENT_POOL', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', :db_owner, 'D', 0, 'CONTRAGENT_POOL', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', :db_owner, 'I', 0, 'CONTRAGENT_POOL', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', :db_owner, 'R', 0, 'CONTRAGENT_POOL', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', :db_owner, 'U', 0, 'CONTRAGENT_POOL_HISTORY', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', :db_owner, 'D', 0, 'CONTRAGENT_POOL_HISTORY', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', :db_owner, 'I', 0, 'CONTRAGENT_POOL_HISTORY', null, 5, 0);
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE) values ('SYSTEM_POOL_TOTAL', :db_owner, 'R', 0, 'CONTRAGENT_POOL_HISTORY', null, 5, 0);
    end else if (:enterprise_mode in ('CENTRAL', 'CENTRAL_V2')) then begin
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      select :app_role, :db_owner, RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, 13, RDB$OBJECT_TYPE
      from   RDB$USER_PRIVILEGES U
      where  U.RDB$USER = :db_owner
             and U.RDB$PRIVILEGE in ('I', 'U', 'D')
             and U.RDB$RELATION_NAME in (
             /* REPLICATED TABLES FROM CENTRAL TO REMOTE DATABASE */
             'MENU_HEAD', 'MENU_LINE', 'MENU_GROUP', 'MENU_LINE_PRICE',
             'MENU_PRICE_LIST', 'RIGHT_DEFINE', 'INVENT', 'CONTRAGENT',
             'CONTRAGENT_DATA', 'CONTRAGENT_GROUP', 'CONTRAGENT_PAYMENT',
             'CONTRAGENT_CARD', 'NOM_DISCOUNT', 'NOM_VAT_GROUP', 'NOM_VOID_REASON',
             'NOM_PAYMENT_TYPE', 'NOM_FIRM_DATA', 'STOCK', 'STOCK_GROUP',
             'CL_DISCOUNT_SCHEME', 'CL_DISCOUNT_SCHEME_DATA', 'CL_PROGRAM',
             'CL_PROGRAM_LEVEL', 'CONTRAGENT_POOL', 'CONTRAGENT_POOL_HISTORY',
             'NOM_POOL', 'NOM_POOL_RATE', 'NOM_TABLE_MAP', 'SCHEDULER_HEAD',
             'SCHEDULER_PERIOD', 'SCHEDULER_RULE',
             /* COMMON, LOCAL ONLY TABLES */
             'CONTRAGENT_POOL_CHRONOLOGY',
             'DUMMY', 'KEYBOARD_LAYOUT', 'KEYBOARD_SHORTCUT', 'RIGHT_DEFINE',
             'RIGHT_GROUP', 'RIGHT_GROUP_LINE', 'RIGHT_GROUP_REPORT', 'SYSTEM_CLIENT_SECURE',
             'RIGHT_LOGON_LOG', 'RIGHT_OVERRULE', 'RIGHT_USER', 'RIGHT_USER_GROUP',
              'SETTING_VALUE', 'SYSTEM_CFG', 'SYSTEM_LOCK', 
             'SYSTEM_CLIENT', 'SETTING_PROFILE', 'SETTING_PROFILE_PRINTER_CMD',
             'RIGHT_SECURE_LEVEL', 'SYSTEM_NOMERATOR', 'REPL_LOG',
             'SETTING_PROFILE_FH', 'SETTING_PROFILE_PRINTER', 'NOM_TAXES'
--             , 'NOM_PRINTER_TYPE', 'NOM_PRINTER_COMMAND'
             );
    end

    if (:enterprise_mode = 'CENTRAL_V2') then begin
      insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
      select :app_role, :db_owner, RDB$PRIVILEGE, 0, RDB$RELATION_NAME, RDB$FIELD_NAME, 13, RDB$OBJECT_TYPE
      from   RDB$USER_PRIVILEGES U
      where  U.RDB$USER = :db_owner
             and U.RDB$PRIVILEGE in ('I', 'U', 'D')
             and U.RDB$RELATION_NAME in ('NOM_OBJECT');

      delete from RDB$USER_PRIVILEGES UP
      where  UP.RDB$USER = :app_role
             and UP.RDB$RELATION_NAME in ('NOM_VOID_REASON', 'NOM_TABLE_MAP')
             and UP.RDB$PRIVILEGE in ('I', 'U', 'D');
      -- this privileges are granted in REMOTE_V2 base
    end
  end

  /* THE :infomatic_role ROLE MUST HAVE ALL PRIVILIGES LIKE THE USER :db_owner.
  || ONLY Infomatic APPLICATION ARE USING THIS ROLE.
  */
  insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION,
         RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
  select :infomatic_role, :db_owner, U.RDB$PRIVILEGE, 0,
         U.RDB$RELATION_NAME, U.RDB$FIELD_NAME, 13, U.RDB$OBJECT_TYPE
  from   RDB$USER_PRIVILEGES U
  where  U.RDB$USER = :db_owner
         and (not exists(
           select 'X'
           from   RDB$USER_PRIVILEGES u2
           where  u2.RDB$USER = :infomatic_role
                  and u2.RDB$PRIVILEGE = U.RDB$PRIVILEGE 
                  and u2.RDB$RELATION_NAME = u.RDB$RELATION_NAME
          ))
       and U.RDB$PRIVILEGE <> 'M' /* MEMBERSHIP */;

  /* grant :infomatic_role && 'ENTERPRISE' user */
  insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
  values ('ENTERPRISE', :db_owner, 'M', 0, :infomatic_role, NULL, 8, 13);

  /* grant READ access to AUDITOR_ROLE  */
  insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
  select :auditor_role , :db_owner , U.RDB$PRIVILEGE, 0 , U.RDB$RELATION_NAME, U.RDB$FIELD_NAME, 13, U.RDB$OBJECT_TYPE
  from   RDB$USER_PRIVILEGES U
  where  U.RDB$USER = :db_owner
         and U.RDB$PRIVILEGE in ('S', 'R', 'X')
         and not exists
           (select *
            from RDB$USER_PRIVILEGES U2
            where U2.RDB$USER = :auditor_role
                  and U2.RDB$PRIVILEGE = U.RDB$PRIVILEGE 
                  and U2.RDB$RELATION_NAME = U.RDB$RELATION_NAME);           

  /* grant :app_role && :auditor_role to POS user */
  insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
  values (:pos_app_user, :db_owner, 'M', 0, :auditor_role, NULL, 8, 13);
  insert into RDB$USER_PRIVILEGES (RDB$USER, RDB$GRANTOR, RDB$PRIVILEGE, RDB$GRANT_OPTION, RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$USER_TYPE, RDB$OBJECT_TYPE)
  values (:pos_app_user, :db_owner, 'M', 0, :app_role, NULL, 8, 13);
  /* ---------- */
end
^

/* Restore proc. body: RIGHTS_REGRANT */
ALTER PROCEDURE RIGHTS_REGRANT AS
begin
  execute procedure RIGHTS_REVOKE;
end
^

/* Alter Procedure... */
/* Alter (RIGHTS_REGRANT) */
ALTER PROCEDURE RIGHTS_REGRANT AS
begin
  execute procedure RIGHTS_REVOKE;
end
^

SET TERM ; ^

