MySQL database name options

[theScien]

I manage to configure MySQL, and I can connect and do all needed operations from ASP.NET, but there are two problems that I just can't get solved.

I'm doing all administrative operations using MySQL Control Center.

1. How can I configure MySQL so that databases created from certain users obey to a predefined name policy? For example: user1, from web site web1, can only create databses which name starts with web1_dbname, then user2 and so on. I have tried to add to the db table in MySQL the prefix web1_* and web1_% but the users would still be able to create databases without that prefix.

2. How can I configure MySQL so that all my users databases are served out a different directory per user, as opposed to the common MySQL\Data directory. Looked around for this one, and some say it's very possible but they have not posted the how.

Thanks.

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[sleipnir214]

1. You can't. Not without putting your own homegrown application between your users and the database server.

2. If this is possible, I'd love to know how myself -- but I don't think it's possible. Anyway, I can't see any real need for this feature.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[theScien]

Not possible?

I know what you mean, but I would still prefer if users can do the creation themselves with MySQLCC.

So in short, if I have 50 hosted web sites, all using MySQL, some web sites will inevitably have several databases, all served out from a single directory! What a soap that would be.

There's got to be a way, MySQL hasn't come this far just to suffer from the lack of a basic piece of functionality.

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[sleipnir214]

Wait. We've crossed wires.

MySQL doesn't store all databases in a single directory. Each database has its own directory, each table its files.

To the best of my knowledge, MySQL must store all its databases as subdirectories of a single database. It won't split them.

I don't see where this functionality is necessary.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[theScien]

I know, I'm not talking about tables, those go inside the database directory, but the databases themselfes can only go inside the data directory and that's where the problem is, with time, there will be 100's of databases all with all sorts of names, what a mess, then there will happen that a user would need to create a database which name already exists.

Wouldn't it be better, if it was possible to say all databases from web site 1 would go inside data\web1, web site inside data\web2 and so on.

In the lack of this, at least if it was possible to enforce a name policy, so web site 1 can only create tables with the prefix web1_ and so on.

I know for most people and web sites this is not an issue, but for a private hosting venture it does, 100's and 100's of databases with god knows what names, and all from a single directory.

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[sleipnir214]

Sure, that would be an interesting feature. But since MySQL's engineering methodology emphasizes light weight, speed, and robustness over feeping creaturism, it's unlikely that this kind of thing will happen any time soon. It would require way too many changes to MySQL's entire permissions system, for a start. But MySQL is distributed under the GPL, so you could always add the feature yourself.

At any rate, your perceived need for this feature also seems to be a function of the fact that you're giving your users a great deal more power over the server than is probably wise. To my experience, which spans many years and multiple database servers, users rarely need the right to create databases.

Also, I'm curious about your allowing users to communicate directly with MySQL from outside your network. I assume that you're implementing some kind of VPN system for your clients to connect to your network -- allowing open access to your database server, regardless of software, is, of course, foolhardy.

Rather than wishing for features, work around them. Set up a web page which feeds user input into some kind of script. That script will create databases, set up permissions, and enforce naming conventions.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[theScien]

Thanks, yes I guess I have to create an application for users to manage their databases.

At the moment I'm allowing them to access the server from MySQLCC but they can only access the databases they have permission to, even thou, I'm a bit raw on the administration side of things, any special advice here?

I just came from MSDE in favour of MySQL specially because it offeres better administering tools, and there's no limitations, surely it lacks stored procedures, but that will be available to us in the next major release.

Also, I'm having difficulty understanding all those MySQL database types, MyISAM, ISAM, InnoDB, and the field types, charvar, text, medtext and all that, is there any good documentation on this?

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[sleipnir214]

My general advice on database access is never let the general public even know you have a database servers. The only time I have ever allowed an outside organization to access my database server directly was when that customer had a VPN into my network. And even then, I had them locked down very tightly.

My canonical reference on MySQL is the online manual. The discussion starts in Chapter 7 of the manual (

http://www.mysql.com/doc/en/Table_types.html).

Column types are discussed in the online manual in Chapter 6 (

http://www.mysql.com/doc/en/Column_types.html).

Each type has different size limits, different table overhead.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[theScien]

Thanks you've been phenomenal and very prompt, you must sleep right next to the computer just like me.

Thanks once again.

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------