This discussion is archived
13 Replies Latest reply: Feb 12, 2013 6:01 AM by Nicosa RSS

How to create a public synynom in oracle

990301 Newbie
Currently Being Moderated
There is a schema say 'A' in which there is a package called 'B' in which this is a funtcion (below). In this function 'TIMESTMAP' is used which while compiling in 11g is giving error. I want to create a public synonym for TIMESTAMP. Can anyone please provide me the script for the same.
FUNCTION generate_random_number
Return Number
IS
l_seq_no VARCHA2(6)
l_sys_date CHAR(10)
BEGIN
SELECT LTRIM(TO_CHAR(TIMESTAMP.NEXTVAL,'000000'), ' ')
INTO l_seq_no
from DUAL;

SELECT TO_CHAR(SYSDATE, 'H24:MI:SS')
INTO l_sys_date
from DUAL
  • 1. Re: How to create a public synynom in oracle
    Karthick_Arp Guru
    Currently Being Moderated
    Create synonym syntax is well documented by oracle.

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7001.htm#SQLRF01401
  • 2. Re: How to create a public synynom in oracle
    990301 Newbie
    Currently Being Moderated
    I have already read this document. I am not able to make it. Can you provide me the exact script for the above question?
  • 3. Re: How to create a public synynom in oracle
    jeneesh Guru
    Currently Being Moderated
    987298 wrote:
    I have already read this document. I am not able to make it.
    What have you tried?

    Are you getting any error?
  • 4. Re: How to create a public synynom in oracle
    hm Expert
    Currently Being Moderated
    I don't know what you really want, but it TIMESTAMP is a reserved word. You must not use reserved word for synonym names!

    http://docs.oracle.com/cd/E14072_01/appdev.112/e10472/reservewords.htm


    When you want to create a public synonym please take a look at this:

    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7001.htm

    (please consider the system privileges that are mentioned in the above article)

    hm

    Edited by: hm on 11.02.2013 00:11
  • 5. Re: How to create a public synynom in oracle
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    An app developer that creates public synonyms, should face disciplinary sanction. It is a gross violation of database security and scope resolution.

    Create a local synonym for the sequence. E.g.
    SQL> create sequence testseq start with 1 increment by 1 nocycle;
    
    Sequence created.
    
    SQL> 
    SQL> create or replace synonym seqsyn for testseq;
    
    Synonym created.
    
    SQL> --// using sequence
    SQL> select testseq.nextval from dual;
    
       NEXTVAL
    ----------
             1
    
    SQL> --// using synonym
    SQL> select seqsyn.nextval from dual;
    
       NEXTVAL
    ----------
             2
    
    SQL> 
  • 6. Re: How to create a public synynom in oracle
    Rahul_India Journeyer
    Currently Being Moderated
    Billy  Verreynne  wrote:
    An app developer that creates public synonyms, should face disciplinary sanction. It is a gross violation of database security and scope resolution.
    Billy
    Why?
  • 7. Re: How to create a public synynom in oracle
    BluShadow Guru Moderator
    Currently Being Moderated
    Rahul India wrote:
    Billy  Verreynne  wrote:
    An app developer that creates public synonyms, should face disciplinary sanction. It is a gross violation of database security and scope resolution.
    Billy
    Why?
    Becuase you are making visible the database object for anybody who can connect to the database as any user. It also means that some developers in another schema may believe that that object is the one they need to use for their development, because they can see it, and cause much confusion, and as Billy already mentioned it can create scope resolution problems.

    The point about controlling database objects is that you do it in a controlled and specific manner. It's not good practice to go creating public synonyms (unless of course there is proper justification for it), or granting execute, read, write, select etc. to public... as you should know who and what has access to the various objects as part of your design.
  • 8. Re: How to create a public synynom in oracle
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Rahul India wrote:
    Billy  Verreynne  wrote:
    An app developer that creates public synonyms, should face disciplinary sanction. It is a gross violation of database security and scope resolution.
    Billy
    Why?
    Because I can make your code call my code and thus inject all kinds of nasty stuff into the database, courtesy of your code.

    Simple example. Your code calls UTL_MAIL to send an e-mail. I create a UTL_MAIL wrapper - looks exactly the same (same interface/package header). Internally it calls the real SYS.UTL_MAIL.

    I define a public synonym that points to my UTL_MAIL package.

    Your code now calls my wrapper and not the system package. It still sends the mail as you have intended. Everything seems to work okay.

    Except I have defined my UTL_MAIL to run with your rights (authid current user) - and internally, I have just stolen credit card numbers, application user passwords and e-mail addresses, from your tables. And also locked you out of your schema by changing the password. And left a DBMS_JOB for you that will nuke your entire schema (truncate all tables) in 30 minutes time....

    The issue is that your code trusts public scope. Trust that calls to DBMS_JOB, DBMS_SCHEDULER, UTL_FILE, UTL_MAIL and numerous other system interfaces will not be subverted and intercepted by malicious code. And this is a reasonable expectation and trust from your side. Nothing inherently wrong with that.

    Except when the DBA screws up security by allowing app developers the right to muck around with public scope.

    There is no need for application code to define public synonyms. Ever.

    And I was being kind that say this should result in disciplinary action. It should be grounds for immediate dismissal.
  • 9. Re: How to create a public synynom in oracle
    ascheffer Expert
    Currently Being Moderated
    If I write code which uses UTL_MAIL instead of sys.UTL_MAIL I do something wrong, which can be (mis)used by someone else who creates public synonyms.
    The error is not with the public synonym but with the code whick allows the misuse.
  • 10. Re: How to create a public synynom in oracle
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    ascheffer wrote:
    If I write code which uses UTL_MAIL instead of sys.UTL_MAIL I do something wrong, which can be (mis)used by someone else who creates public synonyms.
    The error is not with the public synonym but with the code whick allows the misuse.
    That is a matter of your opinion. Which I totally disagree with.

    The issue at hand is how local and public scope resolution are used. Public scope is the sole domain of system code - managed and maintained by the DBA. Any application specific reference within the public scope domain is wrong.

    Code should be written for implicit scope resolution as that offers flexibility and manageability. So it is correct to write UTL_MAIL in application and not to specify scope as SYS.

    If there are security concerns, that that is addressed by local scope - which takes precedence over public scope. This means that a local synonym called UTL_MAIL is created that resolves the scope to SYS.UTL_MAIL.

    That is the correct way to deal with scope.

    It as wrong to manually and explicitly specify scope using SYS.UTL_MAIL in app code, as it is for app code to directly call raise_application_error().
  • 11. Re: How to create a public synynom in oracle
    Nicosa Expert
    Currently Being Moderated
    Hi Billy,
    Billy  Verreynne  wrote:
    It as wrong to (...), as it is for app code to directly call raise_application_error().
    You mean directly call raise_application_error within an exception ? or any use of raise_application_error ?
    +(I totally got lost on that one...)+
  • 12. Re: How to create a public synynom in oracle
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Nicosa wrote:
    Hi Billy,
    Billy  Verreynne  wrote:
    It as wrong to (...), as it is for app code to directly call raise_application_error().
    You mean directly call raise_application_error within an exception ? or any use of raise_application_error ?
    +(I totally got lost on that one...)+
    I was referring to using raise_application_error directly in application code as being wrong - just like application code using explicit scope definition, is also wrong.

    If an app code calls raise_application_error directly, and you get the instruction to change application behaviour to log errors via a new error log (anonymous transaction) interface, prior to raising the error - you will have a major maintenance task on your hand. You need to go through all app source code, hunting for raise_application_error calls and modify these.

    If instead, raise_application_error was wrapped by the application, as wrapper procedure RaiseError(), you have a single place to modify to make a consistent application wide behaviour change.

    Wrapping system calls for app code usage, is a basic software engineering approach.

    Same principle applies to scope. If you code explicit scope everywhere in your code, and scope needs to be changed, you have to hunt for hard-coded object scope references in your code, and modify these.

    If you have a single scope resolution definition (aka synonym in Oracle), you only need to change that - to make a single and consistent change, schema wide.
  • 13. Re: How to create a public synynom in oracle
    Nicosa Expert
    Currently Being Moderated
    Hi Billy,
    Billy  Verreynne  wrote:
    I was referring to using raise_application_error directly in application code as being wrong - just like application code using explicit scope definition, is also wrong.
    Thanks for the clarification.
    My misunderstanding comes from the fact that i understood "application code" as being "the bunch of PL/SQL packages called by the application layer", not "the code running on the application layer itself".
    I guess it's good coding practice to have raise_application_error called from packages used by the application layer +(Well that is what I learned here and on askTom)+.

    I fully agree about the public synonym being "not a good idea®".

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points