Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: What Sort of Privilege?
I wouldn't say "Wrong".
Perhaps it is more a matter of preference. There are good and bad sides to both..
Mark
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Goulet, Dick
Sent: 24 August 2004 14:12
To: oracle-l_at_freelists.org
Subject: RE: Re[2]: What Sort of Privilege?
Tom,
Sorry, but you are wrong. I prefer the many schema method as well.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Mercadante, Thomas F [mailto:thomas.mercadante_at_labor.state.ny.us]
Sent: Tuesday, August 24, 2004 8:21 AM
To: 'oracle-l_at_freelists.org'
Subject: RE: Re[2]: What Sort of Privilege?
Jo,
Well, The Oracle Apps applications have many schema's - much like what = you described. I'm guessing that the majority of home-grown applications have only one schema for all of the tables. I could be wrong. In my view, it is = easier to manage. Everything is in one place. It prevents you from creating = two database objects with the same name. Managing security is easier - = again, you can issue all your grants from one schema, rather than needing to = keep re-logging in.
Maybe it's just me!
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: jo_holvoet_at_amis.com [mailto:jo_holvoet_at_amis.com]=20
Sent: Tuesday, August 24, 2004 8:13 AM
To: oracle-l_at_freelists.org
Cc: 'oracle-l_at_freelists.org'; oracle-l-bounce_at_freelists.org
Subject: RE: Re[2]: What Sort of Privilege?
Hi Tom,
we (well, it was in place before I got here, but anyway) basically have=20 one schema per application. We also have one schema which houses = "common"=20 data, i.e. data used in many different places/apps; e.g. employees,=20 customers, products, plants, .... So we have many cross-schema FKs to = the=20 "common" data but outside that everything is pretty well partitioned.=20 Maybe that's why I've never really had any problems with it.
Is having what is basically a one-schema database common practice ?
mvg/regards
Jo
"Mercadante, Thomas F" <thomas.mercadante_at_labor.state.ny.us>
Sent by: oracle-l-bounce_at_freelists.org
08/24/2004 14:03
Please respond to oracle-l
=20
To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org> cc:=20 Subject: RE: Re[2]: What Sort of Privilege?
Jo,
I guess I should clarify.
If you made a consious decision to maintain multiple schema's within = your database, then it is your choice.
I prefer one schema holding all of the database tables that make up the corporate-wide application.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: jo_holvoet_at_amis.com [mailto:jo_holvoet_at_amis.com]=20
Sent: Tuesday, August 24, 2004 7:58 AM
To: oracle-l_at_freelists.org
Cc: 'oracle-l_at_freelists.org'; oracle-l-bounce_at_freelists.org
Subject: RE: Re[2]: What Sort of Privilege?
Not sure about that. We have for example an employee table that is=20 referenced just about everywhere (e.g. which salesperson is responsible=20 for this customer, which employee registered for this training course,=20 which operator filed this incident report, ...). These are all different =
apps/schemas referencing this table so I don't see how we can avoid=20 cross-schema FKs.
mvg/regards
Jo
"Mercadante, Thomas F" <thomas.mercadante_at_labor.state.ny.us>
Sent by: oracle-l-bounce_at_freelists.org
08/24/2004 13:49
Please respond to oracle-l
=20
To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org> cc:=20 Subject: RE: Re[2]: What Sort of Privilege?
Peter,
I totally agree with you. Allowing Fk references from outside of the=20 schema is inviting confusion, duplication and disaster. There is really no = good reason for it.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Peter Robson [mailto:pgro_at_bgs.ac.uk]=20
Sent: Tuesday, August 24, 2004 4:57 AM
To: Jackie Brock
Cc: oracle-l_at_freelists.org
Subject: Re[2]: What Sort of Privilege?
Whoa there everybody!
While the answer is quite correct, of course (I read the FM years ago),=20 what has not been raised is the danger of allowing reference to multiply unhindered across numerous schemas.
Be very careful if you choose to do this, otherwise, without a little=20 care, you may manage to lock your entire database solid with conflicting FKs!
We did, learned our lesson, and now reference is ONLY permitted within = the one corporate schema.
peter
edinburgh
.............
JB> references
JB> All,
JB> what kind of privilege new for creating a FK constraint which=20 JB> reference table belongs to another schema.
JB> ALTER TABLE PAM.table1 JB> ADD CONSTRAINT FKCONS1 JB> FOREIGN KEY (ID) JB> REFERENCES schema1.table2 (ID2);
JB> I have grant select,insert,update,delete for table2 BUT still I JB> get=20 insufficient privilege. any idea?
JB> Hamid Alavi
--=20
mailto:pgro_at_bgs.ac.uk
BGS. . http://www.bgs.ac.uk *********************************************************************Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Tue Aug 24 2004 - 08:13:33 CDT
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
--- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.740 / Virus Database: 494 - Release Date: 16/08/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.740 / Virus Database: 494 - Release Date: 16/08/2004
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
![]() |
![]() |