Forum archive 2000-2006

Zbigniew Fiedorowicz - WWK2 SQL Security Concerns

Zbigniew Fiedorowicz - WWK2 SQL Security Concerns

by Arnold Pizer -
Number of replies: 0
inactiveTopicWWK2 SQL Security Concerns topic started 8/4/2004; 11:42:50 AM
last post 8/5/2004; 2:30:51 PM
userZbigniew Fiedorowicz - WWK2 SQL Security Concerns  blueArrow
8/4/2004; 11:42:50 AM (reads: 1785, responses: 10)
I just took a look at our MySQL server after installing some SQL courses in WWK2, and it seems to me that there are some security shortcomings in the way things are set up. It seems that WWK2 creates two MySQL users, webworkRead and webworkWrite, without passwords, and there doesn't seem be any way of assigning passwords to them.

Another problem is that creating or deleting WWK courses requires root access to the MySQL server. This is because WWK creates a new SQL database for every course. It would be safer to create a single SQL database for all the webwork courses and then create different tables for different courses by prefixing the courseid to the current table names. This would allow the user webworkWrite to add/delete courses.

Zig

<| Post or View Comments |>


userZbigniew Fiedorowicz - Re: WWK2 SQL Security Concerns  blueArrow
8/4/2004; 12:11:01 PM (reads: 1981, responses: 1)
Never mind the first point. I found that you can specify passwords for webworkRead and webworkWrite in database.conf

However, my second point stands.

<| Post or View Comments |>


userMichael Gage - Re: WWK2 SQL Security Concerns  blueArrow
8/4/2004; 2:40:17 PM (reads: 2193, responses: 0)
I think it's a good point Zig. We noticed that moodle uses this one-database many tables approach. There is another potential problem that having many courses and many database entries causes many connections to mysql. This may or may not be faster, but there is a limit to the number of connections mysql allows.

Does anyone know of any disadvantages to using one database for all webwork courses and using course name prefixes on the tables?

--Mike

<| Post or View Comments |>


userZbigniew Fiedorowicz - Re: WWK2 SQL Security Concerns  blueArrow
8/4/2004; 11:24:04 PM (reads: 1975, responses: 0)
Hi Mike,

I've just finished hacking the WWK2 code to use this one database with course prefixes for tables approach. It only requires minor modifications to DB/Driver/SQL.pm and DB/Schema/SQL.pm, as well as somewhat more extensive modifications to Utils/CourseManagement.pm [The major modification in the last is an extensive rewrite of deleteCourseSQL().] I haven't tested it extensively, but the basic functions seem to be working correctly. [I don't understand the multiple sources stuff in addCourseSQL() and deleteCourseSQL().]

I think it is fair to say that Martin Dougiamas and Eloy Lafuente, the lead developers of Moodle, are very knowledgable about SQL. So I am fairly certain that there are no crushing disadvantages to the one database many tables approach. There are currently some very large Moodle installations - there is one in England with some 17000 users. The moodle.org site has some 2400 very active users (and steadily growing). On the other hand, one of their considerations for their Moodle database design is that it be capable of running in a shared hosting arrangement, which would not accomodate the WeBWorK many databases setup.

I will ask them for their thoughts on the matter.

Zig

<| Post or View Comments |>


userZbigniew Fiedorowicz - Re: WWK2 SQL Security Concerns  blueArrow
8/5/2004; 7:15:05 AM (reads: 1972, responses: 0)
Hi Mike,

You can see the discussion on this topic that I started on the moodle.org forum at the following URL:

http://moodle.org/mod/forum/discuss.php?d=10762&username=guest

[Drop the username=guest parameter in the URL if you want to participate in the discussion.]

Martin and another poster (who also claims extensive MySQL experience) see no efficiency benefits in the many databases approach and agree with me that there are significant security issues with it.

Zig

<| Post or View Comments |>


userZbigniew Fiedorowicz - Re: WWK2 SQL Security Concerns  blueArrow
8/5/2004; 8:30:32 AM (reads: 1930, responses: 0)
Hi Mike,

Here's a link to my SQL modifications for WeBWorK2

https://webwork2.math.ohio-state.edu/WW2SQLmods.tar.gz

Zig

PS. I should add that this code assume that the unique WeBWorK database has already been created and that it has no underscores in its name. Also that the users webworkRead and webworkWrite (or whatever names they have in database.conf) have been created and that the read user has SELECT privileges on this database and the write user has SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP privileges for this database. (Perhaps ALTER is not necessary.) In database.conf the entries for the "source" fields in dbLayouts{sql} should be "dbi:mysql:#####_$courseName" where ##### is the SQL database name.

<| Post or View Comments |>


userZbigniew Fiedorowicz - Re: WWK2 SQL Security Concerns  blueArrow
8/5/2004; 9:21:32 AM (reads: 1942, responses: 0)
Hi Mike,

I also notice that WeBWorK2 makes very little use of indexing in its SQL tables. A cursory look at the table structure seems to indicate that the only table which uses indexing is set_user, which is indexed on the psvn field. Surely more use of indexing would improve the speed?

Zig

<| Post or View Comments |>


userJohn Jones - Re: WWK2 SQL Security Concerns  blueArrow
8/5/2004; 11:17:23 AM (reads: 1960, responses: 0)
Hi Zig,

I think changes to indexing and database/table structure were coming. One option on the tables is what you did. Another was to have one database with the standard tables, but to add course as a field in each. This cuts down on the number of tables mysql would have open at a given time.

You will definitely want keys/indexing for tables if you have a large tables. I think your other post mentioned 1000 students in a course. Assigning the third or even the second problem set would take quite a long time. The alternative table structure mentioned in the first paragraph would also make it essential since it would lead to very big tables.

John

<| Post or View Comments |>


userZbigniew Fiedorowicz - Re: WWK2 SQL Security Concerns  blueArrow
8/5/2004; 12:58:18 PM (reads: 1940, responses: 0)
Hi John,

It seems to me that the current situation, i.e. encouraging WeBWorK installations to upgrade to WeBWorK2+SQL while the basic structure of the SQL DB layout is still open, will lead to future compatibility nightmares. Although I suppose one could use the export/import database facility, this will be tedious and error-prone. One would require users to export all their old databases with an older version of WeBWorK before upgrading to the newer version and reimporting. I don't think this will be very pleasant prospect for WeBWorK users.

I think that your alternative database design proposal will lead to truly humongous tables, unless you have a policy of deleting old course from previous academic terms. In our current WWK 1.9 setup we have some 90 courses from years 2000-2004. Assuming 100 students per course, 10 problem sets with 10 problems each, we wind up with the problem_user table having some 900,000 records and steadily growing.

With my proposal, you get much smaller tables. Although there are many tables in the database, you would expect only a few of them to be actively read/written to. I think in our situation, one would expect students from maybe 5 (10 tops) courses to be logged in at any given time, and only 2 tables per course, problem_user and set_user, to be in frequent use. Also I think my proposal is easier to implement with the current codebase -- indeed I've basically implemented it already.

Zig

<| Post or View Comments |>


userSam Hathaway - Re: WWK2 SQL Security Concerns  blueArrow
8/5/2004; 2:17:44 PM (reads: 1959, responses: 0)
Zig,

Thank you for your contribution to the WeBWorK project. We were already aware that the current system of using a separate database for each course is suboptimal. Your solution of using a separate group of tables in the same database for each course is excellent, assuming the current structure of the database. However, this structure is transitional, designed to allow a common API for both SQL- and DBM-based courses.

Sometime in the near future, we will be redesigning the database structure completely, to better satisfy current and future needs. During this process, we will evaluate the best way to represent "courses" within a relational model. We may go with a one-database-many-tables approach, or with some other approach.

See the WeBWorK Wiki topic DatabaseRedesign for more information. As the design process progresses, we'd appreciate your input.

We would like to add your database modifications to WeBWorK 2.1. It would help us if you could submit your changes as a patch file in unified diff format by adding a "feature request" to our bugzilla database and attaching the patch file to it.

As for upgrading current courses, it seems to me that it would be possible for per-course database courses and multi-course database courses to coexist on the same system. One would only have to "upgrade" courses if desired, and a utility could be written that would do the job in a way that would not be "tedious" or "error-prone".

Thanks again for your contribution.
-sam

<| Post or View Comments |>


userJohn Jones - Re: WWK2 SQL Security Concerns  blueArrow
8/5/2004; 2:30:51 PM (reads: 1964, responses: 0)
Hi Zig,

I can't take credit for the alternate approach I mentioned above. I knew of it and thought it was worth bringing into the discussion.

You are right that changing the database interface is problematic. In fact, any change would be. Part of the challenge for whoever codes the change is to provide a smooth transistion as well. I haven't tried it, but I think the overall setup in webwork 2 might allow it to support multiple sql setups, where individual courses can specify which they are using.

On the actual merits of large tables vs. many smaller tables, I am not sure which functions better. I am not an expert on databases, but my impression is that their claim to fame is that they are good as accessing bits out of large amounts of data. If the tables have keys, then lookup time should grow like log(number of rows).

I don't know if this is slower than having more, but smaller tables. It may even depend on the site. We run more, but smaller courses. During the fall we can easliy have 30 courses active at the same time. So, openning/closing of tables might be slower than accessing a smaller number of large tables.

By the way, I am expecting to make a script for adding indexes to existing webwork courses since I know we will need that here very soon. I will post to the list when it is available.

John

<| Post or View Comments |>