Table of Contents Previous Next


4 Security : 4.1 Protecting Against SQL Injection Attacks : 4.1.3 Common Maintenance Operations

protect_role('rolename')
unprotect_role('rolename')
Note: The variation of the function using the OID is useful if you remove the role using the DROP ROLE or DROP USER SQL statement before removing the role from the protected roles list. If a query on a SQL/Protect relation returns a value such as unknown (OID=16458) for the user name, use the unprotect_role(roleoid) form of the function to remove the entry for the deleted role from the protected roles list.
The following is an example of the unprotect_role function:
Change the Boolean value for the column in edb_sql_protect corresponding to the type of SQL injection attack for which protection of a role is to be disabled or enabled.
Be sure to qualify the following columns in your WHERE clause of the statement that updates edb_sql_protect:
dbid. OID of the database for which you are making the change
roleid. OID of the role for which you are changing the Boolean settings
You can verify the change was made by querying edb_sql_protect or list_protected_users. In the following query note that column allow_utility_cmds now contains t.
Delete its entry from the edb_sql_protect_rel table using any of the following functions:
unprotect_rel('rolename', 'relname')
unprotect_rel('rolename', 'schema', 'relname')
unprotect_rel(roleoid, reloid)
If the relation given by relname is not in your current search path, specify the relation’s schema using the second function format.
The following example illustrates the removal of the public.emp relation from the protected relations list of the role appuser.
SQL/Protect will now issue a warning or completely block access (depending upon the setting of edb_sql_protect.level) whenever the role attempts to utilize that relation.
You can delete statistics from view edb_sql_protect_stats using either of the two following functions:
drop_stats('rolename')
drop_stats(roleoid)
Note: The variation of the function using the OID is useful if you remove the role using the DROP ROLE or DROP USER SQL statement before deleting the role’s statistics using drop_stats('rolename'). If a query on edb_sql_protect_stats returns a value such as unknown (OID=16458) for the user name, use the drop_stats(roleoid) form of the function to remove the deleted role’s statistics from edb_sql_protect_stats.
The following is an example of using the drop_stats(roleoid) form of the function when a role is dropped before deleting its statistics:
You can delete offending queries from view edb_sql_protect_queries using either of the two following functions:
drop_queries('rolename')
Note: The variation of the function using the OID is useful if you remove the role using the DROP ROLE or DROP USER SQL statement before deleting the role’s offending queries using drop_queries('rolename'). If a query on edb_sql_protect_queries returns a value such as unknown (OID=16454) for the user name, use the drop_queries(roleoid) form of the function to remove the deleted role’s offending queries from edb_sql_protect_queries.
The following is an example of using the drop_queries(roleoid) form of the function when a role is dropped before deleting its queries:
Step 1: Set the configuration parameter edb_sql_protect.enabled to off in the postgresql.conf file.
The entry for edb_sql_protect.enabled should look like the following:
Step 2: Reload the configuration file as shown in Step 2 of Section 4.1.2.2.1.
Step 1: Set the configuration parameter edb_sql_protect.enabled to on in the postgresql.conf file.
The entry for edb_sql_protect.enabled should look like the following:
Step 2: Reload the configuration file as shown in Step 2 of Section 4.1.2.2.1.

4 Security : 4.1 Protecting Against SQL Injection Attacks : 4.1.3 Common Maintenance Operations

Table of Contents Previous Next