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

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
drizzle>

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.

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.

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.

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?

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: https://launchpad.net/pkg-drizzle/+download and DEBs should be coming up any day now. Or yes, you could compile from source, see the README for instructions.

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):

https://code.launchpad.net/~clint-fewbar/drizzle/regex-policy-cache-limiter

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

Backticks are fine! :)