PolarDB:SQL firewalls

Types of SQL injection attacks

Protected roles

Protected roles are users or groups protected by SQL/Protect. Database administrators can use SQL/Protect to specify protected roles. You can use SQL/Protect to customize different levels of injection attack prevention for different protected roles. The types of SQL injection attacks vary based on the levels.

When SQL/Protect is running, a protected role that has the superuser privilege is changed to a common role or restored to an unprotected role.

In addition, each command run by a protected role is recorded in a statistics view. This view helps you identify the start of a potential SQL injection attack against the role. The statistics are collected based on the type of SQL injection attack.

Use the administrator role to configure SQL/Protect for a database

1. Modify the parameters in the following code block to enable SQL/Protect.

   set polar_sql_protect.enabled = on; #(The default value is off.)
   set polar_sql_protect.level = passive; #(Valid values: learn, active, and passive. The default value is passive.)

2. 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');

   View the list of protected roles.

   SELECT * FROM sqlprotect.list_protected_users;
   SELECT * FROM sqlprotect.polar_sql_protect;

4. Change the mode in which SQL/Protect works based on your requirements.

   SQL/Protect works in three modes: learn, active, and passive. The default mode is passive. For more information, see Configure the mode in which SQL/Protect works to monitor a protected role.

   • Change the mode in which SQL/Protect works to learn.

     polar_sql_protect.level = learn; #(Valid values: learn, active, and passive. The default value is passive.)

     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. View the learned information about tables used by the test user.

        SELECT * FROM sqlprotect.polar_sql_protect_rel;
        SELECT * FROM sqlprotect.list_protected_rels;

   • Change the mode in which SQL/Protect works to passive.

     polar_sql_protect.level = passive; #(Valid values: learn, active, and passive. The default value is passive.)

     1. Log on to the targetdb database as the test user.
     2. Inject SQL statements.

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

        Note SQL/Protect returns a message that indicates unauthorized SQL statements. However, SQL/Protect does not prevent the SQL statements from being executed.
   • Change the mode in which SQL/Protect works to active.

     polar_sql_protect.level = active; #(Valid values: learn, active, and passive. The default value is passive.)

     1. Log on to the targetdb database as the test user.
     2. Inject SQL statements.

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

        Note SQL/Protect returns a message that indicates unauthorized SQL statements. SQL/Protect also prevents the SQL statements from being executed.

Configure protected roles

Protected roles are stored in the polar_sql_protect table. The database administrator can choose the users and user groups that are protected, and add the users and user groups to the table.

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

  SELECT sqlprotect.protect_role('userA');

• Query the information about the tables that SQL/Protect learned for the protected roles.

  select * from sqlprotect.list_protected_users;
  select * from sqlprotect.polar_sql_protect;

• Invoke the unprotect_role function to remove a protected role.

  SELECT sqlprotect.unprotect_role('userA');

Configure the mode in which SQL/Protect works to monitor a protected role

 polar_sql_protect.level = active;  #Set the mode in which SQL/Protect works to active.

To modify some fields in the polar_sql_protect table to specify what need to be protected for a role, execute the following statement:

targetdb=# \d sqlprotect.polar_sql_protect;
        Table "sqlprotect.polar_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: 
   "polar_sql_protect_pkey" PRIMARY KEY, btree (roleid)

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

Other operations

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

  polar_sql_protect.enabled = off #(The default value of this parameter is off.)
  polar_sql_protect.level = passive #(Valid values: learn, active, and passive. The default value is passive.)

• To view statistics about the SQL statements blocked by SQL/Protect, execute the following statement:

  SELECT * FROM sqlprotect.polar_sql_protect_stats;

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

  SELECT sqlprotect.drop_stats('username');