Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news.primus.ca!news.primus.ca!newsfeed.telusplanet.net!newsfeed.telus.net!news3.optonline.net!newscene!novia!novia!sequencer.newscene.com!not-for-mail From: gters@zdas.com (Gters) Newsgroups: comp.databases.oracle.server Subject: Re: Fine-grained Access Control and constraint violations Date: 8 Aug 2003 12:01:15 -0500 Lines: 35 Message-ID: <3f33d6b9$0$83915$45beb828@newscene.com> References: <945a3a61.0308080346.67708a83@posting.google.com> X-Newsreader: News Xpress 2.01 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240116 In article , quarkman wrote: >On 8 Aug 2003 04:46:37 -0700, Ronald In 't Velt wrote: > > >Not sure why you'd think using a unique constraint is 'clunky'. > >What's clunky is the way you've designed this. If uniqueness of names is >that important to you, create a sequence, and append the sequence to >whatever name the user enters, regardless of whether it's the first >instance of that name or not. > >Either that, or think things through: what's the primary key on this table >(I'd have thought it was the ID column)? If that's the primary key, then >what does it matter if a name duplicates? And if it *does* matter that the >name duplicates, then ID is not the primary key (or shouldn't be), but name >itself should be (at least part of) the primary key. > >Fix the design, not hunt around for workarounds that really will be clunky. > >~QM sometimes it is not possible to redesign, you inherit a poorrly designed db and can't change it. you stated The > procedure that checks the name is executed with definer rights, > unfortunately those rights do no extend to the row-level security > policies, as these are based on CURRENT_USER which is the actual > logged-in user in all cases. Have you looked at the 8i feature of have procedures take on Invoker rights rather than Definer rights? That way the user can see what they need to. Need to determine if it works with FGAC