Re: Using SQL Macro Package Vs "Traditional" package

Thanks , Arian for the link.
I am confused about the following statement. What does this mean? <quote>
The SQL macro owner must grant inherit privileges to the invoking function. </quote>

On Wed, Nov 6, 2024 at 3:37 PM Arian Stijf <arian_at_stijf.com> wrote:

> 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