Hi.
On Tue, Nov 20, 2001 at 04:59:38PM -0500, [EMAIL PROTECTED] wrote:
[...]
> I'd like to normalize these fields by having a separate 'filenames'
> table with an integer key that's used in the 'transfers' table. But
> since I don't know what all the files will be until I load the
> table, I have to do several passes through the files to load all the
> data, or populate it as I go -- and in either case, I have to
> maintain a local associative array to load the appropriate keys into
> each record, which means that perl's doing most of the work that it
> seems mysql should, and it's much more time consuming to load and
> maintain.
Well, that depends on the point of view. IMHO it's not the task of the
database to pre-process data, but the one of the application.
> Is there any way to autoload data while normalizing what gets
> inserted? This seems like a relatively common situation.
As far as I know, there is no reasonable way to get the database to do
this automatically. You can do this by database means, if you
want. E.g.
CREATE TABLE filename (
id INT AUTO_INCREMENT PRIMARY KEY,
filename VARCHAR(255),
UNIQUE(filename)
) SELECT NULL, DISTINCT(filename) FROM data;
[the same for other fields]
CREATE TABLE data_neu (
filename_id INT,
when_id INT,
[other fields]
) SELECT f.id, w.id [, ..., other fields]
FROM filename f, when w [, ...], data
WHERE f.filename = data.filename AND
w.when_id = data.when AND
[...]
Well, I think you get the idea. If that will be "better" than a
solution within your application, I don't know.
[...]
> The docs are sparse on using the mysql.host table,
See http://www.mysql.com/doc/P/r/Privileges.html, especially the
following:
----------------------------------------------------------------------
* The user table scope fields determine whether to allow or reject
incoming connections. For allowed connections, any privileges
granted in the user table indicate the user's global (superuser)
privileges. These privileges apply to all databases on the
server.
* The db and host tables are used together:
* The db table scope fields determine which users can access
which databases from which hosts. The privilege fields
determine which operations are allowed.
* The host table is used as an extension of the db table
when you want a given db table entry to apply to several
hosts. For example, if you want a user to be able to use a
database from several hosts in your network, leave the
Host value empty in the user's db table entry, then
populate the host table with an entry for each of those
hosts. This mechanism is described more detail in section
4.2.9 Access Control, Stage 2: Request Verification.
----------------------------------------------------------------------
Also see http://www.mysql.com/doc/R/e/Request_access.html
> but it seems that I can make this work by leaving the Host column
> blank for a user in the mysql.user table and putting all the allowed
> IPs in the mysql.host table ... but this doesn't work -- any IP that
> can get through the firewall can log in to the server with the
> appropriate password.
Yes, as explained above, only the "user" table is used to determine
whether to allow or reject incoming connections. The host table is
only used to check access privileges.
> Using DNS wildcards doesn't work because of our weird local DNS
> config.
Well, it seems, that this is your real problem. IMHO it's not the task
of privilege system is not intended to remedy a weird DNS config.
The usual advice in your situation would indeed be to use wildcards
either for IPs or host names. Aren't you able to at least specify IP
aliases on the database server itself in the hosts table?
> I can work around this by putting [EMAIL PROTECTED]@ip6 for
> each user in the mysql.user table, but this is a maintenance
> headache -- password changes need to be duped to each record, and
> each time we add a server or change an IP, it requires multiple new
> records. Am I missing how the mysql.host table works?
Well, there is no way around listing all hosts in the user table,
because that is checked on incoming connections. You can use the hosts
table to avoid listing all ips again in the db table.
Additionally, one can use the hosts table to create groups of hosts of
different trust levels, because the privileges in the hosts tables are
ANDed, i.e. you can set the hosts table so that users coming from the
internet will have less privileges than users connecting from the
intranet.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
