Public Synonym - Insufficient privileges

Hi all,

I am trying to create a public synonym for my tables. When I write the command 'CREATE PUBLIC SYNONYM....' on the sql prompt, I am able to get it done but when I try to do it using an "Execute immediate ('create public syonym....') " in a stored procedure, I get a "ORA-01031: Insufficient privileges" error. Does anyone know what I am missing. This user has connect & resource roles and I even granted him the 'Create any synonym' privilege too.

Any help greatly appreciated.
Thanks,
Shiva.

what's the purpose of doing this in a procedure?

- Magnus

CREATE SYNONYM privilege is good enough

Code:

---

declare
  2  v varchar2(1000);
  3  begin
  4  v := 'CREATE PUBLIC SYNONYM syn_name FOR schema.object';
  5  execute immediate v;
  6  end;
  7  /

---

Sam

Do u have create synonym privilege directly granted to user who is executing the procedure.

The user who is executing the procedure, owner of the procedure and the owner of the tables - all are same.

I have around 100 tables in the schema and I want to create public synonyms for all of them - hence I want to do it in a procedure.

Here is the script :

cursor s1 is
select table_name
from all_tables
where owner = 'W_ALERON'
and table_name like 'OM%';

tablename varchar2(50);
sql1 varchar2(1000);

begin
open s1;
loop
fetch s1 into tablename;
exit when s1%notfound;
DBMS_OUTPUT.PUT_LINE(TABLENAME);
sql1 := 'CREATE PUBLIC SYNONYM syn_'|| tablename||' for '|| tablename;
execute immediate sql1;
end loop;
close s1;
end;

Thanks,
shiva.

The problem here lies in the privileges to the view all_tables and not in the execute immediate;

As a SYS

Code:

---

GRANT SELECT ON all_tables TO schema_owner;

---

Change your cursor as

Code:

---

  CURSOR s1 IS
  SELECT  table_name
   FROM SYS.ALL_TABLES
    WHERE owner = 'W_ALERON'
        AND table_name like 'OM%';

---

Let us know the results

Say that you want to create the synonyms for those tables that exists on the user schema, then you might want to use the view "USER_TABLES" instead of ALL_TABLES. Remember for this you don't have to grant any privileges, you only would have to modify the cursor "SYS.ALL_TABLES" to "USER_TABLES"

Code:

---

  CURSOR s1 IS
  SELECT  table_name
   FROM USER_TABLES
    WHERE table_name like 'OM%';

---

Sam

[Edited by sambavan on 11-20-2001 at 02:44 PM]

Hi Sam,

Thanks for your reply, but I do not think that is the case because I am able to select the table names from all_tables. I even tried executing the same procedure by commenting out the cursor & using a test table name, still I get the same error - the beauty here is that when I run the "create public syonym...' command directly on my sql prompt, it gets through without any issues.

Shiva.

On such case, I would suggest you alter your procedure and comment out the execute immediate option from the code and try to just print the data on the dbms_out.put_line('...'); and confirm that your cursor is working correct!

Sam

Thanks for your continuous help Sam, and ofcourse I did output the value of the variables in each step and they seem perfect. And as far as the problem being with 'Execute immediate..' goes, I already have a procedure which in the same way uses the 'Execute Immediate...' command to grant access rights to other users on all the tables in this schema and that works fine - why does this one give me a problem ??


Shiva.

SQL> declare
2
3 cursor s1 is
4 select table_name
5 from all_tables
6 where owner = 'MY_USER'
7 and table_name like 'CC%';
8
9 tablename varchar2(50);
10 sql1 varchar2(1000);
11
12 begin
13 open s1;
14 loop
15 fetch s1 into tablename;
16 exit when s1%notfound;
17 DBMS_OUTPUT.PUT_LINE(TABLENAME);
18 sql1 := 'CREATE PUBLIC SYNONYM syn_'|| tablename||' for '|| tablename;
19 --execute immediate sql1;
20 DBMS_OUTPUT.PUT_LINE(SQL1);
21 end loop;
22 close s1;
23 end;
24 /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> /
CC
CREATE PUBLIC SYNONYM syn_CC for CC
CC1
CREATE PUBLIC SYNONYM syn_CC1 for CC1
CC_BATCH_LOG
CREATE PUBLIC SYNONYM syn_CC_BATCH_LOG for CC_BATCH_LOG
CC_BLACKLIST
CREATE PUBLIC SYNONYM syn_CC_BLACKLIST for CC_BLACKLIST
CC_CARD_TYPE
CREATE PUBLIC SYNONYM syn_CC_CARD_TYPE for CC_CARD_TYPE
CC_DEVICE_INFO
CREATE PUBLIC SYNONYM syn_CC_DEVICE_INFO for CC_DEVICE_INFO
CC_IO
CREATE PUBLIC SYNONYM syn_CC_IO for CC_IO
CC_IO_REFUSED_LOG
CREATE PUBLIC SYNONYM syn_CC_IO_REFUSED_LOG for CC_IO_REFUSED_LOG
CC_RESPONSE_CODE
CREATE PUBLIC SYNONYM syn_CC_RESPONSE_CODE for CC_RESPONSE_CODE
CC_TRANSACTION_CODE
CREATE PUBLIC SYNONYM syn_CC_TRANSACTION_CODE for CC_TRANSACTION_CODE
CC_TRANSACTION_LOG
CREATE PUBLIC SYNONYM syn_CC_TRANSACTION_LOG for CC_TRANSACTION_LOG

PL/SQL procedure successfully completed.


The output proves that the procedure is executable.

if you have no privileges to create synonym it is a different story.

In that case connect as internal/ sys and then grant create synonym privilege to this user who is to execute the plsql and then run..

Using this procedure, I generated the 'Create Public Synonym ....' statements for all the tables and then used them individually to generate the synonyms - without using the 'Execute Immediate..'. My work is done - in a indirect way - but if anyone can think of anything that we may have missed here, I would surely like to know about it.

Thanks for all your help.
Shiva.

GRANT the user "CREATE PUBLIC SYNONYM" Priv before executing the procedure.

-amar

HI.........

U HAVE TO GRANT CREATE PUBLIC SYNONYM TO THE USER EXECUTING THE PROCEDURE.ONLY THEN HE CAN CREATE A PUBLIC SYNONYM.CREATE ANY SYNONYM PRIV. WILL NOT ALLOW TO CREATE PUBLIC SYNONYM