notification when alter occurs on oracle database

Question

We have a database that many persons have to have access to. I am looking for a way that will allow us to get notification whenever "alter" occurs on this database, so other parties can be aware of it. Please advise.

Answer 1

Many persons should NOT have ALTER permission on a production database - very few people should have ALTER permission on a production database. Actually, you probably shouldn't have many people empowered to make changes in test databases, either.

You can consider whether auditing is the correct solution - in some DBMS, it would be one way of detecting changes, but alerting would be harder (other than by some variation on the theme of 'grepping the audit log'). There may be other ways in Oracle.

Answer 2

You can create TRIGGERs to catch a number of database events...

http://www.psoug.org/reference/ddl_trigger.html

...including before/after an ALTER on a schema.

Answer 3

ALTER what ?

ALTER SESSION may be a very common command (especially ALTER SESSION SET NLS_DATE_FORMAT or CURRENT_SCHEMA).

More commonly you'd be want to track ALTER schema_object, maybe ALTER SYSTEM and ALTER DATABASE

Answer 4

Regarding your comment above:

It is development database, so we need many people to get access to it. – whizmaven (8 hours ago)

@Jonathan Leffler is correect: Many people may have access to it, but the group of people who have DDL rights (CREATE, DROP, ALTER, etc.) should be very limited.

The better solution is for any such changes to be done locally (either on a locally hosted DB, or as a different user account on a shared test DB). Only once the changes are verified there should they be rolled out to a fully shared environment. And that rollout/change-control process should be handled in a much more formal manner.

At a bare minimum, upon that rollout, an email notification of the changes and ramifications of those changes (new testing needed, updates, etc.) should be used.

Answer 5

Seems 110g put it into consideration:

http://sysdba.wordpress.com/2007/10/16/ddd-logging-in-the-alert-log-in-11g/