• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5456
  • Last Modified:

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.
0
setushah_3000
Asked:
setushah_3000
  • 3
  • 3
  • 2
  • +4
2 Solutions
 
GGuzdziolCommented:
>> 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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
schwertnerCommented:
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
Technology Partners: 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!

 
setushah_3000Author 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.
0
 
schwertnerCommented:
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
 
setushah_3000Author 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.
0
 
GGuzdziolCommented:
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
 
GGuzdziolCommented:
Another option could be switching to dynamic sql - but I wouldn't recommend it...
0
 
schwertnerCommented:
connect as the user who owns the object 'tbl_schema2 '

connect user/password@some_db
grant select on tbl_schema2 to schema1;
0
 
hyderabadbluesCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now