Database security

[Sebastian Roth]

Finally I got some hours to concentrate on this topic. Turns out it’s not just the root DB password that cannot be set using identical SQL syntax across different DB versions but also the process of creating new user account and granting access. Here is a first proposal of a script to use. So far only tested on MySQL 8.0 (CentOS 8):

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI';
USE fog ;
DROP PROCEDURE IF EXISTS fog.drop_user_if_exists ;
DELIMITER $$
CREATE PROCEDURE fog.drop_user_if_exists()
BEGIN
  DECLARE foo BIGINT DEFAULT 0 ;
  SELECT COUNT(*)
  INTO foo
    FROM mysql.user
      WHERE User = 'fogdbuser' and  Host = '%';
   IF foo = 0 THEN
         CREATE USER 'fogdbuser'@'%' IDENTIFIED BY 'Passw0rd';
         GRANT ALL PRIVILEGES ON fog.* TO 'fogdbuser'@'%';
         FLUSH PRIVILEGES;
  END IF;
END ;$$
DELIMITER ;
CALL fog.drop_user_if_exists() ;
DROP PROCEDURE IF EXISTS fog.drop_users_if_exists ;
SET SQL_MODE=@OLD_SQL_MODE ;

Anyone keen to test in their machines (shouldn’t cause any trouble to your DB!) is more than welcome to!! Use:

mysql -u root -p <script.sql
mysql -u fogdbuser -pPassw0rd fog -e "SELECT * FROM images"
mysql -u root -p -e "DROP USER 'fogdbuser'@'%'"

Note: I am constantly updating the initial post to show the state of testing we have done so far.

[Sebastian Roth]

After some intense work on this I pushed a commit to the db-security branch. Just looks terrible because I created a bit of a mess when trying to pull the latest changes from dev-branch into db-security as well and merge it all in. But should still be all fine I hope. https://github.com/FOGProject/fogproject/commit/2f70a7872c379ec3c6b4a433b02c48b2b7c03d81

To see the actual diff of todays work see here: https://github.com/FOGProject/fogproject/compare/dev-branch...db-security

Just posting this here as I feel we are on a good track with this but it takes some very rough changes as well. @Tom-Elliott would you take a look at this. If you feel like it you are more than welcome to comment on things. May I ask you to not add this to working-1.6 before I have some more testing done!

So far only tested on CentOS 7 - looking forward to the results on Wayne’s great test environment.

[Tom Elliott]

I haven’t added anything in working-1.6 but I have added a few comments for review if you’d be so kind and when you have enough time.

Thank you,

[Sebastian Roth]

@Tom-Elliott Great comments, Tom! Thanks! Just pushed new commits and answered as well.

[Sebastian Roth]

@george1421 @Tom-Elliott Further tested and improved db-security branch. Also testing upgrades from 1.4.4 and 1.5.7 now. Could you think of more tests we should do? Maybe in a storage node setup? So far I have not touched the fogstorage DB user password. So it shouldn’t cause any trouble with storage node setups but you never know.

EDIT: Now that I think more about it… the fogstorage DB user password is generated as fs[0-9]* and therefore fairly easy to brute force.

Current diff: https://github.com/FOGProject/fogproject/compare/dev-branch...db-security

[george1421]

@Sebastian-Roth said in Database security:

EDIT: Now that I think more about it… the fogstorage DB user password is generated as fs[0-9]* and therefore fairly easy to brute force.

Could you run the generated password through md5sum or sha1sum to create a hash value. That hash value would then become the password. It would do a good job of scrambling of the password and make it over 15 characters (guess) long.

[Tom Elliott]

@george1421 md5 would at least be 32 characters but would do a good amount of scrambling. I don’t remember sha1-128-256-512 lengths off top of my head but pretty sure they have specific character lengths as well

[Sebastian Roth]

@george1421 I’d like to keep at least one special character in the password. Just pushed a new implementation a few minutes ago: https://github.com/FOGProject/fogproject/blob/4caa9f0e2f98a95c95057aaac9021f13ca2d9128/lib/common/functions.sh#L2471

This will generate passwords of adjustable length with at least but no more than one special character.

[george1421]

[[ $length -ge 8 && $length -le 128 ]] || length=16

For password length NIST recommends at least 12 characters min for normal users and 20 characters min for Admin accounts (yes this is my life). I know FOG is not going towards any certification, but using a standard min recommendation is always a good start. FOG admins should never need to key this password in so a longer one is a bit better. I’m wondering if its a good idea to store this password in the .fogsettings file in some kind of reversible encrypted form?

[Sebastian Roth]

@george1421 said:

For password length NIST recommends at least 12 characters min for normal users and 20 characters min for Admin accounts (yes this is my life).

I can see what you mean. I’ll make it 12 as minimum and 20 default.

I’m wondering if its a good idea to store this password in the .fogsettings file in some kind of reversible encrypted form?

For any kind of reversible encryption you need to have a secret. That needs to be stored somewhere. If a person is able to grab /opt/fog/.fogsettings the encryption key is also seen to be compromised. So I don’t see any better security with this.

@Tom-Elliott I have done a fair amount of testing now and I have a good feeling about the changes not causing too much of trouble for users installing or upgrading FOG. You are welcome to look through the current change set again and comment. I plan to merge this into dev-branch tomorrow.

[Tom Elliott]

@Sebastian-Roth while agree we need a more secure password for database and all, I don’t understand why we wouldn’t store the password with fogsettings. I say this because anybody who can get this file can just as easily get the config.class.php file which would have the password stored in plain text.

So removing it from this file doesn’t make it any more secure.

Similarly storing it in a reversible encrypted form wouldn’t matter either. Why waste time trying to encrypt or decrypt if it has to be stored in plain text on the system anyway? Just go to the file that has it stored.

Unfortunately there really isn’t a better way to access the database with a password than to have it called out in plain text to begin with.

Essentially it boils down to:

If the person can access /opt/fog/.fogsettings, they just as easily have access to the rest of the system and any files therein that may be accessible. It doesn’t matter how much we make the password secure, if they are on the system, you’re toast anyway.

Removing the password from .fogsettings just makes it harder for us and others to troubleshoot an issue with the database in general.

[Sebastian Roth]

@Tom-Elliott said in Database security:

while agree we need a more secure password for database and all, I don’t understand why we wouldn’t store the password with fogsettings

I don’t intend to remove the stored password in .fogsettings. The password used to access the DB will be in .fogsettings and config.class.php in plain text. I don’t see any way around this.

[Sebastian Roth]

Finally merged all the work into dev-branch. Done.