DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Public Synonym - Insufficient privileges

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking purpose?


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

    - Magnus

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Feb 2001
    Posts
    389
    Do u have create synonym privilege directly granted to user who is executing the procedure.

  5. #5
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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]
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

  10. #10
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    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..

    sonofsita
    http://www.ordba.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width