Database Security Config

[jafarian]

Are there any recommendations for the appropriate privilage setting, or database GRANT's, for the "shopper" userid. I would assume, for optimal security, we would want to use an alternate userid with more limited privs than the privileged userid used to set-up the db.

[jafarian]

to clarify a bit more...

 

During osC installation you are first prompted for the mysql admin username/password. This is used to create and populate the tables within the database.

 

Then we are promplted again for the mysql "shopper" username/password (which shows that the designer are very aware of the security issues hear); however, the installation script assumes this secondary username/password is already setup.

 

I have always ended up using my privileged username/password for the second prompt as well becuase the installation fails if the username/password entered doesn't already exit.

 

I haven't seen any discussion on the appropriate access rights for the second, non-privileged "shopper" mysql username/password.

 

Has anyone setup a seconday, non-privileged, shopper userid?

if so, what are the minimal access rights required?

[jafarian]

Since there were no responses, I assume that there isn't much interest in this area. I am only a beginner, but it seems to me that databas security needs a little consideration.

 

My premis is that when we initially get our database created for us, we get a username that has all privileges including UPDATE even DROP table. Do we really want to operate an online store where any potential shopper has complete control over the database - well of course not. You want to give all the appropriate access for the shopper to browse and order products, but you don't want them to change product description, prices...etc

 

osCommerce designers do have this in mind; however, it's not discussed much. in fact when you first install osC, you wil see that your are prompted for both and admin database userid/password and a shopper database userid/password.

 

I went through and looked at all the tables and came up with the minimum access required by any shopper.

 

NOTE: this is a draft proposal for discussion

*** IT HAS NOT BEEN TESTED

 

shopperuid = your non-privileged database username

shopperpw = your non-privileged password for database username

 

Once the username and password has been established, then the following sql statements might be appropriate for a more secure osCommerce store.

 

 

GRANT select,insert,update ON address_book TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,insert,update ON address_format TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON banners TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,update ON banners_history TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON categories TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON categories_description TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON configuration TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON configuration_group TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,update ON counter TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,update ON counter_history TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON countries TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON currencies TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,insert,update ON customers TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT all ON customers_basket TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT all ON customers_basket_attributes TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT all ON customers_info TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON languages TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON manufacturers TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON manufacturers_info TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON newsletters TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT all ON orders TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT all ON orders_products TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT all ON orders_status TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,insert,update ON orders_status_history TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,insert,update ON orders_products_attributes TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,insert,update ON orders_products_download TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,insert,update ON orders_total TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,update ON products TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON products_attributes TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON products_attributes_download TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON products_description TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,insert,update ON products_notifications TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON products_options TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON products_options_values TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON products_options_values_to_products_options TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON products_to_categories TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,insert,update ON reviews TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,insert,update ON reviews_description TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,insert,update ON sessions TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON specials TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON tax_class TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON tax_rates TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON geo_zones TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select,update ON whos_online TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON zones TO shopperuid IDENTIFIED BY 'shopperpw';

GRANT select ON zones_to_geo_zones TO shopperuid IDENTIFIED BY 'shopperpw';

 

Comments/Suggestion?

[smiddy]

I'm also after the info that you seek. I've just got a copy of source and need to know what the shopperid priv. need to be to maintain security.

 

It's only my second DB so seek some input but as yet can't find out much, as the database security documentation pages were not included in zip.

 

If I find out any more I'll reply, could please advise me if you get solution.

[jafarian]

Any comments from the osCommerce design team?

 

Is this an issue we need to be concerned about?

 

I would be more than happy to research and contibute further in this area if need be.