Re: Using SQL Macro Package Vs "Traditional" package

Mark

Typically , I don't allow direct access to the underlying tables/ views to appschema.
I usually have a package and grant execute on that package. Tha package ( mostly) will return a ref cursor.

I was planning to use Macros , as it might be easy for the appdev team. I did not read the documentation thoroughly enough. :-(

Zahir Mohideen

On Wed, Nov 6, 2024 at 6:30 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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