Grant permission to execute one specific query

[victormassy]

I would like to grant permission to the reader to execute one specific query (ideally with a parameter) and nothing else. From what I read in MariaDB documentation, with GRANT SELECT (column_list), I need to give full access to all columns used by the query. For confidentiality reasons, this is not possible.

The stored procedures mention in the documentation could solve this issue by allowing the user to perform actions he wouldn't be able otherwise. Is it possible to use stored procedure in edgelessDB ? If yes, can I create a procedure in the manifest or should it be created by an "admin" user ? If not, do you have another solution ?

Thank you.

[thomasten]

Yes, you can create a stored procedure in the manifest like this (modified from the demo manifest):

{
    "sql": [
        "CREATE USER reader REQUIRE ISSUER '/CN=Owner CA' SUBJECT '/CN=Reader'",
        "CREATE USER writer REQUIRE ISSUER '/CN=Owner CA' SUBJECT '/CN=Writer'",
        "CREATE USER countGreaterThanUser ACCOUNT LOCK",
        "CREATE DATABASE test",
        "CREATE TABLE test.data (i INT)",
        "CREATE DEFINER=countGreaterThanUser PROCEDURE test.countGreaterThan(x INT) BEGIN SELECT COUNT(*) FROM data WHERE i > x; END",
        "GRANT EXECUTE ON PROCEDURE test.countGreaterThan TO reader",
        "GRANT INSERT ON test.data TO writer",
        "GRANT EXECUTE ON PROCEDURE test.countGreaterThan TO countGreaterThanUser",
        "GRANT SELECT ON test.data TO countGreaterThanUser"
    ]
}

• You need an additional user that has the required permissions to execute the statements in the procedure.
• You need to set this user as the DEFINER of the procedure.
• Due to the JSON format of the manifest, you need to write your procedure in one line.