处理未经许可的DML操作的异常 [英] Exception to handle unpermitted DML-operation

问题描述

我正试图阻止不是我的用户在表上执行DML操作.我正在使用触发器，但是语法上有一些问题.是让用户感到困扰的是如果用户不是'我'部分.另外，是否声明回滚"足以撤消操作?谢谢！

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;
/

推荐答案

在您的代码中，您使用了'USER'关键字，它实际上是从中执行操作的架构.例如. SCOTT是emp表的所有者.如果您以SCOTT身份登录emp表上执行更新，则触发器中使用的USER是SCOTT.如果以SYS身份登录并在表USER上执行DML，则SYS为SYS.总结一下:

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.

实际上，您可能需要了解操作系统用户，而不是schema(user):

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

SYS_CONTEXT('USERENV','OS_USER')

基于您的评论，这是出于学术目的，因此我对触发器进行了更改，因此它现在可以编译并正常工作(我坚持使用声明异常，引发异常并将其作为引发应用程序错误的方法来处理，对我来说没有多大意义，但没关系)

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;
/

请注意，我将您的触发器从级别行触发器更改为语句触发器，因为它只需要执行一次，并且我省略了rollback关键字，因为不需要回滚，所以不需要回滚关键字(除非您要回滚之前的某些操作)交易中)；

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);

这篇关于处理未经许可的DML操作的异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！