View Single Post
Old 2nd August 2010, 15:55
till till is offline
Super Moderator
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 36,796
Thanks: 840
Thanked 5,612 Times in 4,423 Posts

Originally Posted by fatbear View Post
On the matter of Unix user names and group names and MySQL usernames:

Using the Unix GID as the MySQL username (and keeping the passwords of the client and the MySQL user sync'd) is my preference for ease of administration. I prefer highly mnemonic user and group names because:

1. It helps me to spot errors as I look at files in the Unix filesystem.

2. Such names are easier for customers (and me) to remember.

3. It appeals to customer's vanity (the acme customer would much rather see and use "acme" instead of "client23" when logging in, ftp'ing files, etc.)

To address your concerns:

1. Note that my preference is to have the Unix GID be the MySQL username for an account. This implies that the GID should be restricted to a certain number of characters less than 16. It all depends on how much variability one would want for MySQL usernames that have a GID prefix. This can be a settable parameter. But, let's say, by default, the GID length is 10. That would still leave 6 characters for MySQL "sub" users.

2. The UID is used for other Unix services (such as mail, web, and ftp), but not for MySQL.

Again, is there any reason to not allow a user of ISPConfig to not choose this approach if they want to? It does satisfy all technical requirements. Again, it may be reasonable for ISPConfig to try to influence the user's behavior. But, if the system administrator prefers to adopt naming such as I've described above, I say the control panel should let them.

Lastly, regarding the "_" issue for the database name.... It is useful to be able to just look at the database name and know which MySQL user it belongs to. In the example I previously gave (with users abc and abcdef and the database name abcdefghi), you can't tell which user owns the database named abcdefghi by simple inspection of the database name. Some character, not permitted as part of the database name prefix (the MySQL user name), would guarantee the ability to identify the database name's user by simple inspection. Note that the "_" is not special as is indicated in Section 8.2 Schema Object Names of the MySQL documentation. It is only when the underscore is used for searching that problems arise. But, that being said, I do agree that it might be best to avoid needing to escape underscores when doing searching. So, we could use other character that is not going to be allowed as a GID. I think the period (".") or the hyphen ("-") might be better choices based on your concern.

Again, this comes down to: can this be a feature, or will this require hacking of the codebase. Obviously, I prefer this to be a feature.
Feel free to implement it if you like. If it is disabled by default and the current default behaviour does not get changed, then I will integrate it in the main branch. We will also have to add some warnings so that administrators are ware that using the clientname as prefix might break their sites when they change a client.

Regarding unserscores in mysql users, this is not just related to searching, we had trhis problem in some older ispconfig 2 releases and it is a huge security problem as it allows access with phpmyadmin to databases that are not owned by the user. For example, see here:

This is not a bug. Mysql uses _ as the single-character wildcard, and % as
the multi-character wildcard in pattern matches. This is clearly explained in
the manual <>:

Note: the '_' and '%' wildcards are allowed when specifying database
names in GRANT statements that grant privileges at the global or database
levels. This means, for example, that if you want to use a '_' character
as part of a database name, you should specify it as '\_' in the GRANT
statement, to prevent the user from being able to access additional
databases matching the wildcard pattern; for example, GRANT ... ON
`foo\_bar`.* TO ....
Till Brehm
Get ISPConfig support and the ISPConfig 3 manual from
Reply With Quote