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.
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.
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
'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.te st
SQL>grant execute on test_bolinf_api.test to schema2
SQL>connect schema2/password
SQL>execute schema1.test_bolinf_api.te
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_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"
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.te st
SQL>
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.te
SQL>
ASKER
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.t est
but how do I remove the compiler errors while compiling test_schema1_api : TABLE OR VIEW tbl_schema2 DOES NOT EXIST.
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.t
but how do I remove the compiler errors while compiling test_schema1_api : TABLE OR VIEW tbl_schema2 DOES NOT EXIST.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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
the tbl_schema2 tables. Thats the privilege missing i suppose
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
>> 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?