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.

This definitely ranks as one of the worst designs, and use the word 'design' is taking great license I have ever heard of. There isn't a chance this will be stable, scalable, or perform well.

That said ... the solution is to use a DDL trigger and a table that tracks who did what and when. You can find examples you can modify at:
http://www.psoug.org/reference/library.html then scroll down to DDL Triggers

BTW: I have kept a copy of your post as a 'bad example' to show my students. That should tell you just how horrible an idea this is.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)