SQL Training Infrastructure

[CAM]

The NM is away so I haven't cleared this with him, I want to be armed with all the information I need before proposing it first!

 

I'm looking at getting a copy of SQL Express installed to allow students to access it for training. I banded ideas around with the junior tech who came up with the idea of installing SQL Express on the teacher's machine in our IT lab. SQL Studio on the student machines can then connect to the training database which is maintained by our Head of IT, probably using database permissions to allow them their own private space to work in. The junior tech is keen to learn and try this and I reckon it will benefit the sixth formers (SQL Server is an employable skill after all and the NM wants to learn it!).

 

What would the security and technical considerations be for this project?

[LosOjos]

First and foremost, make sure your DB permissions are water tight. You don't want students losing all of their work because another student accidentally/maliciously issues a DROP on the DB.

 

If you're using Windows machines, you can set it up to use Windows Authentication, which makes it quite easy to assign permissions. I'd seriously consider setting up each student with their own DB (just use their username for the table name for simplicity) then you can give them free reign over that DB. I'd still lock down permissions to drop the DB though, for their own good.

 

You'll want to back it up fairly regularly too for the reasons listed above. Daily 2 week rolling backups ought to be plenty I would have thought, YMMV.

[CAM]

So if I use Windows Authentication for students, I assign permissions for each DB by pulling the usernames from Active Directory? That sounds quite handy.

[LosOjos]

So if I use Windows Authentication for students, I assign permissions for each DB by pulling the usernames from Active Directory? That sounds quite handy.

 

Yeah that's the idea. You have to create a login for the server (using their domain name), then a user on each DB they can access using that login. For instance, this SQL script will create a database for the user "JoeBloggs" on the domain "ALPHA" and assign them the rights to administer it (sans 'DROP DATABASE/TABLE'):

 

CREATE DATABASE JoeBloggs;
CREATE LOGIN [ALPHA\JoeBloggs] FROM WINDOWS;
GO

USE JoeBloggs;
GO

CREATE USER [ALPHA\JoeBloggs] FROM LOGIN [ALPHA\JoeBloggs];
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLE ON JoeBloggs TO [ALPHA\JoeBloggs];

 

I've not tested it because I'm not an NM so don't have any test accounts to try it on, so I'd suggest you play around with it. You could of course create a script to parse a CSV list of usernames and set them all up for you in one go. Another way I was thinking of doing this here was to have a PHP/C# intranet page on the server which, when accessed, would check for the existence of the currently logged in Windows user in the DB. If it didn't find them, it'd run the above script to set them up. If it did find them, it'd list some details about their DB, such as table names, to give them some feedback on anything they've done. Never actually got past fag packet planning though!

Edited May 20, 2015 by LosOjos

[jinnantonnixx]

I don't want to divert you if you have a plan already, but I'm a big fan of SQLite.

https://www.sqlite.org/

 

It's a server-less system, so the database files are totally portable and can be moved anywhere. This makes management easy. SQLite is everywhere, on your phone, in your car, your TV...

 

If you fancy a GUI to administer your SQLite databases, there's a rather nice Firefox add-on to do just that.

https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

 

I've developed systems on Linux using SQLite and Python, and migrated them straight to a Windows machine and it ran with no modifications.

 

Anyway, just a thought.....

Edited May 20, 2015 by jinnantonnixx

[LosOjos]

I don't want to divert you if you have a plan already, but I'm a big fan of SQLite.

https://www.sqlite.org/

 

It's a server-less system, so the database files are totally portable and can be moved anywhere. SQLite is everywhere, on your phone, in your car, your TV...

 

If you fancy a GUI to administer your SQLite databases, there's a rather nice Firefox add-on to do just that.

https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

 

I've developed systems on Linux using SQLite and Python, and migrated them straight to a Windows machine and it ran with no modifications.

 

Anyway, just a thought.....

 

I keep meaning to look in to this, sounds perfect for school use as the DB can exist in their documents folder, totally isolated. @CAM - I'd look in to this if I were you!

[jinnantonnixx]

@CAM take a gander at this tutorial, see if it's of use. It covers the basics, code to create tables and data, as well as queries. It looks OK.

 

SQLite tutorial

 

SQLite is the most widely used database system in the world, so it's a good thing to learn anyway.

Edited May 20, 2015 by jinnantonnixx

[CAM]

I'll have a look at that, cheers.

 

Might even be useful for my work!

[matt40k]

Have you thought about what data you'll use? Got some name data you can use - https://github.com/matt40k/Names. Do you have a project idea for them to do?

[LosOjos]

Have you thought about what data you'll use? Got some name data you can use - https://github.com/matt40k/Names. Do you have a project idea for them to do?

 

I found this a while back too, can generate all sorts of random data for tests: https://www.mockaroo.com/

[jinnantonnixx]

I found this a while back too, can generate all sorts of random data for tests: https://www.mockaroo.com/

 

That's brilliant!

[matt40k]

Only bug bear is the lack of UK-ness. However everything seems to be US focused

[jinnantonnixx]

Here's something so obvious I completely overlooked it.

 

Libre Office's 'BASE' database application. https://www.libreoffice.org/discover/base/

 

I'd be quite happy using this for small applications. It has a nice front-end for table design, data entry, relations, reports and queries. Being free means the kids can download it for home use. I think it's well worth considering. Another advantage is that the entire database is stored in a single file.

 

Manual/tutorial : https://wiki.documentfoundation.org/images/e/e8/BH40-BaseHandbook.pdf

Edited May 28, 2015 by jinnantonnixx