Dynamic data masking

[penberg]

SQL Server, for example, has a neat feature, which is useful for data protection:

https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16

What you can do is attach a "masking policy" to a column in a table. For example, email addresses (that are personal information) could be masked by having a MASKED WITH augmentation on a SQL table:

CREATE TABLE users(
    email TEXT MASKED WITH (FUNCTION = 'email()') NOT NULL,
);

Queries to the table would mask out the email address with an anonymized "anon@example.com" version, for example. However, users could still see their own email addresses with another SQL extension that SQL Server has:

EXECUTE AS USER = 'penberg';  

Dynamic data masking is particularly useful in scenarios where database is replicated and you want the extra layer of protection while still serving queries. Please note that there's bound to be some overlap with LumoSQL security features here.

[psarna]

SQLite already has a notion of virtual tables:

• https://www.sqlite.org/vtab.html
• https://www.sqlite.org/c3ref/module.html

It could also be used to approximate dynamic data masking by wrapping a regular table and overriding the "Select" method. The module interface also accepts a database connection as a parameter, and that could store the user execution context. That could even be our internal implementation, with MASKED WITH being just syntactic sugar over a virtual table.