Cannot create databases with name different than the username with simple-user-policy-plugin

Asked by Theodotos Andreou on 2012-01-20

I want to create a mysql like database for the students in the university I work. Community editions of mysql do not support authentication through pam (I want mysql to authenticate through pam, not vice versa) so mysql is excluded. drizzle supoorts this through the auth-pam plugin and throught the simple-user-policy plugin users can create and manage their own databases. The problem is that the usernmae have dots in them eg "name.surname" and drizzle refuses to create a database with a dot in the name. Any ideas about possible workarounds?

Question information

English Edit question
Drizzle Edit question
No assignee Edit question
Solved by:
Theodotos Andreou
Last query:
Last reply:
Revision history for this message
Henrik Ingo (hingo) said :

Hi Theodotos!

Nice to see that the fliexibility offered by the Drizzle authentication and authorization plugins is interesting to users.

A dot of course has special meaning in SQL: SELECT * FROM myschema.mytable.

However, you can use the backtick to quote names and create objects with almost any characters in them:

drizzle> create schema `henrik.ingo`;
Query OK, 1 row affected (0.0085 sec)

drizzle> use henrik.ingo
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Schema changed

If your students mainly have one schema of their own and do their work inside of that, it is probably fine, but there might be cases where they too have to use the backtick to quote their schema name. Also, I don't know how this will work in something like JDBC and PHP driver connection strings...

It might therefore also be a good idea in the future to allow drizzle users to do something where a schema name with underscore (henrik_ingo) would match a username with a dot (henrik.ingo). This would probably require like 2 lines of code in simple_user_policy. If you want to file a feature request in the bug tracker, it would make for a great "low-hanging-fruit" feature. Maybe even one of your students will implement it!

If you're using Drizzle for education, I'd like to keep in touch and hear about your experiences. My email is <email address hidden> or just use the drizzle-discuss mailing list.

Revision history for this message
Theodotos Andreou (theodotos) said :

Thanks Henry. I never thought to use backticks, although I did try quotes and double quotes. This solved the problem!

The students are not actually mine; I am merely the sysadmin of the server they connect. But nevertheless feel free to contact me about the whole experience. On the end of the semester I will post my experiences on your discuss list.

I will file a feature request as you suggested.

Revision history for this message
Henrik Ingo (hingo) said :

Btw, I realize now you could already use regex_policy to create a rule that does exactly this: matches username and also considerd _ and . equals.

Revision history for this message
Theodotos Andreou (theodotos) said :

The regex_policy plugin looks promising. It can replace also replace the functionality of GRANT and REVOKE in mysql AFAICT. But Is not in the drizzle ppa. And it is not included in the main package of drizzle. Any idea where to find it? Or do I have to compile it?

Revision history for this message
Henrik Ingo (hingo) said :

You're right, it wasn't in the 7 (2011.03.x) release.

We are working on publishing debs and rpms for our Fremont beta series (will be known as 7.1). In fact, for Centos I already did a first round: and DEBs should be coming up any day now. Or yes, you could compile from source, see the README for instructions.

Revision history for this message
Clint Byrum (clint-fewbar) said :

Please also be careful with regex_policy. It was a proof of concept and it needs some work. In particular, it caches denials in memory, and so can grow memory usage quite a bit. It also should, IMO, stat the file and load changes, but currently requires a restart of drizzle to pick them up.

There is a branch that fixes the memory growth, but it was rejected at one point because it caused a segfault on SuSE (I suspect this was a boost bug for which I was never able to find a workaround):

Probably worth trying out with drizzle's current CI targets.

Revision history for this message
Theodotos Andreou (theodotos) said :

Backticks are fine! :)