We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

AUTHID CURRENT_USER issue...

setushah_3000
on
Medium Priority
5,681 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.
Comment
Watch Question

>> 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?
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Top Expert 2008

Commented:
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

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2008

Commented:
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>

Author

Commented:
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.
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Another option could be switching to dynamic sql - but I wouldn't recommend it...
CERTIFIED EXPERT
Top Expert 2008

Commented:
connect as the user who owns the object 'tbl_schema2 '

connect user/password@some_db
grant select on tbl_schema2 to schema1;
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
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
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.
Forced accept.

Computer101
EE Admin
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.