For security purpose, we will create a database log that will contain all changes done on different tables on the database, to achieve this we will use triggers as stated here but my concern is that if the system admin or anyone who has the root privilege changes the data on the logs for their benefit it will then make having logs meaningless. thus, I would like to know if there is a way for me to prevent anyone and I mean no one at all from doing any changes on the logs table, i.e dropping the table, updating, and deleting a row. if this is even possible? also in regards to my logs table, is it possible to keep track of the previous data that was changed using the update query? I would like to have a previous and new data on my logs table so that we may know what changes were made.
-
1Dump the new content daily to csv and email it to someone,but the question is if you dont trust your sys admin than you have bigger problems – Mihai Oct 23 '15 at 13:57
-
2sounds like a lot of fun where you work – Drew Oct 23 '15 at 14:02
-
if money is involved you can't really trust anyone, @Mihai that's actually not a really bad idea, maybe I should just limit their privilege to not being able to delete any row, it's not possible to limit the privilege per table right, only per database? – magicianiam Oct 23 '15 at 14:06
-
This sounds like you're plotting on building a nuclear bomb to kill a mosquito in the next room... as soon as you want to do something like this with a **relational** database, you can be 100% certain you guys did something wrong in the architecture (thus you ended up doing this, and got stuck on permission model). – Mjh Oct 23 '15 at 14:30
3 Answers
The problem you are trying to fix is hard, as you want someone who can administer you system, but you don't want them to be able to actually do something with all parts of the system. That means you either need to administer the system yourself and give someone limited access, trust all administrators, or look for an external solution.
What you could do is write your logs to a system where only you (or at least: a different adminsitrotor then the first) have access.
Then, if you only ever write (and don't allow changes/updates and deletes) on this system, you will be able to keep a trusted log and even spot inconsistencies in case of tampering.
A second method would be to use a specific method to write logs, one that adds a signed message. In this manner you can be sure that the logs have been added by that system. If you'd also save (signed) message of the state of the complete system, you are probably going to be able to recognize any tampering. The 'system' used for signing should live on another machine obviously, making it somewhat equivalent to the first option.
- 64,065
- 16
- 119
- 163
-
by signed message you mean a code right? that only I can understand, that would not be a bad idea at all, though considering this will be done using triggers adding a code would be troublesome or not? I'm not really sure as this will be my first time using triggers, and I'm just refreshing on my Java as well. I might as well be the super admin for the system and just limit the access of the system admin, it's not like they need to manually change the data anyway. – magicianiam Oct 23 '15 at 14:12
-
If you want to be safe against the system adminstrator, you cannot just use triggers. they can be read, changed, etc. For signing you need to have the signing done by an external system, and in such a way that you cannot have someone call this system to do their nasty work of course. You could indeed make yourself the administrator, and just not give any others the rights to change the table, that would do it, but is hardly a structural solution I'd say. – Nanne Oct 23 '15 at 14:19
-
what other alternatives can you suggest aside from triggers for me to log any changes on the tables in the database? so far I've only read about triggers in my research, that is indeed true it would be last resort solution for me to be the administrator. this is a really hard dilemma thank you for your time – magicianiam Oct 23 '15 at 14:22
-
well, you could just have the contents of your database signed. So on app-level add a signed message that makes you KNOW it is from the app. Any changes from another source (e.g. an admin trying to fix his salary) will not have the sigingn. Be sure to a. have the signing secure from said admin, and b. sign all data going in to the db – Nanne Oct 23 '15 at 14:30
-
I kinda get it now, but I have to make sure the signed message are random and that only I know about them though I still need to keep logs so I'm guessing I would still use some triggers for them – magicianiam Oct 23 '15 at 14:38
There is no way to stop root access from having permissions to make alterations. A combination approach can help you detect tampering though. You could create another server that has more limited access and clone the database table there. Log all login activity on both servers and cross backup the logs between servers. also, make very regular off server backups. You could also create a hashing table that matches each row of the log table. They would not only have to find the code that creates the hash, but reverse engineer it and alter the time stamp to match. However, I think your best bet is to make a cloned server that has no net login. Physical login only. If you think there has been any tampering, you will have to do some forensics. You can even add a USB key to the physical clone server and keep it with a CEO or something. Of course, if you can't trust the sysadmin's, no matter what your job is very difficult. The trick is not to create solid wall, but a fine net and scrutinize everything coming through the net.
Once you setup the Master Slave relationship, and only give untrusted users access to the slave database, you won't need to alter your code. Just use the master database as the primary in your code. The link below is information on setting up a master slave replication. To be fully effective though, these need to be on different servers. I don't know how this solution would work on one server. It may be possible, I just don't know.
- 820
- 11
- 29
-
encrypting the logs is also not a bad idea, not only would they know what's written there and they won't be also to crack it. but would the root user have access to the trigger code in mysql? – magicianiam Oct 23 '15 at 14:16
-
If you are just using triggers yes. You need an something outside of the database to encrypt or create a hash. This only adds complexity to the hack, not making it impossible. Really, the best solution is to keep logs and fast synchronization backups as well as a database clone. Often times logs are written to a different server that doesn't have any simple access path. – Brandon Bearden Oct 23 '15 at 14:21
-
true it is possible to just create the logs on a different database where I am the only user as to prevent anyone from manipulating the data, it seems to be the safest approach, but how would I approach this solution using database triggers? Do I connect to 2 databases in my Java code and run the insert query on the different database? forgive me as I have not yet experienced using 2 database at the same time – magicianiam Oct 23 '15 at 14:26
-
-
I would have to think harder about this... but if you setup a master slave replication and used the slave as the primary database, and all writes were done to the master, tampering on the slave would be noticed. Admins have access to the slave, only trusted workers have access to the master. And to answer your question, at this point, you wouldn't have to connect two DB's to your code. – Brandon Bearden Oct 23 '15 at 14:49
-
I also did some reading and I found out that I can use triggers for 2 databases, also they will all be in one server so no server to server communication here if you can modify your answer to present a pseudocode it would really be appreciated. Do I really need to setup a database relationship for this? thank you again – magicianiam Oct 23 '15 at 14:54
Open PhpMyAdmin open the table and assign table level privileges on the table
- 2,248
- 1
- 10
- 15
-
it is possible. Limit admin access to that table from a particular IP and rest can only read. – Lucky Chingi Oct 23 '15 at 14:10