sql - Exception to handle unpermitted DML-operation

Question

I'm trying to hinder a user who is not me from performing DML-operations on a table. I'm using a trigger but have some issues with the syntax. It is the "if user is not 'me' part that troubles me. Also, is stating "rollback;" enough to undo the operation? Thanks!

create or replace trigger only_me_ex
before insert or update or delete on table
for each row
declare
only_me_ex exception;
begin
if user is not 'me' then
raise only_boss exception;
end if;
exception
when only_me_ex then
raise_appication_error(-200001,'Not permitted!');
rollback;
end;
/

Answer 1

In your code you use 'USER' keyword, which is actually schema from which is operation performed. E.g. SCOTT is owner of emp table. If you log in as SCOTT an perform update on emp table, the USER as used in your trigger is SCOTT. If you log in as SYS and perform DML on table USER will be SYS. To sum it up:

You don't need trigger like this, you need to grant insert, update, delete privileges on this table only to those users who ought to be allowed to.

In fact rather than schema(user) you may need to know operation system user:

SYS_CONTEXT('USERENV','OS_USER')

EDIT: based on your comment that this is for academic purpose I made changes to your trigger, so it now compiles and works (I stick to your method of declaring exception, raising it and handle it as rerising an application error, which doesn't make much sense to me, but nevermind)

create table my_table (id number)
/
create or replace trigger only_me_ex
before insert or update or delete on my_table

declare
only_me_ex exception;
begin
if user!='TESTUSER' then
raise only_me_ex ;
end if;
exception
when only_me_ex then
raise_application_error(-20001,'Not permitted!');
end;
/

Note that I changed your trigger from level row trigger to statement trigger because it needs to be executed only once and I omitted rollback keyword which is not needed because there will not be anything to rollback (unless you want to rollback some previous operation in transaction);