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?
That is what you can read in Oracle documentation about SQL_Macro Clause
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.
Let’s test it. I’ll create two users: definer_user and invoker_user
SQL> drop user if exists invoker_user cascade;
SQL>
SQL> create user if not exists invoker_user identified by oracle;
SQL>
SQL> grant create session to invoker_user;
SQL>
SQL>
SQL> drop user if exists definer_user cascade;
SQL>
SQL> create user if not exists definer_user identified by oracle quota unlimited on users;
SQL>
SQL> grant create session, create table, create procedure to definer_user;
SQL>
As you can see, the invoker_user has no privileges except create session. Now let’s create a table as definer_user 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.
connect definer_user/...@localhost:1522/freepdb1
create table col_config (column_name varchar2(128 byte));
insert into col_config (column_name) values ('ENAME');
-- a sql macro function
create or replace function get_column_macro return varchar2
sql_macro(scalar) as
v_column_name dbms_id;
begin
select column_name into v_column_name from col_config;
return ''''||v_column_name||'''';
end;
/
-- a normal but invoker-rights function
create or replace function get_column_no_macro return varchar2
authid current_user as
v_column_name dbms_id;
begin
select column_name into v_column_name from col_config;
return ''''||v_column_name||'''';
end;
/
grant execute on get_column_macro to invoker_user;
grant execute on get_column_no_macro to invoker_user;
Now, the invoker_user only has the right to execute both functions, but no privileges on the table col_config which belongs definer_user. 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:
SQL> connect invoker_user/...@localhost:1522/freepdb1
SQL>
SQL> select definer_user.get_column_no_macro;
Error starting at line : 1 in command -
select definer_user.get_column_no_macro
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00942: table or view does not exist
ORA-06512: at "DEFINER_USER.GET_COLUMN_NO_MACRO", line 5
00942. 00000 - "table or view%s does not exist"
*Cause: The specified table or view did not exist, or a synonym
pointed to a table or view that did not exist.
To find existing user tables and views, query the
ALL_TABLES and ALL_VIEWS data dictionary views. Certain
privileges may be required to access the table. If an
application returned this message, then the table that the
application tried to access did not exist in the database, or
the application did not have access to it.
*Action: Check each of the following
- The spelling of the table or view name is correct.
- The referenced table or view name does exist.
- The synonym points to an existing table or view.
More Details :
https://docs.oracle.com/error-help/db/ora-00942/
https://docs.oracle.com/error-help/db/ora-06512/
SQL>
SQL> select definer_user.get_column_macro;
GET_COLUMN_MACRO
___________________
ENAME
SQL>
This tells me, my SQL macro was indeed executed with definer rights!
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.” 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? 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?
Maybe I am missing something or it is just a bug or a documentation bug or both . All comments are welcome!
UPDATE:
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: