Computing & MySQL - How have you got it setup?

[FN-GM]

Hi,

 

How are chaps sorting out the MySQL task in the new computing curriculum please?

 

Thanks

[Steve21]

Not had any requests for MySQL at all this year. Had some normally MS SQL stuff last year though, did they add something new this year?

 

Steve

[TechieWils]

Forgive me if I'm wrong but I was under the impression that document had to be kept under lock and key as it's the actual assessment they will be doing. The Head of ICT made a point of saying it was to be kept confidential.

 

Any way I was planning on setting up WAMP on a server and making accounts for the students to log in to and use. Not really though more past that.

 

Any advice is welcome same as fn_gm

Edited July 19, 2016 by TechieWils
spelling mistakle

[FN-GM]

Forgive me if I'm wrong but I was under the impression that document had to be kept under lock and key as it's the actual assessment they will be doing. The Head of ICT made a point of saying it was to be kept confidential.

 

Any way I was planning on setting up WAMP on a server and making accounts for the students to log in to and use. Not really though more past that.

 

Any advice is welcome same as fn_gm

 

I have deleted it! Didn't know that!

 

Apologies

 

Basically,you have to create a DB and do some of the usual MySQL stuff inside of the DB.

[TechieWils]

Not a problem, just didn't want anyone in trouble. It looks like basic SQL commands and queries nothing fancy. As I said was going to investigate WAMP on a server and knocking up a SQL script making individual databases for each student to work on so I can back everything up. The notes on the document talk about students have a personal laptop with WAMP installed on it but I just think that's a bad idea.

[FN-GM]

Not a problem, just didn't want anyone in trouble. It looks like basic SQL commands and queries nothing fancy. As I said was going to investigate WAMP on a server and knocking up a SQL script making individual databases for each student to work on so I can back everything up. The notes on the document talk about students have a personal laptop with WAMP installed on it but I just think that's a bad idea.

 

Don't the students have to make the database for them selves? If thats the case how do you give them the permissions to do that and not break anyone elses?

[Blue_Cookeh]

You could install MySQL Workbench on the machines and have kids login to the MySQL DB on a remote server using their own credentials? This way you only need 1 DB server too if you setup permissions correctly.

 

Could also do this using PHPMyAdmin.

 

Permissions for MySQL: MySQL :: MySQL 5.7 Reference Manual :: 7.2.1 Privileges Provided by MySQL

 

You'd want to give them CREATE permissions.

Edited July 19, 2016 by Blue_Cookeh

[FN-GM]

You could install MySQL Workbench on the machines and have kids login to the MySQL DB on a remote server using their own credentials? This way you only need 1 DB server too if you setup permissions correctly.

 

Could also do this using PHPMyAdmin.

 

1. They need to use the CLI.

2. They need to create the DB for themselves. So that makes a problem as described in post 6

[Blue_Cookeh]

1. They need to use the CLI.

2. They need to create the DB for themselves. So that makes a problem as described in post 6

 

See my edit. You can install the MySQL CLI to remote onto a server.

 

mysql -uUSERNAME -pPASSWORD -hHOSTNAME

 

Obviously you could provide a desktop shortcut for this.

[Steve21]

Does it need to be CLI? For MS SQL our guys are using PHP, so all the creation/selects etc is done via the PHP page they run. (Only HTTP/FTP ports open on the server externally)

 

Steve

[FN-GM]

See my edit. You can install the MySQL CLI to remote onto a server.

 

mysql -uUSERNAME -pPASSWORD -hHOSTNAME

 

Obviously you could provide a desktop shortcut for this.

 

That isn't an issue, but how do we sort permissions so they can create a DB and not touch anyone elses?

 

Does it need to be CLI? For MS SQL our guys are using PHP, so all the creation/selects etc is done via the PHP page they run. (Only HTTP/FTP ports open on the server externally)

 

Steve

 

According the exam board yes.

[howartp]

I'm using the same VMs that they had/have for programming Python - luckily when I set them up last year I had the foresight to suspect someone would want PHP and MySQL at some point in the future, so I built WampServer into the base image.

 

They each have their own VM which only they can access, and only IT staff can power on, so they can do what they like to the MySQL DBs within it.

 

Peter

[FN-GM]

I'm using the same VMs that they had/have for programming Python - luckily when I set them up last year I had the foresight to suspect someone would want PHP and MySQL at some point in the future, so I built WampServer into the base image.

 

They each have their own VM which only they can access, and only IT staff can power on, so they can do what they like to the MySQL DBs within it.

 

Peter

 

Would love to do that, but we haven't got the resources

[howartp]

Would love to do that, but we haven't got the resources

Ours is running on what will be our DR equipment should the main server room go down. Seconds of a job to power off (and wipe if necessary) all the VMs, then fire up the DR.

 

I know I'm fortunate to have more resource than other schools, but do you have any backup server that could run VMs daytime and backups/DR night-time?

 

Peter

[mdrabble]

Could you not create a VM with Virtualbox with MySQL installed and save changes to the users home folder?

[FN-GM]

Could you not create a VM with Virtualbox with MySQL installed and save changes to the users home folder?

 

Machines won't cut it, they only just about running the host OS. This particular school doesn't have the IT equipment the others have.

[Blue_Cookeh]

That isn't an issue, but how do we sort permissions so they can create a DB and not touch anyone elses?

 

I just tested it.

 

CREATE USER 'blue'@'localhost' IDENTIFIED BY 'new_password';

GRANT ALL PRIVILEGES ON `blue\_%` .  * TO 'blue'@'%';

 

This will create a MySQL user called 'blue' and only allow them access to databases that are prefixed with blue_ in the name. This includes creating databases so they're forced to create databases with their names in, which makes backups far easier for you.

 

It also gives the added bonus that if they run a `show databases;` on the server, they'll only see their own.

 

Example of my CLI here with a user called tom_test with a pattern rule to only allow tom_ names:

mysql> create database MyDB;
ERROR 1044 (42000): Access denied for user 'tom_test'@'%' to database 'MyDB'
mysql> create database tom_MyDB;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| tom_MyDB           |
| tom_test123        |
| tom_testdb1        |
+--------------------+
4 rows in set (0.00 sec)

mysql>

Edited July 19, 2016 by Blue_Cookeh

[FN-GM]

I just tested it.

 

CREATE USER 'blue'@'localhost' IDENTIFIED BY 'new_password';

GRANT ALL PRIVILEGES ON `blue\_%` .  * TO 'blue'@'%';

 

This will create a MySQL user called 'blue' and only allow them access to databases that are prefixed with blue_ in the name. This includes creating databases so they're forced to create databases with their names in, which makes backups far easier for you.

 

It also gives the added bonus that if they run a `show databases;` on the server, they'll only see their own.

 

Example of my CLI here with a user called tom_test with a pattern rule to only allow tom_ names:

mysql> create database MyDB;
ERROR 1044 (42000): Access denied for user 'tom_test'@'%' to database 'MyDB'
mysql> create database tom_MyDB;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| tom_MyDB           |
| tom_test123        |
| tom_testdb1        |
+--------------------+
4 rows in set (0.00 sec)

mysql>

 

Thats great thanks.

 

Next thing, how to get them in the CLI without opening up the entire command prompt?

 

Thanks

[Marshall_IT]

I'll be watching this thread too as i've been asked to do exactly the same thing for september. on the LAST DAY OF TERM!

[mavhc]

Also you don't need all of the MySQL stuff installed, just the cli exe I think.

 

Banning command prompt = pupils that just use Office and play games. Same with task manager etc. How are you going to create programmers of people who don't understand computers?

 

Secure your systems, remind pupils it's illegal to hack and that they're being logged, reward people who find security flaws.

[Blue_Cookeh]

I'm pretty sure blocking Command Prompt is more of a hinderance for power users nowadays, rather than a deterrent for people screwing with things?

 

If you have things setup properly, users should still be able to use PowerShell and Command Prompt.

[matt40k]

Surely this is what the Raspberry Pi was designed for?

[3s-gtech]

I'm pretty sure blocking Command Prompt is more of a hinderance for power users nowadays, rather than a deterrent for people screwing with things?

 

If you have things setup properly, users should still be able to use PowerShell and Command Prompt.

 

Where appropriate. We allow them in isolated VMs. The students can run the CLIs as admins. Nothing more frustrating than a restricted and hobbled CLI for users, or broken machines/shares/servers as a sysadmin.

[markwilfan]

I setup a centos 7 server running virtualmin in my last school to.do all this. Once the virtual host template is setup it's a 1 line command to set up each user

[cogrady84]

I've just set this up this morning:

 

Installed mysql-installer-community-5.7.13.0.msi on a server with some spare resources, configured with all defaults.

Deployed GPO to controlled assessment machines with mysql-shell-1.0.4-winx64.msi

Created shortcut to MySQL Shell executable in their network start menu

Using MySQL Workbench on my workstation, configured a connection to the mysql instance and setup new users with a schema privilege allowing them to only work on a database that matches their username, granting all privileges on said schema.

After launching MySQL Shell, the command to connect to remote instance in sql/classic mode:

 

\sql

\c -c username: password@serverip:serverport

 

From this point it is just normal sql commands, no command prompt access required.