all_objects behaving differently when called from a pl/sql function (merged 3) [message #607823] |
Wed, 12 February 2014 06:05 |
|
veeraiahmannem
Messages: 7 Registered: September 2013 Location: Bangalore
|
Junior Member |
|
|
I am getting an issue when i use the all_objects view in database function.
both the below functions are same,only the difference is the count(1) in CHECK_TABLE_NAME2
and object_name in CHECK_TABLE_NAME in the select list
function CHECK_TABLE_NAME2 is retuns value as 1 where as CHECK_TABLE_NAME is returning null value for the same table name of another schema.
CREATE OR REPLACE Function CHECK_TABLE_NAME2
( P_TABLE_NAME in Varchar2 )
RETURN varchar2
IS
V_OBJECT_NAME varchar2(30);
cursor CUR_TABLE_NAME is
SELECT count(1)
FROM all_objects
WHERE OBJECT_NAME=P_TABLE_NAME;
BEGIN
open CUR_TABLE_NAME;
fetch CUR_TABLE_NAME into V_OBJECT_NAME;
if CUR_TABLE_NAME%found then
V_OBJECT_NAME :=1 ;
end if;
close CUR_TABLE_NAME;
RETURN V_OBJECT_NAME;
end;
/
CREATE OR REPLACE Function GFL_COMMON_DATA.CHECK_TABLE_NAME
( P_TABLE_NAME in Varchar2 )
RETURN varchar2
IS
V_OBJECT_NAME varchar2(30);
cursor CUR_TABLE_NAME is
SELECT object_name
FROM all_objects
WHERE OBJECT_NAME=P_TABLE_NAME;
BEGIN
open CUR_TABLE_NAME;
fetch CUR_TABLE_NAME into V_OBJECT_NAME;
if CUR_TABLE_NAME%found then
V_OBJECT_NAME :=1 ;
end if;
close CUR_TABLE_NAME;
RETURN V_OBJECT_NAME;
end;
/
please suggest
Thanks,
Veeru
|
|
|
|
|
Re: strange issue in a pl/sql function (merged 2) [message #608022 is a reply to message #608017] |
Fri, 14 February 2014 06:17 |
|
dariyoosh
Messages: 538 Registered: March 2009 Location: France
|
Senior Member |
|
|
veeraiahmannem wrote on Fri, 14 February 2014 12:41when i give the select access to the table to schema where function resides it is giving the result.
/
I don't really understand the purpose of your function (or maybe I misunderstood your requirement) but your function works for me (Oracle 12c 12.1.0.1.0) if the other schema has the select privilege on the given table.
Here is the test that I did
SQL> connect / as sysdba
Connected.
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL> create user testuser identified by testuser;
User created.
SQL> grant create session to testuser;
Grant succeeded.
SQL> alter user testuser quota unlimited on users;
User altered.
SQL> grant create table to testuser;
Grant succeeded.
SQL> connect testuser/testuser
Connected.
SQL> show user
USER is "TESTUSER"
SQL> create table tmptab as (select rownum rn from all_objects where rownum <= 10);
Table created.
SQL> select * from tmptab;
RN
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> -- So now connecting with another schema which HAS NOT select privilege
SQL> -- on that table
SQL> connect vmtraining
Enter password:
Connected.
SQL> show user;
USER is "VMTRAINING"
SQL> create or replace function check_table_name1(p_table_name in varchar2)
2 return varchar2
3 is
4 v_object_name varchar2(30);
5 begin
6 select
7 t1.object_name
8 into
9 v_object_name
10 from
11 all_objects t1
12 where
13 t1.object_name = p_table_name;
14 --
15 return v_object_name;
16 end check_table_name1;
17 /
Function created.
SQL> show errors;
No errors.
SQL>
SQL> select check_table_name1(p_table_name=>'TMPTAB') from "PUBLIC".dual;
CHECK_TABLE_NAME1(P_TABLE_NAME=>'TMPTAB')
--------------------------------------------------------------------------------
SQL> connect / as sysdba
Connected.
SQL>
SQL> grant select on testuser.tmptab to vmtraining
Grant succeeded.
SQL> connect vmtraining;
Enter password:
Connected.
SQL>
SQL>
SQL> select check_table_name1(p_table_name=>'TMPTAB') from "PUBLIC".dual;
CHECK_TABLE_NAME1(P_TABLE_NAME=>'TMPTAB')
--------------------------------------------------------------------------------
TMPTAB
SQL>
However there is something that I didn't understand in your code
. . .
return V_OBJECT_NAME;
DBMS_OUTPUT.PUT_LINE(V_OBJECT_NAME);
END check_table_name1;
/
What does a sys.dbms_output.put_line statement after a return statement suppose to mean?
[Updated on: Fri, 14 February 2014 07:49] Report message to a moderator
|
|
|
Re: strange issue in a pl/sql function (merged 2) [message #608043 is a reply to message #608017] |
Fri, 14 February 2014 08:41 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
veeraiahmannem wrote on Fri, 14 February 2014 06:41i am getting a strange issue /
By default stored objects are created with definer rights which means they run under stored object schema and ignores role based privileges. So it doesn't matter who runs your function - current user will be function owner not function caller. And ALL_OBJECTS describes all objects accessible to the current user, function will always see same list of objects - public objects, objects owned by function owner and objects function owner has privileges granted directly. So, if you want function to look at objects owned by function caller and honor role based privileges, you need to create function with invoker rights (AUTHID CURRENT_USER), not definer rights.
SY.
|
|
|
|