Hot answers tagged

4

This looks like number of 100 nanosecond ticks since 1601-01-01 (FILETIME) So based on that the following works (125911584000000000 is the number of these ticks between 1601 and 2000. This is subtracted first to avoid overflowing an int) It also looks like the time returned is UTC so you will need to convert to your local time zone (which I have made an ...


3

All enabled audit rules can be found in the below views: Standard audit + FGA: select * from dba_stmt_audit_opts; select * from dba_priv_audit_opts; select * from dba_obj_audit_opts; select * from dba_audit_policies; Unified audit (12c and above): select * from audit_unified_enabled_policies; Unified audit policy details: select * from ...


2

This isn't clutter. You need to figure out why the audit is failing, and resolve it.


2

Since you are using SQL Server 2016, you are having advantage of using temporal tables. It seems like you need to have combination of things for addressing your objective. As far as tracking changes in terms of insert/updates and deletes are concerned, this can be very much addressed by using temporal table. This will also serve your second question i.e. ...


2

You cannot. You need to have SYSDBA privilege on the database. SYSDBA doest not allow to delete from DBA_AUDIT_TRAIL view but it allows to use DBMS_AUDIT_MGMT which is the right way to purge the audit trail.


2

In Oracle 11g this cannot be audited. There are ways of doing it in 12c and up. That said, only users with the "select any dictionary" privilege should be able to select from that table, and anyone with that privilege should be trusted.


2

With Unified Auditing. Oracle Database Auditing Oracle Database Unified Auditing enables selective and effective auditing inside the Oracle database using policies and conditions. The new policy based syntax simplifies management of auditing within the database and provides the ability to accelerate auditing based on conditions. For example, audit policies ...


2

There are a number of things wrong with your config file: enable true should be enabled true log_filename C:\DB_trace\audit_trace_database.log should be log_filename C:\\DB_trace\\audit_trace_database.log log_connection true should be log_connections true log_transaction true should be log_transactions true Most of these errors could have been detected by ...


2

An alternative to modifying model might be to create a stored procedure in the master database that contains whatever you need to do. Then you mark this as startup proc (sp_procoption). You might have to put in a WAITFOR in there in case SQL Server executes this before model has been recovered and tempdb has been created. For you to play with. I've only used ...


2

If I understand your question correctly, you are asking how to pass an application user name to MySQL to be recorded in an audit table. You can use a user-defined variable, which is local to the session in which it is created. Your application code will set it to the name of the logged-in user once it is authenticated. Your audit code can then reference that ...


2

Another way is to exploit JSON/JSONB functions that come in recent versions of PostgreSQL. It has the advantage of working both with anything that can be converted to a JSON object (rows or any other structured data), and you don't even need to know the record type. See my original StackOverflow post with appropriate examples.


1

We can use oracle fine grained audit (dbms_fga package). But as always - any audit costs some additional work for database )) Here is an example: SYS@mydb> BEGIN 2 DBMS_FGA.add_policy (object_schema => 'scott', 3 object_name => 'EMP', 4 policy_name => 'EMP_SEL', ...


1

I gave up on EE and instead using SQL AUDIT now. https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15


1

Short answer: Yes, it is perfectly acceptable to split the audit tables into two subject areas if one attribute is updated very frequently, and the other attribute(s) are updated infrequently. But this has more to do with lowering write overhead and possibly making changes of certain attributes easier to find, not good or bad design. Long answer: There are ...


1

I think your assessment is heading in the right direction. You do appear to be capturing too much information. Based on your description, I think you are describing a consumption event and not product history. My recommendation is to look at splitting out the consumption attributes from the PRODUCT_HISTORY table into a new table. Without knowing the ...


1

You could use a trigger on the log table that rolls back when the object_id being inserted is not found in sys.objects - since triggers are the typical mechanism we use when a foreign key doesn't make sense or isn't possible (like maintaining integrity across database or instance boundaries). But do you really want to lose all of the data because one column ...


1

Yes, that is entirely possible. The use of any privilege or grant can be audited by instance, by session, by success, or by failure. You can audit all uses, or just uses by specific users. See here for a basic guide to getting started: https://oracle-base.com/articles/8i/auditing, and here: https://oracle-base.com/articles/12c/auditing-enhancements-12cr1 For ...


1

despite having never done this before that's not a problem at all - I just would recommend you do all this on a test server first. the whole process. then you do it in live. This is how I would do it: you create the domain account sqlaudits as you mentioned and take note of its password as you will need it later on create a login for sqlaudit on the ...


1

I fully agree with Grant's comments. You have some requirement to "audit" something, so you need to meet up to those requirements. Just saying "audit" isn't enough, you have to tell them to be more specific. If they don't care enough to have a discussion, then you can argue that you already have an auditing solution - SQL Server logs ...


1

Default trace is just a collection of behavioral information about the system. It's not the same as auditing. They do different things and support different purposes. One doesn't supplant the other. Further, default trace is actually inferior to another thing running on all systems by default, the system_health Extended Events session. In terms of ...


1

Not knowing your systems or the load on them, I cannot stress enough the need to test this suggested solution, very thoroughly. However, that said, you can do this, partly, through Extended Events. Here's a sample script: CREATE EVENT SESSION [ColumnAudit] ON SERVER ADD EVENT sqlserver.sp_statement_completed (WHERE ( [sqlserver].[...


1

I am not sure that using an IsDeleted column would create unnecessary headache. Locking down the table, and allowing non-administrative action via a view which filters on that column, while not displaying it, isn't very difficult. Add an Instead Of Delete trigger to the view, and you're all set. Given that it's essentially boiler plate once you have the ...


1

Auditing should be enabled based on requirement from Compliance/IT security or Audit department of your organization. If you enable auditing as mentioned in this link, it would start auditing each and every thing. It will not only make audit files to grow rather, it would impact on performance of your instance badly. Ideally if you follow C2 auditing, it ...


1

DBMS_AUDIT_MGMT First execute INIT_CLEANUP, that you need to use only once. Next execute CREATE_PURGE_JOB with use_last_arch_timestamp => true, which creates the scheduled job for cleaning the audit trail, also need to use only once. Finally you need to take care of advancing the last archive timestamp with SET_LAST_ARCHIVE_TIMESTAMP, which you can ...


1

Oracle has a purge job that you can run to limit the number of days to keep audit records.So you can have the audit records automatically purged. As Eduard Okhvat stated, the default is to audit certain events, whether you configure it or not. You probably want to go through all of the audit events and see which ones are relevant for your organization. ...


1

Connections as sysdba or as sysoper, also startup and shutdown events are always audited, whether AUDIT_TRAIL is set or not.


1

As mentioned by Dan Guzman if you are using the T-SQL SHUTDOWN command to shut down SQL Server, you see see the details with username in two places: SQL Server Error Log Date 3/15/2020 7:44:16 PM Log SQL Server (Archive #1 - 3/15/2020 7:44:00 PM) Source spid67 Message Server shut down by NOWAIT request from login ...


1

Doc https://docs.oracle.com/database/121/SQLRF/statements_4007.htm#SQLRF01107 for AUDIT statement says "Java schema objects (sources, classes, and resources) are considered the same as procedures for purposes of auditing SQL statements." So I assume that you can audit these Java objects with AUDIT statement (but I have not tested this).


1

Depending on the method you use for auditing, you can find that information in the below views: DBA_OBJ_AUDIT_OPTS DBA_STMT_AUDIT_OPTS DBA_PRIV_AUDIT_OPTS AUDIT_UNIFIED_POLICIES AUDIT_UNIFIED_ENABLED_POLICIES The content of the audit trail, also depending on configuration and version, can be found in the below views: DBA_AUDIT_TRAIL UNIFIED_AUDIT_TRAIL


1

Assuming you can find a way for your monitoring software to use an actual account, and further assuming your list of trusted/non-audited users is more static, then using the "exclude user" options should mean less maintenance. To exclude a list of trusted users from being logged, the MariaDB audit plugin has a server_audit_excl_users option, and similarly ...


Only top voted, non community-wiki answers of a minimum length are eligible