db creation - required tables

[69giapmyata]

What tables are minimum required in a newly created DB? I found campaign_settings in the list of coiped tables - can't we keep this information in the CORE db?

[69giaptushka]

No we need to let the camp_user edit campaign_settings, and we are not giving the camp_user access to boswar_db. Secondly, there is no good reason to keep the detailed settings in boswar_db because they aren't used by boswar_db. Other than the initial definitions which are set and used by boswar_db (campaign name, campaign database, campaign db user and campaign db password) the detailed settings are only used by the campaign itself. The only other purpose of the boswar_db campaign_settings table is to serve as a template for the single-line campaign versions of that table.

As for the list of tables, The current list satisfies all the demands of the parser. I expect you'll have extra tables to add for manipulating groups and I expect to need an extra table or two to track units once we've figured out how we'll handle that.

[69giapstenka]

I don't understand why a user needs to enter host information.

he can't connect to the create session without connecting to the host on the server??

Not sure what will happen when we move the website and database to a real server.

I presume user connects to server via ip and php still connects to mysql server user via localhost?

Am currently blocked reinstalling mysql on my development system.

[69giaptushka]

We have two different classes of user in our db model. The central user, let's call it boswar_hq, with broad (but not root broad) powers who creates the databases for new campaigns, and a lesser user, camp_user, with powers limited to just one or more campaign dbs and no ability to edit the central boswar_db. This minimizes risk to the central database. Once the campaign has been created it is totally independent of the central db, just as with SEOW. (And this is another reason I like the idea of having a camp_admin... fewer people with the ability to mess up the central boswar_db).

The create-a-new-campaign function is doing this through php and mysqli, which needs to connect to the db. However that connection is defined in the connect_db.php file. The one you are being asked to define the first time you create a new campaign is the camp_user, with privileges limited to the campaign (plus reading and writing files on the server).

Once you have mysql running again on your development system I highly recommend that you import the Global SQL/create_boswar_hq_user.sql to create a proper boswar_hq user (now used by the most recent connect_db.php file) and also Global SQL/create_rofwar_campaign_user.sql to create the boswar user which is the default user for my four example RoF campaigns. I have been using both of these users for the past two days without any problems, and I hope you and Myata will use them also so we can be sure that we've identified the minimal set of privileges that both types of user require.

We might consider using both of these as standard users in a release, and we might consider offering the rofwar user as an option even for the first campaign to be created (where we are now asking for a new user to be created). We could easily have the campaign-create-script create that campaign db user as easily as a new campaign db user.

[69giapstenka]

Honestly I think the creation of a campaign entails the creation of a database, now that is to all extents a 'root' type user.

Also the deletion of a campaign is the same level.

If you look at that in GIAP terms we run a server. I would not let Redvo or Radco or... create a forum. They would ask a GIAP admin to create it for them and an admin would decide when something is old/obselete enough to delete.

So we have an BOSWAR Admin user, capable of creation/deletion, software installation, with full access to our web server.

This will allways be a vetted giap member, never an outsider. He will have 'root' powers.

Once the campaign is created we are at the level of a "campaign user". In database terms this requires create, delete, update tables, read files write files. They are doing all of this via the menu system and BOSWAR scripts but this is a very powerful user. As far as I can see the only thing he's missing is database create/delete.

This user class does actualy split into three types, not as far as database rights go but as far as access to the menu options.

The campaign admin will create and upddate key campaign information affecting both sides, For example sector, points values, vehicles availiable in campaign. He will be able to see the results and data from both allied and central sides to arbitrate and run planning on either side. Much of the time he will be the same as the BOSWAR Admin user but not always, this could be REDVO.

Next we have the Campaign planners same database rights as campaign admin but access only to the Central or Allied Menus.

Finaly we have the players. The only thing they need is to view the stats - and they don't need a login or user to do this, just an URL accessible to the world.

Now to keep security relatively tight against the world. We generate an HTML results file which is published on the server. This is not connected to the database and is not making online extractions.

So in application terms we have only two classes of user with the lower class split into 3 user profiles which only affect menu choices.

The techniques we are using in reading and writing files, creating and dropping tables at will need very powerfull users. If one of these users is hijacked the server will be out of control and easily wrecked. However we are not in a high risk business. As long as we backup well we could move to another physical server and be operational quickly.

----- Mail original ----- De: "=69.GIAP=TUSHKA" notifications@github.com À: "69GIAP/69GIAPBosWar" 69GIAPBosWar@noreply.github.com Cc: "69giapstenka" ptthome@free.fr Envoyé: Dimanche 13 Octobre 2013 19:29:28 Objet: Re: [69GIAPBosWar] db creation - required tables (#38)

We have two different classes of user in our db model. The central user, let's call it boswar_hq, with broad (but not root broad) powers who creates the databases for new campaigns, and a lesser user, camp_user, with powers limited to just one or more campaign dbs and no ability to edit the central boswar_db. This minimizes risk to the central database. Once the campaign has been created it is totally independent of the central db, just as with SEOW. (And this is another reason I like the idea of having a camp_admin... fewer people with the ability to mess up the central boswar_db).

The create-a-new-campaign function is doing this through php and mysqli, which needs to connect to the db. However that connection is defined in the connect_db.php file. The one you are being asked to define the first time you create a new campaign is the camp_user, with privileges limited to the campaign (plus reading and writing files on the server).

Once you have mysql running again on your development system I highly recommend that you import the Global SQL/create_boswar_hq_user.sql to create a proper boswar_hq user (now used by the most recent connect_db.php file) and also Global SQL/create_rofwar_campaign_user.sql to create the boswar user which is the default user for my four example RoF campaigns. I have been using both of these users for the past two days without any problems, and I hope you and Myata will use them also so we can be sure that we've identified the minimal set of privileges that both types of user require.

We might consider using both of these as standard users in a release.

— Reply to this email directly or view it on GitHub .

[69giaptushka]

I'm glad you now agree with me about the campaign_admin user.

As for db users, best security practice demands that a user only be granted the rights needed to get the job done. While you may think our boswar_hq user is root-like, in fact at the moment that user has only 10 of the 28 global rights that a root user has.

And, at the moment, the boswar campaign user has only one of those 28 global rights.

These two users are all we should need.

Of course it is useful to give root powers during development, but for a release we will want these users to have the minimal required privileges. We are in the process of determining exactly what those are for both db users (or db user types).

[69giapstenka]

My tests show that it's possibly impossible to get an atype3 An atype2 is proof of destruction of one span track them in stats and we will disable the bridge and measure rebuild times from there