Can we log changing of object in database. For example I change something in procedure. And I want t-CodePudding

Log changing by using trigger. I know about old and new in trigger, where you can find out old row of DML and new. But I don't know how to log what user changes in object.

CodePudding user response：

There's no way I'm aware of to track old code in Oracle. You would need to use an external tool like Git to properly apply version control and catalog your code. If you want to track who changes code in the database, then use auditing; that is the preferred method of tracking DDL changes. Using USER_OBJECTS will only show you the latest change, not a complete history, so it is easy to miss things. Flashback generally has a very limited time span to allow you to look back, so that's not ideal for your purposes either.

Auditing can capture most if not all session parameters, include client OS user, database proxy user, and others so that you can identify the true user even when a common database account/schema is being used by several developers.

CodePudding user response：

I don't know how to log what user changes in object.

You said it. It is user, a function that returns current session user.

SQL> connect scott/tiger
Connected.
SQL>
SQL> select user from dual;

USER
------------------------------
SCOTT

SQL>

Therefore, just include user into your trigger and that should do.

Based on comment you posted, it looks you're looking for user who modified the procedure code (not who modified data via the procedure).

In that case, that's the procedure's owner (unless someone else has been granted ALTER ANY PROCEDURE system privilege).

If many people log into that schema and can modify procedure's code, then: think whether that's a correct approach to the database. There should be a development database, with versioning enabled (consider Git or Subversion, for example) which then collects data about what's been changed in code, who did it and when.

As of production database: developers shouldn't be allowed to access it. Let the DBA apply changes to objects.

If you query user_objects, there's information of the last DDL time.

SQL> create or replace procedure p_test is
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

SQL> select created, last_ddl_time from user_objects where object_name = 'P_TEST';

CREATED             LAST_DDL_TIME
------------------- -------------------
03.10.2021 20:59:53 03.10.2021 20:59:53

SQL> alter procedure p_test compile;

Procedure altered.

SQL> select created, last_ddl_time from user_objects where object_name = 'P_TEST';

CREATED             LAST_DDL_TIME
------------------- -------------------
03.10.2021 20:59:53 03.10.2021 21:00:08

SQL>

Additionally: if you use flashback, you can see how the procedure looked like some time ago, but I'm not sure you can use it for logging purposes.

SQL> connect scott/tiger
Connected.
SQL> create or replace procedure p_test is
  2  begin
  3    dbms_output.put_line('Hello');
  4  end;
  5  /

Procedure created.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select text from dba_source
  2  as of timestamp systimestamp - interval '5' minute
  3  where owner = 'SCOTT'
  4    and name = 'P_TEST';

TEXT
--------------------------------------------------------------------------------
procedure p_test is
begin
  null;
end;

SQL>