John Singleton wrote: > > > > > >It looks nearly correct. > >But public synonym would not be level 4, but level 2, increasing the > levels of search in dba2 and dba. > > > > Hurm. Okay, then how does it find something like this? > > Catalog: > Users: > DBA.DBA > DBA.DBAUSER (RESOURCE) > DBA.DBA2 (DBA) > > DBA2.USERDBA2 (RESOURCE) > > Objects: > DBAUSER.SOMETABLE (Table) > DBAUSER.S_SOMETABLE (Public Synonym) > > If USERDBA2 searches for the table "SOMETABLE" it will never be found.
It will never be found with the implicit search_levels. Yes, that is true. Usually you will and have to prefix table/view-names with the owner/schema of that table/view. Therefore, you will write DBAUSER.SOMETABLE and you will find it. The implicit search levels are only for those people not willing to type one character more than the minimum, who will rely on the search levels (hoping that DBA2 will not create a table xyz if DBA had one before and has it still and USERDBA2 was used to write just xyz, expecting to name DBA.xyz in this way, which will not be true any more after DBA2's table-creation). The implicit search levels should not be used in real applications, used for years, only for adhoc-queries. > However, if USERDBA2 searches for S_SOMETABLE, it will be found. My > guess was that there was a special synonym searching step... what > explains this behavior? > As I said, there is a special search for PUBLIC synonyms : 1. look in your own namespace/schema 2. search for PUBLIC synonym 3. search in the namespace/schema of the owner of you (user) --> DBA2 4. search in SYSDBA's namespace/schema Elke SAP Labs Berlin > Best, > JLS > > Zabach, Elke wrote: > > >John Singleton wrote: > > > > > >>Hello all, > >> > >>So I think I've pretty much got MaxDB's name resolution system down. But > >>I just wanted to post to the list to make sure :) > >> > >>So, from what I've been able to determine, it works like this: > >> > >>1) Say I have three users. DBA, DBA2, and DBA2USER. DBA creates DBA2 > >>then DBA2 creates the resource user DBA2USER. > >>The ownership looks like this: > >>DBA > >> DBA2 > >> DBA2USER > >> > >>1) As user DBA2USER I issue the query, "Select * from sometable" > >> > >> > >>(Please excuse the pseudo-code :) ) > >> > >>Level 1: > >>Searches for an object named "sometable" owned by DBA2USER. > >> > >>IF FOUND > >> RETURN SUCCESS > >>ELSE > >> CONTINUE TO LEVEL 2 > >> > >>Level 2: > >>Searches for an object named "sometable" owned by DBA2 (the owner of > >>DBA2USER) > >> > >>IF FOUND > >> IF DBA2USER HAS PRIVILEGES > >> RETURN SUCCESS > >> ELSE > >> RETURN FAIL > >>ELSE > >> CONTINUE TO LEVEL3 > >> > >>Level3: > >>Searches for an object "sometable" owned by DBA (the owner of DBA2) > >> > >>IF FOUND > >> IF DBA2USER HAS PRIVILEGES > >> RETURN SUCCESS > >> ELSE > >> RETURN FAIL > >>ELSE > >> CONTINUE TO LEVEL 4 > >> > >>Level 4: > >>Searches for a public synonym named "sometable" > >> > >>IF EXISTS A PUBLIC SYNONYM "SOMETABLE" > >> IF DBA2USER HAS PRIVILEGES > >> RETURN SUCCESS > >> ELSE > >> RETURN FAIL > >> ELSE > >> RETURN FAIL > >> > >> > >>Test Questions: > >>1) How does this look? This model has been working in all of my tests > >>so far. > >> > >> > > > >It looks nearly correct. > >But public synonym would not be level 4, but level 2, increasing the > levels of search in dba2 and dba. > > > > > > > >>2) Some statements do not perform resolution. For example, the CREATE > >>SYNONYM statement does not perform resolution. The Select statement, > >>however, does. > >> > >>Example: > >>Given the catalog: > >> DBA.SOMETABLE > >> DBA.DBA2 > >> DBA2.DBA2USER (where the user DBA2USER has all privileges on > >>DBA.SOMETABLE) > >> > >>If DBA2USER does: "select * from SOMETABLE" then the query succeeds. > >>However if DBA2USER does, "CREATE PUBLIC SYNONYM S_SOMETABLE FOR > >>SOMETABLE" then the query fails. > >> > >>Is there a list of statements that do not perform name resolution? > >> > >> > >> > > > >Yes, DDL does not perform name resolution > > > >Elke > >SAP Labs Berlin > > > > > > > >>Many many thanks, > >> > >>JLS > >> > >> > >> > >>-- > >>MaxDB Discussion Mailing List > >>For list archives: http://lists.mysql.com/maxdb > >>To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >> > >> -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
