不允许新增/删除表 =============== 界面层封住,Oracle用户名和密码不对外提供即可。 增加/减少/修改字段类型 ==================== 使用alter table add ... alter table drop ... 修改字段可以使用先增加字段,然后删除来做。 如果Oracle用户名和密码别人知道了,只能通过职业道德来规范了。
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的例子
-- 试下 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; /
这可以用权限控制吗。只给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;
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
CREATE OR REPLACE TRIGGER ddl_trigger BEFORE CREATE OR ALTER OR DROP ON SCHEMADECLARE 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; /
===============
界面层封住,Oracle用户名和密码不对外提供即可。
增加/减少/修改字段类型
====================
使用alter table add ...
alter table drop ...
修改字段可以使用先增加字段,然后删除来做。
如果Oracle用户名和密码别人知道了,只能通过职业道德来规范了。
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的例子
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;
/
如果实在要用触发器,给你个限制增删改的例子:
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;
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 CREATE OR ALTER OR DROP
ON SCHEMADECLARE
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;
/