This content has been marked as final. Show 20 replies
I was asking the above of the OP. I am presuming that the problem is with a non-application user but an individual user account. If not, an application account should only be used by the DBAs and the app owner to connect to the DB - which is a different security issue entirely.
The OP says they know there has been malicious usage - I presume he/she already has evidence of this. If so, continuing to allow the user to access the data in a Production database is crazy. First is to prevent the user from being able to perform this malicious activity and then restore the ability to perform normal operations once this has been sorted out. The priority has to be safeguard the data.
If evidence is needed of malicious usage, then the OP is getting a bit ahead of themselves in assuming its malicious - and auditing is required. The user might be accessing legitimate data using legitimate permissions, but being reckless with it.
A review of user privileges is something that should be performed on a regular basis and certainly something I would assume that the OP has already checked into.
First the log miner feature which would only allow you to see DML is a time consuming feature to use. If you want to look at activity that has already occurred it would be an option since you cannot use auditing or trace for time periods in which these features were not configured.
As far as flashback goes you have to be more specific as there is flashback database, flashback table, and flashback query. Flashback query depends on undo and once the undo is go so is your ability to flashback. Flashback database is everything to a point in time in the past so I do not see that as being an option. For that matter neither is flashback table since it is the entire table and you probably need some of the changes that were made.
Auditing which has been part of the database for as long as I can remember is probably your best bet providing you are working with 10g or higher and can used extended auditing to capture the actual SQL statement issued by the user.
HTH -- Mark D Powell --
Osama_mustafa wrote:Even if a user is at some level a legit user of an application, if there is a suspicion of malicious activity, there is a strong case for locking the user immediately. So what if he can't do his job? There are bigger considerations. As in all forensic activity, management needs to weigh the options of
marksmithusa wrote:By doing that the user will be unable to insert or update columns which maybe required by the application, so it not the right solution , as i told you before you need to check privileges for this user and grant him the required one only.
If you know that the user is executing suspicious DML operations, why not remove their ability to perform those and give them read-only access?
Is the user being malicious or just lacking in knowledge?maybe both
Auditing on all that user's activity is the best way to do this - but if I KNEW the user running MALICIOUS code, that account would be locked down immediately.to do that you should monitor your database , and check user activity prevent running unknown scripts on prod database you, for test script you should have something called Dev database
1) immediately shutting down the user to prevent further damage, or
2) allow the user to continue, but under very tight auditing/monitoring, in order to gather necessary evidence.
Just some thoughts triggered by Ed's comments:
For all we know the user is suspect because of a bug in the application behavior and data anomalies created by the user using a screen or series of screens differently than the logic should allow. If you lock the user out of the system you have to be able to explain why. If you have no proof, which audit records would give you, then depending on your legal jurisdiction you have better be very careful in what the user is told and even in what you report to management. Locking out the user is after all a management, rather than a DBA, decision.
The pre-existance or absence of published company policy on the subject of protecting corporate data would also figure into this.
IMHO -- Mark D Powell --
I totally Agree, also the company should spend small fortune to secure their database to avoid such cases like this, Best practices of monitoring focus on the activities of logon/logoff activities of administrators, database schema structure changes and DML (update, insert, delete) activities against the critical tables.and just adding to mark archive logs and LogMiner can detect wrongdoings by the database users,production databases have turned on the archive log to write archive logs to multiple destinations. The archive logs are used to restore Oracle databases to a point in time. All DDL and DML activities can be reversed using the archive logs. included to all this The fraudulent activities of SYS or SYSTEM are recorded there.
The above is conclusion for the below article
Auditing on all that user's activity is the best way to do this - but if I KNEW the user running MALICIOUS code, that account would be locked down immediately.I am auditing my bosses ;) the lead dba and the lead app dev. I suspect they are corrupted :(