Solved

AUTHID CURRENT_USER issue...

Posted on 2006-11-03
13
5,377 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
  • 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 47

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
 
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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 47

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now