请问如何用oracle触发器实现不允许新增/删除表/增加/减少/修改字段类型

给本帖投票

56211打赏收藏

转发到动态举报

写回复

性能测试中发现oracle11g数据库每天22点,oralce进程CPU占用率突增>>

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

microsoft_fly 2008-11-18

还是用权限控制比较好，触发器影响数据库性能，越复杂对性能影响越大

fuxiaoyang13 2008-11-18

路过！

hongqi162 2008-11-17

楼主说的似乎不是处理表数据，而是处理建表，删除/修改表结构的操作,还是通过权限控制比较好

wfqqwer5213 2008-11-17

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER OR DROP
ON SCHEMA

DECLARE
oper ddl_log.operation%TYPE;
sql_text ora_name_list_t;
i PLS_INTEGER;
BEGIN
SELECT ora_sysevent
INTO oper
FROM dual;

i := sql_txt(sql_text);

IF oper IN ('CREATE', 'DROP') THEN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, sql_text(1), USER, SYSDATE
FROM dual;
ELSIF oper = 'ALTER' THEN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, sql_text(1), USER, SYSDATE
FROM sys.gv_$sqltext
WHERE UPPER(sql_text) LIKE 'ALTER%'
AND UPPER(sql_text) LIKE '%NEW_TABLE%';
END IF;
END ddl_trigger;
/

wfqqwer5213 2008-11-17

DDL Trigger - Triggering Operations
BEFORE / AFTER ALTER
BEFORE / AFTER ANALYZE
BEFORE / AFTER ASSOCIATE STATISTICS
BEFORE / AFTER AUDIT
BEFORE / AFTER COMMENT
BEFORE / AFTER CREATE
BEFORE / AFTER DDL
BEFORE / AFTER DISASSOCIATE STATISTICS
BEFORE / AFTER DROP
BEFORE / AFTER GRANT
BEFORE / AFTER NOAUDIT
BEFORE / AFTER RENAME
BEFORE / AFTER REVOKE
BEFORE / AFTER TRUNCATE
AFTER SUSPEND

范佩西_11 2008-11-17

这可以用权限控制吗。只给select权限就可以了
如果实在要用触发器，给你个限制增删改的例子：

create or replace trigger tr before insert or update or delete on act
begin
if updating or deleting or inserting
then
raise_application_error(-20001,'不允许增删改');
end if;
end;

sleepzzzzz 2008-11-17

-- 试下
Create table test(a number);
Create trigger tri_test
Before insert or update or delete
On foo
Begin
Raise_application_error(-20001, 'You don’t have access to modify this table.');
End;
/

sleepzzzzz 2008-11-17

好奇怪的需求

wfqqwer5213 2008-11-17

CREATE OR REPLACE TRIGGER trg_drop_table
BEFORE DROP ON DATABASE
BEGIN
raise_application_error(num => -20000,
msg => '主机:' ||
SYS_CONTEXT('USERENV', 'HOST') ||
chr(13) || 'IP:' ||
SYS_CONTEXT('USERENV', 'IP_ADDRESS') ||
'试图删除' || ora_dict_obj_name() || '表');

END;

一个禁止drop的例子

hongqi162 2008-11-17

用权限控制一下不就可以了么，这些操作和触发器没有什么关系

BlueskyWide 2008-11-17

不允许新增/删除表
===============
界面层封住，Oracle用户名和密码不对外提供即可。

增加/减少/修改字段类型
====================
使用alter table add ...
alter table drop ...
修改字段可以使用先增加字段，然后删除来做。

如果Oracle用户名和密码别人知道了，只能通过职业道德来规范了。

[Quote=引用楼主 anning241 的帖子:]
急:
请问如何用oracle触发器实现不允许新增/删除表/增加/减少/修改字段类型
[/Quote]