Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: how to prevent drop on a specific table
<PMDORAIS_at_gmail.com> wrote in message
news:1151080257.622025.106650_at_c74g2000cwc.googlegroups.com...
In fact, we all use the same user id who has all privileges...... it
is a schema with personal tables and reference tables and i want to be
sure that a specific table is not dropped..
I know that in the best world, we should have different user id ... but i'm not the dba......
Greg a écrit :
> the owner can drop the table, so if you want to prevent users from
> being able to drop the table or other tables, here is what I'd do:
>
> 1. Give each user their own userid/password
> 2. Don't give any user dba privs
> 3. Create a role that only allows, insert, update, delete data (or
> whatever you think is approperiate) to the table/tables in question
> 4. Grant that role to all userids that you want to have access the
> system
> 5. Change the password on the owner so that users can't login with it.
>
> I keep all my users very restricted in what they can do. Only I as the
> DBA can do any harm to the DB, users are only allowed to view/select,
> update or delete (if I deem necessary) data. Users are never allowed
> to create, add, drop tables, indexes, constraints, etc -- that is all
> done by me only.
>
> -Greg
>
>
> PMDORAIS_at_gmail.com wrote:
> > HI, i'm not a big wiz with oracle but i would like to prevent dropping
> > a specfic table only for all users.
> >
> > How can i do that?? ps. i use Oracle9i Enterprise Edition Release
> > 9.2.0.5.0 .
> >
> >
> > Thanks!!!!
top-posting is not the best practice either ;-)
you'll have to write a DDL trigger, preferably in another schema so the users of the common schema can't drop or disable it, and use SYS_CONTEXT('USERENV','OS_USER') to determine which OS user is logged in to the common schema -- you do use different OS accounts, no? ;-)
++ mcs Received on Fri Jun 23 2006 - 11:41:36 CDT