Re: Using SQL Macro Package Vs "Traditional" package

Hi Zahir,

the result of the SQL Macro will run with invoker's rights.

 From the doco:

The AUTHID property cannot be specified. When a SQL macro is invoked, the function body executes with definer's rights to construct the text to return. The resulting expression is evaluated with invoker's rights. The SQL macro owner must grant inherit privileges to the invoking function.

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/SQL_MACRO_clause.html

Kind regards,

Arian

On 2024-11-06 20:13, Zahir Mohideen wrote:

> I am trying to use Macro in a package and granted 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 ?
>
> Note: I posted this question to asktom website this morning as well.
>
> 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

--
http://www.freelists.org/webpage/oracle-l