Blacklisting specific functions for specific columns can already be done

rule denied_ssn_select deny columns ssn on_queries select

rule denied_functions deny function upper lower concat

users appuser@% match all rules  denied_functions  denied_ssn_select

According to comment on MXS-1301 by Markus(https://jira.mariadb.org/browse/MXS-1301?focusedCommentId=98354&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-98354) The rule that is provided in the above comment - not only blocks function on the specific column(i.e. upper function on ssn column), but also block if ssn was without function and some other column was using one of the denied function.

i.e. above rule will block "select upper(ssn), name from person" as well as "select upper(name), ssn from person".

This current Jira is requesting database firewall filter to say that I want to block a specific or all the functions on column xyz only. e.g. if I have a rule to block function upper on column ssn, then the function upper could be used on column name and query will be allowed.

Hence the reason to open this Jira.

This is not possible currently. From the query classifier the firewall filter can get the information what functions are used in a statement, but not what columns were used as arguments to those functions.

Thus, it is possible to white-list or black-list a function, but not in on conjunction with a particular column.

If the query classifier were to expose that information, then the firewall filter configuration needs to be extended.

johan.wikmanCan you please provide estimate for doing this ?

This is somewhat complex from many perspectives. Consider e.g. this:

with recursive 

ancestors(id,name,dob)

as

(

  with 

  father(child_id,id,name,dob)

  as

  (

    select folks.id, f.id, f.name, f.dob

      from folks, folks f

        where folks.father=f.id 

  ),

  mother(child_id,id,name,dob)

  as

  (

    select folks.id, m.id, m.name, m.dob

      from folks, folks m

        where folks.mother=m.id 

  )  

  select folks.id, folks.name, folks.dob

    from folks

      where name='Me'

  union 

  select f.id, f.name, f.dob

    from ancestors a, father f

      where f.child_id=a.id

  union 

  select m.id, m.name, m.dob

    from ancestors a, mother m

      where m.child_id=a.id

)

select ancestors.name, ancestors.dob from ancestors;

There, ancestors.name, m.name, f.name all refer to the actual column folks.name. All that needs to be tracked and be possible to express in the query classifier interface.

Further, any of the queries used to produce a intermediate resultset with a name, say father could use some function for generating those columns, instead of just picking some real columns. It's not self-evident how that should be expressed in the query classifier API.

As a first step - let us get function masking per column captured for simple queries.

For complex queries such as above in your last comment, stored procedure can be used by DBA, and then subsequently not allow those users with masking rules to have permission to create stored procedures.

Added support for the following syntax:

rule <name> deny function <list of functions> columns <list of columns>

The rule matches if one the columns uses one of the functions.