Solved

AUTHID CURRENT_USER issue...

Posted on 2006-11-03
13
5,426 Views
Last Modified: 2010-05-18
Hi,
 I was trying out a use of AUTHID CURRENT_USER wherein I encountered the following issue:

Schema1:

CREATE OR REPLACE PACKAGE test_bolinf_api AUTHID CURRENT_USER AS
 PROCEDURE test ;

END test_bolinf_api;

CREATE OR REPLACE PACKAGE BODY test_schema1_api AS
 PROCEDURE test  AS
 c AS NUMBER;
 begin
 
  SELECT count(*)
  INTO c
  FROM tbl_schema2  WHERE ROWNUM < 5;

  dbms_output.put_line ('');
 END test;
END test_schema1_api;

EXECUTE priviledge on test_schema1_api has been given to schema2

Schema2:
tbl_schema2

Now, since AUTHID CURRENT_USER will allow the package to be executed with the priviledges of the CALLING USER, so if I call
schema1.test_schema1.api;

It should execute, but it does not. I guess I have missed a step somewhere.

2ndly how does the pkg compile with AUTHID CURRENT_USER construct ?
There is a pkg which has to access tables in other schema of which it does NOT have access to, but the USER/SCHEMA calling the pkg has access to those tables.

Sounds like fun doesn't it.
Cheers 2 all.
0
Comment
Question by:setushah_3000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +4
13 Comments
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17865018
>> PACKAGE test_bolinf_api
>> PACKAGE BODY test_schema1_api

something wrong here isn't it?

>> It should execute, but it does not.

So what happens? Error message is shown?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17865019
did u grant the privileges on tbl_schema2  table to schema 1 from schema 2 ?

'It should execute, but it does not.' - what does this mean ? did it give some errors ? if so what are they ?

Thanks
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17865149
SQL>connect schema1/password
SQL>grant execute on test_bolinf_api.test to schema2
SQL>connect schema2/password
SQL>execute schema1.test_bolinf_api.test

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:setushah_3000
ID: 17865163
1. Correction...

CREATE OR REPLACE PACKAGE test_schema1_api AUTHID CURRENT_USER AS
 PROCEDURE test ;

END test_schema1_api;

CREATE OR REPLACE PACKAGE BODY test_schema1_api AS
 PROCEDURE test  AS
 c AS NUMBER;
 begin
 
  SELECT count(*)
  INTO c
  FROM tbl_schema2  WHERE ROWNUM < 5;

  dbms_output.put_line ('');
 END test;
END test_schema1_api;


2. I have not granted access of tbl_schema2 to schema1
- Reason: AUTH_ID CURRENT_USER execute the pkg with its own priviledge. So this should not be a requirement.

3. 'It should execute, but it does not'
- with the above setup invoking schema1.test_schema1_api from schema1 ... gives the following error
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "schema1.test_schema1_api" has errors
ORA-06508: PL/SQL: could not find program unit being called

Reason is quite apt that the test_schema1_api is created in schema1 but with compile errors, that is...
During compiling test_schema1_api  table tbl_schema2 does not EXIST in schema1.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17865166
CREATE OR REPLACE PACKAGE test_bolinf_api AUTHID CURRENT_USER AS
 PROCEDURE test (c  OUT NUMBER);

END test_bolinf_api;

CREATE OR REPLACE PACKAGE BODY test_schema1_api AS
 PROCEDURE test  ( c  OUT NUMBER) IS
 begin
  dbms_output.enable(5000);
  SELECT count(*)
  INTO c
  FROM tbl_schema2  WHERE ROWNUM < 5;

  dbms_output.put_line (c);
 END test;
END test_schema1_api;


......
SQL>set erveroutput on
SQL>execute schema1.test_bolinf_api.test
SQL>
0
 
LVL 1

Author Comment

by:setushah_3000
ID: 17865173
Hi schwertner,
 Tried this...
SQL>connect schema1/password
SQL>grant execute on test_schema1_api.test to schema2
SQL>connect schema2/password
SQL>execute schema1.test_schema1_api.test

but how do I remove the compiler errors while compiling test_schema1_api : TABLE OR VIEW tbl_schema2 DOES NOT EXIST.
0
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 250 total points
ID: 17865197
create such table in schema where this package is created (I guess schema1). or grant access to this in another schema and create synonym for it.
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17865200
Another option could be switching to dynamic sql - but I wouldn't recommend it...
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17865291
connect as the user who owns the object 'tbl_schema2 '

connect user/password@some_db
grant select on tbl_schema2 to schema1;
0
 

Expert Comment

by:hyderabadblues
ID: 17866578
search if tbl_schema1 has been given the select previliges on the tbl_schema2, as tbl_schema1 package is using
the tbl_schema2 tables. Thats the privilege missing i suppose
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 250 total points
ID: 17870041
You cannot compile a PL\SQL procedure (or package) that directly references a table that does not exist at compile time!

If you want to use AUTHID CURRENT_USER, then you need schemas that contain identical objects.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20296196
Forced accept.

Computer101
EE Admin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question