/* 
 * 过程：revoke_table_privilege(user,table,p1,p2,p3,p4,p5,p6)
 * 功能：回收某个用户在某表的某个权限
 * 参数：用户/public,表名/all,权限1/ALL,权限2,权限3,权限4,权限5,权限6
 * 说明：不支持DELIMIDENT=y时创建的含有大写的表名
 * 编写：liaosnet@gbasedbt.com 2023-12-20
 */
DROP PROCEDURE IF EXISTS revoke_table_privilege(VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR);
CREATE PROCEDURE revoke_table_privilege(
  p_user    varchar(128) DEFAULT 'public',
  p_tabname varchar(128) DEFAULT 'all',
  p_1 varchar(20) DEFAULT 'all', 
  p_2 varchar(20) DEFAULT '', 
  p_3 varchar(20) DEFAULT '',
  p_4 varchar(20) DEFAULT '',
  p_5 varchar(20) DEFAULT '',
  p_6 varchar(20) DEFAULT ''
)
  define v_sqlstr    varchar(255);
  define v_tmpsql    varchar(255);
  define v_tablist   varchar(128);
  define v_tabowner  varchar(128);

  IF lower(p_1) = 'all' THEN
    let v_sqlstr = 'revoke all on ';
  ELSE
    let v_sqlstr = 'revoke ' || rtrim(p_1 || ',' || p_2 || ',' || p_3 || ',' || p_4 || ',' || p_5 || ',' || p_6,',') || ' on ';
  END IF;
  
  IF lower(p_tabname) = 'all' THEN
    foreach SELECT t.tabname,t.owner INTO v_tablist,v_tabowner FROM systables t WHERE t.tabid > 99 AND t.tabtype IN ('T','V','S')
      -- SKIP DELIMIDENT=y TABLES
      IF v_tablist = lower(v_tablist) THEN
        let v_tmpsql = v_sqlstr || v_tablist || ' from ' || p_user || ';';
        EXECUTE IMMEDIATE v_tmpsql;
      END IF;
    END foreach;
  ELSE
    foreach SELECT t.tabname,t.owner INTO v_tablist,v_tabowner FROM systables t WHERE t.tabname LIKE lower(p_tabname) AND t.tabtype IN ('T','V','S')
      let v_tmpsql = v_sqlstr || v_tablist || ' from ' || p_user || ';';
      EXECUTE IMMEDIATE v_tmpsql;
    END foreach;
  END IF;
END PROCEDURE;

/* 
 * 过程：grant_table_privilege(user,table,p1,p2,p3,p4,p5,p6)
 * 功能：授权某表/同义词的某个权限给某个用户
 * 参数：用户,表名/all,权限1/ALL,权限2,权限3,权限4,权限5,权限6
 * 说明：不支持DELIMIDENT=y时创建的含有大写的表名
 * 编写：liaosnet@gbasedbt.com 2023-12-20
 */
DROP PROCEDURE IF EXISTS grant_table_privilege(VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR,VARCHAR);
CREATE PROCEDURE grant_table_privilege(
  p_user    varchar(128),
  p_tabname varchar(128) DEFAULT 'all',
  p_1 varchar(20) DEFAULT 'all', 
  p_2 varchar(20) DEFAULT '', 
  p_3 varchar(20) DEFAULT '',
  p_4 varchar(20) DEFAULT '',
  p_5 varchar(20) DEFAULT '',
  p_6 varchar(20) DEFAULT ''
)
  define v_sqlstr    varchar(255);
  define v_tmpsql    varchar(255);
  define v_tablist   varchar(128);
  define v_tabowner  varchar(128);
  
  IF lower(p_1) = 'all' THEN
    let v_sqlstr = 'grant all on ';
  ELSE
    let v_sqlstr = 'grant ' || rtrim(p_1 || ',' || p_2 || ',' || p_3 || ',' || p_4 || ',' || p_5 || ',' || p_6,',') || ' on ';
  END IF;
  
  IF lower(p_tabname) = 'all' THEN
    foreach SELECT t.tabname,t.owner INTO v_tablist,v_tabowner FROM systables t WHERE t.tabid > 99 AND t.tabtype IN ('T','S')
      -- SKIP DELIMIDENT=y TABLES
      IF v_tablist = lower(v_tablist) THEN
        let v_tmpsql = v_sqlstr || v_tablist || ' to ' || p_user || ' with grant option as ' || rtrim(v_tabowner) || ';';
        EXECUTE IMMEDIATE v_tmpsql;
      END IF;
    END foreach;
  ELSE
    foreach SELECT t.tabname,t.owner INTO v_tablist,v_tabowner FROM systables t WHERE t.tabname LIKE lower(p_tabname) AND t.tabtype IN ('T','S')
      let v_tmpsql = v_sqlstr || v_tablist || ' to ' || p_user || ' with grant option as ' || rtrim(v_tabowner) || ';';
      EXECUTE IMMEDIATE v_tmpsql;
    END foreach;
  END IF;
END PROCEDURE;