grant question
I am here in the docs.
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR count |
MAX_UPDATES_PER_HOUR count |
MAX_CONNECTIONS_PER_HOUR count |
MAX_USER_CONNECTIONS count]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
I have a database with tables, and I want to set up a "user" with a "password"
that I can use to display this database and tables in a webapp.
I do not want to use the root/password for connecting. So the docs say to use
this command. Problem is I cannot find what to put in for the priv_type and I
am unclear exactly how to pull this together. If all tables are allowed to be
used can someone help me out with this?
Thanks,
Scott
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant question
At 11:19 -0500 4/22/05, Scott Purcell wrote:
I am here in the docs.
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR count |
MAX_UPDATES_PER_HOUR count |
MAX_CONNECTIONS_PER_HOUR count |
MAX_USER_CONNECTIONS count]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
I have a database with tables, and I want to set up a "user" with a
"password" that I can use to display this database and tables in a
webapp.
I do not want to use the root/password for connecting. So the docs
say to use this command. Problem is I cannot find what to put in for
the priv_type and I am unclear exactly how to pull this together. If
all tables are allowed to be used can someone help me out with this?
If you want the account to have read-only access, priv_type should be
SELECT.
GRANT SELECT ON db_name.* TO 'some_user'@'some_host'
IDENTIFIED BY 'some_password';
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Grant question
[snip] Is it possible to grant all these databases in just one GRANT instruction such as: GRANT SELECT,INSERT,UPDATE,DELETE ON dbexample*.* TO 'user'@'localhost' BY 'password'; [/snip] GRANT [privs] ON `dbexample%`.* TO 'user'@'localhost' IDENTIFIED BY 'password'; --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Andre Matos [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 04, 2005 4:26 PM To: mysql@lists.mysql.com Subject: Grant question Hi list, I have many databases on my MySQL server such as: dbexample dbexample_clients_abc dbexample_clients_def dbexample_clients_ghi dbexample_local_abc dbexample_local_def dbexample_local_ghi Is it possible to grant all these databases in just one GRANT instruction such as: GRANT SELECT,INSERT,UPDATE,DELETE ON dbexample*.* TO 'user'@'localhost' BY 'password'; I tried this instruction but did not work. Is someone knows how to do if it is possible? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grant question
Hi list, I have many databases on my MySQL server such as: dbexample dbexample_clients_abc dbexample_clients_def dbexample_clients_ghi dbexample_local_abc dbexample_local_def dbexample_local_ghi Is it possible to grant all these databases in just one GRANT instruction such as: GRANT SELECT,INSERT,UPDATE,DELETE ON dbexample*.* TO 'user'@'localhost' BY 'password'; I tried this instruction but did not work. Is someone knows how to do if it is possible? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT question
At 9:31 + 2/23/04, [EMAIL PROTECTED] wrote: "Rhino" <[EMAIL PROTECTED]> wrote on 21/02/2004 16:59:27: I am new to most aspects of MySQL administration so I was wondering if someone can help me figure out what GRANTs I need for a particular situation. I have a user who needs to be able to run some MySQL scripts that create and load tables in a database named NFL. He is accessing MySQL remotely from a client on a Windows machine. Our server is running in Linux Mandrake 9.1; the client is Windows 98SE. I've run the following grants for him: grant all on NFL.* to [EMAIL PROTECTED] indentified by 'x'; grant all on NFL.* to [EMAIL PROTECTED] identified by 'x'; grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; When I try signing on as him from my remote Windows client, he can connect to MySQL and get to the mysql prompt. He can run the script which creates and loads the tables. However, he gets "Access Denied" on the Load Data Infile statements when the script executes. 1. Why is this happening? The Load Data article says he needs the File privilege and I've given it to him. He also has all privileges on the NFL database. What more do I need to do for him? 2. Are the privileges given to [EMAIL PROTECTED] redundant with the privileges given to [EMAIL PROTECTED] They *look* redundant to me; I suspect I've misinterpreted something I read in the manual. Can I get by with giving him just the [EMAIL PROTECTED] privileges? Do I need to give him anything else to account for the fact that he is coming in from a remote client? I note that you are running a mixed Windows/Linux system. My experience is that using uppercase in such systems is a source of create problems. Since database names and table names for MyISAM tables are file names, they are case significant on Linux and non-significant on Windows. While I am sure that there is an analytic way of handling the problems which arise, I found that it was very difficult to understand and a source of avoidable confusion. I would recommend that in any case where mixed *nix and Windows systems are involved, you keep database and table names entirely to lower case. The world will give you enough unavoidable problems - don't add an avoidable one. Alec I would say that it shouldn't matter whether you use uppercase or lowercase. What matters is that you pick one lettercase and use it consistently. Another option is to set lower_case_table_names to 1 on all platforms. Before using it, however, you should change all database and table names to lowercase manually. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT question
"Rhino" <[EMAIL PROTECTED]> wrote on 21/02/2004 16:59:27: > I am new to most aspects of MySQL administration so I was wondering > if someone can help me figure out what GRANTs I need for a > particular situation. > > I have a user who needs to be able to run some MySQL scripts that > create and load tables in a database named NFL. He is accessing > MySQL remotely from a client on a Windows machine. Our server is > running in Linux Mandrake 9.1; the client is Windows 98SE. > > I've run the following grants for him: > grant all on NFL.* to [EMAIL PROTECTED] indentified by 'x'; > grant all on NFL.* to [EMAIL PROTECTED] identified by 'x'; > grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; > grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; > > When I try signing on as him from my remote Windows client, he can > connect to MySQL and get to the mysql prompt. He can run the script > which creates and loads the tables. However, he gets "Access Denied" > on the Load Data Infile statements when the script executes. > > 1. Why is this happening? The Load Data article says he needs the > File privilege and I've given it to him. He also has all privileges > on the NFL database. What more do I need to do for him? > > 2. Are the privileges given to [EMAIL PROTECTED] redundant with the > privileges given to [EMAIL PROTECTED] They *look* redundant to me; I > suspect I've misinterpreted something I read in the manual. Can I > get by with giving him just the [EMAIL PROTECTED] privileges? Do I > need to give him anything else to account for the fact that he is > coming in from a remote client? I note that you are running a mixed Windows/Linux system. My experience is that using uppercase in such systems is a source of create problems. Since database names and table names for MyISAM tables are file names, they are case significant on Linux and non-significant on Windows. While I am sure that there is an analytic way of handling the problems which arise, I found that it was very difficult to understand and a source of avoidable confusion. I would recommend that in any case where mixed *nix and Windows systems are involved, you keep database and table names entirely to lower case. The world will give you enough unavoidable problems - don't add an avoidable one. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT question
At 14:56 -0500 2/21/04, Rhino wrote: Thanks, that clears things up pretty well. By the way, since you're on the documentation team, can you tell me what the "official channels" are for making suggestions about the documentation. I have some ideas about how to make it better Mail to [EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT question
Thanks, that clears things up pretty well. By the way, since you're on the documentation team, can you tell me what the "official channels" are for making suggestions about the documentation. I have some ideas about how to make it better Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT question
At 14:30 -0500 2/21/04, Rhino wrote: More followups below > >> > 1. Why is this happening? The Load Data article says he needs the File >privilege and I've given it to him. He also has all privileges on the NFL >database. What more do I need to do for him? >> >> FILE is a global level privilege. >> You should grant: >> >> GRANT FILE ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'x'; >> >So, am I correct in understanding that I can't give the FILE privilege for a >single database or table? That means my user can load *any* table in *any* >database when I only want him to be able to load the tables in one database, >which is more access than I wanted to give him. I guess I'll either have to >trust him or run the scripts myself. No. You must grant FILE on the global level. That means you can read or write files. It doesn't imply anything about which tables you can access. If you have no access to a table, you can't read a file into it, for example. Okay, that's a relief; I didn't want to give him access to other databases. He doesn't have many computer skills yet so there's a very good chance that he could mess up the other databases if he decided to give it a try. Now that I know he can't access the other databases, I'm no longer worried. You should still be worried. FILE is a dangerous privilege. http://www.mysql.com/doc/en/Privileges_provided.html See the mention of FILE near the end. > >> >2. Are the privileges given to [EMAIL PROTECTED] redundant with the >privileges given to [EMAIL PROTECTED] They *look* redundant to me; I suspect >I've misinterpreted something I read in the manual. Can I get by with giving >him just the [EMAIL PROTECTED] privileges? Do I need to give him anything else >to account for the fact that he is coming in from a remote client? >> >> localhost on Unix system means that you are using Unix socket connection. >> 127.0.0.1 means that you are using TCP/IP connection. >> But you can't use these accounts to connect to the MySQL server remotely. >Check with CURRENT_USER() function username and hostname that current >connection was authenticated as. >> >I'm confused by your answer. The privileges he currently has *do* allow him >to connect to the MySQL server remotely. (We are using SSH as our Windows > >client.) Then you're not connecting to the MySQL server remotely. You're using SSH to connect to the MySQL server *host* remotely, and then from that host making a local connection to the MySQL server running on that host. So in this case, all you do need is to grant privileges to a [EMAIL PROTECTED] account. > > >I ran the query "select current_user() from NFL.Teams" and got the following >result while signed on as my user: [EMAIL PROTECTED] Does this mean that I >should revoke the privileges given to [EMAIL PROTECTED] Honestly, I'm not >sure whether I should be using Unix sockets or TCP/IP; I'm not sure I >understand the implications of using either one to MySQL. If CURRENT_USER() is returning [EMAIL PROTECTED], it means you're not actually connecting from a remote server, you are connecting to the MySQL server from the same host where the server is running. Are you saying that connecting via SSH is effectively the same as sitting in the same room as the server and accessing its command line directly? My user and I are not even in the same city as the server so I thought that inevitably meant that a program like SSH had to be treated as a client, not as if it were the host itself. Forgive me if this is a stupid question but my knowledge of networking is sketchy at best. See above. As far as the MySQL server is concerned, you are accessing it from the same host were it is running. It doesn't know that you're "really" on some other machine. Yes, it's easy to get confused by this kind of stuff. :-) The host that you specify in the GRANT statement is not the host that > the MySQL server runs on. It is the client host *from which* you plan to connect to the server. If the use of SSH means that I am directly accessing the server in a *local* fashion, rather than as a client, does this mean I can eliminate the GRANTs to 127.0.0.1 or localhost? If yes, which one would be the sensible one to eliminate? I don't know the pros and cons of using Unix sockets vs. TCP/IP. Just use localhost. To connect using the Unix socket file, connect to localhost (the default). To connect over TCP/IP, connect to 127.0.0.1. localhost should match them both. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT question
More followups below - Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, February 21, 2004 1:25 PM Subject: Re: GRANT question > At 13:09 -0500 2/21/04, Rhino wrote: > >Followup questions interspersed below > > > >- Original Message - > >From: "Egor Egorov" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Sent: Saturday, February 21, 2004 12:29 PM > >Subject: Re: GRANT question > > > > > >> "Rhino" <[EMAIL PROTECTED]> wrote: > >> > I am new to most aspects of MySQL administration so I was wondering if > >someone can help me figure out what GRANTs I need for a particular > >situation. > >> > > >> > I have a user who needs to be able to run some MySQL scripts that create > >and load tables in a database named NFL. He is accessing MySQL remotely from > >a client on a Windows machine. Our server is running in Linux Mandrake 9.1; > >the client is Windows 98SE. > >> > > >> > I've run the following grants for him: > >> > grant all on NFL.* to [EMAIL PROTECTED] indentified by 'x'; > >> > grant all on NFL.* to [EMAIL PROTECTED] identified by 'x'; > >> > grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; > >> > grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; > >> > > >> > When I try signing on as him from my remote Windows client, he can > >connect to MySQL and get to the mysql prompt. He can run the script which > >creates and loads the tables. However, he gets "Access Denied" on the Load > >Data Infile statements when the script executes. > >> > > >> > 1. Why is this happening? The Load Data article says he needs the File > >privilege and I've given it to him. He also has all privileges on the NFL > >database. What more do I need to do for him? > >> > >> FILE is a global level privilege. > >> You should grant: > >> > >> GRANT FILE ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'x'; > >> > >So, am I correct in understanding that I can't give the FILE privilege for a > >single database or table? That means my user can load *any* table in *any* > >database when I only want him to be able to load the tables in one database, > >which is more access than I wanted to give him. I guess I'll either have to > >trust him or run the scripts myself. > > No. You must grant FILE on the global level. That means you can read or > write files. It doesn't imply anything about which tables you can access. > If you have no access to a table, you can't read a file into it, for example. > Okay, that's a relief; I didn't want to give him access to other databases. He doesn't have many computer skills yet so there's a very good chance that he could mess up the other databases if he decided to give it a try. Now that I know he can't access the other databases, I'm no longer worried. > > > > >> >2. Are the privileges given to [EMAIL PROTECTED] redundant with the > >privileges given to [EMAIL PROTECTED] They *look* redundant to me; I suspect > >I've misinterpreted something I read in the manual. Can I get by with giving > >him just the [EMAIL PROTECTED] privileges? Do I need to give him anything else > >to account for the fact that he is coming in from a remote client? > >> > >> localhost on Unix system means that you are using Unix socket connection. > >> 127.0.0.1 means that you are using TCP/IP connection. > >> But you can't use these accounts to connect to the MySQL server remotely. > >Check with CURRENT_USER() function username and hostname that current > >connection was authenticated as. > >> > >I'm confused by your answer. The privileges he currently has *do* allow him > >to connect to the MySQL server remotely. (We are using SSH as our Windows > >client.) > > > >I ran the query "select current_user() from NFL.Teams" and got the following > >result while signed on as my user: [EMAIL PROTECTED] Does this mean that I > >should revoke the privileges given to [EMAIL PROTECTED] Honestly, I'm not > >sure whether I should be using Unix sockets or TCP/IP; I'm not sure I > >understand the implications of using either one to MySQL. > > If CURRENT_USER() is returning [EMAIL PROTECTED], it means you're not actuall
Re: GRANT question
At 13:09 -0500 2/21/04, Rhino wrote: Followup questions interspersed below - Original Message - From: "Egor Egorov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, February 21, 2004 12:29 PM Subject: Re: GRANT question "Rhino" <[EMAIL PROTECTED]> wrote: > I am new to most aspects of MySQL administration so I was wondering if someone can help me figure out what GRANTs I need for a particular situation. > > I have a user who needs to be able to run some MySQL scripts that create and load tables in a database named NFL. He is accessing MySQL remotely from a client on a Windows machine. Our server is running in Linux Mandrake 9.1; the client is Windows 98SE. > > I've run the following grants for him: > grant all on NFL.* to [EMAIL PROTECTED] indentified by 'x'; > grant all on NFL.* to [EMAIL PROTECTED] identified by 'x'; > grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; > grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; > > When I try signing on as him from my remote Windows client, he can connect to MySQL and get to the mysql prompt. He can run the script which creates and loads the tables. However, he gets "Access Denied" on the Load Data Infile statements when the script executes. > > 1. Why is this happening? The Load Data article says he needs the File privilege and I've given it to him. He also has all privileges on the NFL database. What more do I need to do for him? FILE is a global level privilege. You should grant: GRANT FILE ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'x'; So, am I correct in understanding that I can't give the FILE privilege for a single database or table? That means my user can load *any* table in *any* database when I only want him to be able to load the tables in one database, which is more access than I wanted to give him. I guess I'll either have to trust him or run the scripts myself. No. You must grant FILE on the global level. That means you can read or write files. It doesn't imply anything about which tables you can access. If you have no access to a table, you can't read a file into it, for example. > >2. Are the privileges given to [EMAIL PROTECTED] redundant with the privileges given to [EMAIL PROTECTED] They *look* redundant to me; I suspect I've misinterpreted something I read in the manual. Can I get by with giving him just the [EMAIL PROTECTED] privileges? Do I need to give him anything else to account for the fact that he is coming in from a remote client? localhost on Unix system means that you are using Unix socket connection. 127.0.0.1 means that you are using TCP/IP connection. But you can't use these accounts to connect to the MySQL server remotely. Check with CURRENT_USER() function username and hostname that current connection was authenticated as. I'm confused by your answer. The privileges he currently has *do* allow him to connect to the MySQL server remotely. (We are using SSH as our Windows client.) I ran the query "select current_user() from NFL.Teams" and got the following result while signed on as my user: [EMAIL PROTECTED] Does this mean that I should revoke the privileges given to [EMAIL PROTECTED] Honestly, I'm not sure whether I should be using Unix sockets or TCP/IP; I'm not sure I understand the implications of using either one to MySQL. If CURRENT_USER() is returning [EMAIL PROTECTED], it means you're not actually connecting from a remote server, you are connecting to the MySQL server from the same host where the server is running. The host that you specify in the GRANT statement is not the host that the MySQL server runs on. It is the client host *from which* you plan to connect to the server. By the way, I change the File privilege as you suggested - and nothing else - and I can now execute the script successfully, including the LOAD DATA commands, while logged on as 'brian'. Rhino -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT question
Followup questions interspersed below - Original Message - From: "Egor Egorov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, February 21, 2004 12:29 PM Subject: Re: GRANT question > "Rhino" <[EMAIL PROTECTED]> wrote: > > I am new to most aspects of MySQL administration so I was wondering if someone can help me figure out what GRANTs I need for a particular situation. > > > > I have a user who needs to be able to run some MySQL scripts that create and load tables in a database named NFL. He is accessing MySQL remotely from a client on a Windows machine. Our server is running in Linux Mandrake 9.1; the client is Windows 98SE. > > > > I've run the following grants for him: > > grant all on NFL.* to [EMAIL PROTECTED] indentified by 'x'; > > grant all on NFL.* to [EMAIL PROTECTED] identified by 'x'; > > grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; > > grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; > > > > When I try signing on as him from my remote Windows client, he can connect to MySQL and get to the mysql prompt. He can run the script which creates and loads the tables. However, he gets "Access Denied" on the Load Data Infile statements when the script executes. > > > > 1. Why is this happening? The Load Data article says he needs the File privilege and I've given it to him. He also has all privileges on the NFL database. What more do I need to do for him? > > FILE is a global level privilege. > You should grant: > > GRANT FILE ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'x'; > So, am I correct in understanding that I can't give the FILE privilege for a single database or table? That means my user can load *any* table in *any* database when I only want him to be able to load the tables in one database, which is more access than I wanted to give him. I guess I'll either have to trust him or run the scripts myself. > > >2. Are the privileges given to [EMAIL PROTECTED] redundant with the privileges given to [EMAIL PROTECTED] They *look* redundant to me; I suspect I've misinterpreted something I read in the manual. Can I get by with giving him just the [EMAIL PROTECTED] privileges? Do I need to give him anything else to account for the fact that he is coming in from a remote client? > > localhost on Unix system means that you are using Unix socket connection. > 127.0.0.1 means that you are using TCP/IP connection. > But you can't use these accounts to connect to the MySQL server remotely. Check with CURRENT_USER() function username and hostname that current connection was authenticated as. > I'm confused by your answer. The privileges he currently has *do* allow him to connect to the MySQL server remotely. (We are using SSH as our Windows client.) I ran the query "select current_user() from NFL.Teams" and got the following result while signed on as my user: [EMAIL PROTECTED] Does this mean that I should revoke the privileges given to [EMAIL PROTECTED] Honestly, I'm not sure whether I should be using Unix sockets or TCP/IP; I'm not sure I understand the implications of using either one to MySQL. By the way, I change the File privilege as you suggested - and nothing else - and I can now execute the script successfully, including the LOAD DATA commands, while logged on as 'brian'. Rhino > > > >We are running MySQL 4.0.11a. gamma. > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT question
"Rhino" <[EMAIL PROTECTED]> wrote: > I am new to most aspects of MySQL administration so I was wondering if someone can > help me figure out what GRANTs I need for a particular situation. > > I have a user who needs to be able to run some MySQL scripts that create and load > tables in a database named NFL. He is accessing MySQL remotely from a client on a > Windows machine. Our server is running in Linux Mandrake 9.1; the client is Windows > 98SE. > > I've run the following grants for him: > grant all on NFL.* to [EMAIL PROTECTED] indentified by 'x'; > grant all on NFL.* to [EMAIL PROTECTED] identified by 'x'; > grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; > grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; > > When I try signing on as him from my remote Windows client, he can connect to MySQL > and get to the mysql prompt. He can run the script which creates and loads the > tables. However, he gets "Access Denied" on the Load Data Infile statements when the > script executes. > > 1. Why is this happening? The Load Data article says he needs the File privilege and > I've given it to him. He also has all privileges on the NFL database. What more do I > need to do for him? FILE is a global level privilege. You should grant: GRANT FILE ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'x'; >2. Are the privileges given to [EMAIL PROTECTED] redundant with the privileges given >to [EMAIL PROTECTED] They *look* redundant to me; I suspect I've misinterpreted >something I read in the manual. Can I get by with giving him just the [EMAIL >PROTECTED] privileges? Do I need to give him anything else to account for the fact >that he is coming in from a remote client? localhost on Unix system means that you are using Unix socket connection. 127.0.0.1 means that you are using TCP/IP connection. But you can't use these accounts to connect to the MySQL server remotely. Check with CURRENT_USER() function username and hostname that current connection was authenticated as. > >We are running MySQL 4.0.11a. gamma. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT question
I am new to most aspects of MySQL administration so I was wondering if someone can help me figure out what GRANTs I need for a particular situation. I have a user who needs to be able to run some MySQL scripts that create and load tables in a database named NFL. He is accessing MySQL remotely from a client on a Windows machine. Our server is running in Linux Mandrake 9.1; the client is Windows 98SE. I've run the following grants for him: grant all on NFL.* to [EMAIL PROTECTED] indentified by 'x'; grant all on NFL.* to [EMAIL PROTECTED] identified by 'x'; grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; grant file on NFL.* to [EMAIL PROTECTED] identified by 'x'; When I try signing on as him from my remote Windows client, he can connect to MySQL and get to the mysql prompt. He can run the script which creates and loads the tables. However, he gets "Access Denied" on the Load Data Infile statements when the script executes. 1. Why is this happening? The Load Data article says he needs the File privilege and I've given it to him. He also has all privileges on the NFL database. What more do I need to do for him? 2. Are the privileges given to [EMAIL PROTECTED] redundant with the privileges given to [EMAIL PROTECTED]? They *look* redundant to me; I suspect I've misinterpreted something I read in the manual. Can I get by with giving him just the [EMAIL PROTECTED] privileges? Do I need to give him anything else to account for the fact that he is coming in from a remote client? We are running MySQL 4.0.11a. gamma. Rhino---rhino1 AT sympatico DOT ca"If you want the best seat in the house, you'll have to move the cat."
Re: GRANT question
Hi, Yes, you're right. GRANT and REVOKE do not need FLUSH PRIVILEGES afterwards. Flushing is only needed when manually updating the grant tables via normal INSERT/UPDATE/DELETE commands. Regards, Iikka On Wed, 2 Oct 2002 [EMAIL PROTECTED] wrote: > If i use GRANT and REVOKE to change user privileges, it is my understanding > that i don't have > to issue a FLUSH PRIVILEGES statement. > Is this correct? > > Our sysadmin rebooted the MySQL server this morning, and there were some > privilege problems > afterward. I had to reset the privileges for some mysql users. > The only thing i can think of is that there were some pending privilege > changes that > were read by mysqld for the first time when the server restarted. > > any other theories about why this would happen? > > regards, > sean peters > [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 > ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - 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
GRANT question
If i use GRANT and REVOKE to change user privileges, it is my understanding that i don't have to issue a FLUSH PRIVILEGES statement. Is this correct? Our sysadmin rebooted the MySQL server this morning, and there were some privilege problems afterward. I had to reset the privileges for some mysql users. The only thing i can think of is that there were some pending privilege changes that were read by mysqld for the first time when the server restarted. any other theories about why this would happen? regards, sean peters [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
Re: Grant question
Another grant command for the existing privileges + index privs. should work Bhavin. - Original Message - From: "Paul Maine" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 24, 2002 5:33 PM Subject: RE: Grant question > I have an existing mysql user that I wish to add the INDEX privilege. Is > there a > command that I can use to add a privilege without first deleting the user > and then recreating with the GRANT command? > > Thank You > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, July 24, 2002 6:27 PM > To: Paul Maine > Subject: Re: Grant question > > > Your message cannot be posted because it appears to be either spam or > simply off topic to our filter. To bypass the filter you must include > one of the following words in your message: > > sql,query > > If you just reply to this message, and include the entire text of it in the > reply, your reply will go through. However, you should > first review the text of the message to make sure it has something to do > with MySQL. Just typing the word MySQL once will be sufficient, for example. > > You have written the following: > > I have an existing user that I wish to add the INDEX privilege. Is there a > command that I can use to add a privilege without first deleting the user > and then recreating with the GRANT command? > > Thank You > > > > - > 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 > - 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 Grant Question
Pada Wed, 24 Jul 2002 18:52:44 -0500 "Paul Maine" <[EMAIL PROTECTED]> menulis : > I have an existing mysql user that I wish to add the INDEX privilege. Is > there a command that I can use to add a privilege without first deleting the > user and then recreating with the GRANT command? you can run : grant on . to @; flush privileges; it will add/update new privilege for user; or you can edit the values on DB mysql ... with table user or db ;-) -- "Who is General Failure and why is he reading my hard disk?" Microsoft spel chekar vor sail, worgs grate !! -- Felix von Leitner, [EMAIL PROTECTED] MySQL 3.23.51 : up 33 days, Queries : 353.598 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790) Phone : +62 21 79199577 - Web : http://1rstwap.com - 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: Grant question
I have an existing mysql user that I wish to add the INDEX privilege. Is there a command that I can use to add a privilege without first deleting the user and then recreating with the GRANT command? Thank You -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 24, 2002 6:27 PM To: Paul Maine Subject: Re: Grant question Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I have an existing user that I wish to add the INDEX privilege. Is there a command that I can use to add a privilege without first deleting the user and then recreating with the GRANT command? Thank You - 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
MySQL Grant Question
I have an existing mysql user that I wish to add the INDEX privilege. Is there a command that I can use to add a privilege without first deleting the user and then recreating with the GRANT command? Thank You - 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
