A reader, February 10, 2003 - 4:21 pm UTC
Is there any case?
Bo, February 10, 2003 - 4:25 pm UTC
Hi, Tom.
There're several discussions about public synonym on this web site. You suggested to avoid public synonym if it's possible.
Is there any case to show advantage of public synonym? Otherwise, why do we need it?
February 11, 2003 - 7:52 am UTC
I use it for a few top level things to get into the application. for example, look at the URL above. F is a public synonym in my database. there are hundreds of stored procedures "below f", but we do not use synonyms for anything other then F.
it is purely a matter of convienence - it has its good sides (ease of use) and its downsides (scalability, resource consumption, confusion)
Is this a bug?
A reader, November 26, 2003 - 1:38 pm UTC
I ran the following in 9.2.0.4.
SQL> conn scott/tiger@test
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> create public synonym emp for hr.employees;
Synonym created.
SQL> conn hr/hr@test
Connected.
SQL> grant select on hr.employees to scott;
Grant succeeded.
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> conn scott/tiger@test
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> rename emp to emps;
Table renamed.
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> rename emps to emp;
Table renamed.
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> select count(1) from emp;
COUNT(1)
----------
14
Why do the last two queries return different results?
November 26, 2003 - 2:52 pm UTC
whats session_cached_cursors set to?
Thanks Tom. Here are the parameters related with cursors:
A reader, November 26, 2003 - 4:38 pm UTC
SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 0
November 27, 2003 - 10:15 am UTC
that, that would be an issue -- yes. I've a simplier testcase. i filed a bug on this. #3285915 (its not public right at this instant until it gets screened and all)
Public Synonym under Public Schema
Abhi, December 17, 2003 - 5:53 am UTC
hello Sir,
Could you pls. Explain relationship between
OBJECT_NAME OWNER OBJECT_TYPE
---------------------------------------- -------- ----------------
GV_$MAP_FILE_EXTENT SYS VIEW
V_$MAP_FILE_EXTENT SYS VIEW
GV$MAP_FILE_EXTENT PUBLIC SYNONYM
V$MAP_FILE_EXTENT PUBLIC SYNONYM
How could v$map and gv$map be created under public schema .. and whats is reason for that.
Thanks
December 17, 2003 - 7:01 am UTC
public synonyms are always in the schema -- PUBLIC.
v_$<name> is a view (as all v_$.... views are)
v$<name> is the public synonym we all use to access this view.
GV is for RAC (clustered environments) -- lets you see V$ views from all instances in a single monster view.
It is just the way it is. there is a view (v_$....) and a public synonym that points to the view (v$.....)
Thanks Sir,
Abhi, December 17, 2003 - 7:17 am UTC
Thanks Sir ... :)
A personal Request.
Sir From where we can get your pics.
I have searched many times at www.oracle.com for your
Video Clips .. etc.. but did not get any.
Thanks
Abhi
A reader, December 17, 2003 - 3:33 pm UTC
Abhi, the lastest issue of Oracle Nov/Dec 03
has a picture of Tom on the cover page and inside as well,
didn't you see it.
FYI last row 2nd column.
Reply to Reader
A reader, December 17, 2003 - 10:33 pm UTC
Dear Reader,
In last two lines ..
I said , "I have searched for VIDEO CLIPS"
VIDEO CLIPS != PICS
FYI
Sir, I am expecting your answer .. :)
Stalker Alert !
Another Reader, December 18, 2003 - 5:45 am UTC
Scarey..... VERY Scarey
Ravi, July 06, 2005 - 3:04 am UTC
Tom,
In the original question, if the user had logged on to IND schema and simply said
exec Test;
1)Would that have executed Test?
2)If yes, is it better practice NOT to qualify schema names while running jobs and let Oracle decide name resolution?
3)Is the order of resolution when NOT qualifying schema is
Say if I'd just said exec test;
a)Look for an object with name Test in current Schema
b)If a) not found, look for a Private Synonym
c)If a)and b) not found, look for a Public Synonym?
4)I find that Oracle gives almost similar priority for objects and private synonyms, ie wouldn't let me create same named objects and private synonyms. So is it right in saying that they pretty much run dead-heat during name resolution?
Am I right in saying the reason why 4) happens is because if I were to do something like
Create table x as select * from x;
Where x is an existing object and an either be a table or a private synonym, the Select part of query would evaluate to the existing private synonym and prevent duplication of object name. Am I right here?
July 06, 2005 - 7:49 am UTC
1) then wrote:
<b>When connected as IND</b> or any other user, execution
of TEST fails:
SQL> exec ind.test
BEGIN ind.test; END;
A reader, July 06, 2005 - 8:05 am UTC
Did you on purpose leave white space for answers to Questions other than 1)?
July 06, 2005 - 8:25 am UTC
yes, they were "and if so" questions -- but it was not so...
public synonyms
ian galllacher, September 02, 2005 - 7:36 am UTC
Tom,
I note your distaste for public synomyms !
However, I have been using then on tables for the last 10 years without any hassle. I use Oracle Forms for my applications and have very few stored procedures - all code in Forms ( except some nice ones pickuped from yourself !)
Each public synonym has the same name as the table it is created for
ie. table patient has public synomym patient
All users access the database via Forms and never via say
sqlplus
Have i been going about it the wrong way for the last 10 Years?
Any comments would be appreciated
Thanks
Ian
September 03, 2005 - 7:03 am UTC
consolidate your application with 5 other applications now.
You have a "global namespace" name. There can only be one public synonym "ABC", suppose both you and some other application want it?
Yes, I would suggest reducing the number of public synonyms to as little as possible, 0 and 1 being the best numbers :)
public synonyms again
Ian W Gallacher, September 11, 2005 - 10:00 am UTC
Hi Tom,
The chances of my system being consolidated with another are about 0.
If you accept this then what are the downwfalls of using public synomyms ? Again bearing mind I use Oracle forms exclusively and have have only one package in the database -
the one described by yourself? for auditing columns
Comments again would be appreciated
Thanks
Ian
September 11, 2005 - 10:28 am UTC
you say 0 today, three years from now - you have no idea.
I prefer to not use any more than ONE public synonym personally.
You can put an
alter session set current_schema=YOUR_APP_SCHEMA;
in your form right after you get logged in, then
select * from t;
becomes:
select * from YOUR_APP_SCHEMA.t;
intead of
select * from CURRENT_USER.t;
public synonym
ian gallache, September 11, 2005 - 12:16 pm UTC
Thanks
have tried that out some time ago once I read your comments
worked fine in the form that i put the edit in but once i called another form ( another session ? ) schema was lost and couldnt access table(s) in form called
so gave up !! and put public synonyms back on
Application has been running for last 15 years without any
consolidation with other apps
Regards
Ian
September 11, 2005 - 12:27 pm UTC
"when new form instance" trigger.
If you are happy, that is perfectly fine. 15 years ago, a single machine typically couldn't consolidate.
Now, I'm quite sure my laptop could blow away the server you were using back then and consolidation a fact.
public synonym
ian gallacher, September 11, 2005 - 3:58 pm UTC
Hi
What makes consolidation a big issue ?. Most pcs today would blow away servers 15 years old - big deal !
Again, forget about consolidation. any other reasons for NOT
using public synonyms ?
Performance issues ?
Look forward to hearing your comments
September 11, 2005 - 6:31 pm UTC
I already told you, if you be happy - cool, great.
The point about the pc's of today was -- 15 years ago consolidation wasn't an option perhaps.
Today it is. Things change. You are 100% sure this will never be consolidated into a larger collection of applications (it is one thing I would do immediately personally)
The only thing I know is that the second I'm 100% sure of something - something will come along to change my mind. Or someone else will change my mind for me.
Yes, there are performance implications - used to be very measurable, have gotten less so over time.
But, if you've been running this for 15 years, it must already run faster than fast enough
public synonym
A reader, September 12, 2005 - 4:12 am UTC
Hi
Thanks for getting back to me
Will leave public synonym issue alone and look for more pertinent issues
As a point of intererst my particualr application is allows Clinical Information within Hospitals to be recorded and reported on. Interfaces are made to other Hospital systems via ODBC/HS connections and uploading of CSV files
Thanks again for your time
Ian
drop public synonym dual
A reader, September 12, 2005 - 5:58 am UTC
September 12, 2005 - 7:35 am UTC
er? useful, thanks.
public synonym
ian gallacher, September 12, 2005 - 4:53 pm UTC
Came across this trigger on the ioxra web site which replaces public synonyms
REM my version
REM From IXORA web Site
REM
REM replace public synonyms with this trigger
REM dont have to amend application code
REM
drop table system.default_schema;
create table system.default_schema (user_name varchar2(30), schema_name varchar2(30));
REM
REM User PRS has created all tables and indexes
REM
insert into system.default_schema values ('PRS','PRS');
create or replace trigger
system.set_current_schema after logon on database
declare
default_schema varchar2(30);
begin
select schema_name into default_schema from system.default_schema where user_name = 'PRS';
execute immediate 'alter session set current_schema = ' || default_schema;
end;
/
Have dropped my 1000+ public synonyms and application running without any problems and forms compiling Ok
Is this a reasonable solution to replacing synonyms with
alter session ?
Seems fine to me
any comments would be appreciated
Ian
ps bookmarked this page and now getting
ORA-01400: cannot insert NULL into ("ASK_TOM"."WWC_ASK_QUESTION_ACCESS_LOG$"."DISPLAYID")
Error !
September 12, 2005 - 5:44 pm UTC
Use the bookmark this page link to get a safe link to bookmark.
Yes, that is just like using a when new form instance trigger to do the same in your application.
I don't like the idea of using SYSTEM however, use your own account
public synonym
ian gallacher, September 13, 2005 - 4:47 am UTC
Hi
Thanks for your comments
will use my account to create trigger
Hopefully last of this thread from me
Ian
Object Name Resolution
VLS, January 08, 2009 - 5:13 am UTC
Hi Tom,
This is in the same line of the Original Question posted by a reader long back.
I was going thru Oracle 10g Concepts Documentation, Chapter 6 "Dependencies Among Schema Objects". The document, under heading "Object Name Resolution" says, that
"1. Oracle attempts to qualify the first piece of the name referenced in the SQL statement. For example, in hr.employees, hr is the first piece".
Further, it says
"a. In the current schema, Oracle searches for an object whose name matches the first piece of the object name. If it does not find such an object, then it continues with step b."
Now, assuming that I have a select privilege on hr.employees and I create a table called HR in my own schema, if I execute a query from my schema
"select count(*) from hr.employees",
since hr is a local table in my current schema, shall it get the count from hr table or from hr.employees ? If later, then can you please explain this concept.
Regards
VLS
Security Issue in using Public Synonym
A reader, December 16, 2009 - 11:41 am UTC
Is there any securoty issue in using public synonym ?
December 16, 2009 - 1:22 pm UTC
not specifically, some would say "yes", I vote "no, not really"
If you do not have access to the object the synonym points to, you won't be able to select/modify/execute it. It does not give you any privileges beyond what you have.
Those that say it is a security risk would point to the fact that "if you can see the synonym, you can see the name of what it points to, it'll tell you the name of a table/procedure/whatever - that you shouldn't know"
for example
scott%ORA11GR2> select * from all_synonyms where synonym_name = 'DBA_OBJECTS';
OWNER SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
DB_LINK
-------------------------------------------------------------------------------
PUBLIC DBA_OBJECTS
SYS DBA_OBJECTS
scott%ORA11GR2> desc dba_objects
ERROR:
ORA-04043: object "SYS"."DBA_OBJECTS" does not exist
Scott can tell that DBA_OBJECTS is a synonym.
Further, Scott knows that SYS owned a Thing called DBA_OBJECTS. This is what some would say is a security issue.
It is, to a degree, but a pretty small degree. It is part of what I call 'security via obscurity', the less you know about something, the harder it would be for you to attack it.
But if it were a big deal, then open source software..... you fill in the blanks.
In a system that is not secured - so that I could gain access to schema X's objects - yes, knowing the name of what is there would get me one step closer. But the fact remains - that you have allowed me somehow to gain access to schema X's objects!! and I'm not supposed to.
security issue with public synonyms
Ben, December 16, 2009 - 2:02 pm UTC
The security problem I see with public synonyms is who has the CREATE PUBLIC SYNONYM privilege. Every schema with this priv in effect has the priv to REPLACE any object by
1)create their own object
2)modify public synonym to point to their object
yes, there are security restrictions that limit the impact of this (the man in middle schema would need privs on supporting objects or call the real object, etc)
good policing of this priv makes this a mostly moot point.
However, I can attest that overuse of public synonms can cause development headaches as developers copy objects into their own schemas
Tom - I laughed and cried during your WorstPractice presentation at SLOUG & thought you should include a "create a public synonm for every object" because it makes it simpler to code ;)
December 16, 2009 - 2:29 pm UTC
Yes, there are many reasons to avoid public synonyms like the plague - I should mention that there.
Public synonyms should not be used - it makes consolidation virtually impossible.
And thanks for the Trojan Horse story - yes, they can be abused by a privileged user with create public synonym - very much like the 'ANY' privileges, it should not be granted lightly.
Public synonyms: can't we use them in Stored procedures/packages.
Durgesh, December 28, 2009 - 2:38 am UTC
Hi Tom,
Thanks for very useful information on synonyms!
Could you please also put some light on how to use a public synonym in a stored procedure or package.
On doing so, I'm getting ORA-00942 "table or view does not exist" error.Whereas, the stand alone SELECTs/DML are working fine for the same public synonym.
Even, If I use the same code of the procedure in an anonymous block it works.
Please Help!
January 04, 2010 - 7:23 am UTC
sounds like
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551289900368934430 scott%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2> drop public synonym tkyte_t;
Synonym dropped.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> create public synonym tkyte_t for ops$tkyte.t;
Synonym created.
ops$tkyte%ORA10GR2> grant select on t to CONNECT;
Grant succeeded.
ops$tkyte%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> select * from tkyte_t;
no rows selected
scott%ORA10GR2> begin
2 for x in (select * from tkyte_t) loop null; end loop;
3 end;
4 /
PL/SQL procedure successfully completed.
scott%ORA10GR2> create or replace procedure p
2 as
3 begin
4 for x in (select * from tkyte_t)
5 loop
6 null;
7 end loop;
8 end;
9 /
Warning: Procedure created with compilation errors.
scott%ORA10GR2> show errors
Errors for PROCEDURE P:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11 PL/SQL: SQL Statement ignored
4/25 PL/SQL: ORA-00942: table or view does not exist
scott%ORA10GR2>
scott%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> grant select on t to scott;
Grant succeeded.
ops$tkyte%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> create or replace procedure p
2 as
3 begin
4 for x in (select * from tkyte_t)
5 loop
6 null;
7 end loop;
8 end;
9 /
Procedure created.
scott%ORA10GR2> show errors
No errors.
scott%ORA10GR2>
you have access to the object via a role, roles are not enabled during the compilation of a stored procedure by design.
public synonym
A reader, May 25, 2010 - 10:24 pm UTC
Tom:
someone suggests to use a public synonym instead of a public db link like "xxx_link".
Do you see any correlation between the two or any benefit of using a synonym for a db link (I think that is what they mean).
May 26, 2010 - 7:20 am UTC
you cannot use a synonym INSTEAD OF a database link
you can use a synonym to HIDE a database link
or you can use a view
I will never propose the widespread use of PUBLIC synonyms - they are evil, to be avoided at all costs. They make consolidation impossible (namespace is cluttered) and when used widely can open opportunities for security issues (not because they are insecure, but because it is far too easy to redirect code to use something it was not meant to use).
I will propose the use of a view if you want to hide the database link, create view v as select * from t@remote.
In fact, I would prefer that over using the dblink directly, that create view will import the metadata from the remote size - populate the dictionary - make it so you can compile plsql faster (since it need not dereference the dblink to compile) - it is a pretty good idea.
So, use a view.
public synonym
A reader, May 26, 2010 - 8:36 am UTC
Tom:
yes, the claim that the code is easier to maintain using the synonym and if we merge the databses in th efuture we wont need to change the code hat reference the link. you just change the synonym.
so if the code is like this
select * from tableA@db1_link
create synonym tableA for tableA@db1_link
and then code becomes
select * from tableA
DO you agree with that?
I do not like the idea much. Synonyms makes things hidden for me. I like things more visible when reading code.
If I use views, i need to create a view per table in the remote DB. THe only issue here is that you have to recreate the view everytime the remote table changes if you want the view to mirror the table.
May 26, 2010 - 9:02 am UTC
use the view
do not use the synonym.
I tried to be unambiguous above, this should clear it up:
do not use public synonyms.
please do use a VIEW to hide the database link.
INSTEAD OF:
create public synonym a_bad_idea for t@remote;
DO THIS:
create view a_good_idea as select * from t@remote;
... THe only
issue here is that you have to recreate the view everytime the remote table
changes if you want the view to mirror the table.
...
so, I view (all pun intended) that as a positive good thing. It'll help you with change management and change control. It'll be a very good thing indeed, something to embrace, not a downside.
Let me ask you something - are you planning on using this in real time - frequently - or as a method to pull data in the background on a schedule?
synonym
A reader, May 26, 2010 - 10:59 am UTC
Tom:
It is used real-time in an online transactional system.
I have been thinking about what you said when code reference a synonym it is really insecure. Some one changes the definition of the synonym and the code is screwed.
Refrencing a table@db_link would be better.
is the only reason though to avoid using them?
From what i gather, you also say that using a view is faster and better than referencing table@db_link. Would it make maintenance anyway easier if we consolidated both databases in the future.
May 26, 2010 - 11:11 am UTC
... It is used real-time in an online transactional system....
horribly bad idea, you should rethink that - honestly.
do you want to add a rather large performance penalty?
do you want to make your system infinitely less available?
do you want to make your system infinitely harder to maintain?
forget about this dblink and synonym versus view thing, what do I have to do to convince you to change your entire approach here - a distributed database is not a good idea for a real time online transactional system - not even close to a good idea, in fact about as far away from a good idea as you can get.
synonym
A reader, May 26, 2010 - 5:23 pm UTC
Tom:
it is a long story. One db used to be on another server and was moved over now to the same server the other db is.
so even though there is a link, they are sitting on same machine. That should probably make a difference when you have a remote DB on another location. right?
the future plan is to merge them together and get rid of the links but there are some expenese involved in this.
May 27, 2010 - 7:25 am UTC
... That should
probably make a difference when you have a remote DB on another location.
right?
...
nope, it'll be
<same list as above goes right here>
Until they are SO CLOSE that they are actually THE SAME, it'll be a headache and performance issue forever.
... are some expenese involved in this. ...
as there are in the current status quo
synonym
A reader, May 27, 2010 - 12:38 pm UTC
You did not explain your rationale of why a distributed system is very bad for OLTP. You said performance will suffer, less available and harder to maintain.
How so?
I tried several queries using DB link and local tables (I copies the tables locally for testing) and did not see much difference in Elapsed time (ms).
Would now what you said also be true for high-volume OLTP such as stock brokerage, ariline reservations, amazon, etc.
If your application is trivial, it many have minimal impact.
Kind of strange, the you said if one DB is is New Your and one DB is in L.A it would be the same performance impact as if the two DB sitting one machine in New York.
You mean when DB1 makes SQL statement to DB2 on same box, it can go from server 1, then to China, stop at Russia and make it back to New York on the same box. I was thinking you are using very minimal route across the network.
May 27, 2010 - 12:51 pm UTC
failure of one database = failure of the other, less available.
2pc is a performance ping.
database links are slower than not using a database link.
distributed queries (even if the database is right next to you) are not as optimal as a single query.
Think of it this way. You are a ticket office clerk. You sell tickets (perform the transaction). Which is more reliable, faster, easier to maintain (think schedule):
a) you get ticket request, you tell them how much money, they pay you, done (commit)
b) you get ticket request, you ask BOB (and only bob, if bob went to take a bio-break, you wait) how much to charge, BOB answers, you tell them how much money, they pay you, you ask BOB "is this good", when and if he says yes, done. (commit)
as for your "kinda strange", it is always very strange to have words put into my mouth that I did not actually say...
When did I same the *same* performance impact???? I said THERE WILL BE one until and unless there is a single instance. Network performance is a tiny piece of the pie here, it would be slower than a non-database link. Just because the two things are near each other does solve anything - the only thing it MIGHT reduce would be some latency.
distributed complexity is what I call this, plain and simple, there is no reason for it.
synonym
A reader, June 01, 2010 - 4:40 pm UTC
Tom:
Did you discuss in any of your books/articles or has oracle release any articles on the cons of using PUBLIC SYNONYMS for code maintenance.
anything in oracle magazine.
thanks,
June 08, 2010 - 9:17 am UTC
search for
"public synonyms" avoid
from the home page, you'll see various articles.
Public synonym for data avilability
Nikhilesh, June 15, 2010 - 6:23 am UTC
Dear Tom,
We have to load a big table T1 every week. We load another table T2 using SQLLDR and then rename T1 to T3 and T2 to T1.
But sometimes another process that we can't avoid (its IMP) use (select only) T2 and fails.
To avoid its failure and maintain data availability can we try following
1) Load T2 with latest data -- data is accessed from T1 using table name
2) create public synonym T1 on T2 -- data is accessed from T1 using table name
3) rename table T1 to T3 -- data is accessed from T2 using synonym name T1
4) rename table T2 to T1 -- data is accessed from T2 using table name T1
5) point public synonym to T1. -- data is accessed from T2 using synonym name T1
I have tested it on DEV box (10g Rel-2) and its working fine.
Thanks in advance
Regards
Nikhilesh
June 22, 2010 - 10:19 am UTC
why not just use a partition exchange, create T1 as a partitioned table (values less than maxvalue) and then exchange the existing partition in T1 with the table T2?
Public Synonym Vs Partition Exchange
Nikhilesh, June 24, 2010 - 5:19 am UTC
Dear Tom,
I'm not able to convince my manager for "PARTITION EXCHANGE" option. He don't want to alter existing "working functionaly fine" code on production.
He wants to know any potential draw back/risk of "public synonym" option. As we tested it on test intsance we can't show him any. Also with synonym we don't need to change any table structure, no index rebuilding and it needs minimum code change. Moreover I only had suggested the approach to him.... :(
In your reply you didn't mention not use it but suggested another better option. Can you please help me out of this. Is public synonym approach has any draw backs? Any particuler reason (apart from performance) to go for "partition exchange"?
Thanks in advance.
Reagrds
Nikhilesh.
June 24, 2010 - 7:51 am UTC
... He don't
want to alter existing "working functionaly fine" code on production.
...
backup the bus.
What you are proposing to do is alter existing working functionaly fine code on production.
If not, you wouldn't be asking me this question. So, what gives? Are you going to
a) make a change to production
b) not make a change to production
because if it is (b) we don't need to discuss anything.
There would be no index rebuilding with partitions - why do you think there would be?
There would be no code change to existing applications - why do you think there would be?
I see massive shared pool invalidations with all of those renames/synonym repoints - much more than a simple exchange. Be prepared for a hard parse storm.
Also, public synonyms should just be avoided - it makes consolidation impossible - there can only be "one" public synonym. I don't like them for that reason.
Public synonyms can also be considered a security issue as it is easy to put a "trojan" in there by creating some object in the local schema that overrides the public synonym in scope.
Dynamic Synonyms
John Gilmore, October 05, 2010 - 4:33 am UTC
Hi Tom,
I want to set up an admin role so that I can dynamically assign the admin responsibility to and from specific users.
This means giving them permissions on some admin tables. So I can grant these permissions to a role called ap_admin and then grant ap_admin to users whenever I like.
The trouble is I only want users to see the admin tables when they've been granted the ap_admin role.
I don't want to use public synonyms as, apart from the reasons given above, I'd prefer if users are not able to "describe" the admin tables unless they have privileges on them.
I also don't want to create private synonyms each time I grant the admin role to a user.
How can this be done? Basically I need the functionality of a synonym owned by a role but that's not possible.
October 05, 2010 - 12:20 pm UTC
... I don't want to use public synonyms as, apart from the reasons given above, I'd
prefer if users are not able to "describe" the admin tables unless they have
privileges on them.
...
I agree you don't want public synonyms for many reasons - however, I don't see the tie in between public synonyms and "describe", if you don't have access to DBA_USERS, you cannot describe it - but yet there IS a public synonym dba-users. the synonym DOES NOT let you see the object in any way, shape or form.
You don't want synonyms - using schema names is NOT a bad idea. Just simply teach the people that have this role that the name of the table is:
ap_admin_schema.table_name
period, you can refer to things by their schema name - it is OK, acceptable, a good thing.
Hint to use Public Synonym
Snehasish Das, January 03, 2013 - 5:02 am UTC
Hi Tom,
Wish you a happy new year 2013.
I know the down side of using Public synonym (reading from the posts above). Now As a test case I did the below.
1. Grant select on Table T1 to public in schema test.
2. Create public synonym for test.t1;
3. Login to schema DEV.
4. Create table T1;
Now can I hint Oracle to use T1 the public synonym rather than the Table in schema DEV (I am logged on to DEV schema).
This is a test i am doing for my knowledge and its not used in any application.
Regards,
Snehasish Das
January 04, 2013 - 3:15 pm UTC
You cannot - it wouldn't even make sense. if you are going to "hint", you would just reference it directly??
The normal scoping rules take precedence here, as soon as DEV created T1, the public synonym is "invisible"