请问如何用oracle触发器实现不允许新增/删除表/增加/减少/修改字段类型
请问如何用oracle触发器实现不允许新增/删除表/增加/减少/修改字段类型
11 个解决方案
#1
不允许新增/删除表
===============
界面层封住，Oracle用户名和密码不对外提供即可。
增加/减少/修改字段类型
====================
使用alter table add ...
alter table drop ...
修改字段可以使用先增加字段，然后删除来做。
如果Oracle用户名和密码别人知道了，只能通过职业道德来规范了。
===============
界面层封住，Oracle用户名和密码不对外提供即可。
增加/减少/修改字段类型
====================
使用alter table add ...
alter table drop ...
修改字段可以使用先增加字段，然后删除来做。
如果Oracle用户名和密码别人知道了，只能通过职业道德来规范了。
#2
用权限控制一下不就可以了么，这些操作和触发器没有什么关系
#3
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的例子
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的例子
#4
好奇怪的需求
#5
-- 试下
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;
/
#6
这可以用权限控制吗。只给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;
#7
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
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
#8
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;
/
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;
/
#9
楼主说的似乎不是处理表数据，而是处理建表，删除/修改表结构的操作,还是通过权限控制比较好
#10
路过！
#11
还是用权限控制比较好，触发器影响数据库性能，越复杂对性能影响越大
智能推荐
注意！
本站转载的文章为个人学习借鉴使用，本站对版权不负任何法律责任。如果侵犯了您的隐私权益，请联系我们删除。