Re: Cross Schema Deletions

Christopher,

You forgot REFERENCES. And it doesn't make sense to cross-grant.

GRANT SELECT, DELETE, REFERENCES on SNAPUSER.TABLE to PRODUSER

should be sufficient. Reverse grants to snapshot schema are not needed (in fact, they should be avoided to prevent occasional data deletion from production schema by snapshot schema user).

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Christopher Luther" <cluther*nospam*@*nospam*xybernaut.com> wrote in message
news:9nj6qa$ub5$1_at_nntp2-cm.news.eni.net...


> I've got two db schemas: one is a production db, the other is an archive

> snapshot db (i.e., many snapshot records can exist for one production

> record).

>

> I want to have foreign key constraints on the archive schema that reference

> records in the production schema.  These foreign key constraints also need

> the "on delete cascade" functionality so that when a production record gets

> deleted, all archived snapshot records get deleted.

>

> I've granted both schema owners select and delete permissions (I also tried

> update and insert too) on the tables in each schema that are referenced by

> the other schema (make sense?), but when I try to implement the foreign key

> constraint, I get a "ORA-01031: insufficient privileges" message.

>

> What permissions, either system or object, am I missing?

>

> --

>

> Sincerely,

>

> L. Christopher Luther

> Technology Manager

> Xybernaut Solutions, Inc.

> (703) 506-0400 x230

> cluther_at_xybernaut.com

> http://www.xybernaut.com/selfware/selfware.htm

>

> My PGP Public Key:

> http://keyserver.pgp.com:11371/pks/lookup?op=get&search=0x21261B88

>

> CONFIDENTIALITY NOTE:  This communication contains

> information that is confidential and/or legally privileged.

> This information is intended only for the use of the individual

> or entity named on this communication. If you are not the

> intended recipient, you are hereby notified that any disclosure,

> copying, distribution, printing or other use of, or any action

> in reliance on, the contents of this communication is strictly

> prohibited.  If you receive this communication in error, please

> immediately notify us by telephone at (703) 506-0400.

>

> ------------------------------------------------------------

> Unsolicited commercial e-mail will automatically be reported

> to the appropriate abuse@ - without exception.

>

>

>