Link to home
Start Free TrialLog in
Avatar of setushah_3000
setushah_3000

asked on

AUTHID CURRENT_USER issue...

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.
Avatar of GGuzdziol
GGuzdziol
Flag of Luxembourg image

>> 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?
Avatar of Naveen Kumar
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
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

Avatar of setushah_3000
setushah_3000

ASKER

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.
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>
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.
ASKER CERTIFIED SOLUTION
Avatar of GGuzdziol
GGuzdziol
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Another option could be switching to dynamic sql - but I wouldn't recommend it...
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forced accept.

Computer101
EE Admin