{"id":1866,"date":"2023-11-06T16:38:14","date_gmt":"2023-11-06T14:38:14","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1866"},"modified":"2023-11-29T23:17:29","modified_gmt":"2023-11-29T21:17:29","slug":"sql-macros-and-invoker-rights","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/sql-macros-and-invoker-rights\/","title":{"rendered":"SQL-Macros and Invoker Rights"},"content":{"rendered":"\n

This note is meant more as an illustrative example for a question, because the behavior seems strange to me. If I want to do something in my SQL macro that needs additional privileges, how it will run then, when other users will call my SQL macro? <\/p>\n\n\n\n\n\n\n\n

That is what you can read in Oracle documentation about SQL_Macro Clause<\/a><\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

<\/p>\n\n\n\n

How I read this: I cannot specify AUTHID CURRENT_USER, but it will anyway run with invoker rights. It is just supposed to always do so.<\/p>\n\n\n\n

Let’s test it. I’ll create two users: definer_user<\/strong> and invoker_user<\/strong><\/p>\n\n\n

\nSQL> drop user if exists invoker_user cascade;\nSQL>\nSQL> create user if not exists invoker_user identified by oracle;\nSQL>\nSQL> grant create session to invoker_user;\nSQL>\nSQL>\nSQL> drop user if exists definer_user cascade;\nSQL>\nSQL> create user if not exists definer_user identified by oracle quota unlimited on users;\nSQL>\nSQL> grant create session, create table, create procedure to definer_user;\nSQL>\n<\/pre><\/div>\n\n\n
As you can see, the invoker_user<\/strong> has no privileges except create session<\/strong>. Now let’s create a table as definer_user<\/strong> and use it inside a SQL macro. I will also do the same with a normal function (no SQL macro) but defined as invoker rights function (AUTHID CURRENT_USER). I expect the behavior to be more or less the same.<\/p>\n\n\n
\nconnect definer_user\/...@localhost:1522\/freepdb1\n\ncreate table col_config (column_name varchar2(128 byte));\n\ninsert into col_config (column_name) values ('ENAME');\n\n-- a sql macro function\ncreate or replace function get_column_macro return varchar2 \nsql_macro(scalar) as\n    v_column_name dbms_id;\nbegin\n    select column_name into v_column_name from col_config;\n    return  ''''||v_column_name||'''';\nend;\n\/\n\n-- a normal but invoker-rights function\ncreate or replace function get_column_no_macro return varchar2 \nauthid current_user as\n    v_column_name dbms_id;\nbegin\n    select column_name into v_column_name from col_config;\n    return  ''''||v_column_name||'''';\nend;\n\/\n\ngrant execute on get_column_macro to invoker_user;\n\ngrant execute on get_column_no_macro to invoker_user;\n<\/pre><\/div>\n\n\n
Now, the invoker_user<\/strong> only has the right to execute both functions, but no privileges on the table col_config<\/strong> which belongs definer_user<\/strong>. That’s why I expect a “table or view doesn’t exist” error while executing both functions as invoker_user. But see what happens: only “no-macro” function behaves so! Our SQL macro was executed without an error:<\/p>\n\n\n
\nSQL> connect invoker_user\/...@localhost:1522\/freepdb1\nSQL>\nSQL> select definer_user.get_column_no_macro;\n\nError starting at line : 1 in command -\nselect definer_user.get_column_no_macro\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00942: table or view does not exist\nORA-06512: at "DEFINER_USER.GET_COLUMN_NO_MACRO", line 5\n00942. 00000 -  "table or view%s does not exist"\n*Cause:    The specified table or view did not exist, or a synonym\n           pointed to a table or view that did not exist.\n           To find existing user tables and views, query the\n           ALL_TABLES and ALL_VIEWS data dictionary views. Certain\n           privileges may be required to access the table. If an\n           application returned this message, then the table that the\n           application tried to access did not exist in the database, or\n           the application did not have access to it.\n*Action:   Check each of the following\n           - The spelling of the table or view name is correct.\n           - The referenced table or view name does exist.\n           - The synonym points to an existing table or view.\n\nMore Details :\nhttps:\/\/docs.oracle.com\/error-help\/db\/ora-00942\/\nhttps:\/\/docs.oracle.com\/error-help\/db\/ora-06512\/\nSQL>\nSQL> select definer_user.get_column_macro;\n\nGET_COLUMN_MACRO\n___________________\nENAME\nSQL>\n<\/pre><\/div>\n\n\n
This tells me, my SQL macro was indeed executed with definer rights!<\/p>\n\n\n\n
Another thing I don’t really understood is the second sentence from the above documentation quote: “The SQL macro owner must grant inherit privileges to the invoking function<\/em>.”  As far as I know INHERIT PRIVILEGES is about granting it from high-privileged user to a user who creates an invoker-right program units. But in this case it makes no sense to me… And what is invoking function<\/strong>? Even if knew that and wanted to grant INHERIT PRIVILEGES to this function, how can i do this? I thought, it is only possible to grant roles to program units, not system privileges? <\/p>\n\n\n\n
Maybe I am missing something or it is just a bug or a documentation bug or both . All comments are welcome!<\/p>\n\n\n\n
UPDATE:<\/h2>\n\n\n\n
Thanks, Chris Saxon for the answer. The behavior we could see above is indeed intended. The body of a SQL Macro runs with definer privileges<\/strong>:<\/p>\n\n\n\n
\n
I've checked with development – the macro body executes with definer's privileges to construct the return text

The expression it returns runs with invoker's privs

We'll get the docs updated to reflect this<\/p>— Chris Saxon (@ChrisRSaxon) November 20, 2023<\/a><\/blockquote>