Re: How to implement this security model?

Aaron wrote:

> Running Oracle 9i (9.2.0.4) on Solaris
>
> We need to be able to allow end-users to add and drop columns from a
> particular table, but need to restrict their drops to only the columns
> which they (end-users) have added. In other words, we have a table
> with columns which must remain, but the end-user should be able to add
> and remove his own columns at will.
>
> I thought DBMS_RLS might be a path to take, but it doesn't seem to
> have an ADD_POLICY for ALTER statement.
>
> Any idea's would be appreciated.
>
> Thanks.

We can give advice on how to execute/implement code all day long... but in order to give the "right" answer on design questions would require a better understanding of why you think this is the only way to acheive your end goal - ie what is the business objective, what other restrictions would apply... and, and, and... this list goes on....

Due to the nature of DDL and the locking contention that would occur while adding/dropping, how are you going to address database performance when you have 100 (or even 2 or 3) people trying to do this at the same or near same moment?

You might consider leaving the table with the "primary" columns intact and have each user add his own table with the "user-specific" columns plus one or two "reference" columns and create a view with a synonym that does a join of these two tables. Or just add a "user" column and use a view that "filters" by this user. There are many ways to achieve this...

If you are in need of a design consultant to assist in this endeavor, there are many qualifed individuals and companies that make their living fixing problems like this. All you need to do is ask (and probably sign a contract and agree to pay them as well :)

Michael Austin
DBA Consultant Received on Sun May 30 2004 - 14:31:52 CDT