I am trying to use Macro in a package and grant execute privs to another user ( in my case , rptuser).
DBUSER has a table , regular package , SQL macro package.
Both are with DEFINER rights .
Also , learn that , we can't have a SQL macro package with invoker rights.
When I execute the "regular" package from another user, I get the results as expected.
When I execute the "SQL Macro" package from another , I get "invalid permissions" ( ORA-00942).
Here is my test case below.
Am I misunderstanding about the usage of "SQL Macro" package ?
DB Version
----------
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
DBUSER
-------
Here , I am creating tables and packages under "DBUSER".
create table emp ( eid int , ename varchar2(50) )
insert into emp values ( 100 , 'Zahir Mohideen') ;
insert into emp values ( 101 , 'Abdul Hameed') ;
commit;
create or replace package pkg_emp
as
procedure listemp( p_eid int , rfc_emp OUT SYS_REFCURSOR );
end pkg_emp;
/
CREATE OR REPLACE PACKAGE BODY pkg_emp AS
PROCEDURE listemp (
p_eid INT,
rfc_emp OUT SYS_REFCURSOR
) AS
BEGIN
OPEN rfc_emp FOR SELECT
ename
FROM
dbuser.emp
WHERE
eid = p_eid;
END listemp;
END pkg_emp;
/
create or replace package pkg_emp_mac
as
FUNCTION listemp ( p_eid int ) RETURN VARCHAR2 SQL_MACRO ;
end pkg_emp_mac;
/
CREATE OR REPLACE PACKAGE BODY pkg_emp_mac AS
FUNCTION listemp ( p_eid int ) RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
RETURN q'{
Select ename from emp where eid = p_eid
}';
END;
END pkg_emp_mac
;
/
grant execute on pkg_emp_mac to rptuser;
grant execute on pkg_emp to rptuser;
Here , I am executing package calls as "RPTUSER"
SQL> show user
USER is "RPTUSER"
SQL> var x refcursor;
SQL> exec dbuser.pkg_emp.listemp( 100 , :x);
PL/SQL procedure successfully completed.
SQL> print :x;
ENAME
--------------------------------------------------
Zahir Mohideen
SQL> select * from dbuser.pkg_emp_mac.listemp(100);
select * from dbuser.pkg_emp_mac.listemp(100)
*
ERROR at line 1:
ORA-00942: table or view does not exist
November 07, 2024 - 1:27 pm UTC
The text returned by a SQL macro becomes part of the parent SQL statement.
So:
select * from dbuser.pkg_emp_mac.listemp(100);
is equivalent to
Select ename from dbuser.emp where eid = 100
To call the macro, RPTUSER needs the same privileges as it would to run the underlying query - so READ/SELECT on dbuser.emp in this example