Re: Indexing View Columns?

From: NewUser2008 <mcseven_at_gmail.com>
Date: Mon, 14 Mar 2011 07:52:58 -0700 (PDT)
Message-ID: <e738b9dd-d312-4200-aee7-77b5e230423a_at_r4g2000vbq.googlegroups.com>

Thanks Mark,

I apologise if my description caused confusion. But yes, you're right. The conditions that define group membership can come from any table in the system; the system being a huge monolithic data storage for a several thousand employees company.

So to clear things up: An implicit group never exists as single records [identity <==> group]; it just exists as constraint definition set on _any_ system tables, with the requirement that it returns an identity ID and a group ID.

Since the group ID is unique to each of these constraint sets, I did not have a better idea except hard coding it into the SELECT-Statement or the resulting view respectively.

I hope the view definition from my previous post gives a good example of what I'm trying to accomplish: At some point I'd like to:
--> SELECT ident_id, group_id FROM AllGroups

Which results in a 2-column result set like this:

  1000, 512 -- from implicit group 512
  201, 512 -- from implicit group 512
  201, 4099 -- from implicit group 4099

  2409, 4099 -- from implicit group 4099   201, 102 -- from explicit group 102 given there were such an assignment
  201, 516 -- from explicit group 516 given there were such an assignment

Obviously, I now could limit the statement to only the desired group number(s):
--> SELECT ident_id, group_id FROM AllGroups WHERE group_id IN (512)

Which results in a 2-column result set like this:   1000, 512 -- from implicit group 512
  201, 512 -- from implicit group 512

And no, it does not have anything to do with FGAC or a VPD. I do not want to limit database users to access only some information; this mechanism will be implemented into the business logic, since other RDBMS do not support it like Oracle does.

Thanks again for your help,
NewUser2008 Received on Mon Mar 14 2011 - 09:52:58 CDT