Topic: Different Database for Every Company

When sharing the same database for different companies, there exists the possibility that a user of one company maliciously overwrites the data of another company. To eliminate this risk, one has to use a different database for each company.

I have eased the process this way:

in fa/admin/create_coy.php add the following code:

function rand_string( $length ) {
    $chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";   

    $size = strlen( $chars );
    for( $i = 0; $i < $length; $i++ ) {
        $str .= $chars[ rand( 0, $size - 1 ) ];
    }

    return $str;
}

then CHANGE the following lines:
        else
        {
                $_POST['tbpref'] = $tb_pref_counter."_";
                // Insert the current settings as default
                $conn = $db_connections[user_company()];
                $_POST['name'] = '';
                $_POST['host']  = $conn['host'];
                $_POST['dbuser']  = $conn['dbuser'];
                $_POST['dbpassword']  = $conn['dbpassword'];
                $_POST['dbname']  = $conn['dbname'];
        }

TO:

        else
        {
                $_POST['tbpref'] = $tb_pref_counter."_";
                // Insert the current settings as default
                $conn = $db_connections[user_company()];
                $_POST['name'] = '';
                $_POST['host']  = $conn['host'];
                $_POST['dbuser']  = $conn['dbuser'];
                $_POST['dbpassword']  = $conn['dbpassword'];
                $_POST['dbname']  = rand_string(8);             //$conn['dbname'];
        }

This would put a random 8 character string in the database name every time a new company is about to be created. One can always overwrite what is presented.

Can we have this included in the next release?

Re: Different Database for Every Company

Even without this, one can still change the database name before submitting the form to add a new company. Infact, the new different database may already have been created outside of FA and that is what v2.3.12 actually solves.

Re: Different Database for Every Company

While it is true that one can change the database even without this modification, the default setting is to show the current database in use. Sharing the same database name between companies creates a vulnerability in the system.

4 (edited by apmuthu 09/25/2012 01:45:40 pm)

Re: Different Database for Every Company

Disable rights for the dbuser for the said default db in question!

Anyway, only the default admin company (0) gets to use the Create/Add New Company Meny Item - so where's the security vulnerability?

5 (edited by carmelr 09/26/2012 04:59:21 am)

Re: Different Database for Every Company

apmuthu wrote:

Disable rights for the dbuser for the said default db in question!

Anyway, only the default admin company (0) gets to use the Create/Add New Company Meny Item - so where's the security vulnerability?

Try this for a quick experiment.

**** Procedure removed to reduce risks to existing users ****

With the above procedure, the company with the table prefix 10 will get over-written.

Do you think that is a vulnerability? Is there a way around it?
Yes, there is. Create each new company with a different database.

Cheers

6 (edited by apmuthu 09/25/2012 07:30:38 pm)

Re: Different Database for Every Company

The same would apply to different databases (assuming db names too are sequential / guessable) if permitted for the same db user. That is why the db user too must be different for each company's different database.

A config variable can be introduced to decide if the session variables (db user, db name, db pwd) should be allowed to pre-populate the create company form with initial values. Each company should have their own db user with privileges for their company's prefix only.

Thankyou for pointing out the vulnerability with fine clarity. In the meanwhile, we should not give restore functionality to the company admins and keep it with the Super Admin only.

Another possible solution (may not apply to limited hosting accounts) would be to auto generate the db name, db username, and db password for the company using a non displayed privileged db user.

One more solution would be to parse the sqls for create / insert (no select or update) statements only and pertaining to the current user's company prefix alone during a restore. Beware of compound insert cum select statements.

7 (edited by carmelr 09/26/2012 05:01:02 am)

Re: Different Database for Every Company

apmuthu wrote:

The same would apply to different databases (assuming db names too are sequential / guessable) if permitted for the same db user. That is why the db user too must be different for each company's different database.

I have tested with different db names and the fact that the user tries to restore to his own database does not affect other companies installed. And that is why I urge one and all to have a different database for each company.

To be more clear about this:
When a user restores his database backup, FA connects him to the database that is indicated in the config_db.php file for the company he has logged in. Therefore, although the restore would be successfull, it would not overwrite other companies' data, as these would be in different databases.

As for database names being guessable, that is exactly the reason why I suggested the code in the beginning of this thread.

Cheers,
Carmelo

Re: Different Database for Every Company

from FA wiki

Create Companies.

Never use the same database for two or more companies unless you really trust every admin on all companies. As Backup and Restore area is not reserved for site admin, but available for any single company admin, tables which belong to other companies can be overwritten accidentally or intentionally without warning.

I understand your suggestion for assigning a new database to every new company.
How about the Training Co., which you suggest to keep for testing purposes, after some testing then I feel confident and I start the Real Company ... Should that be in a different database or you think it can share the same database with the Training Co.?

Many thanks

Re: Different Database for Every Company

Vdonatiello wrote:

from FA wiki

Create Companies.

Never use the same database for two or more companies unless you really trust every admin on all companies. As Backup and Restore area is not reserved for site admin, but available for any single company admin, tables which belong to other companies can be overwritten accidentally or intentionally without warning.

I understand your suggestion for assigning a new database to every new company.
How about the Training Co., which you suggest to keep for testing purposes, after some testing then I feel confident and I start the Real Company ... Should that be in a different database or you think it can share the same database with the Training Co.?

Many thanks

To play it safe, I suggest a different database for each different company. However, if you are the only one using FA on your setup, then you might opt to use the same database.

Cheers

Re: Different Database for Every Company

carmelr,

thanks for your reply

in the near future we will be 2 people using FA for the "Real Co." but i will be the "superuser" and i so i will be the only one to use the "Training Co.", my partner will use only the "Real Co."

furthermore we are on a shared webserver hosting plan and we have bought a database package including 100 MB and only 5 database, so i don't want to use 2 databases for the accounting only (of course if possible)

i have noticed the first Training Co. has "table pref 0_", then when i create the real Co. i suppose i should assign "table pref 1_", isn't it?

what is exactly "table pref"? is it to prevent company overlapping?

thanks