Security: is 'root' truly neccessary?
Being new to MySQL, it took a while to grok how security works. Now that I have a bit of a better understanding, a mental revalation is coming to the surface of my mind: since mysql users are NOT unix/windows-domain users, is the root user truly needed for a functional mysql environment? I do realize that there needs to be some user who essentially has all the grantable columns set to Y in the USER table, otherwise you could lose the ability to add or delete users, specify new databases, etc. I'm thinking this super user could (should?) be identified by something such as dba or admin -- anything other than the name of root. This would avoid the [probable] security hole of using the unix password as the mysql password for the root user (something I suspect many people have done without realizing the implications) simply because there would be no root user. I'm kind of guessing that one reason that the name root was chosen was because the command-line interface defaults the user name to your (unix) session name. By pre-building a root user, the authors avoided the need to teach the use of the -u switch during the initial setup of mySql (which is good and bad: good because it is one less thing for a new mysql admin to have to learn, bad because new admins haven't even been introduced to the security system, so they are likely to use their actual root password because they haven't yet been informed that mysql-users unix-users...) - 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
RE: query problem
I am by no means an SQL-expert, but I'll give this a shot... :)
-Original Message-
From: Richard Brenner [mailto:[EMAIL PROTECTED]]
Subject: query problem
I have two tables with the following structure:
Users:
| Field| Type
| id | int(10) unsigned
| name | blob
[etc]
+--+--
answers:
+--+-+
| id | int(10) unsigned|
| userid | int(10) unsigned|
| question | int(10) unsigned|
| correct | set('true','false') |
| date | date|
+--+-+
This is for a quiz. Every user has to answer 4 questions ...
I want to print out all users, that
have answered all 4 questions correct.
Can I do this with one query?
I built a couple of tables similar to what you have [trimmed to the
essentials], populated with some data, and ran the following:
SELECT id,name,userid,correct,count(correct) as numcorrect
FROM users,answers
WHEREid=userid AND correct='true'
GROUP BY id
which created:
++-++-++
| id | name| userid | correct | numcorrect |
++-++-++
| 1 | alfred | 1 | true| 3 |
| 2 | bobby | 2 | true| 4 |
| 3 | carol | 3 | true| 2 |
| 4 | diane | 4 | true| 3 |
| 5 | edward | 5 | true| 3 |
| 6 | frank | 6 | true| 3 |
| 7 | george | 7 | true| 1 |
| 8 | harry | 8 | true| 3 |
| 9 | larry | 9 | true| 1 |
| 10 | mark| 10 | true| 3 |
| 11 | nancy | 11 | true| 4 |
| 12 | oliver | 12 | true| 3 |
| 13 | paul| 13 | true| 2 |
| 14 | quentin | 14 | true| 4 |
| 15 | ralph | 15 | true| 3 |
| 16 | samuel | 16 | true| 3 |
| 17 | thomas | 17 | true| 1 |
| 18 | ursula | 18 | true| 4 |
| 19 | victor | 19 | true| 2 |
| 20 | walter | 20 | true| 3 |
| 21 | xavier | 21 | true| 4 |
| 22 | yvonne | 22 | true| 3 |
| 23 | zack| 23 | true| 2 |
| 24 | igor| 24 | true| 3 |
| 25 | jack| 25 | true| 4 |
| 26 | kristen | 26 | true| 4 |
++-++-++
unfortunately, adding and numcorrect=4 to the WHERE clause caused an error
(numcorrect undefined), but if this were put into a temporary table, it is
then trivial to
SELECT * FROM results WHERE numcorrect=4;
-
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
RE: mysql is driving me mad (3)
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Subject: [which I usually snip as it's redundant] Re: RE: mysql is driving me mad (3) [however it appears it would have avoided this:] Your message cannot be posted because it appears to be either spam or simply off topic to our filter.[...] first review the text of the message to make sure it has [a keyword of sql, query, or (I presume) MySQL] -Original Message- From: Bob G [mailto:[EMAIL PROTECTED]] [...] I (re) created the table as follows ;- create table clients( [...] purchasers_name varchar (50),[...] ); I put the data away thus :- Set objRS = Server.CreateObject(ADODB.Recordset) [...] objRS(Purchasers_name) = Session(customerName) ** this is the culprit * [...] objRS.Update Anything less than 15 characters works o.k. anything more it produces an error. Here is a screwy question for you: is the length of the VERY FIRST item you put into this set 15 characters? I ran into a similar problem where the first time I opened a dataset I could enter values for any length up to the actual maximum, but after I closed the set and re-opened it (i.e, the next time the program ran), the longest item in the set determined the maximum length. IT TURNS OUT that in the myodbc connector/driver, there is a page of options -- you need to ensure bit 1 (client can't handle the real length) is set. Bit 2 may be of use as well, (though I can't remember off the top of my head what it is -- I just know I have it set) This bit of info was gleaned from an example in the anual that used an option value of 3 (and, of course, little explanantion as to WHY that particular option value was chosen...) Well, what do you know -- I didn't actually use one of those keywords in my message after all... - 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
possibly off topic: any VB programmers out there?
I've got a strange problem using VB to connect to a mysql database via ODBC. The problem isn't with connecting, but rather with one particular control -- the datalist/combo control. In fact, the problem doesn't seem to have anything to do with databases directly, BUT I figured that there is sufficient VB talent on this list that someone has seen this and knows how to fix it. (indirectly, I'm not able to reliably detect if a row exists or not using this control) Background: the datacombo control will automatically load itself with values taken from a dataset -- this is really cool for lookup fields where you want to make it easy for the end user to select related data. Being a combo type item, it ALSO allows the user to type in a completely NEW value, and that's where things seem to go downhill. In particular, the control has a property/field called macthedwithlist which returns true if the user selects or types in a value actually in the list, and another property called selecteditem which returns a bookmark [record number] of the selected value. This bookmark can then be applied to the related dataset to retrieve the full record for display or editing. The problem I'm running into is that if you TYPE IN the value directly (i.e., without using the mouse to point-n-click or the arrow keys to scroll through the list), the bookmark contains a null value EVEN IF THE USER TYPES IN A MATCHING ENTRY. (and here, a null bookmark would imply that the entered value doesn't match anything) What I'm trying to accomplish is to combine two activities into one logical activity -- for example, inventory item maintenance. I want to build a form that allows editing of all the particulars for an inventory item (item code, description, qty, etc.) There are two (well, three) activities that can occur: a new item can be added, or an existing item can be modified (or deleted). The traditional way to do this would be to place an add button on the form to clear the form and allow an item to be defined, generating an annoying error if the user enters an existing code number. Likewise, you would place a find button that generates an equally annoying error if you DON'T type in the proper item code. What I want to do is combine these activities via the combo box -- if you select a existing item the form displays the data and allows for modifications. Likewise, if you type in a new value, the program implicitly adds a new (blank) record -- note that since I've determined from the key value entered whether to add or modify an entry, there is no need to display an annoying error. The part my program falls over on is that if you TYPE in an EXISTING item code, the two tests that you can perform to see if this is a new entry generate conflicting values: matchedwithlist will return TRUE (meaning we should MODIFY the entry), and selecteditem=null also returns TRUE (which would indicate we need to ADD a new entry...) - 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
user password self management -- how?
This is actually a bit of a general-database question, since I'm sure it has the same or similar repercussions regardless of which RDBMS you use, but I'm curious: is it possible to safely allow users to maintain their own passwords to a database? [and if so, how?] I'm sure I can do this programmatically by having my program connect to the database using the root user/password, but that means embedding the password in the program [i.e., viewable with string...] so once a user deciphers that, they are free to run mysql -u root from anywhere the application could run. maybe I'm thinking in a deranged way, but it seems that granting update access WITHOUT granting select access to the user table in mysql [and perhaps even limited to the password column] would enable a user to change their password without being able to read anyone else's (encrypted) password value; HOWEVER that would also allow them to CHANGE anyone ELSE'S password [even root] -- is it possible to limit access at the ROW level? (i.e., user x can only select/update records with user='x') The reason I'm doing this is that I'm designing an application that logs on with a fixed username and uses it's own user/password table to validate end-users. The program notes if a password in the database is blank -- if so, it compares the user-supplied password with a (eventually well-known) first-time password and forces the user to set a new password, so obviously the fixed user ID needs the ability to udpate the application's password table, which has the same problems as the system level table, but limits the damage that can be caused to just the application and not all of mysql... (i.e., crackers could determine the fixed user's password by scanning the executable, but that won't let them harm anything other than the application itself...) Ultimately, I'd like to use mysql's user/password (grant) facillities directly, but since the application would be distributed to offices nationwide [all phoning home to the central database via the internet], I don't want to burden the one system manager/DBA with the responsibility of maintaining every single clerk and technician that can use the system -- I'm willing to let the office manager at each location enter and maintain employees local to that office [which makes sense because that person also does the hiring/firing...] but for obvious reasons, the managers cannot manipulate values for other offices [though, I suppose, I have to trust SOME of them... ;) ] If I could guarantee that the only access to the database was via my program, then I'm certain I could create logic to maintain passwords safely; however this is not the case -- not only is mysql available as a simple client, but anyone with a smattering of knowledge of Access and the myodbc connector could create their own manipulation program and circumvent any safety protocols I establish. - 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
Bitten by a strange bug...
I've been pounding my head against a wall for the last couple of days trying to make a simple program work (to then use as the basis for future programs, etc. -- in other words, I'm still just learning this stuff) I'd really like to point the finger at Microsoft visual basic, but I've been around long enough to know that there are too many fingers in this pie to write it off that easily. So, I'm not sure if I've encountered a real bug, or just a known issue for which there may be a workaround -- what I've tried so far hasn't done what I need, and the workaround I *know* will work is a kludge at best: ugly, but functional. But enough of the background, here is the problem: developing a visual-basic (6) application using myodbc connected to a mysql database on the network. Adding records works well the first time, but after the database has closed (i.e., the next time I run the program), I run into problems. I've tracked it down to what VB believes is the maximum field size for a given field, and it appears to be limited to whatever the longest value is in the particular field -- when a table is new (empty), there are no entries, so VB thinks the fields are -1 in length (technically, unlimited), and the program works fine. The next time the program runs, the fields definedlength property is set to whatever the longest value happens to be in the table (hence the ugly workaround is to insert a bogus entry with spaces or some filler character padded out to the maximum length -- this is fine for master [key] tables, but for detail entries it might become problematic.) I've even tried the pad char fields to maximum option via the ODBC driver window in the control panel, but that doesn't seem to have any effect. The version(s) of the various programs is or should be the latest -- I just downloaded and installed the vis-studio service pack 5 MDAC 2.7 today; the myodbc driver is the stable one from the myodbc site [downloaded a couple of days ago]; and mysql itself was installed initially from the SuSE 8.0 distribution updated accordingly, so it is no more than a month or two old at best. So, is this a real bug [for which I should develop a proper repeatable environment/program], a known issue [with hopefully a simple solution], or am I simply doing it wrong -- within VB there is a query-like program which can retrieve update arbitrary tables and it CAN enter a new field with longer-than-previous values [and I'm reasonably certain this is a vb/vc++ application], so I know somehow/somewhere this is possible. Another possible part of the wrinkle: I'm developing this using what VB calls a dataenvironment, and that is the component that seems to have the problem. Another wizard within VB is a data view window, and that one seems to be able to determine the proper field sizes, but when a table is drag-n-dropped from the data view window to the dataenvironment builder (component), it loses that bit of information about each field. Tom Emerson p.s. although I've posted this to the main mysql list, I have a sneaking suspicion this is more appropriate for the myodbc list -- if so, please point me that way and I'll take the discussion there :) - 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
