Executing procedure without prefixing schema name [message #339256] |
Thu, 07 August 2008 02:52  |
hparulkar
Messages: 15 Registered: August 2008 Location: India
|
Junior Member |
|
|
I have created a procedure 123 for ABC user.
created public synonym for procedure 123 and given grant execute abc.123to xyz.
I want xyz can execute procedure 123 with prefixing abc (schema name)
If the above implmentation is wrong , please let me know how can you implement to execute the procedure without prefixing schema name.
|
|
|
|
Re: Executing procedure without prefixing schema name [message #339259 is a reply to message #339256] |
Thu, 07 August 2008 02:58   |
hparulkar
Messages: 15 Registered: August 2008 Location: India
|
Junior Member |
|
|
schema owner= abc
procedure=truncate_table
created synonym truncate_table
granted execute permission on truncate_table to 123.
DECLARE
TABLE_NAME VARCHAR2(200);
STORAGE_TYPE VARCHAR2(200);
BEGIN
TABLE_NAME := 'PLAN_TABLE';
STORAGE_TYPE := NULL;
TRUNCATE_TABLE ( TABLE_NAME, STORAGE_TYPE );
COMMIT;
END;
Error:
TRUNCATE_TABLE ( TABLE_NAME, STORAGE_TYPE );
*
ERROR at line 9:
ORA-06550: line 9, column 3:
PLS-00201: identifier 'TRUNCATE_TABLE' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
|
|
|
|
Re: Executing procedure without prefixing schema name [message #339263 is a reply to message #339256] |
Thu, 07 August 2008 03:10   |
hparulkar
Messages: 15 Registered: August 2008 Location: India
|
Junior Member |
|
|
output .. still gives error..
SQL> VARIABLE TABLE_NAME VARCHAR2(200);
SQL> VARIABLE STORAGE_TYPE VARCHAR2(200);
SQL> EXEC TRUNCATE_TABLE('PLAN_TABLE', NULL);
BEGIN TRUNCATE_TABLE('PLAN_TABLE', NULL); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_TABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
|
|
|
Re: Executing procedure without prefixing schema name [message #339264 is a reply to message #339259] |
Thu, 07 August 2008 03:12   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I meant show us the SQL that you used to create the synonym and grant the privileges - preferable the actual SQL, with the results of running it.
It's clear you're not showing us exactly what's going on, because 123 is not a valid username.
|
|
|
Re: Executing procedure without prefixing schema name [message #339265 is a reply to message #339256] |
Thu, 07 August 2008 03:21   |
hparulkar
Messages: 15 Registered: August 2008 Location: India
|
Junior Member |
|
|
ok..
Schema owner =bobip
application user=bobippuser
SQL> CREATE OR REPLACE PUBLIC SYNONYM TRUNCATE_TABLE FOR BOBIP. TRUNCATE_TABLE;
Synonym created.
SQL> grant execute on BOBIP.TRUNCATE_TABLE to BOBIPUSER;
Grant succeeded.
SQL> sho user
USER is "BOBIPUSER"
SQL> EXEC TRUNCATE_TABLE('PLAN_TABLE', NULL);
BEGIN TRUNCATE_TABLE('PLAN_TABLE', NULL); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_TABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
|
|
|
|
|
|
|
|
|
|
Re: Executing procedure without prefixing schema name [message #339314 is a reply to message #339256] |
Thu, 07 August 2008 04:49   |
hparulkar
Messages: 15 Registered: August 2008 Location: India
|
Junior Member |
|
|
already posted. Still again posting
SQL> CREATE OR REPLACE PUBLIC SYNONYM TRUNCATE_TABLE FOR BOBIP. TRUNCATE_TABLE;
Synonym created.
SQL> grant execute on BOBIP.TRUNCATE_TABLE to BOBIPUSER;
Grant succeeded.
SQL> sho user
USER is "BOBIPUSER"
SQL> EXEC TRUNCATE_TABLE('PLAN_TABLE', NULL);
BEGIN TRUNCATE_TABLE('PLAN_TABLE', NULL); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_TABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
|
|
|
|
|
|
|
Re: Executing procedure without prefixing schema name [message #339321 is a reply to message #339320] |
Thu, 07 August 2008 05:06   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You are doing something that you're not telling us about.
The idea works perfectly, and I bet that if you run this script as a DBA, you'll see that it works for you too:Scriptdrop user test_user_1 cascade;
drop user test_user_2 cascade;
create user test_user_1 identified by test_user_1;
grant connect,resource to test_user_1;
grant create public synonym to test_user_1;
create user test_user_2 identified by test_user_2;
grant connect,resource to test_user_2;
connect test_user_1/test_user_1
create procedure test_proc (p_var in varchar2) as begin null; end;
/
create public synonym test_proc for test_user_1.test_proc;
grant execute on test_proc to test_user_2;
connect test_user_2/test_user_2;
begin
test_proc('A');
end;
/
ResultsSQL> drop user test_user_1 cascade;
User dropped.
SQL> drop user test_user_2 cascade;
User dropped.
SQL> create user test_user_1 identified by test_user_1;
User created.
SQL> grant connect,resource to test_user_1;
Grant succeeded.
SQL> grant create public synonym to test_user_1;
Grant succeeded.
SQL> create user test_user_2 identified by test_user_2;
User created.
SQL> grant connect,resource to test_user_2;
Grant succeeded.
SQL> connect test_user_1/test_user_1
Connected.
SQL> create procedure test_proc (p_var in varchar2) as begin null; end;
2 /
Procedure created.
SQL> create public synonym test_proc for test_user_1.test_proc;
Synonym created.
SQL> grant execute on test_proc to test_user_2;
Grant succeeded.
SQL> connect test_user_2/test_user_2;
Connected.
SQL> begin
2 test_proc('A');
3 end;
4 /
PL/SQL procedure successfully completed.
What user were you connected as when you granted Execute on Truncate_Table?
|
|
|
Re: Executing procedure without prefixing schema name [message #339327 is a reply to message #339256] |
Thu, 07 August 2008 05:14   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
SQL> CREATE OR REPLACE PUBLIC SYNONYM TRUNCATE_TABLE FOR BOBIP. TRUNCATE_TABLE;
Synonym created.
SQL> grant execute on BOBIP.TRUNCATE_TABLE to BOBIPUSER;
Grant succeeded.
SQL> sho user
USER is "BOBIPUSER"
SQL> EXEC TRUNCATE_TABLE('PLAN_TABLE', NULL);
BEGIN TRUNCATE_TABLE('PLAN_TABLE', NULL); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_TABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Here OP is trying all as "BOBIPUSER"
|
|
|
|
Re: Executing procedure without prefixing schema name [message #339343 is a reply to message #339327] |
Thu, 07 August 2008 05:38   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Connected as a a DBA user, can you run:SELECT object_name,object_type,owner
FROM dba_objects
WHERE object_name = 'TRUNCATE_TABLE';
Olivia: The only way that the BOBIPUSER could grant itself Execute privileges on BOBIP.TRUNCATE table would be if it already had either Execute Any Procedure, or had Execute granted to it with the 'With Grant Option' specified.
It's things like that that convince me that convince me that we're not being shown everything that is happening.
|
|
|
|
Re: Executing procedure without prefixing schema name [message #339367 is a reply to message #339256] |
Thu, 07 August 2008 06:40   |
hparulkar
Messages: 15 Registered: August 2008 Location: India
|
Junior Member |
|
|
Thanks a ton JRowbottom. I got the issue.
GRANT given to BOBIPUSER on BOBIP.TRUNCATE_TABLE was given by sys user.
surprisegnly, even though sys is owner the grant didn't work.
As suggested by you I gave the grant from BOBIP(actual owner) and I am glad it worked.
Learnt new thing today. Thanks a lot.
|
|
|
Re: Executing procedure without prefixing schema name [message #339371 is a reply to message #339367] |
Thu, 07 August 2008 06:48  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
As a general rule, do nothing as SYS that you don't have to be SYS to do.
This is why I wanted you to show us the exact SQL that you'd used - if you had done, we'd have seen this a lot earlier. As it was, the composite SQL that you posted made it look like you were logged on as BOBIPUSER when the synonym and grant were being made, which just couldn't have been true.
I can't think of a reason why the GRANT from SYS wouldn't have worked, but without seeing exactly what you did, theres no way of telling.
|
|
|