Re: tough choices

Sigh...

Serge Rielau wrote:
> Mark Townsend wrote:
>

<snip>

>> * Schema Independent Users
>
> Is that just what it says? DB2 UDB for LUW has only one connection
> between Schema and User: The default setting of the CURRENT SCHEMA
> register upon login. Users may not have the right to create schemata
> (including one with their own name) depending on the CREATE SCHEMA
> privilege.

This may be specific to Oracle - basically, its info in the directory that says to "connect me to the HR schema rather than my own" on logon

>> * Security Policies (policies attached to tables and views that
>> determine what rows can be accessed based on information known about
>> the user)
>
> Can be handled with views. Let the DBMS do what the DBMS does best.

For simple cases, views will work, especially if they are parameterized. For more complex cases, security policies are easier.

However, the real difference is that a security policy cannot be bypassed - in the view scenario, if the user does get access to the base tables, they can bypass the view. With security policies, the user can never bypass it. And this then gets really important in compliance environments.

<snip>

>> * Relevant Column Enforcement (applies security policy only when query
>> accesses named columns)
>
> I get less rows if I refer to a protected column? Never heard of that
> requirement before...

New in Oracle Database 10g (I think, may have sneaked in in Oracle 9i Release 2)

>> * Relevant Column Masking (all rows are returned, but relevant columns
>> are masked (hidden) according to security policy)
>
> Views, relying on join-elimination, unused column drop.

Solving a different problem. Let's say I can see all employee information, but my company doesn't want me to see Larry's actual salary. When I query for rows, I get Larry's info, but his salary column is masked. I get everybody elses salaries however, so you can't drop the column from the report.

>> * Partitioned Fine Grained Access Control (allows multiple security
>> policies to be applied to the same table, information about the user
>> determines which policy is applied)
>
> Like security clearances? Views again...
>

We added this after we saw people using these capabilities for consolidation. 500 companies all hosted in the same database, all using the same schema. However, each company want their own specific security rules for their data. You can't glom them together into the same policy, so we allow multiple policies to be applied (sort of like having multiple triggers instead of one large trigger)

> How does Oracle enforce? Hooks in runtime (like procedure calls) or
> "implicit views"? The first would be unfortunate since it would takes
> the optimizer out of the picture, cardinalities get messed up, join
> choices taken away.

Query gets re-written at run time. Optimizer still in picture Received on Fri Jun 25 2004 - 09:43:04 CDT