RE: Using SQL Macro Package Vs "Traditional" package

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 6 Nov 2024 18:29:59 -0500
Message-ID: <18f401db30a3$cc756610$65603230$_at_rsiz.com>

What you are trying to do is a feature of stored procedures and functions. SQL Macro is just yanking the text into the place you put it. It is for convenience, not security.  

So what is your motivation for trying to do it via macro inclusion?  

mwf  

PS: Recommended reading is finding the collection of essays about using PL/SQL by Bryn Llewellyn and Charles Whetherell.    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zahir Mohideen Sent: Wednesday, November 06, 2024 2:32 PM To: Sayan Malakshinov
Cc: oracle-l_at_freelists.org
Subject: 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

Received on Thu Nov 07 2024 - 00:29:59 CET