SQL firewall

Types of SQL injection attacks

Protected roles

A protected role is a user or group that the database administrator has chosen to monitor by using SQL/Protect. Each protected role can be customized for the types of SQL injection attacks for which it is to be monitored, thus providing different levels of protection by role.

A protected role that has the superuser privilege must either be altered so that it is no longer a superuser, or it must be reverted back to an unprotected role.

Additionally, every command issued by a protected role is recorded incrementally in a statistics view. This view helps to identify the start of a potential SQL injection attack against the role. The statistics are collected by type of SQL injection attack.

Configure SQL/Protect as an administrator to monitor a database

1. Edit the following parameters to start SQL/Protect:

   edb_sql_protect.enabled = on # (This parameter is set to off by default.)
   edb_sql_protect.level = passive # (SQL/Protect has three modes: learn, active, and passive. The default mode is passive.)

2. Execute the following statements to create a test database named targetdb and a test user named test:

   CREATE DATABASE targetdb;
   CREATE ROLE test;
   GRANT ALL ON DATABASE targetdb TO test;
   ALTER ROLE test LOGIN;

3. Log on to the test database targetdb. Then, execute the following statements to create SQL/Protect and add protected roles:

   CREATE EXTENSION sqlprotect;
   SELECT sqlprotect.protect_role('test');

   Execute the following statements to display the list of protected roles:

   SELECT * FROM sqlprotect.list_protected_users;
   SELECT * FROM sqlprotect.edb_sql_protect;

4. Change the operating mode of SQL/Protect as needed.

   SQL/Protect operates in three modes: learn, active, and passive. The default mode is passive. For more information, see Configure SQL/Protect operating mode.

   • Switch SQL/Protect to the learn mode:

     edb_sql_protect.level = learn; #

     1. Log on to the targetdb database as the test user. Then, create a test table named company, and execute the SELECT and INSERT statements:

        CREATE TABLE company(name VARCHAR(100), employee_num INT);
        SELECT * FROM company;
        INSERT INTO company VALUES('new', 1);
        SELECT * FROM company;

     2. Execute the following statements to display statistics about the test user accessing tables:

        SELECT * FROM sqlprotect.edb_sql_protect_rel;
        SELECT * FROM sqlprotect.list_protected_rels;

   • Switch SQL/Protect to the passive mode:

     edb_sql_protect.level = passive; #

     1. Log on to the targetdb database as the test user.
     2. Execute the following statements to inject SQL statements:

        SELECT * FROM company WHERE 1 = 1;
        DELETE FROM company;

        Note SQL/Protect displays a message suggesting unauthorized SQL statements, but does not block the execution of the SQL statements.
   • Switch SQL/Protect to the active mode:

     edb_sql_protect.level = active; #

     1. Log on to the targetdb database as the test user.
     2. Execute the following statements to inject SQL statements:

        SELECT * FROM company WHERE 1 = 1;
        DELETE FROM company;

        Note SQL/Protect displays a message suggesting unauthorized SQL statements, and blocks the execution of the SQL statements.

Configure protected roles

Protected roles are stored in the edb_sql_protect table. The database administrator can choose the users and user groups to be protected and add them to the table.

• Invoke the protect_role function to add a user to the table:

  SELECT sqlprotect.protect_role('userA');

• Execute the following statements to display the protected roles in the table:

  select * from sqlprotect.list_protected_users;
  select * from sqlprotect.edb_sql_protect;

• Invoke the unprotect_role function to revert a protected role back to unprotected:

  SELECT sqlprotect.unprotect_role('userA');

Configure SQL/Protect operating mode

 edb_sql_protect.level = active;  #

You can edit certain fields in the edb_sql_protect table to specify what need to be protected for a role.

targetdb=# \d sqlprotect.edb_sql_protect;
        Table "sqlprotect.edb_sql_protect"
      Column       |  Type   | Collation | Nullable | Default 
--------------------+---------+-----------+----------+--------- 
dbid               | oid     |           | not null |  
roleid             | oid     |           | not null |  
protect_relations  | boolean |           |          |  
allow_utility_cmds | boolean |           |          |  
allow_tautology    | boolean |           |          |  
allow_empty_dml    | boolean |           |          | 
Indexes: 
   "edb_sql_protect_pkey" PRIMARY KEY, btree (roleid)

UPDATE sqlprotect.edb_sql_protect SET allow_utility_cmds = TRUE WHERE roleid = 16480; 

Other related operations

• To stop SQL/Protect, execute the following statements:

  edb_sql_protect.enabled = off
  edb_sql_protect.level = passive #

• To display statistics of SQL statements that are blocked by SQL/Protect, execute the following statement:

  SELECT * FROM sqlprotect.edb_sql_protect_stats;

• To delete statistics of SQL statements that are blocked by SQL/Protect for a specified user, execute the following statement:

  SELECT sqlprotect.drop_stats('username');