MYSQL :: Create Users Table For Users To Log On Or ...?

How To Create Users On A Mac?
I have installed MySql on my Mac, running Mac OSX 10.4

MySql is started automatically on system startup, and I can see the
"MySql" icon in the system preferences. So far, everything should run
normally.

Now how can I administrate, in order to specify users? From the
readme file, I concluded that by default, the at least a user
"mysql" should be initially defined with empty password, but when
I enter on the bash command line

mysql -u mysql

I get the error message

'Access denied for user 'mysql'@'localhost' (using password: NO)'

Create Users In MySQL Programmatically
I'm looking for a way to create a new user for a MySQL database. This should be done most preferrable w ADO. Is it possible? Perhaps even to append an user to an existing user group within the database?

Grab All Users Not In Second Table.
I have two tables. I want to grab all the users from the users table who are not found in the second table. It's just seems to be one of those days where I can't perform the simple tasks, I think I need to go back to bed.

[table] users
[field] userid

[table] user_details
[field] userid

Select userid from users where userid not in user details

Best Practices For A 'Users' Table
I am wondering what would be the best way to design the table(s) for users in a database, or at least get some opionions of how people would go about it.

Say I have the normal stuff about a user such as name, email, phone etc, a good number of user attributes. Then I also have profile kind of attributes such as photo, more personal details, various preferences etc, again pleanty of attibutes. Then I also have a bunch of access or priviledges attributes such as status and whether they can access/modify certain modules.

I could get all these in a single table since a user is going to potentially have all of these attributes once (though a lot could initially be null) but this is going to be one huge (wide) table maybe 30 columns or more.

Would it make sense to split it into 3 tables like 'users', 'profiles', 'priviledges'? If so, should all these be created at the same time when a user signs up or as needed when a user decides to add a profile or get assigned special proviledges. If I create then on demand I will probably be saving a lot of unnecessary rows but I will have to be dealing with outer joins and defaults most of the time, ie more complex code. On the other hand if I depend on all three existing (besides the waste issue) I might get the wrong results if, for any reason, a row for each user fails to get created in all tables (or is accidentally removed).

Table Of Users Having Many To Many Relationship With Itself?
i hope to implement a user subscription feature on my webdatabase, where users can subscribe to each other's postings.

Can i achieve this by having one table to map the USERIDs to each other, and then relate this to the main USER table?

Currently I have two user tables that are exact mirrors of each other, and then a junction table to relate the two, but im beginning to think this is kinda dumb.

Fields In Users Db Table For Authentication
which fields do you place in your db for user authenication?

for example..:
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`Username` VARCHAR(40) NOT NULL default '',
`Password` VARCHAR(40) NOT NULL default '',
`Lastlogin` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

i wonder for some more useful fields for software logging/management people sometimes (or seldom :-)) use like:
lastip
createdon
lastlogin
active ('yes', 'no', 'banned') -

Can't Add New Users & Passwords To Grant Table
I am running MySQL 4.1.7 on my Windows XP system. I can create databases, ect. on the command line, but when I try to run a PHP script I receive the 'Access denied' error message. I have tried using the Grant command to add a new user but nothing comes up when I show SHOW GRANT except the root user. How do I add the new users for running the PHP script?

Self Join (?) To Get Data For Multiple Users From One Table
I am trying to generate a report containing ebay feedback scores for multiple users. Here is the database structure for the table that contains the scores:

TABLE: feedback

identities_id int(10) <- contains the user id which is stored in another table
feedback int(10) <- contains the feedback score for a specific date and time
date_recorded datetime <- date the feedback was captured
I need to get the feedback scores for let's say two users (I will want up to five but I figure the query will be almost the same) over a thirty day period. Here is the query I have created thus far (which does not return the correct results):

mysql
SELECT user1.feedback AS user1feedback
, user2.feedback AS user2feedback
, UNIX_TIMESTAMP(user1.date_recorded) AS date_recorded
FROM feedback as user1
LEFT JOIN feedback as user2 ON user1.date_recorded = user2.date_recorded
WHERE user1.identities_id = 1
OR user2.identities_id = 2
AND user1.date_recorded > NOW() - INTERVAL 30 DAY
ORDER BY date_recorded ASC

This returns virtually the same data for both users which means I am not distinguishing the two properly.

Replace Some Username Values With The Users ID In A Different Table
I really can't figure this out. I want to replace some username values with the users ID in a different table.

The tables are

pictures
user (Example FRED)
picture (Example pic.gif)

users
id (Example 5233)
user (Example FRED)

What I want to do is replace the user column in pictures with the correct id from the users table

So in the pictures table, it would no longer be FRED it would be 5233

Is there a query I can run to do this?

I'd really appreciate some feedback

Obtain Most Popular Cities From My Users Table
I've got a 'users' table:

USER_ID --- CITY_ID
1 ---- 32
2 ---- 20
3----- 32
4 ---- 32
5 ----- 17
....

I would like to obtain the most popular cities from this table, and the number of occurrences. Is this possible to make it easy with an SQL statement?

Making Site Login Add Users To Phpbb Database Table
I have a login script for my site that I got off the net and was planning to use, but, I also wanted to make it so that when a user signed up thru the registration form it would also add them to the phpbb database's table. It wasn't discussed heavily from where i got the script but from what was mentioned the encryptions differ which results in a rejected login. Code:

Users
This is my first time with MySQL need a point in the right direction ;).
I have a private page where only one user can access. On the page there is a number beside the number there is a button. Each time the user presses the button the number goes one down (and the value should stay like this it shouldn't return when the user comes back!) when it is zero the button is disabled.

Edited 1 time(s). Last edit at 03/11/2006 07:14AM

How To Switch Users
I tryed my sql manual and could not find a solution to my problem.

when i open mysql command line client 5.x it prompts for root password and when supplied i loged in sucessfully. that is ok.

my question is how to login as a diffrent user from mysql prompt?

assume currently i have loged in as root and wanted to login as xyz.

How To Select All Users?
How do i get all the users that are listed in my database to show up in a table?

Would it be something like ?

<?php SELECT username, id FROM 'users' WHERE rank by 'id' ?>

How would I do that? and how would I make it into a table?

List All Users
I'm running through a book on mySQL and have messed up adding users. I've got everything sorted now, but I want to clean up all the screwed up ones I've entered along the way. The only problem is I can't remember what I typed, so don't know what to delete. Is there a way of listing all the users, so I can pull off the ones I don't need?

Merge Users
im completly new to mysql and have not really touched it before.
i have a gaming website running php fusion, i have a main site and several other subsites (one for each game played) what i want is once someone registers on the top site they are registered on all the sites, php fusion isnt yet able to do this so i thought maybe i could do it in mysql, is it possible to have 2 or more tables that will always be the same apart from the names? ie main_user and bf2_user both with the same info in? either this or can you assign more than one name to the same table?

Users Privilege
i noticed that when i connect to MySQL with php using:

mysql_connect();
or:
mysql_connect("dbhost", "dbusername", "dbpassword");

always will be connected and the user can do anything like creating databases or delete.
how can i prevent it? i just want the root user can do it.

Can't Log In B/c Deleted All Users
I installed the mysql-administrator GUI admin program, and my first task was to make it more secure by disabling anonymous logins and deleting/changing root access. I used the mysql-administrator to delete all accounts, and my goal was to then add another admin account right after, but mysql-administrator crashed on me and had a seg fault for some reason.

Now I don't seem to have any login ability as all users are gone. I've found articles online that speak of changing the root password, but I can't even log in as root or any other user (anonymous, included), so how do I get in? Luckily, I just started playing with mysql and have no valuable data. I'm running Fedora Core 4, and did a 'yum remove' of mysql and mysql-server. Then did a 'yum install' of both of tham again.

The problem is it's still acting like the old install (still looking for old config/password files of the old install??). Can someone tell me how to change/add users or completely remove mysql/d, and install again?

Removing Users
I can't seem to figure out how to remove users with php. I tried to do it with this
$query = mysql_query("REVOKE ALL PRIVILEGES, GRANT OPTION FROM user");

Where user is the name of the user I want to delete. That's what it said to do in the GRANT/REVOKE syntax part of the site but it doesnt do anything when I run the script.

Creating New Users
I have added a new user to the mysql.user table and gave it the same permissions as the 'root' user (all Y's in the database).

When I use these details in an asp connection string I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver]Access denied for user 'nmssiteuser'@'localhost' (using password: YES)

Adding New Users
I can't seem to add a new user to my database! I've tried it through phpMyAdmin (under privileges) and also running the query

GRANT ALL PRIVILEGES ON *.* TO 'myusername'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION

which returned no errors and according to phpMyAdmin the user is entered into the `users` table. But when I try to login with that name I get

Access denied for user: 'myusername@localhost' (Using password: YES)

So all I can use to login is 'root' with no password. Not very secure!

DB Design Users
If you have a database with several table and u want users to access the information. What is the available methods to allow these users to access information ( READING and ADDING at least).

Do I add users like the root. Or creating table which hold users informations in which case I wonder how these users access data without mysql user???

No Users In Mysql
I am in the process of setting up a 'testing server' using IIS, PHP, MySQL etc. The server is up and running and PHP is working fine. I have installed MySQL 4.1.12a and the server is up and running, I have managed to connect to the 'mysql' db through the console without any problems.

However, when I installed phpMyAdmin I got the 'client does not support authentication' error message. I have found this in the manual and understand how to deal with it, but on closer inspection there are no users listed within the user table in 'mysql'. As far as I am aware there should be some default users with a new install??

How do I setup a couple of new users and ensure that phpMyAdmin can access the server etc?

Current Users
How can I check what are the current users right now (except for the admin)?
I can't find this in a manual.

Setting Up Users
I have a website and db where people can login get setup and then enter information into the db, everybody has a unique client number, can I create a user for the db when they set themselves up as a user and restrict them from changing anything except data that is connected to their client number?

Users And Permission
Being that no user is accepted from the command line (even root)
using the command:

mysql -u root

does anyone know if editing the 'users' database in /var/log/mysql
with vi would help?

Listing Users
I would like to list the users and their permissions on my MySQL server.

None Of Users Privileges
I have no privileges anymore to create a database or view privileges and users. Does somebody know how I can add a user or give my root all privileges again?

Adding Users
I can add users but how do I make it so the users can not see all the DB, I know I can restrict the user so they cannot veiw the data but I dont want them to even see the other DB's

ADDING NEW USERS
I have set up a linux machine running ubuntu. I finally was able to login to mysql with root, but I am unable to add other users. I use this command

mysql> grant select, insert, update, delete, index, alter, create, drop
-> on books.*
-> to bookorama identified by 'bookorama123';

and the response I get is:

QUERY OK 0 ROWS affected

I have no clue as to what is wrong. I tried logging into the user I thought I created, but it acts like it's not there.

List All Users
im trying to make a activity list that list all users who havent logged in in the last 60 days

i have the following code but it wont only list the last 60 days activity....

SELECT * FROM hq_user
WHERE user_currentvisit < (CURRENT_TIMESTAMP() - INTERVAL 60 DAY);
ORDER BY user_currentvisit ASC;

Creating Users
I've been having a little trouble creating users cross-platform. I have
a little script that works just fine on Solaris 8 using this command:

eval "/path/to/mysql --user=root --password=*****
--socket=/path/to/socket --database=mysql --execute="INSERT INTO user
(Host, User, Password, Select_priv, Insert_priv, Update_priv,
Delete_priv, Shutdown_priv, File_priv, Create_tmp_table_priv,
Lock_tables_priv) VALUES ('localhost', 'LocalUser', PASSWORD('******'),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')""

With the obvious changes to make the statement actually work. However,
if I move this over to Linux, I get Invalid Authorization Specification
errors when I try to connect as LocalUser after a reload, and using
GRANT seems never actually to grant privileges (e.g. no "Y"s in the user
table). Does anybody know how I can successfully create a user with my
script?

Ranking Users
i'm trying to rank the users in our site.
table contains:
- userid
- userrates
and with thousands of records.
member with the highest rate goes #1, next is #2 and so on.
i want the query to be like:
Code:
<something goes here> WHERE userid = '$userid' order by userrates desc
from the code above it will show the rank of this user.
then the next user logsin and it shows again his/her ranking.
hope someone understands this
any input is appreaciated

Finding Users
ive got 2 tables, one of users and one of orders created by those users. The orders keep track of the user_id. I am looking to get a list of users who have not placed an order, or in other words, users who do not have their user_id in the order table.

Users And Privileges
I just installed mysql and was testing to see if i could setup privileges, now I am logged in as root using the mysql console and wanted to create a user and also give them some privileges..
I am using

mysql> GRANT SELECT,INSERT,UPDATE
-> ON *.*
-> TO bro@localhost
-> IDENTIFIED BY 'bobo';

mysql doesn`t give me an error or anything so i figured that it accepted it, but when i try to login using this new user it seems as if its now there. now i used phpMyAdmin to login using root then went to look at privileges and I donb`t see the new user anywhere..
I guess i did something wrong somewhere, any ideas on what it was? abit lost as im new and just following an ebook on this stuff

Registered Users
I have a simple bit of mysql to display the number of users on my service, by simply selecting the highest userid from the database.

"SELECT MAX(id) FROM users";

It would appear that i have not thought this through correctly, as when a user is deleted from the database, it still shows the number of registered users as being the same.
Is there an easy way to cound the number of registered users, without affecting performance too much?

Users Without Entries
Note that I'm using mysql 3....ugh.
I have a table containing users, primary key is 'id'
Another table contains their activities on a specific day
What I want to find is all users who have not entered an activity between 2 dates.
Basically the problem is that I don't know how to join 1 table along only non-existant rows of another.

Users And Trucks
I have 2 tables users and trucks. In the trucks I have a field that stores the users id.
I want to make a query that will give my all the trucks which users id doesn't exist in the table users. So if you know how this query should look like please respond.

This query gives me the total opppsite of what I want.
SELECT * FROM users, trucks WHERE truck_user=user_id

Delete All Users
I want to delete all users in my db with the userid between 10 and 20. But the following sql code wont let me.

delete * from nn_users
where userid
between (10 and 20);

Copying Users From One Database To Another
I have a database on my clients hosting server, and I want to set up these exact users on my MySQL instance on my local PC. How can I do this? Or how can I see the permissions set for each user so that I can create them from new and set the relevant priviliges?

Best Way To Track Online Users?
I'm currently using a custom-made login class that basically sends a REPLACE INTO online_user SET id=$userid every time a page with a registered session is loaded

Adding Users In Phpmyadmin
Why is it always so difficult to create new mysql users? I just can't get the hang of it, whatever I method I follow from the mysql.com website just doesn't work.

I am running mysql 4.1.20 can someone please tell me how I can create new users?

Droppingg All Users From A Database
Please could someone tell me how I can drop all users from an SQL database even when they are connected. I need to detach a database but it wont let me until all users are disconnected. Is there a stored procedure to drop all users in one hit?

Most Active Users Query
I have a site where users can post in different categories (topics, questions, answers, comments). There is a table for each of those types, and each one has a userId field on it.
I'd like to add a "most active users" section that shows the top 10 users with the most entries in topics + questions + answers + comments, but I'm not sure what query to use to get that information.

Newbie: Creating Users?
I'm tring to create new users. I've read the docs and still kee recieving the error:

mysql> CREATE USER joeuser IDENTIFIED BY PASSWORD somepass;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'USER joeuser IDENTIFIED BY PASSWORD somepass' at line 1

How can I check to see if I have access to create users?
I created tables, I don't understand why I can't create users.

13.5.1.1. CREATE USER Syntax
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...

The CREATE USER statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new record in the mysql.user table that has no privileges. An error occurs if the account already exists.

The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the keyword PASSWORD. See Section 13.5.1.3, â€œGRANT and REVOKE Syntaxâ€.

Best Practice: Different Db For Different Users OR User ID In Same Db?
I am not sure what the most effective way to organize a db for users
is.
I have 40 users (teachers) and 20 tables (grades, assignments,
students, etc).
Each needs access to its OWN grades, assignemnts, etc.

NOW I am not sure how to orgainize users in the DB. Right now I have
the code for 1 user and need to adapt it to several users.

1. A user id in each table and the same db for all users.?

OR

2. A different db with its own set of tables (same names) for each
user.?

Option 1. would be easier for me because it would only involve adding
an id to a the connection string in the include. Option 2, would
require modifying query strings throughout the code in the
application.

However I heard that ISPs usually limit the number of dbases per Mysql
server (don't know why).
On the other hand the dbase per user approach seems to me more
efficient since the number of records in a table is greatly reduced
and each user has access only to its own db.

What is best/common practice in this situation having in mind that the
number of users is limited to around 40 if that makes any diffrence at
all.