MYSQL :: User Permissions: MySQL DB Design

Access And MySQL User Permissions
I'm wondering how Access FrontEnd Users' username and passwords may be sent within a connection string when performing a query on MySQL tables. Maybe I'm just missing how Access and MySQl communicate here? If I set up security permissions on the Access front end forms/reports and database/table/record priveleges in MySQL how do I get them to work in conjunction with each other?

The only way I know how is to create the database connection in code using ADO and pass the username and password of the current user in the connection string. I don't know how to get a handle on the current user's username and password that were supplied when logging into Access. Could anyone shed some light as to how this arrangement is supposed to operate when verfiying user permissions on MySQL objects?

User Doesn't Have Permissions?
I install a mysql backup utility to backup my database if things go wrong. I went to test it to make sure it would work as it emails me the sql file, I deleted my database (using phpmyadmin) and tried to import the sql file which shoots me an error giving me an error that I didn't have permission to create a database. How do I give myself permission to create a database?

User Permissions
Is there a special permission required for a user to be able to run a "select ... into dumpfile" statement? I'm getting a 1045 error when attempting this on my server. I've already verified that file permissions are not an issue.

User Permissions
Is there a way to query MySQL to find out what the permissions of the user currently connected
For Example:
Database: customer_contacts
User : cust1
Table: Archives
User Permissions: Read Only(SELECT).

Is there a way to ask MySQL what User cust1 can do?

User Permissions
I'm kinda new to administering MySQL. I have a remote db that I want to
backup to my local MySQL server (windows). I can do a dump of the db
from phpMyAdmin on the remote server. I try running it on my local
server through the MySQL Control Center, and it says "Read Only". I
don't have to enter password for root@localhost to login to the Control
Center. I assume I don't have permissions to run DDL scripts as this
user, and I can't figure out how to change this users permissions or
create a new user with different privileges.Someone please help. I need to set this notebook up so I can work while
out of town this week.

Database User Permissions.
I have duplicated a working database which I wish to you. I have also duplicated the code (.asp) which reads, writes and updates the database.

All works great when I am logged in to my PC because I have admin rights on this PC but when I test the exact same website with a general users account they get the following error:

Inheritance Of User Permissions
how the user permissions can be inherited... or if they can be herited or not. how do you do manage this and what options do you have?

Security Question For User Permissions
I've had a site for half a year now, and I have this set up:

1 User with all permission to add/edit/delete etc. data
1 User with SELECT and INDEX permissions

My question is do I need to make this (second) user to be secure? Because on my new site I want to my users(going to create user system) to be able to add comments and such and I don't want injections or etc. security hazards.

Giving Database User INDEX Permissions
Just setting a few database users in cpanel, I see that one option I can grant is INDEX.

And I found this in the cpanel documentation:
INDEX (Allows USERNAME to create index on table column in the database. This can speed up a database.)

I won't be granting alter permissions, which is what I thought would be needed to add a permanent index?

I'm wondering what exactly is meant by this?

Does it mean, that they have access to an index key that already exists?

Presumably mysql allows the word INDEX in the query that maybe replicates the above? In which case, giving a user INDEX permission means letting them create a "temporary index" in the query?

User Messaging System Design
I am designing a website in which users can send messages to one
another. Each user will have a mailbox where they can read their
incoming and outgoing messages and delete them if necessary. Anyone
have suggestions on the optimal way to design the database for such a
system?

Site User To A Paid Member? Database Design HELP
I am currently working on a website where there are 2 types of users. The regular site user and a paid member. I am trying to figure out the best way to design the database (mysql) without redundancy issues.

The site user can eventually become a paid member but a member does not necessarily have to be a regular site user (in terms of category). Information stored on a site user - first name, last name, address etc. The userID being the primary key. The member pretty much the same thing but new info will be memberID, join date, expiry date, status (pending, active, expired).

I thought the easiest way to do this was to have separate tables. Do you think the best way?

My thoughts are: When the regular site user becomes a member then store the userID in the members table.

The issue I am faced with is how to store the other pieces of information. The address is not mandatory for the site user but is mandatory for the member. So, if the site user has an address then store it in the member table? that's redundancy there. Then a member do not necessarily have to be a site user in the sense of storing the info in that table.

Design issues to think about
- activation of membership once a confirmed payment has been received - Client is using Paypal.
-What if the user clicks on membership and the user is also a site user?
-What if the user is logged in as a site user and clicks membership?

I would greatly appreciate your design recommendations.

Best Design For Hierachical User Subscription To Manydifferent Things?
Currently:
Message system allows users to "subscribe" to messages that other users submit. So User1 can subscribe to User2's submitted messages. These messages will appear in User1's inbox.

Groups also exist. Each group has a memberbase of users, and each group has a related theme. For example, a group called "MUSIC LOVERS" can have members who are musicians. Users can also subscribe to "GROUPS" so any messages posted by these users in these groups, can also appear in the user's INBOX.

Currently i have 2 tables that link up:
A) USERID / USERID (facilitate user-user subscription)
B) USERID /GROUPID (facilitate user-group subscription)

However, i foresee in the near future, i will add many many more subscription options. THus i was thinking, rather than having to create many new tables for each  type (group/user/etc) of subscription, would it be feasible to create just ONE table that will universally cater to ANY type of subscription (now or in the future).

My idea was to create a linking table with fields
A)USERID / String Defining Subscription

So for example:
User A subscribes to:
Group: "Music"
Group: "Cars"
User: "UserB"
User: "UserC"

The link table would then have entries as follows (assuming USERA's USERID is 0001):

USERID / Subscription String
0001   /  #Music
0001   /  #Cars
0001   /  $UserB
0001   /  $UserC

To find the list of users that userA subscribed to, i would

1. filter for all "0001s" (thus having a list of all the Subscription strings belonging to 0001)

2. filter these strings for all "$" (this symbol denotes a subscription to another user) I would thus obtain "$UserB" and "$UserC"

3  Extract the "USERX" portion from "$USERX" and go to the user table and run a search for "UserX" in this case "UserB" and "UserC" grab their respective USERIDs.

Same would be for the groups as well.

In fact, I could even define the Subscription string like this: #Cars$UserB.

This would mean that a person can subscribe to messages from the "Cars" group that are ONLY sent by "UserB".

I feel this would make my script future proof for watever type of subscription (possibly even hierachical) options.

I was wondering if there were any other more efficient methods of accomplishing this, esp since this may require a lot of queries to just accomplish one function, possibly loading the server qutie a bit.

MySQL Permissions
I would like mysql to create and manage the database files in
my normal /home directory (rather than in the mysql directories). Is
this possible? Is this desirable? Which settings would I need to
change to allow this? Or should I just let mysql create the databases
in it's default directory and set the permissions there?

MySQL Permissions
I have installed mysql (under SuSE 8.1) and everything seems to work
fine when I am logged in as root. However, with my normal user profile
I can get a mysql> prompt but have no permissions to carry out any
database operations.

I would really like to work with my normal profile and am hoping that it
is merely a question of setting permissions correctly. Can anyone tell
me which files and paths I should check (and change)?

Ideally, I would like mysql to create and manage the database files in
my normal /home directory (rather than in the mysql directories). Is
this possible? Is this desirable? Which settings would I need to
change to allow this? Or should I just let mysql create the databases
in it's default directory and set the permissions there?

Problem With MySQL Connection Permissions.
I'm running mySQL locally on my computer. When I run my C# application I have the connect as

string MySQLDBConnect = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=filetransfer;" +
"UID=********;" +
"PASSWORD=********;" +
"OPTION=3";

Everything works fine when it's set at localhost. But that's pretty useless as it onlly works on mmy PC. So instead I'll put in my IP adress. Let's say I change Localhost to 111.111.111.111. Then I get an error.

ERROR #0
Message: [MySQL][ODBC 3.51 Driver] Host '111.111.111.111' is not allowed to connect to this MySQL server.
Native: 1130
Source:
SQL: HY000

The database does get connected. I can tell because I can see the connect going through my firewall. But I get a premission error. I'm guessing it's with my database setup but I couldn't be sure.

Creating Group Permissions In MySQL
I would like to be able to assign my employees to groups based on what department they are in (all users in accounting would belong to the accounting group, all users in customer service would belong to the customer service group).

Then I want to be able to grant database permissions based on what group a user is in: something like

GRANT SELECT TO myTable FOR AccountingGroup
(obviously not ANSI SQL).

Is there an easy way to do this?

Newb Starting MySql-Permissions
I have debian running and this is how I have it setup

# ls -ld /var/lib/mysql
drwxr-xr-x 4 mysql mysql 4096 Aug 19 11:29 /var/lib/mysql

I was shown how to setup a password for user mysql here
http://dle.ascendant.ca/restoring-mysql-root-HOWTO.txt,

because at one time all I would have to do it hit enter after
# mysql -h localhost -p -u mysql
Enter password: (no password is added I just hit enter)
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 16 to server version: 4.0.14-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> ( can do whatever I want now???)

If I type in the correct password it lets me in, if I type in no passowrd it
lets me in, if I type in the INCORRECT password it does NOT let me in. How
can I make it so that if no password is typed it still treats it as
incorrect?

Permissions In Mysql Control Center
I need to allow 3 different servers to access my MySQL Server. How do I do this? Also, I was told that having MySQL configured so it doesn't restrict access is bad - how do I make sure that's not the case?I feel like I know what they're saying, but I don't know how to actually implement this in the Control Center, and I must be using the wrong keywords because when I look it up, all I find is stuff on how to connect to the MySQL Server from my web page using php (which is what I'm using and already doing).

Deleted Root User From Mysql.user !!!
While trying to grant access to root user, i deleted all entries for root from mysql.user.

I've tried to use root% (anonimous built-in administrator user) but to no good).

Is there another admin user i could use to run mysql monitor and insert root records to mysql.user? I've read

Could I use a copy of the mysql.user table?

I'm using version 5.0.16-standard on Linux Fedora Core 3.

Before ruinin the root user, i made a backup of a databasefrom the same server (not mysql database); i was wondering if that backup contains the mysql.user information somehow?

How Best To Design DB On MySQL 5.0 So That ..
I couldn't find this by a forum search.

On my local PC, I have MYSQL 5.x and was wondering how best to design certain fields in my table that MySQl 4.1 ( my hosting service ) won't complain about.

For instance, I noticed that when I pushed a PHP app from my local PC, up to my Host provider account, things failed because MySQl 4.1 didn't like 1024 character varchar's.

Is there a guide somewhere that would make it easy to design an downward-compatible DB ?

OO Design With MySQL
I know how to pull data from MySQL into an ASP.NET page using MySql.Data.MySqlClient and its connection, command, and data reader classes. Now I would like to kick it up a notch and start working in OO design principles into my code. The tutorials I've seen seem to be geared toward SQL Server and so I'm spinning my wheels.

Can anybody help me find a good tutorial (or a good book) that spells out in detail how to get the most out of MySQL in an ASP.NET environment. I'm especially interested in learning how to build a data access layer and/or typed datasets.

Mysql Table Design
Any advise are welcome with this inquiry. I have learned MySQL for two weeks after buying books from Kevin Yank and I would like to make a project called Attendance Monitoring System or TimeIn-TimeOut Monitoring System using PHP/MySQL flatforms. However, I have a huge vague or dont know what to do on database or table designing.

Is it feasible if I make a one table for employee's TimeIn and TimeOut? Or do I have to make a separate table for TimeIn and TimeOut. If so, how I would fetch and match employees TimeIn with Timeout for particular shifts. Have 3 shifts in work 23:00 - 08:00, 02:00 - 22:00 and 8:00 - 17:00 on weekdays and weekends are their rest days.

MySql Design Tools
Hello I would like to know if you could advise which would be good design tools for MySql databases.

I already have tried MySQL-Front which is ok but no very graphical, and I tried DBDesigner 4 but this is ineffective as it refuses to connect to any of MySql databases I have setup.

Design Effective MySQL DB
I develop multi-user web tracker system (php+mysql). In these days I have cca 150 users and each of the users has a set of the tables which are all included in central DB. In DB is on the whole cca 900 tables. Each user has also one (log) table in which are logged accesses to the web page. The table has 16 fields. It's OK, but if it's observed some web with huge traffic, the table has millions of records and performance (speed) is falling down. And the number of records in the table is still increasing.

So I consider to automatically create each month new (log) table for each user. The main disadvantage of this solution is fact that by 150 users will be cca 2500 tables in database after 1 year. But on the other hand, the tables have less records so the speed of analysing is higher. I don't know whether it's OK to have 2500 tables in one DB... It's also difficult to manage so big DB with phpMyAdmin.

Design Mysql Frontend
I wish to design an entirely new GUI frontend for mysql without the use of php. I would also like to avoid the readily available GUI frontends like EMS mysql manager , rekall etc. I would like to know which programming language is suitable for this purpose. Is it possible to do it with python or perl. If possible which language is better. I wish to run the frontend as a standalone application. Cross platform compatibility is also one of my main concerns. i would be using it mainly in linux, freebsd and windows.
i dont wish to have a webpage frontend for mysql. the frontend has to run as a standalone application.

MySQL Table Design For Web Tracker
I'm currenty writing a PHP based tracking script and was wondering about an expedient mysql table design. My experiences with mysql are not as good as with PHP at all, so I need a little help.

The main problem is performance, so you may already asume that I'm not looking for poorly conceived table structures but some kind of denormalized ones, in order to improve the performance.

So whats the best way to store the logged information? What you see here is the db structure from phpOpenTracker and that exactly is the way it shouldn't be.
This normalized schema is horrible slow if you have to evaluate a lot of data.

I know a lot about application design but not about db design, so it would be great if anyone could give me some approaches and/or examples.

Permissions
This is a fairly simple question but I'm new to mySQL. To log on to
mySQL I need to type at the prompt:

mysql --user='username' --password='password'

as opposed to just typing mysql and being recognized without having to
type identification. Anyone know how I can change this?

Permissions On Mac
I used MAMP to set up MySQL and Apache on my Mac and installed Drupal. Everything was working fine but I thought that I had forgotten to change the password for the 'root' user in mysql. So,with MAMP when you go to "localhost:8888 it gives you a start page where you can access mysqladmin and others. I used mysqladmin to change the root user properties but now when I try to launch the start page I get "could not connect with mysql server"

Any idea how I can get to MySQL another way and fix it without having to reinstall? My Drupal installation still works so I know MySQL is working but I can't get to the admin/start page anymore.

Also, I'm a complete newb at this stuff.. I know I can go to a terminal session on my Mac to access mysql directly but I don't know enough about it to do anything.

Permissions
I connect to my MySQL database using MyODBC.
I have a table called 'log' in a database called 'home'. When I try to
do an insert like this:
insert into log (userid,event,event_date) values (blah blah....

I get this error response from MySQL:
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 'log
(userid,event,event_d
ate) values ('nt','HomeDB Started',now())' at line 1

However, if I use :
insert into home.log (userid,event,event_date) values (blah blah....I have another table in the same database which doesn't exhibit this problem.
Is this some sort of permissions or access rights issue?

Permissions
I'm developing in Groupware (a php / template-based tool for making office-like applications). It creates mysql tables as it goes. I am not seeing those tables being created, and I'd like to determine if it's a permissions problem.
I'm in a shell on a Linux system, but I'm very unfamiliar with Linux command line.
So far, I can logon as root, use database, show tables; (this is how I know my tables aren't there.)
So, what can I do to ensure that my permissions are set up correctly? When I was setting permissions in the Linux directories, I used 'chown'.

Visual Design / Editor Of Mysql Databases
I am wondering if any tools exist that provide a GUI for developing mysql databases.

Search Design: Keywords Table (MySQL)
I'm trying to implement keyword searching in my database.
Currently, I have a table containing all my entries with description and name.
The existing sql search utilises fulltext search to find the relevant data from the description and name fields. I'm thinking of adding an additional keyword table that would have higher relevancy in returning results (currently, you'll be lucky to find any relevant entries).
I have a separate table and the design is as follows:
REATE TABLE `keywords` (
`id` smallint(5) NOT NULL auto_increment,
`entryid` mediumint(9) NOT NULL,
`keyword` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
My other table that contains the description and name fields are `entries`

My problem now is, how can I match the search keywords with that of the table `entries` and `keywords`? I want to avoid using more than one sql query for each search.

For each entry in the table `entries`, there may be up to 20 keywords (e.g. 20 rows in `keywords` table). I'm open to another approach for the keyword table. The reason why I have a separate table is to facilitate management and hopefully be able to pool results in a way of a cloud for keyword statistics.
Can anyone offer any advice/help in this regard?

Permissions Problem
I can query my MySQL database with any SELECT, UPDATE, DELETE, etc. queries, and it works perfectly, but when I try to do a LOAD DATA INFILE query, I get a permissions problem. Any ideas why this might be?

Root Permissions
root permissions are messed up, i'm unable to create new databases with root account and i cant change permissions for the root account while logged in as root

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Custom Permissions
I need to assign different 3 different types of users, each with his own permissions and am unsure how to proceed. One of these users will require remote access to all records. I have mySQL 5 and mySQLadmin tool. Both are installed on WinXP and mySQL will be accessed through PHP's connection functions.

Can someone show me an example of setting a user with specific table and row privileges for security purposes? ie: a a user can't modify adata in x table, only read. Another user can write in rows w and x of table y only and can read all of y table but not table z.

Permissions Change
Running MySQL 4.0.18 (upgraded from MySQL 3.x), I can set the host for a user to "%", and can connect from localhost just fine, as well as remote locations.On another server running 4.0.21 (clean install), if I set the host to "%", I cannot connect from localhost unless I also set a "localhost" entry as well for that user.

Resolving Permissions
I have mySQL Administrator (and subsequently mySQL) installed on my iBook. I want to connect to mySQL on my Red Had Linux 9 test server with the mySQL Administrator, but I get the following error message:
Code:

Could not connect to mySQL instance at [SERVER IP ADDRESS]
Error: Host '[IBOOK IP ADDRESS]' is not allowed to connect to
this mySQL server (code 1130)

I'm sure there's an easy fix for this, but since I'm a bit new at this, I'm not sure where to look.

Permissions With New Db's
I've got php script that created a database, then the tables in the database.
The user in which the script is run, doesn't have the permissions to create a 'new' db. Moreover, mysql says that because the user is trying to create a new db, and there isn't a row specifying that the use has permission, doesn't allow the user to create the db.
My solution is that I can create a row in the mysql.db table that will allow for the user to have proper permissions.
My problem is that once I insert that row, it takes another post to the page for the permission to actually take hold.
Is there a way to have the permissions take hold sooner?
Some of the things I have tried would be toRedirect the page to (with a php header call) to re-load the pageClose the mysql connection so that a new one would have to be opened

Database Permissions
My managed server is running 4.1.21-standard. We have been able to connect and read a .csv file into the tables, but can't write or append data from tables to a .csv file for import into another application even thought all permissions have been granted. Is this because File is not in the list of permissions to be granted?
Also, first user gets kicked out when second user access the MySQL Account Maintenance page. Second user does not even have to log in to cause problems.
Are these MySQL issues or cPanel issues? How can I fix them?

Database Permissions
I've got databases on one box i have to
transfer to another box, both are running Linux one a Centos4 machine, the
other rh9 i think. I could tar up the /var/lib/mysql directory, but i
was lead to believe that was a bad idea. I don't know the permissions of the
user's and i don't want to give out global permissions. What i was wondering
is there a way i can do a mysqldump on the databases on machine1, which will
drop everything needed to recreate them in to a .sql file, transfer that to
machine2 and then use mysqladmin to load it and have all the settings back
in business? I believe they're both mysql4 installs, though machine1 might
be mysql3.23, not sure.

MySQL Table Design Style With Foreign Keys
I just wondering what proper (My)SQL style would be for the following situation (which I made up for the sake of this)...

You have a SpiceRack table and a Spice table. Each rack has many spices, in typical one-to-many fashion. So the Spice table would have rackID column. Easy.

But let's say each rack has one default spice.

So should the SpiceRack table have a column "DefaultSpiceID" or should the Spice table have a column "isDefault"?

In one sense, I would say the former, to help enforce a single default spice. In another sense, I would say having two tables point to each other is ugly and just do some extra queries to enforce that there is only one with the latter.

And while I'm at it... for that isDefault, and similar T/F columns... What's the best data type? SMALLINT (1) UNSIGNED, ENUM(0,1), or LONGBLOB?

MYSQL User Information: Error While Storing The User Information
I have a proplem creating a new user and/or adding additional
information for root user in MySQL Administrator 1.0.19. When I do I
get this message: error while storing the user information.

Never the less, when I close the Admin program and open it again, the
changes I tryed to make are saved, (althoug the program asked me if I
wanted to save changes and I said no)

Is this a know bug, should I be alarmed, any solutions ?

I´m running
MySQL 4.3.11
Admin 1.0.19
Query 1.1.5
Apache2
php5

Stored Procedures + Permissions
I'm on a shared host with MySQL 5.0.33. I want to use stored procedures and functions. Unfortunately I'm getting an error message when I try to create a new function. For example:

SQL
CREATE FUNCTION `gutenTag`()
RETURNS varchar(20) CHARSET utf8
RETURN 'Guten Tag'

... ends with:

#1044 - Access denied for user 'blah'@'%' to database 'blahblah'

How can I check if I have permissions to create procedures/function?

How To Handle DHCP And Permissions
I am using an Access front-end, but the users are on machines set to DHCP. SO when the IP changes, they are denied access.

Database Creation Permissions
I have setup different MySQL users. I have removed all permissions for the users, and granted them full permission to their default databases I created for the.

My question is, how can I grant the users permission to create databases. I want them to have full control to create databases, and delete any that they create. Is this possible, or do I need to create and remove databases for them as requested?

I'm running mysql 3.2.5 on Redhat 9. I am not using any host permissions, table, or row permissions on the databases.

Need Info On LOB's And Permissions
i want to know wat the equivalents to LOB's of oracle i mean we have clob and blob in oracle so wat do we have similar to that in Mysql,

And i notice after installation of mysql it is some thing like a raw database kinda thing... we got to create our own users and so... can you tell me how to create new users and wat tare the access permissions that i can use for common environments where i want to develope a client- server kinda environment. I mean some thing similar to 'grant'in oracle.

Cannot Revoke Databases Permissions
I have Mysql 4.1.12 running on FreeBSD 4.9

in that i have 4 databases, mysql, test, new, and xyz I have one user abc, if i login through abc user i can see three databases new, test and xyz i want to remove access to xyz for user abc tried all command to remove access but didnt help and tried phpmyadmin also it seems when ever i create new abc user deleteing old it takes the old privileges of its own and again it shows access to 3 databases i just want access to test, new for user abc.