Re: View performance problem

You probably have selects in your procedures, right?

They will always be treated as a separate recursive sql level, and hence won't get *merged* in the main query.
This is one of the main reasons, IMO, to avoid such a setup, at *all cost*.

As you don't mention which version of Oracle you are running (which you preferably should always do), I can only tell you in Oracle 8i (and most likely in the Enterprise Edition only) row level security has been implemented, and in a better way than you will ever be capable of doing it manually.

Finally, Oracle always had column level security, so to me it is unclear why you are trying to implement this yourself.

Regards,

Sybrand Bakker, Oracle DBA

"Thomas Ruschak" <truschki_at_jam.rr.com> wrote in message news:yXba6.915$k%.322179_at_typhoon.austin.rr.com...
> Hi,
>
> I'm a relative newcomer to Oracle. I'm trying to implement row &
 column
> security through views. Not unexpectedly, I'm running into performance
> problems. As an example, I've got the following view:
>
> CREATE OR REPLACE VIEW V_OBJECT (
> PK_TABLE_NAME,
> PK_COL_NAME
> ) AS SELECT
> DECODE( PP2_SECURITY.PERM_BIT( PP2_SECURITY.PERMISSION( 'OBJECT',
> 'PK_TABLE_NAME', FK_REC_SEC_ID ), 1 ), 0, NULL, 1, RTRIM(PK_TABLE_NAME) )
> PK_TABLE_NAME,
> DECODE( PP2_SECURITY.PERM_BIT( PP2_SECURITY.PERMISSION( 'OBJECT',
> 'PK_COL_NAME', FK_REC_SEC_ID ), 1 ), 0, NULL, 1, RTRIM(PK_COL_NAME) )
> PK_COL_NAME
> FROM OBJECT
> WHERE PP2_SECURITY.VIEW_PERM( 'OBJECT', 'N/A', FK_REC_SEC_ID ) = 1
>
>
> The column level security is in this part:
>
> PP2_SECURITY.PERM_BIT( PP2_SECURITY.PERMISSION( 'OBJECT',
> 'PK_TABLE_NAME', FK_REC_SEC_ID ), 1 )
>
> If this procedure returns 1, they can see the column, otherwise they
> can't. The decode returns either the contents of the column from the base
> table or NULL, depending on their permission.
>
> The row level security is in the view's WHERE clause:
>
> WHERE PP2_SECURITY.VIEW_PERM( 'OBJECT', 'N/A', FK_REC_SEC_ID ) = 1
>
> Again, if this returns 1, they can see the row, otherwise not, so the
> view's output only includes rows they are allowed to see.
>
> This all works fine & fast for something like this:
>
> SELECT * FROM V_OBJECT;
>
> It all works and the performance on that is just fine. However, when
 I
> do this:
>
> SELECT * FROM V_OBJECT WHERE PK_COL_NAME = 'FOO';
>
> That's not so fine. It seems to take 10 seconds or so before it
 selects
> anything (vs. almost no time for a similar query on the base table) and
 then
> it doesn't seem like it's using any indexes, as it seems to be traversing
> the entire table. My guess is that the initial delay is the query
 optimizer
> trying to figure out what indexes to use, etc??? and then it decides to
 not
> use any when it can't figure it out.
>
> So, my question is, how can I improve this, or is there a better way?
>
> Any help will be gratefully accepted.
>
> Thanks,
> Tom
>
>
Received on Sat Jan 20 2001 - 04:10:41 CST