SQL firewall

Background information

The SQL firewall of OceanBase Database Proxy (ODP) can block SQL statements by user, type, or syntax to prevent malicious SQL ingestion and unexpected database access.

Considerations

• The version of ODP must be V4.3.2 or later.

• The SQL firewall can cause the performance to deteriorate by less than 10%.

• After the SQL firewall is configured, it takes effect for all new transactions and for existing sessions after existing transactions on the sessions are completed.

• The SQL firewall cannot completely block statements for batch execution.

• The settings of the SQL firewall do not take effect for the root@proxysys user.

• The SQL firewall can block only general SQL statements and prepared statements.

• When you use the sql_firewall_config parameter to configure the firewall, VIP-level firewall settings mask global firewall settings.

• When you configure the sql_firewall_config parameter, the table name-based blocking rule does not take effect for DDL statements. To block DDL statements, you can configure a keyword-based blocking rule.

Examples

Update or add firewall settings

• Update or add global firewall settings

  replace into proxy_config(name, value, config_level) values ('sql_firewall_config', '{"limiters":
      [
          {"limitName":"limit_use_like_1","qps":"0","rule":{"scene":"Uselike", "sqlType":["SELECT", "UPDATE", "DELETE"]}, "status":"OBSERVER", "inUse":true}
      ]
  }', 'LEVEL_GLOBAL');
  

• Update or add cluster-level firewall settings

  replace into proxy_config(cluster_name, name, value, config_level) values ('test_cluster', 'sql_firewall_config', '{"limiters":
      [
          {"limitName":"limit_key_word_no_order_by_or_group_by","qps":"0","rule":{"keyWords":"ORDER\s+BY|GROUP\s+BY"}, "status":"RUNNING", "username":["user1","user2"],"inUse":true},
          {"limitName":"limit_use_like_1","qps":"0","rule":{"scene":"Nowhere", "sqlType":["SELECT", "UPDATE"]}, "status":"OBSERVER", "inUse":true},
      ]
  }', 'LEVEL_CLUSTER');
  

• Update or add tenant-level firewall settings

  replace into proxy_config(cluster_name, tenant_name, name, value, config_level) values ('test_cluster', 'test_tenant', 'sql_firewall_config', '{"limiters":
      [
          {"limitName":"limit_sql_types_1","qps":"0","rule":{"sqlType":["SELECT", "INSERT", "DELETE"]}, "status":"RUNNING","inUse":false}
      ]
  }', 'LEVEL_TENANT');
  

• Update or add VIP-level firewall settings

  replace into proxy_config(vid, vip, vport, cluster_name, tenant_name, name, value, config_level) values (, "10.10.10.1", 2883, "test_cluster", "test_tenant", "sql_firewall_config", "{"limiters":
      [
          {"limitName":"limit_no_where_1","qps":"0","rule":{"scene":"Nowhere", "sqlType":["SELECT", "UPDATE", "DELETE"]}, "status":"RUNNING", "inUse":true}
      ]
  }", "LEVEL_VIP");
  

Query firewall settings

• Query VIP-level firewall settings

  select * from proxy_config where vid = 0 and vip = '10.10.10.1' and vport = 2883 and name = 'sql_firewall_config';
  

• Query global firewall settings

  show proxyconfig like 'sql_firewall_config';
  

Delete firewall settings

• Delete VIP-level firewall settings

  delete from proxy_config where vid = 0 and vip = '10.10.10.1' and vport = 2883 and name = 'sql_firewall_config';
  

• Delete global firewall settings

  replace into proxy_config(name, value, config_level) values ('sql_firewall_config', '', 'LEVEL_GLOBAL');