User access logging

[Jawelin]

I would keep a history log of all accesses to the board for auditing reasons (the third 'A' of 'security' concept, along with Authentication and Authorization.... !!!! :p ).

Infact I had some problem of stolen pw or accounts... Now, running 2.2.1 with MD5 hashing, still have some users saying they didn't something .... etc...

So, I WOULD A COMPLETE LOG of all accessed pages (even read!), with userid and IPs, not only the time-periood-limited session.
Infact I saw the session table storing that infos, but only for the time specified in options about the 'online' time...
Just later that info are deleted from that table and lost forever.
Could I - for example - move them to an history-purpose table or, better, to a seq file on the server ?

Could someone help me in this hack/not hack idea ?

Thanks a lot.
Bye

[Scott MacVicar]

I already log all logins to the board, what IP they came from and time etc, though I think logging every single action would be pushing it and take up a ridiculous amount of space.

[Jawelin]

Sorry but haven't understood... Where are you logging all these access infos ?
I guess are logged ONLY the registration time IP and the last access time to the board, not the actions and the source of these actions (actually they are trashed, with the browser info and so on...)
What else and where stored ?


P.S.: 'ridiculous amount of space'... Do you mean 'too much amount', don't you ? Yeah, I was thinking about a sequential output (appending to) file which I can 'storicize' periodically, gzip and/or delete after downloading and putting offline...
That's what I mean for auditing issue
;)

Thanks

[Scott MacVicar]

this modification i made logs the IP and userid of every user everytime they login to the board or return to the board after the session timeout, i then dump it from the mysql table and gzip it once a week then i empty the table and the process starts again, I've used it to find out the most popular ISP on my board is AOL x_X

[Jawelin]

Do you mean your great hack Failed Login Logging (https://vborg.vbsupport.ru/showthread.php?s=&threadid=32639) ?

I installed it and follow all the modifications, but it only traces the failed logins (bad pw) to the 'loginlog' table...
Not a real audit !

Or, if you mean something else, could you give me (here?) a hand to create something like yours ?
I guess I should dupe the DB-Query instruction when inserting a row into 'session' table, for example into a 'session_history' one...

Later I could drop that periodically after a backup...

What do you think about ?
Thanks.

[Jawelin]

Hey, are you there ???
Happy new year! ;)

Sorry but, as usual, I tried to do myself but this time I think it's a too-big hack for me...
I searched for all the INSERT/UPDATE to 'session' table, but they are too much - and too complex :rolleyes: - to duplicate them (as I guessed) to another 'session_history' table, for example...
Could you give an hand with the code you talk about or something to localize better the effort I should address ?
:)

Thank you very much.
Bye

[Scott MacVicar]

Nope its not the failed login hack but that inspired it :D

I will make the admin part of this hack in the super near future, but you can start the logging now.

create the following table


CREATE TABLE userlog (
userlogid int(10) unsigned NOT NULL auto_increment,
userid int(10) unsigned NOT NULL default '0',
ip varchar(20) NOT NULL default '',
atime int(10) unsigned NOT NULL default '0',
KEY id (userlogid)
) TYPE=MyISAM;


open /admin/sessions.php the following code will need to be placed somewhere in that file, maybe even multiple times, i'm still trying to work out where to put it o_O

//the creation of a new session lets log the IP and userid as we may need to trace them :D
if($bbuserinfo['userid']) {
//they have a userid so there logged in
$DB_site->query("INSERT INTO userlog (userlogid, userid, ip, atime) VALUES (NULL, '$bbuserinfo[userid]', '$session[host]', '".time()."')");
}

[Jawelin]

Thanks a lot.
I did some modificatons upon your input, as I would like to track some other field, too...
I'll check 'my version' and post it if works... ;)

Meanwhile, could you confirm the very last part of your php-code
[...], '".time().")"); ???
There's a single quote not closed near the round, isn't ?

Besides, I think the best point to include the above if-clause is just below the first$DB_site->query("INSERT INTO session ....
(as the second one is to create session for unregistered users (userid=0)... ;)
Do you agree ?

Thanks.
CU soon.
Bye

[Jawelin]

Just another question... Hard, I think :p
Well.
At the above point in sessions.php, I would know (and store into log table) if the user comes from a 'cookied' login or a 'passworded' login... :)

I tried to check the following vars, but .... :( $createanonsession
$loginpassword (if isset...)
what else ???

Besides, if I would automate like you the backup/emptying of the table, could I use any croned mysql command ? What ?

Thanks again

[Jawelin]

Originally posted by Jawelin

Besides, if I would automate like you the backup/emptying of the table, could I use any croned mysql command ? What ?

Thanks again [/B]

[Scott MacVicar]

why would you want to log the loginpass, it would have been already checked in member.php at the top and it would also compromise security and defeat the purpose of md5 thoughout the 2.2.x series.

Though the simpliest way to check if its a cookied login or not is the fact that the cookie wouldn't have been set yet meaning that $HTTP_COOKIE_VARS[bbuserid] and bbpassword and bbstyleid and bblastvisit will have just been set but they aren't taken into account until a page is loaded after they have been sent.

In case you didn't know if you send a cookie it won't take effect until after the page is reloaded, hence all the transition pages in vBulletin.

[Jawelin]

No, sorry!
I was explained bad: logging the password, even what inserted by the user, would a great security hole, expecially after v2.2.x. Of course... !!

The two or three method (vars) I listed was only due to my needs to log the type of user' access:
simply I would know if the user comes (better, if the session is created when...) from a first login, with password inserted by hand, or from a cookied session.
I tried to check the user cookie preferences, but they are almost = 1 .... :p

So I was looking for another variable, that point of /admin/session.php, to check my question... ;)
That's all...

Could you suggest any way ? I could even check the HTTP_COOKIE_VARS, but can I trust it that point of the script ?
How to check ?

More, what's the best way to backup and flush all these log tables periodically from a crone job ?
MySQL shell (bash) script or any php able to do the work ?

Thanks again. Hope it's clearer, now...
;-)

[Scott MacVicar]

probably a shell script

[Jawelin]

:stupid:

The next question is obvious... :p
I made it in MySQL area on vb.com but is still unsolved...
Do you have any idea about running several queries in the same mySql command-line statement ?
How can I dump only two or three tables, then flush them and reset the increment counter ?

Thanks again
Bye