英文:
How can I audit all DDL for a specific schema in Oracle with the Unified Audit Trail?
问题
我正在运行Oracle 19C,并正在寻找一种审计特定模式的所有DDL操作的方法。具体来说,我想审计对模式本身的所有更改,如创建、删除和修改表和视图、修改和/或编译函数和包,甚至截断。我对任何DML操作,如插入、更新或选择,不感兴趣。我知道我可以像这样做:
CREATE AUDIT POLICY ALTER_TABLE_POLICY
ACTIONS ALTER ON MYSCHEMA.TABLE1,
ACTIONS ALTER ON MYSCHEMA.TABLE2,
ACTIONS ALTER ON MYSCHEMA.TABLE3;
AUDIT POLICY ALTER_TABLE_POLICY;
但这对于我想做的事情来说似乎很繁琐。更不用说,它没有考虑到创建新表。肯定有更好的方法来获取我想要的所有更改。
我看到了这个主题:https://stackoverflow.com/questions/41976094/oracle-audit-trail-for-a-specific-user
但这似乎更多是用于跟踪特定用户的所有操作。我想跟踪对特定用户/模式的操作。而且,当我搜索这方面的结果时,很多结果似乎更侧重于12c及以上版本之前的解决方案。
如何利用统一审计跟踪审计特定模式的DDL操作?
英文:
I'm running Oracle 19C and looking for a way to audit all DDL for a given schema. Specifically, I want to be able to audit all changes to the schema itself. Things like creating, dropping, and altering tables and views, modifying and/or compiling functions and packages, and even truncate. I'm not interested in any DML such as inserts, updates, or selects. I know I can do something like:
CREATE AUDIT POLICY ALTER_TABLE_POLICY
ACTIONS ALTER ON MYSCHEMA.TABLE1,
ACTIONS ALTER ON MYSCHEMA.TABLE2,
ACTIONS ALTER ON MYSCHEMA.TABLE3;
AUDIT POLICY ALTER_TABLE_POLICY;
But this seems very tedious for what I want to do. Not to mention, it doesn't account for creating new tables. Surely there is a better way to get all the changes I want.
I did see this topic here: https://stackoverflow.com/questions/41976094/oracle-audit-trail-for-a-specific-user
But that seems to be more for following all the actions of a specific user. I want to track the actions ON a specific user/schema. Not to mention, a lot of the results I see when I look for this seem to focus more on solutions prior to 12c and above.
How can I audit the DDL for a given schema utilizing the unified audit trail?
答案1
得分: 1
有 enable_ddl_logging
,但存在一些问题:
- 它仅提供SQL,没有标识信息。
- 如果应用程序进程执行大量截断和临时表操作,它可能会填满警报日志。
- 警报日志无法(轻松地)以编程方式访问。
- 只有DBAs能够查看它。
- 这是全包或全不包,整个数据库,每个DDL。
更好的替代方法是创建一个系统触发器(CREATE TRIGGER myschema.tr_audit_ddl AFTER DDL ON SCHEMA
),并填充您自己的自定义审计表。在此触发器中,您可以使用预定义的变量:
ora_sysevent
ora_dict_obj_owner
ora_dict_obj_name
ora_dict_obj_type
original_sql_txt
ora_revokee
ora_grantee
当然,您还可以从 SYS_CONTEXT/USERENV
和 v$session
中获取其他字段。您可以决定忽略哪些DDL,因为它们太频繁。您可以控制谁可以访问此信息,保留多长时间等等... 您甚至可以使用表驱动规则。这比其他选项强大得多。
只要确保,在任何系统触发器中,您都捕获所有异常(WHEN OTHERS THEN NULL
),以避免在触发器代码中出现问题时干扰应用程序进程。还明智地使用 EXECUTE IMMEDIATE
打破与日志表和您引用的任何其他对象的依赖链,以便您可以在运行时捕获丢失的对象引用或其他解析问题,而不是有一个无效的触发器阻止应用程序DDL成功执行。
英文:
There is enable_ddl_logging
, but it has some problems:
- It only gives you the SQL, not identifying information.
- It can fill up an alert log if you have application processes that do a lot of truncates and temp table work.
- The alert log is not (easily) programmatically accessible.
- Only the DBAs can look at it.
- It's all-or-nothing. The whole DB, every DDL.
A better alternative is to create a system trigger (CREATE TRIGGER myschema.tr_audit_ddl AFTER DDL ON SCHEMA
) and populate your own custom audit table. Within this trigger you have predefined variables available:
ora_sysevent
ora_dict_obj_owner
ora_dict_obj_name
ora_dict_obj_type
original_sql_txt
ora_revokee
ora_grantee
You could pull in additional fields from SYS_CONTEXT/USERENV
and v$session
of course. You can decide what DDLs to ignore because they're too frequent. You can control who can access this information, how long you keep it, etc... you can even use table-drive rules. This is much more powerful than other options.
Just be sure, as with any system trigger, that you trap all exceptions (WHEN OTHERS THEN NULL
) in the outermost block to avoid disrupting an application process if you have a problem in your trigger code. It's also wise to break the dependency chain to your logging table and any other objects you reference by using EXECUTE IMMEDIATE
so you can trap missing object references or other parse problems at run time rather than have an invalid trigger that prevents application DDL from succeeding.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论