Re: Using SQL Macro Package Vs "Traditional" package

Hi Sayan

#1 . I don't want to grant SELECT privs to the table to "rptuser".

       Thats the reason , I am encapsulating the SELECT in a package .

#2 . I tried prefix "dbuser"...

   Still the same issue.

On Wed, Nov 6, 2024 at 2:26 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi,
> 1. I would grant read on emp to rptuser
> 2. I'd specified schema in your sql_macro "from dbuser.emp"
>
> On Wed, Nov 6, 2024, 19:14 Zahir Mohideen <zahir.dba_at_gmail.com> 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