Re: Replication and user privileges
On 2/26/2019 1:57 PM, Jim wrote: On 2/26/2019 9:44 AM, shawn l.green wrote: Hello Jim, On 2/25/2019 7:29 PM, Jim wrote: On 2/25/2019 5:46 PM, shawn l.green wrote: Hello Jim, On 2/25/2019 5:04 PM, Jim wrote: I have a question about mysql replication. I believe I understand most of it, but have a question about user privileges. I understand on the master, the replication user must have the Repl_slave_priv privilege as described here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave My question is about what replication-related users and privileges must exist on the slave. So, for example, if an insert on the master that is to be replicated is performed by user 'abc' with proper insert permissions on the master, does that same 'abc' user with same insert permissions need to exist on the slave as well? In other words, what user is performing the replication operation on the slave? I don't see any indication of users referenced in the bin logs that I have examined on the master. Are user and privileges regarding replicated queries irrelevant on the slave and that is handled all internally via the replication thread with no regard to user privileges? Thank you. Jim Your final supposition is correct. All privileges were checked and verified on the master when the original command was executed. The Replication system on the slave is going to repeat that change as well as possible given the state of its copy of the data without regards to "who originally performed this change" on the upstream master. We do not store credentials in the Binary Log because they are not important to either of the purposes of the Binary Log * point-in-time recovery or * Replication (which is very much like an automated, continuous point-in-time recovery) === That replication account you mentioned, on the master, is required to give a slave (and you could have several) enough rights to read the Binary Log and not much else. This allows you to create an account that can login from a remote location with the "least privileges" necessary to do its job. This minimizes your data's exposure should that account become compromised. Many other accounts could also have the REPL_SLAVE_PRIV privilege and any of those could be used by a slave to do the same job. However losing control over one of those more privileged accounts could pose a higher risk to your data. Thanks, Shawn. Your response confirms what I had assumed was happening. So bottom line... what I plan to do is strip the various insert/update/delete privileges from appropriate db users on my slaves. I had placed them there originally because I thought they would be needed for the replicated queries, but not true based on your response. I only want the various mysql users used by my code to have select privs on the slaves so that if somehow a slave was mistakenly written to via a bug in my code, that write would fail and I would receive the error. The slaves should only be used for selects and should never experience a write. That would make sense based on our discussion, correct? Thanks again. Jim As masters and slaves can exchange "positions" or "roles" (it depends on how you like to mentally visualize the relationship) within a replication graph in a failover situation, adding time to re-establish actual permissions using GRANT commands to reset user accounts to their old privileges may not be time you want to spend. A cleaner, simpler solution is to set the --super-read-only flag in the server: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only That way, you get the behavior you want (no writes to a read-only slave) without forcing differences to the content of your privileges tables within different nodes of your Replication setup. Each node will remain a transactionally consistent copy of all the others (within the temporal limits of replication being an asynchronous process). Yours, Thanks, Shawn. super-read-only looks perfect for what I want. I can keep my slaves with all the potential users needed to take over as master without risking unwanted writes. Given how you read: "If the |read_only| <https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only> system variable is enabled, the server permits client updates only from users who have the |SUPER| <https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super> privilege. If the |super_read_only| <https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only> system variable is also enabled, the server prohibits client updates even from users who have |SUPER| <https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super>." One somewhat gets the impression that in order to enable super_read_only, one
Re: Replication and user privileges
On 2/26/2019 9:44 AM, shawn l.green wrote: Hello Jim, On 2/25/2019 7:29 PM, Jim wrote: On 2/25/2019 5:46 PM, shawn l.green wrote: Hello Jim, On 2/25/2019 5:04 PM, Jim wrote: I have a question about mysql replication. I believe I understand most of it, but have a question about user privileges. I understand on the master, the replication user must have the Repl_slave_priv privilege as described here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave My question is about what replication-related users and privileges must exist on the slave. So, for example, if an insert on the master that is to be replicated is performed by user 'abc' with proper insert permissions on the master, does that same 'abc' user with same insert permissions need to exist on the slave as well? In other words, what user is performing the replication operation on the slave? I don't see any indication of users referenced in the bin logs that I have examined on the master. Are user and privileges regarding replicated queries irrelevant on the slave and that is handled all internally via the replication thread with no regard to user privileges? Thank you. Jim Your final supposition is correct. All privileges were checked and verified on the master when the original command was executed. The Replication system on the slave is going to repeat that change as well as possible given the state of its copy of the data without regards to "who originally performed this change" on the upstream master. We do not store credentials in the Binary Log because they are not important to either of the purposes of the Binary Log * point-in-time recovery or * Replication (which is very much like an automated, continuous point-in-time recovery) === That replication account you mentioned, on the master, is required to give a slave (and you could have several) enough rights to read the Binary Log and not much else. This allows you to create an account that can login from a remote location with the "least privileges" necessary to do its job. This minimizes your data's exposure should that account become compromised. Many other accounts could also have the REPL_SLAVE_PRIV privilege and any of those could be used by a slave to do the same job. However losing control over one of those more privileged accounts could pose a higher risk to your data. Thanks, Shawn. Your response confirms what I had assumed was happening. So bottom line... what I plan to do is strip the various insert/update/delete privileges from appropriate db users on my slaves. I had placed them there originally because I thought they would be needed for the replicated queries, but not true based on your response. I only want the various mysql users used by my code to have select privs on the slaves so that if somehow a slave was mistakenly written to via a bug in my code, that write would fail and I would receive the error. The slaves should only be used for selects and should never experience a write. That would make sense based on our discussion, correct? Thanks again. Jim As masters and slaves can exchange "positions" or "roles" (it depends on how you like to mentally visualize the relationship) within a replication graph in a failover situation, adding time to re-establish actual permissions using GRANT commands to reset user accounts to their old privileges may not be time you want to spend. A cleaner, simpler solution is to set the --super-read-only flag in the server: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only That way, you get the behavior you want (no writes to a read-only slave) without forcing differences to the content of your privileges tables within different nodes of your Replication setup. Each node will remain a transactionally consistent copy of all the others (within the temporal limits of replication being an asynchronous process). Yours, Thanks, Shawn. super-read-only looks perfect for what I want. I can keep my slaves with all the potential users needed to take over as master without risking unwanted writes. Given how you read: "If the |read_only| <https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only> system variable is enabled, the server permits client updates only from users who have the |SUPER| <https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super> privilege. If the |super_read_only| <https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only> system variable is also enabled, the server prohibits client updates even from users who have |SUPER| <https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super>." One somewhat gets the impression that in order to enable super_read_only, one must also enable read_only. However, base
Re: Replication and user privileges
Hello Jim, On 2/25/2019 7:29 PM, Jim wrote: On 2/25/2019 5:46 PM, shawn l.green wrote: Hello Jim, On 2/25/2019 5:04 PM, Jim wrote: I have a question about mysql replication. I believe I understand most of it, but have a question about user privileges. I understand on the master, the replication user must have the Repl_slave_priv privilege as described here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave My question is about what replication-related users and privileges must exist on the slave. So, for example, if an insert on the master that is to be replicated is performed by user 'abc' with proper insert permissions on the master, does that same 'abc' user with same insert permissions need to exist on the slave as well? In other words, what user is performing the replication operation on the slave? I don't see any indication of users referenced in the bin logs that I have examined on the master. Are user and privileges regarding replicated queries irrelevant on the slave and that is handled all internally via the replication thread with no regard to user privileges? Thank you. Jim Your final supposition is correct. All privileges were checked and verified on the master when the original command was executed. The Replication system on the slave is going to repeat that change as well as possible given the state of its copy of the data without regards to "who originally performed this change" on the upstream master. We do not store credentials in the Binary Log because they are not important to either of the purposes of the Binary Log * point-in-time recovery or * Replication (which is very much like an automated, continuous point-in-time recovery) === That replication account you mentioned, on the master, is required to give a slave (and you could have several) enough rights to read the Binary Log and not much else. This allows you to create an account that can login from a remote location with the "least privileges" necessary to do its job. This minimizes your data's exposure should that account become compromised. Many other accounts could also have the REPL_SLAVE_PRIV privilege and any of those could be used by a slave to do the same job. However losing control over one of those more privileged accounts could pose a higher risk to your data. Thanks, Shawn. Your response confirms what I had assumed was happening. So bottom line... what I plan to do is strip the various insert/update/delete privileges from appropriate db users on my slaves. I had placed them there originally because I thought they would be needed for the replicated queries, but not true based on your response. I only want the various mysql users used by my code to have select privs on the slaves so that if somehow a slave was mistakenly written to via a bug in my code, that write would fail and I would receive the error. The slaves should only be used for selects and should never experience a write. That would make sense based on our discussion, correct? Thanks again. Jim As masters and slaves can exchange "positions" or "roles" (it depends on how you like to mentally visualize the relationship) within a replication graph in a failover situation, adding time to re-establish actual permissions using GRANT commands to reset user accounts to their old privileges may not be time you want to spend. A cleaner, simpler solution is to set the --super-read-only flag in the server: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only That way, you get the behavior you want (no writes to a read-only slave) without forcing differences to the content of your privileges tables within different nodes of your Replication setup. Each node will remain a transactionally consistent copy of all the others (within the temporal limits of replication being an asynchronous process). Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication and user privileges
On 2/25/2019 5:46 PM, shawn l.green wrote: Hello Jim, On 2/25/2019 5:04 PM, Jim wrote: I have a question about mysql replication. I believe I understand most of it, but have a question about user privileges. I understand on the master, the replication user must have the Repl_slave_priv privilege as described here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave My question is about what replication-related users and privileges must exist on the slave. So, for example, if an insert on the master that is to be replicated is performed by user 'abc' with proper insert permissions on the master, does that same 'abc' user with same insert permissions need to exist on the slave as well? In other words, what user is performing the replication operation on the slave? I don't see any indication of users referenced in the bin logs that I have examined on the master. Are user and privileges regarding replicated queries irrelevant on the slave and that is handled all internally via the replication thread with no regard to user privileges? Thank you. Jim Your final supposition is correct. All privileges were checked and verified on the master when the original command was executed. The Replication system on the slave is going to repeat that change as well as possible given the state of its copy of the data without regards to "who originally performed this change" on the upstream master. We do not store credentials in the Binary Log because they are not important to either of the purposes of the Binary Log * point-in-time recovery or * Replication (which is very much like an automated, continuous point-in-time recovery) === That replication account you mentioned, on the master, is required to give a slave (and you could have several) enough rights to read the Binary Log and not much else. This allows you to create an account that can login from a remote location with the "least privileges" necessary to do its job. This minimizes your data's exposure should that account become compromised. Many other accounts could also have the REPL_SLAVE_PRIV privilege and any of those could be used by a slave to do the same job. However losing control over one of those more privileged accounts could pose a higher risk to your data. Thanks, Shawn. Your response confirms what I had assumed was happening. So bottom line... what I plan to do is strip the various insert/update/delete privileges from appropriate db users on my slaves. I had placed them there originally because I thought they would be needed for the replicated queries, but not true based on your response. I only want the various mysql users used by my code to have select privs on the slaves so that if somehow a slave was mistakenly written to via a bug in my code, that write would fail and I would receive the error. The slaves should only be used for selects and should never experience a write. That would make sense based on our discussion, correct? Thanks again. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication and user privileges
Hello Jim, On 2/25/2019 5:04 PM, Jim wrote: I have a question about mysql replication. I believe I understand most of it, but have a question about user privileges. I understand on the master, the replication user must have the Repl_slave_priv privilege as described here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave My question is about what replication-related users and privileges must exist on the slave. So, for example, if an insert on the master that is to be replicated is performed by user 'abc' with proper insert permissions on the master, does that same 'abc' user with same insert permissions need to exist on the slave as well? In other words, what user is performing the replication operation on the slave? I don't see any indication of users referenced in the bin logs that I have examined on the master. Are user and privileges regarding replicated queries irrelevant on the slave and that is handled all internally via the replication thread with no regard to user privileges? Thank you. Jim Your final supposition is correct. All privileges were checked and verified on the master when the original command was executed. The Replication system on the slave is going to repeat that change as well as possible given the state of its copy of the data without regards to "who originally performed this change" on the upstream master. We do not store credentials in the Binary Log because they are not important to either of the purposes of the Binary Log * point-in-time recovery or * Replication (which is very much like an automated, continuous point-in-time recovery) === That replication account you mentioned, on the master, is required to give a slave (and you could have several) enough rights to read the Binary Log and not much else. This allows you to create an account that can login from a remote location with the "least privileges" necessary to do its job. This minimizes your data's exposure should that account become compromised. Many other accounts could also have the REPL_SLAVE_PRIV privilege and any of those could be used by a slave to do the same job. However losing control over one of those more privileged accounts could pose a higher risk to your data. -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Replication and user privileges
I have a question about mysql replication. I believe I understand most of it, but have a question about user privileges. I understand on the master, the replication user must have the Repl_slave_priv privilege as described here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave My question is about what replication-related users and privileges must exist on the slave. So, for example, if an insert on the master that is to be replicated is performed by user 'abc' with proper insert permissions on the master, does that same 'abc' user with same insert permissions need to exist on the slave as well? In other words, what user is performing the replication operation on the slave? I don't see any indication of users referenced in the bin logs that I have examined on the master. Are user and privileges regarding replicated queries irrelevant on the slave and that is handled all internally via the replication thread with no regard to user privileges? Thank you. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Last established connection timestamp by a specific user
Unfortunately not with the standard configuration. You're best bet going forward would be to look at MySQL Enterprise Audit - https://www.mysql.com/products/enterprise/audit.html On Tue, Oct 10, 2017 at 3:54 PM, Gone, Sajan <sg...@lb.com> wrote: > Hi, > >We have a MySQL instance which is currently running on version > `5.7.11-enterprise-commercial-advanced-log`. On this instance I am > trying to figure out the most recent timestamp at which a specific user has > established a connection to this instance (or) performed any DML operations > which might have changed the status of the database. > > Is there any way I can get such information from the > information_schema/performance_schema > tables (or) from any of the mysql logs? > > Thank You, > Sajan Gone > Database Administrator. > > > > Notice: This communication may contain privileged and/or confidential > information. If you are not the intended recipient, please notify the > sender by email, and immediately delete the message and any attachments > without copying or disclosing them. LB may, for any reason, intercept, > access, use, and disclose any information that is communicated by or > through, or which is stored on, its networks, applications, services, and > devices. >
Last established connection timestamp by a specific user
Hi, We have a MySQL instance which is currently running on version `5.7.11-enterprise-commercial-advanced-log`. On this instance I am trying to figure out the most recent timestamp at which a specific user has established a connection to this instance (or) performed any DML operations which might have changed the status of the database. Is there any way I can get such information from the information_schema/performance_schema tables (or) from any of the mysql logs? Thank You, Sajan Gone Database Administrator. Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LB may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices.
QxOrm 1.4.1 released : new documentation (user guide), and boost::serialization becomes optional
Hello,
*QxOrm 1.4.1 just released !* <http://www.qxorm.com/>
Changes in version QxOrm 1.4.1 :
*!!! IMPORTANT NOTE ABOUT THIS VERSION !!!* : it is strongly recommended to
read the QxOrm.pri configuration file
<http://www.qxorm.com/qxorm_en/manual.html#manual_220> of this new version
(compilation options have changed compared to previous versions).
Now, by default, QxOrm library is a much lighter library : QxOrm depends
only on *QtCore* and *QtSql* (boost serialization is now optional and not
required by default).
By default, serialization engine is now based on Qt *QDataStream* class
(but you can still enable boost serialization defining *
_QX_ENABLE_BOOST_SERIALIZATION* compilation option in QxOrm.pri
configuration file).
So now, with default options :
- QxOrm 1.4.1 is much easier to install because you don't have to deal
with boost serialization extra dependency ;
- QxOrm 1.4.1 shared library is 3X smaller than 1.3.2 version ;
- Generated binaries which depends on QxOrm library are 25% smaller ;
- If you are not using serialization functions in current projects based
on QxOrm library, then you can define or not
*_QX_ENABLE_BOOST_SERIALIZATION* compilation option without changing any
line of your source code.
Here are all other changes of version QxOrm 1.4.1 :
- Improve relationships engine : possibility to select columns to fetch
using syntax : my_relation { col_1, col_2, etc... }
<http://www.qxorm.com/qxorm_en/manual.html#manual_3850> ;
- Improve QxTraits module
<http://www.qxorm.com/doxygen/html/group___qx_traits.html> to reduce
compilation times and build smaller binaries ;
- Improve QxOrm website adding possibility to search and replacing the old
FAQ <http://www.qxorm.com/qxorm_en/faq.html> by a more organized manual
(user guide) <http://www.qxorm.com/qxorm_en/manual.html> ;
- New compilation option *_QX_ENABLE_BOOST_SERIALIZATION* to enable boost
serialization dependency (read QxOrm.pri configuration file for more
details) ;
- New compilation option *_QX_ENABLE_QT_NETWORK* to enable QxService module
<http://www.qxorm.com/qxorm_en/manual.html#manual_80> (transfer persistent
layer over network) : read QxOrm.pri configuration file for more details ;
- New compilation option *_QX_NO_RTTI* to build QxOrm library without C++
RTTI type information ;
- Support QDataStream Qt serialization engine
<http://www.qxorm.com/qxorm_en/manual.html#manual_600> (used by default
when *_QX_ENABLE_BOOST_SERIALIZATION* compilation option is not defined) ;
- Improve qx_query class
<http://www.qxorm.com/qxorm_en/manual.html#manual_3600> (SQL queries) : new
method (named *customOperator()*) which gives the possibility to define a
custom operator (for example <@ for PostgreSQL ltree type) ;
- Fix a program startup issue due to 'static initialization order fiasco'
creating singletons (it was an issue with some compilers during the shared
library link process) ;
- New namespace qx::dao::throwable : same functions as qx::dao namespace
<http://www.qxorm.com/doxygen/html/namespaceqx_1_1dao.html>, but they throw
a *qx::dao::sql_error* exception when a SQL error occurred (instead of
returning a *QSqlError* instance) ;
- Add a *qAssertMsg()* macro to put a more explicit error message when
throwing an assertion ;
- Include all **.inl* files (template implementation) in QxOrm.pro project
file : QtCreator can now index these **.inl* files in its project treeview ;
- Rename *QxStringCvt* to *QxConvert* : so if you persist custom types to
database, you have to rename from *QxStringCvt_FromVariant,
QxStringCvt_ToVariant* to *QxConvert_FromVariant, QxConvert_ToVariant*.
You can download latest version of *QxOrm library* and *QxEntityEditor
application* on QxOrm website. <http://www.qxorm.com/>
Regards,
Lionel Marty - QxOrm library
Re: When to create a new user?
From: Richard Reina gatorre...@gmail.com I am writing a web application... As new users sign up for the application should each get their own MySQL username and password or is okay to execute their queries with the same (one generic) MySQL username and password? As others have said, it sounds like one SQL user. Think of MySQL users as roles, rather than users. Segregate these roles according to how much trust you have in the user behind the role, and how much damage that role could perform. You may want a separate MySQL user that can only INSERT, for example, but without DELETE permission. Be a light, not a judge. Be a model, not a critic. Be part of the solution, not part of the problem. -- Stephen R. Covey Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: When to create a new user?
On 8/19/2015 9:24 AM, Reindl Harald wrote: Am 19.08.2015 um 15:18 schrieb Jim: On 8/19/2015 8:40 AM, Reindl Harald wrote: Am 19.08.2015 um 14:29 schrieb Richard Reina: I am writing a web application in perl that will create, edit, update and delete data from a MySQL database. I have written a perl module that will manage the connections (issue database handles ). As new users sign up for the application should each get their own MySQL username and password or is okay to execute their queries with the same (one generic) MySQL username and password? one generic for the application since you normally never ever should connect as root to your application it even don't have the permissions to add mysql-users how would you even imagine working with a usertable on your applications side which is for every user different - chicken/egg One generic, non-admin user is what you'll find in most apps. Some developers take the approach of creating several users based on level of operation and least privilege, particularly for public facing scripts. So, for example, if the operation is to delete data, that might be one user. Perhaps there is some very sensitive data in the environment and you'll create a given user for accessing that data and no other user has access to that data. Each user is given no greater access than is required based on the intent of that user. Then the given script connects with the appropriate user here. Of course, you should program against and have defenses for db-related vulnerabilities like SQL-injection, but the thinking with the multiple users is if you had a script that was vulnerable to some exploit, the damage would be limited to the privileges of the mysql user used to connect to your database. It's a bit harder to manage and requires some more planning up front, but it adds to damage control in case of a db related exploit. You'd make this call based on how sensitive and important your data is and how much effort you are willing to put into the planning and design. yes, but what has this all to do with As new users sign up for the application and create a own mysql-user for each application user? OP's question was generic on when to create db users. I provided an alternative (arguably more secure for public facing scripts) to a single user per app... again... depends on the value of the data and level of programming effort. Read it; don't read it; use it or don't. It's more information and adds to the education of anyone interested. If not used now, there might be a future case or someone else reading this that might consider the approach. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: When to create a new user?
Am 19.08.2015 um 15:18 schrieb Jim: On 8/19/2015 8:40 AM, Reindl Harald wrote: Am 19.08.2015 um 14:29 schrieb Richard Reina: I am writing a web application in perl that will create, edit, update and delete data from a MySQL database. I have written a perl module that will manage the connections (issue database handles ). As new users sign up for the application should each get their own MySQL username and password or is okay to execute their queries with the same (one generic) MySQL username and password? one generic for the application since you normally never ever should connect as root to your application it even don't have the permissions to add mysql-users how would you even imagine working with a usertable on your applications side which is for every user different - chicken/egg One generic, non-admin user is what you'll find in most apps. Some developers take the approach of creating several users based on level of operation and least privilege, particularly for public facing scripts. So, for example, if the operation is to delete data, that might be one user. Perhaps there is some very sensitive data in the environment and you'll create a given user for accessing that data and no other user has access to that data. Each user is given no greater access than is required based on the intent of that user. Then the given script connects with the appropriate user here. Of course, you should program against and have defenses for db-related vulnerabilities like SQL-injection, but the thinking with the multiple users is if you had a script that was vulnerable to some exploit, the damage would be limited to the privileges of the mysql user used to connect to your database. It's a bit harder to manage and requires some more planning up front, but it adds to damage control in case of a db related exploit. You'd make this call based on how sensitive and important your data is and how much effort you are willing to put into the planning and design. yes, but what has this all to do with As new users sign up for the application and create a own mysql-user for each application user? signature.asc Description: OpenPGP digital signature
Re: When to create a new user?
On 8/19/2015 8:40 AM, Reindl Harald wrote: Am 19.08.2015 um 14:29 schrieb Richard Reina: I am writing a web application in perl that will create, edit, update and delete data from a MySQL database. I have written a perl module that will manage the connections (issue database handles ). As new users sign up for the application should each get their own MySQL username and password or is okay to execute their queries with the same (one generic) MySQL username and password? one generic for the application since you normally never ever should connect as root to your application it even don't have the permissions to add mysql-users how would you even imagine working with a usertable on your applications side which is for every user different - chicken/egg One generic, non-admin user is what you'll find in most apps. Some developers take the approach of creating several users based on level of operation and least privilege, particularly for public facing scripts. So, for example, if the operation is to delete data, that might be one user. Perhaps there is some very sensitive data in the environment and you'll create a given user for accessing that data and no other user has access to that data. Each user is given no greater access than is required based on the intent of that user. Then the given script connects with the appropriate user here. Of course, you should program against and have defenses for db-related vulnerabilities like SQL-injection, but the thinking with the multiple users is if you had a script that was vulnerable to some exploit, the damage would be limited to the privileges of the mysql user used to connect to your database. It's a bit harder to manage and requires some more planning up front, but it adds to damage control in case of a db related exploit. You'd make this call based on how sensitive and important your data is and how much effort you are willing to put into the planning and design. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
When to create a new user?
I am writing a web application in perl that will create, edit, update and delete data from a MySQL database. I have written a perl module that will manage the connections (issue database handles ). As new users sign up for the application should each get their own MySQL username and password or is okay to execute their queries with the same (one generic) MySQL username and password? Thanks
Re: When to create a new user?
Am 19.08.2015 um 14:29 schrieb Richard Reina: I am writing a web application in perl that will create, edit, update and delete data from a MySQL database. I have written a perl module that will manage the connections (issue database handles ). As new users sign up for the application should each get their own MySQL username and password or is okay to execute their queries with the same (one generic) MySQL username and password? one generic for the application since you normally never ever should connect as root to your application it even don't have the permissions to add mysql-users how would you even imagine working with a usertable on your applications side which is for every user different - chicken/egg signature.asc Description: OpenPGP digital signature
Re: When to create a new user?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/19/2015 05:29 AM, Richard Reina wrote: As new users sign up for the application should each get their own MySQL username and password or is okay to execute their queries with the same (one generic) MySQL username and password? That is rather vague. What data is stored for each user? What are these queries that a user may perform? - -- James Moe moe dot james at sohnen-moe dot com 520.743.3936 -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlXUvlgACgkQzTcr8Prq0ZPrHQCdFDqY9uEa1mS62LuUr7FhqzEa 6R4AoJu6L5Je6sXivtY31RPGgM8bIYv7 =vyyy -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: When to create a new user?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/19/2015 10:06 AM, Richard Reina wrote: Data stored for each user would be a list of places visited that and details relating to those trips. The type of queries they would be able to perform be able to read, update and create new records. I see no reason to create a unique user account for this use case. Each user's data goes into a singe table, and a view based on the user's ID would restrict data access for each user. - -- James Moe moe dot james at sohnen-moe dot com 520.743.3936 -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlXUv0QACgkQzTcr8Prq0ZOaXACdFrnbcxrJMsVq3cn6fzbfbdn4 iBoAnRX3USjmqnKWgdHGvuVBxrQnH++X =bhJj -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: can't authenticate ssl user account
Hi Reindel, you client configuration shows no indication for SSL, i see it in my.cnf only in the [mysqld] section and remember when you initrialize replication you need to specify it there too i doubt there is anything to change the logging but since you *know* what that user requires that should really not be the problem - said from somebody using SSL for any mysql connection over TCP for years now (replication, php-applications, cli-client...) Ok!! Thanks. But when I try to setup my client configuration to use SSL, mariadb server refuses to start. It times out: [root@db2:~] #systemctl status mysql.service mysql.service - LSB: start and stop MySQL Loaded: loaded (/etc/rc.d/init.d/mysql) Active: failed (Result: exit-code) since Tue 2015-07-21 18:29:24 UTC; 13s ago Process: 19965 ExecStop=/etc/rc.d/init.d/mysql stop (code=exited, status=0/SUCCESS) Process: 21973 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=1/FAILURE) Jul 21 18:29:23 db2 systemd[1]: Starting LSB: start and stop MySQL... Jul 21 18:29:24 db2 mysql[21973]: Starting MySQL. ERROR! Jul 21 18:29:24 db2 systemd[1]: mysql.service: control process exited, code=exited status=1 Jul 21 18:29:24 db2 systemd[1]: Failed to start LSB: start and stop MySQL. Jul 21 18:29:24 db2 systemd[1]: Unit mysql.service entered failed state. Here's my my.cnf on the client side that is causing the time out error to occur: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 old_passwords=1 ssl server-id=2 replicate-do-db=jfwiki [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid master-connect-retry=60 !includedir /etc/my.cnf.d [client] ssl-ca=/opt/mysql/ca-cert.pem ssl-cert=/opt/mysql/client-cert.pem ssl-key=/opt/mysql/client-key.pem Any idea why that's happening or how to correct it? Thanks, Tim On Tue, Jul 21, 2015 at 4:25 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 21.07.2015 um 05:03 schrieb Tim Dunphy: I see absolutely NO indication as to why the login for the 'slave2' user (that requires SSL) is failing So my questions are 1) how to I bump up the verbosity on the logs so I can get an indication as to why this is failing? 2) what is the best way to troubleshoot this? you client configuration shows no indication for SSL, i see it in my.cnf only in the [mysqld] section and remember when you initrialize replication you need to specify it there too i doubt there is anything to change the logging but since you *know* what that user requires that should really not be the problem - said from somebody using SSL for any mysql connection over TCP for years now (replication, php-applications, cli-client...) CHANGE MASTER TO MASTER_HOST='masterip', MASTER_USER='user', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_CONNECT_RETRY=3600, MASTER_SSL=1, MASTER_SSL_CA='/etc/mysqlssl/ca.crt', MASTER_SSL_CERT='/etc/mysqlssl/client.pem', MASTER_SSL_KEY='/etc/mysqlssl/client.pem'; START SLAVE; -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: can't authenticate ssl user account
Am 21.07.2015 um 05:03 schrieb Tim Dunphy: I see absolutely NO indication as to why the login for the 'slave2' user (that requires SSL) is failing So my questions are 1) how to I bump up the verbosity on the logs so I can get an indication as to why this is failing? 2) what is the best way to troubleshoot this? you client configuration shows no indication for SSL, i see it in my.cnf only in the [mysqld] section and remember when you initrialize replication you need to specify it there too i doubt there is anything to change the logging but since you *know* what that user requires that should really not be the problem - said from somebody using SSL for any mysql connection over TCP for years now (replication, php-applications, cli-client...) CHANGE MASTER TO MASTER_HOST='masterip', MASTER_USER='user', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_CONNECT_RETRY=3600, MASTER_SSL=1, MASTER_SSL_CA='/etc/mysqlssl/ca.crt', MASTER_SSL_CERT='/etc/mysqlssl/client.pem', MASTER_SSL_KEY='/etc/mysqlssl/client.pem'; START SLAVE; signature.asc Description: OpenPGP digital signature
can't authenticate ssl user account
Hey all, I need to setup replication via SSL. 2 nodes master/master for H/A. Then 2 slave nodes for backup. Node 3 will slave off of node 1, and node 4 will slave off of node 2 for redundant backups. Nodes 3 4 will store backups to a directory mapped to S3 via S3FS. All nodes are physical. We're using mariadb-5.5.41 server on CentOS 7 hosts. It's a neat plan! However I am unable to get authentication to work with ssl required of the user. We already have a CA certificate and key established in the environment. And we used those to generate the cert and key to be used with mariadb. This is the process we used to generate the cert/key: openssl genrsa -des3 -out db1.example.com.key 4096 openssl req -new -key db1.example.com.key -out db1.example.com.csr openssl x509 -req -days 3650 -in db1.example.com.csr -CA ca.crt -CAkey ca.key -set_serial 01 -out db1.example.com.crt Using those keys I put together this my.cnf file. Haven't gotten around to configuring replication yet, as I have yet to get ssl logins to work. [root@db1:~] #cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd ssl ssl-ca=/opt/mysql/ca.crt ssl-cert=/opt/mysql/db1.example.com.crt ssl-key=/opt/mysql/db1.example.com.key [mysqld_safe] general_log_file=/var/log/mariadb/mariadb.log general_log=1 log-error=/var/log/mariadb/mariadb_error.log pid-file=/var/run/mariadb/mariadb.pid log_slow_queries=/var/log/mysql/mysql-slow.log long_query_time=2 log-queries-not-using-indexes # # include all files from the config directory # !includedir /etc/my.cnf.d Made sure mariadb could read the cert files: [root@db1:~] #ls -ld /opt/mysql/ /opt/mysql/* drwx--. 2 mysql mysql 86 Jul 20 06:20 /opt/mysql/ -r. 1 mysql mysql 2212 Jul 20 05:14 /opt/mysql/ca.crt -r. 1 mysql mysql 1956 Jul 20 05:17 /opt/mysql/db1.example.com.crt -r. 1 mysql mysql 3247 Jul 20 05:15 /opt/mysql/db1.example.com.key And restarted mariadb. And if I take a look at my SSL variables in my mysql command line, everything is looking good. MariaDB [mysql] show variables like '%ssl%'; +---+-+ | Variable_name | Value | +---+-+ | have_openssl | YES | | have_ssl | YES | | ssl_ca| /opt/mysql/ca.crt | | ssl_capath| | | ssl_cert | /opt/mysql/db1.example.com.crt | | ssl_cipher| | | ssl_key | /opt/mysql/db1.example.com.key | +---+-+ 7 rows in set (0.00 sec) If I create one replication user without SSL required, he is able to log in from node 2 to node 1 no problem: MariaDB [mysql] grant replication slave on *.* to 'slave1'@' db2.example.com' identified by 'secret'; Query OK, 0 rows affected (0.00 sec) [root@db2:~] #mysql -uslave1 -p -h db1.example.com Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 15 Server version: 5.5.41-MariaDB MariaDB Server Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)] If I show grants on this user I can confirm that SSL is not required MariaDB [mysql] show grants for 'slave1'@'db2.example.com'; ++ | Grants for sla...@db2.example.com | ++ | GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'db2.example.com' IDENTIFIED BY PASSWORD '*somelongpasswordhash' | ++ 1 row in set (0.00 sec) If I create the second slave user with the SSL requirement, and even flush privileges, I can not log into node 1 from node 2 MariaDB [mysql] grant replication slave on *.* to 'slave2'@' db2.example.com' identified by 'test' require ssl; Query OK, 0 rows affected
Re: how can i login without a password although in the user-table is a password ?
Bernd wrote: a password ? Hi, this is my system: mysql status -- mysql Ver 14.12 Distrib 5.0.26, for suse-linux (x86_64) using readline 5.1 Connection id: 142883 Current database: mysql Current user: root@localhost SSL:Not in use Current pager: less Using outfile: '' Using delimiter:; Server version: 5.0.26-Max-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 223 days 22 hours 38 min 49 sec Threads: 1 Questions: 9178423 Slow queries: 99 Opens: 607644 Flush tables: 3 Open tables: 64 Queries per second avg: 0.474 my user-table looks like this: mysql select host,user,password from user; ++--+---+ | host | user | password | ++--+---+ | localhost | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | pc52974.gsf.de | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | pc52974.gsf.de | | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | localhost | | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | % | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | 127.0.0.1 | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | ++--+---+ I know that this is not the best solution, I will change it. Using mysql on a Linux-system I can login with mysql -u root without supplying a password. How is this possible although there is a password for each user and for root in the user-table ? I found it out by myself: I have a .my.cnf in my home-directory. I forgot. Bernd Helmholtz Zentrum München Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen Registergericht: Amtsgericht München HRB 6466 USt-IdNr: DE 129521671
how can i login without a password although in the user-table is a password ?
Hi, this is my system: mysql status -- mysql Ver 14.12 Distrib 5.0.26, for suse-linux (x86_64) using readline 5.1 Connection id: 142883 Current database: mysql Current user: root@localhost SSL:Not in use Current pager: less Using outfile: '' Using delimiter:; Server version: 5.0.26-Max-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 223 days 22 hours 38 min 49 sec Threads: 1 Questions: 9178423 Slow queries: 99 Opens: 607644 Flush tables: 3 Open tables: 64 Queries per second avg: 0.474 my user-table looks like this: mysql select host,user,password from user; ++--+---+ | host | user | password | ++--+---+ | localhost | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | pc52974.gsf.de | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | pc52974.gsf.de | | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | localhost | | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | % | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | | 127.0.0.1 | root | *1939D66C9255EE78C765CD91B5FB465C7A9472D9 | ++--+---+ I know that this is not the best solution, I will change it. Using mysql on a Linux-system I can login with mysql -u root without supplying a password. How is this possible although there is a password for each user and for root in the user-table ? Thanks in advance. Bernd -- Bernd Lentes Systemadministration Institut für Entwicklungsgenetik Gebäude 35.34 - Raum 208 HelmholtzZentrum münchen bernd.len...@helmholtz-muenchen.de phone: +49 89 3187 1241 fax: +49 89 3187 2294 http://www.helmholtz-muenchen.de/idg Je suis Charlie Helmholtz Zentrum München Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen Registergericht: Amtsgericht München HRB 6466 USt-IdNr: DE 129521671 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
access denied fpr user 'root'@'localhost' (using password: NO)
I have ran the following to test a fix for an app issue : delete from mysql.user where user=''; 2lines got effected after this I can´t connect through command line anymore : ./mysqladmin -u root password pwd I get access denied for user 'root'@'localhost (using password:'NO') how can I restore the db so I can connect through command line again ? thks
Re: access denied fpr user 'root'@'localhost' (using password: NO)
Am 29.05.2014 20:22, schrieb Érico: I have ran the following to test a fix for an app issue : delete from mysql.user where user=''; 2lines got effected after this I can´t connect through command line anymore : ./mysqladmin -u root password pwd I get access denied for user 'root'@'localhost (using password:'NO') your command line is plain wrong as you can see in the response you are *not* using a password ./mysqladmin -u root --password=pwd *don't do that at all* your password ends in the history ./mysqladmin -u root -p after that you get a pwd-prompt how can I restore the db so I can connect through command line again ? if you really need to login with a destroyed userdb make sure that nobody else can access the server and use skip grant http://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables signature.asc Description: OpenPGP digital signature
Re: access denied fpr user 'root'@'localhost' (using password: NO)
Did you tried this.. http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html *thanks,* *-- *Kishore Kumar Vaishnav On Thu, May 29, 2014 at 11:22 AM, Érico erico...@gmail.com wrote: I have ran the following to test a fix for an app issue : delete from mysql.user where user=''; 2lines got effected after this I can´t connect through command line anymore : ./mysqladmin -u root password pwd I get access denied for user 'root'@'localhost (using password:'NO') how can I restore the db so I can connect through command line again ? thks
Re: access denied fpr user 'root'@'localhost' (using password: NO)
Hi
thanks but it is not working either
I wonder if the 2 rows I removed (with empty users for localhost and my
computer names) were responsilbe for allowing the autentication ?
how this table works ?
what mysql checks on it ? the pwd column ? if it is filled ?
what if I have 2 records for root/localhost ... ?
one with pwd filled
and the second with the pwd empty
how can I insert on this table with the mandatory column ssl_cipher as blob
... ? what value must I provide in a insert like :
insert into mysql.user(host,user,ssl_cipher) values('localhost','root' ,
??? )
thks !!
2014-05-29 15:36 GMT-03:00 Kishore Vaishnav kish...@railsfactory.org:
Did you tried this..
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
*thanks,*
*-- *Kishore Kumar Vaishnav
On Thu, May 29, 2014 at 11:22 AM, Érico erico...@gmail.com wrote:
I have ran the following to test a fix for an app issue :
delete from mysql.user where user='';
2lines got effected
after this I can´t connect through command line anymore :
./mysqladmin -u root password pwd
I get access denied for user 'root'@'localhost (using password:'NO')
how can I restore the db so I can connect through command line again ?
thks
Re: access denied fpr user 'root'@'localhost' (using password: NO)
running this : ./mysqladmin -u root -p at this momento it thorws me to mysqladmin man page 2014-05-29 15:35 GMT-03:00 Reindl Harald h.rei...@thelounge.net: Am 29.05.2014 20:22, schrieb Érico: I have ran the following to test a fix for an app issue : delete from mysql.user where user=''; 2lines got effected after this I can´t connect through command line anymore : ./mysqladmin -u root password pwd I get access denied for user 'root'@'localhost (using password:'NO') your command line is plain wrong as you can see in the response you are *not* using a password ./mysqladmin -u root --password=pwd *don't do that at all* your password ends in the history ./mysqladmin -u root -p after that you get a pwd-prompt how can I restore the db so I can connect through command line again ? if you really need to login with a destroyed userdb make sure that nobody else can access the server and use skip grant http://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables
Re: access denied fpr user 'root'@'localhost' (using password: NO)
well, i am mysql administrator over 10 years now and never needed the mysqladmin command because the mysql command line client offers anything i ever needed mysql -u root -p [harry@srv-rhsoft:~]$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 16056 Server version: 5.5.37-MariaDB-log thelounge Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)] Am 29.05.2014 21:19, schrieb Érico: running this : ./mysqladmin -u root -p at this momento it thorws me to mysqladmin man page 2014-05-29 15:35 GMT-03:00 Reindl Harald h.rei...@thelounge.net: Am 29.05.2014 20:22, schrieb Érico: I have ran the following to test a fix for an app issue : delete from mysql.user where user=''; 2lines got effected after this I can´t connect through command line anymore : ./mysqladmin -u root password pwd I get access denied for user 'root'@'localhost (using password:'NO') your command line is plain wrong as you can see in the response you are *not* using a password ./mysqladmin -u root --password=pwd *don't do that at all* your password ends in the history ./mysqladmin -u root -p after that you get a pwd-prompt how can I restore the db so I can connect through command line again ? if you really need to login with a destroyed userdb make sure that nobody else can access the server and use skip grant http://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables signature.asc Description: OpenPGP digital signature
Re: access denied fpr user 'root'@'localhost' (using password: NO)
Hello Érico On 5/29/2014 2:22 PM, Érico wrote: I have ran the following to test a fix for an app issue : delete from mysql.user where user=''; 2lines got effected after this I can´t connect through command line anymore : ./mysqladmin -u root password pwd I get access denied for user 'root'@'localhost (using password:'NO') how can I restore the db so I can connect through command line again ? thks What that tells me is that you were never actually logging in as root but the system was authenticating you as the 'anonymous' user. Quoting from the very fine manual: http://dev.mysql.com/doc/refman/5.6/en/account-names.html A user name is either a nonblank value that literally matches the user name for incoming connection attempts, or a blank value (empty string) that matches any user name. An account with a blank user name is an anonymous user. To specify an anonymous user in SQL statements, use a quoted empty user name part, such as ''@'localhost'. http://dev.mysql.com/doc/refman/5.6/en/connection-access.html Identity checking is performed using the three user table scope columns (Host, User, and Password). The server accepts the connection only if the Host and User columns in some user table row match the client host name and user name and the client supplies the password specified in that row. ... If the User column value is nonblank, the user name in an incoming connection must match exactly. If the User value is blank, it matches any user name. If the user table row that matches an incoming connection has a blank user name, the user is considered to be an anonymous user with no name, not a user with the name that the client actually specified. This means that a blank user name is used for all further access checking for the duration of the connection (that is, during Stage 2). ... If you are able to connect to the server, but your privileges are not what you expect, you probably are being authenticated as some other account. To find out what account the server used to authenticate you, use the CURRENT_USER() function. That same page in the manual (and its siblings) should also answer your questions as to how MySQL uses the `user` table, what the empty `user` and `password` column mean to login attempts, and how to configure SSL-based connections. If you have forgotten your actual root@localhost password, you can reset it following one of the procedures provided here. http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: access denied fpr user 'root'@'localhost' (using password: NO)
I am really sorry about this one .. the connection is ok ... I had not checked that I was using mysqladmin instead of mysql now please how can I check what is wrong with my application ( My SQL Admin ) at its login page it asks for user / pwd / server and db using both localhost and 127.0.01 ... it gets the same error : access denied for user 'root'@'localhost' the app has a php config page where it fills these info I am able to connect to it manually too using : ./mysql -h localhost -u root -pmy_pwd mysql-admin but the app keeps geting the access denied error would it be sometihng related to my /et/hosts ? its content : 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost fe80::1%lo0 localhost 127.0.0.1 mysqld 127.0.0.1 mac localhost mac my SO is a mac os 10.6.8 Thks Again !! 2014-05-29 16:26 GMT-03:00 Reindl Harald h.rei...@thelounge.net: well, i am mysql administrator over 10 years now and never needed the mysqladmin command because the mysql command line client offers anything i ever needed mysql -u root -p [harry@srv-rhsoft:~]$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 16056 Server version: 5.5.37-MariaDB-log thelounge Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)] Am 29.05.2014 21:19, schrieb Érico: running this : ./mysqladmin -u root -p at this momento it thorws me to mysqladmin man page 2014-05-29 15:35 GMT-03:00 Reindl Harald h.rei...@thelounge.net: Am 29.05.2014 20:22, schrieb Érico: I have ran the following to test a fix for an app issue : delete from mysql.user where user=''; 2lines got effected after this I can´t connect through command line anymore : ./mysqladmin -u root password pwd I get access denied for user 'root'@'localhost (using password:'NO') your command line is plain wrong as you can see in the response you are *not* using a password ./mysqladmin -u root --password=pwd *don't do that at all* your password ends in the history ./mysqladmin -u root -p after that you get a pwd-prompt how can I restore the db so I can connect through command line again ? if you really need to login with a destroyed userdb make sure that nobody else can access the server and use skip grant http://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables
Re: access denied fpr user 'root'@'localhost' (using password: NO)
Hello Érico, On 5/29/2014 3:51 PM, Érico wrote: I am really sorry about this one .. the connection is ok ... I had not checked that I was using mysqladmin instead of mysql now please how can I check what is wrong with my application ( My SQL Admin ) at its login page it asks for user / pwd / server and db using both localhost and 127.0.01 ... it gets the same error : access denied for user 'root'@'localhost' the app has a php config page where it fills these info I am able to connect to it manually too using : ./mysql -h localhost -u root -pmy_pwd mysql-admin but the app keeps geting the access denied error would it be sometihng related to my /et/hosts ? its content : 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost fe80::1%lo0 localhost 127.0.0.1 mysqld 127.0.0.1 mac localhost mac my SO is a mac os 10.6.8 Thks Again !! ... snip ... What is the result of this query: SELECT user, host, length(password) from mysql.user; What hapens if you change your login to this? (you should not put your passwords on your command lines if you can avoid it http://dev.mysql.com/doc/refman/5.6/en/password-security-user.html http://dev.mysql.com/doc/refman/5.6/en/connecting.html ) ./mysql -h 127.0.01 --port=3306 --protocol=TCP -u root -p mysql-admin See also: http://dev.mysql.com/doc/refman/5.6/en/access-denied.html -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[5.1 Vs 5.5 ] ERROR 1045 (28000): Access denied for user 'testuser'@'Serv1.corp.domain.in' (using password: YES)
Hi, I am trying to connect two mysql servers with different versions ( 5.1 5.5 ) . But in Mysql 5.1 i am facing strange issues. Below testuser exists in both Mysql Versions : mysql select host,user,password from mysql.user where user='testuser'; +---++---+ | host | user | password | +---++---+ | localhost | testuser | *FJHHEU5746DDHDUDYDH66488 | | %.corp.domain.in| testuser | *FJHHEU5746DDHDUDYDH66488 | +---++---+ and skip_networking is OFF *Mysql Version : 5.1.58-log* root@Serv1:~# mysql -utestuser -p@8AsnM0! -h $(hostname) ERROR 1045 (28000): Access denied for user 'testuser'@'Serv1.corp.domain.in' (using password: YES) It connect successfully if i remove -h option because it connects by localhost then *Mysql version : 5.5.36-log * root@Serv2:~# mysql -utestuser -p@8AsnM0! -h $(hostname) Welcome to the MySQL monitor. Commands end with ; or \g. Server version: 5.5.36-log MySQL Community Server (GPL) mysql mysql select user(),current_user(); +--+---+ | user() | current_user()| +--+---+ | testu...@serv2.corp.domain.in| testuser@%.corp.domain.in | +--+---+ Is dere some bug in Mysql5.1 or i need to set bind_address parameter in it. Thanks
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
ok I have tried these : ifconfig -a lo0: flags=8049UP,LOOPBACK,RUNNING,MULTICAST mtu 16384 inet6 ::1 prefixlen 128 inet6 fe80::1%lo0 prefixlen 64 scopeid 0x1 inet 127.0.0.1 netmask 0xff00 gif0: flags=8010POINTOPOINT,MULTICAST mtu 1280 stf0: flags=0 mtu 1280 fw0: flags=8863UP,BROADCAST,SMART,RUNNING,SIMPLEX,MULTICAST mtu 4078 lladdr 70:cd:60:ff:fe:eb:72:ea media: autoselect full-duplex status: inactive en1: flags=8863UP,BROADCAST,SMART,RUNNING,SIMPLEX,MULTICAST mtu 1500 ether e4:ce:8f:04:7c:f8 inet6 fe80::e6ce:8fff:fe04:7cf8%en1 prefixlen 64 scopeid 0x5 inet 169.254.99.150 netmask 0x broadcast 169.254.255.255 media: autoselect status: active en0: flags=8863UP,BROADCAST,SMART,RUNNING,SIMPLEX,MULTICAST mtu 1500 ether c8:2a:14:1a:47:ea media: autoselect (none) status: inactive wc2: flags=822BROADCAST,SMART,SIMPLEX mtu 1500 ether 00:02:55:11:19:76 media: 1000baseT (unknown type) = mac:bin ericomtx$ nslookup localhost ;; connection timed out; no servers could be reached = mac:bin ericomtx$ netstat -an | grep 3306 tcp46 0 0 *.3306 *.*LISTEN tcp4 0 0 *.3306 *.*LISTEN = mac:bin ericomtx$ netstat -ln | grep mysql ff801403c280 stream 0 00 ff801403b20000 /tmp/mysql.sock ff801403a9c0 stream 0 0 ff801bbb78b8 000 /tmp/mysql.sock ff8013fb0bc0 stream 0 0 ff8014aa8078 000 /opt/local/var/run/mysql5/mysqld.sock *** here ... this last one is related to a previous mysql macports version *** = mac:lib ericomtx$ ps xa | grep mysqld 231 ?? S 0:00.01 /bin/sh /opt/local/lib/mysql5/bin/mysqld_safe --datadir=/opt/local/var/db/mysql5 --pid-file=/opt/local/var/db/mysql5/mac.local.pid 295 ?? S 0:00.74 /opt/local/libexec/mysqld --basedir=/opt/local --datadir=/opt/local/var/db/mysql5 --user=_mysql --log-error=/opt/local/var/db/mysql5/mac.local.err --pid-file=/opt/local/var/db/mysql5/mac.local.pid --socket=/opt/local/var/run/mysql5/mysqld.sock 2175 s000 S 0:00.02 /bin/sh /Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/bin/mysqld_safe --datadir=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/data --pid-file=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/data/mac.local.pid 2273 s000 S 0:00.35 /Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/bin/mysqld --basedir=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86 --datadir=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/data --plugin-dir=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/lib/plugin --user=mysql --log-error=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/data/mac.local.err --pid-file=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/data/mac.local.pid = I have uninstalled everything related to mysql ports these dirs no longer exist : /opt/local/lib/mysql5 /opt/local/libexec I don't know from where they are getting called 2014/1/15 Claudio Nanni claudio.na...@gmail.com Hi | | ericomtxmacbookpro.local | *E85DC00A0137C6171923BE35EDD809573FB3AB4F | mysql DELETE FROM mysql.user WHERE user=''; mysql FLUSH PRIVILEGES; maybe helps? Cheers -- Claudio
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
Hi | | ericomtxmacbookpro.local | *E85DC00A0137C6171923BE35EDD809573FB3AB4F | mysql DELETE FROM mysql.user WHERE user=''; mysql FLUSH PRIVILEGES; maybe helps? Cheers -- Claudio
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
yes it is ... also , the eclipse is also local ... in both ... mysql-admin php application , plus inside eclispe plugin ... same behavior in both 2014/1/13 Reindl Harald h.rei...@thelounge.net i doubt that the webserver is running on the same machine as your mysql command shell Am 13.01.2014 23:59, schrieb Érico: true but please check this out : mysql Select user, host, password from mysql.user; +--+--+---+ | user | host | password | +--+--+---+ | root | localhost| *E85DC00A0137C6171923BE35EDD809573FB3AB4F | | root | ericomtxmacbookpro.local | *E85DC00A0137C6171923BE35EDD809573FB3AB4F | | root | 127.0.0.1| *E85DC00A0137C6171923BE35EDD809573FB3AB4F | | root | ::1 | | | | ericomtxmacbookpro.local | *E85DC00A0137C6171923BE35EDD809573FB3AB4F | +--+--+---+ 5 rows in set (0.00 sec) all passwords are filled in the db ... also ... I can connect through command line what I can't do is : 1. connect or even ping inside eclipse using jconnector 2. connect from a php app (mysql adim) with or with out pwd ... providing the error Access denied for user 'root'@'localhost' (using password: YES) Access denied for user 'root'@'localhost' (using password: NO) 2.1 when I use 127.0.0.1 on mysql admin url ... I get a connection closed so ... my point is : in both cases when using 127.0.0.1 or localhost ... can the OS be blocking the connection ? if so , how could I check this ? Regards Érico 2014/1/13 Reindl Harald h.rei...@thelounge.net mailto: h.rei...@thelounge.net WTF - we are talking about *database connections* and *not* http-URL's the webserver is only the *messenger* Am 13.01.2014 18:54, schrieb Érico: using both urls I get the same error : http://localhost/mysql/index.php http://127.0.0.1/mysql/index.php in 127.0.0.1... after I submit the index.php ... it redirects to localhost too .. 2014/1/13 Reindl Harald h.rei...@thelounge.net mailto: h.rei...@thelounge.net Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 mailto:root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1 http://127.0.0.1: TCP -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development m: +43 (676) 40 221 40, p: +43 (1) 595 3999 33 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
Hi I have installed mysql admin on my local environment I am able to connect to mysql through command line : ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql ... but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen ? I am starting it this way : sudo ./mysql.server start my /etc/hosts file : ## # Host Database # # localhost is used to configure the loopback interface # when the system is booting. Do not change this entry. ## 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost #fe80::1%lo0localhost 127.0.0.1 ericomtxmacbookpro.local 127.0.0.1 mysqld Thks Érico
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP signature.asc Description: OpenPGP digital signature
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
From that error I would suspect you are trying to access to a database where has not external access. And yeah, try by doing $ mysql -u root -p database -h localhost -P 3306 change localhost by 127.0.0.1 in order to test if both cases work, and see which of them (localhost/127.0.0.1) is defined in your web app as you've been told in the mail before. On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote: Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
using both urls I get the same error : http://localhost/mysql/index.php http://127.0.0.1/mysql/index.php in 127.0.0.1... after I submit the index.php ... it redirects to localhost too .. 2014/1/13 Reindl Harald h.rei...@thelounge.net Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
using localhost the coonection works ... ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h localhost -P 3306 Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 97 Server version: 5.6.15 MySQL Community Server (GPL) but using 127.0.0.1 no : ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h 127.0.0.1 -P 3306 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ericomtxmacbookpro:bin ericomtx$ 2014/1/13 Erick Ocrospoma zipper1...@gmail.com From that error I would suspect you are trying to access to a database where has not external access. And yeah, try by doing $ mysql -u root -p database -h localhost -P 3306 change localhost by 127.0.0.1 in order to test if both cases work, and see which of them (localhost/127.0.0.1) is defined in your web app as you've been told in the mail before. On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote: Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
I presume your index.php file uses 127.0.0.1. After login to MySQL try this: $ grant all privileges to *.* 'root'@'127.0.0.1' identified by yourpassword; Of course this could not be the best solution, it's just to skip it, you must look at the query/connection on your php file. On 13 January 2014 12:57, Érico erico...@gmail.com wrote: using localhost the coonection works ... ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h localhost -P 3306 Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 97 Server version: 5.6.15 MySQL Community Server (GPL) but using 127.0.0.1 no : ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h 127.0.0.1 -P 3306 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ericomtxmacbookpro:bin ericomtx$ 2014/1/13 Erick Ocrospoma zipper1...@gmail.com From that error I would suspect you are trying to access to a database where has not external access. And yeah, try by doing $ mysql -u root -p database -h localhost -P 3306 change localhost by 127.0.0.1 in order to test if both cases work, and see which of them (localhost/127.0.0.1) is defined in your web app as you've been told in the mail before. On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote: Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
I forgot this. Do it too. On 13 January 2014 13:01, Erick Ocrospoma zipper1...@gmail.com wrote: I presume your index.php file uses 127.0.0.1. After login to MySQL try this: $ grant all privileges to *.* 'root'@'127.0.0.1' identified by yourpassword; $ flush privileges; Of course this could not be the best solution, it's just to skip it, you must look at the query/connection on your php file. On 13 January 2014 12:57, Érico erico...@gmail.com wrote: using localhost the coonection works ... ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h localhost -P 3306 Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 97 Server version: 5.6.15 MySQL Community Server (GPL) but using 127.0.0.1 no : ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h 127.0.0.1 -P 3306 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ericomtxmacbookpro:bin ericomtx$ 2014/1/13 Erick Ocrospoma zipper1...@gmail.com From that error I would suspect you are trying to access to a database where has not external access. And yeah, try by doing $ mysql -u root -p database -h localhost -P 3306 change localhost by 127.0.0.1 in order to test if both cases work, and see which of them (localhost/127.0.0.1) is defined in your web app as you've been told in the mail before. On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote: Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
the granting is not affecting the tables: mysql grant all privileges on *.* to root@localhost identified by 'pwd'; Query OK, 0 rows affected (0.00 sec) mysql FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) ... and if I try the granting in 127.0.0.1 : mysql grant all privileges to *.* 'root'@'127.0.0.1' identified by kernel26; ERROR 1064 (42000): 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 'to *.* 'root'@'127.0.0.1' identified by kernel26' at line 1 ... in eclipse using jconnector ... I get the same error : when pinging : java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169) Could not connect to New MySQL. Error creating SQL Model Connection connection to New MySQL. (Error: Access denied for user 'root'@'localhost' (using password: YES)) Access denied for user 'root'@'localhost' (using password: YES) 2014/1/13 Erick Ocrospoma zipper1...@gmail.com I forgot this. Do it too. On 13 January 2014 13:01, Erick Ocrospoma zipper1...@gmail.com wrote: I presume your index.php file uses 127.0.0.1. After login to MySQL try this: $ grant all privileges to *.* 'root'@'127.0.0.1' identified by yourpassword; $ flush privileges; Of course this could not be the best solution, it's just to skip it, you must look at the query/connection on your php file. On 13 January 2014 12:57, Érico erico...@gmail.com wrote: using localhost the coonection works ... ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h localhost -P 3306 Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 97 Server version: 5.6.15 MySQL Community Server (GPL) but using 127.0.0.1 no : ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h 127.0.0.1 -P 3306 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ericomtxmacbookpro:bin ericomtx$ 2014/1/13 Erick Ocrospoma zipper1...@gmail.com From that error I would suspect you are trying to access to a database where has not external access. And yeah, try by doing $ mysql -u root -p database -h localhost -P 3306 change localhost by 127.0.0.1 in order to test if both cases work, and see which of them (localhost/127.0.0.1) is defined in your web app as you've been told in the mail before. On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote: Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
On 13 January 2014 13:25, Érico erico...@gmail.com wrote: the granting is not affecting the tables: mysql grant all privileges on *.* to root@localhost identified by 'pwd'; Query OK, 0 rows affected (0.00 sec) mysql FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) ... and if I try the granting in 127.0.0.1 : mysql grant all privileges to *.* 'root'@'127.0.0.1' identified by kernel26; ERROR 1064 (42000): 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 'to *.* 'root'@'127.0.0.1' identified by kernel26' at line 1 It's my error :) It should be: mysql grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'kernel26'; ... in eclipse using jconnector ... I get the same error : when pinging : java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169) Could not connect to New MySQL. Error creating SQL Model Connection connection to New MySQL. (Error: Access denied for user 'root'@'localhost' (using password: YES)) Access denied for user 'root'@'localhost' (using password: YES) 2014/1/13 Erick Ocrospoma zipper1...@gmail.com I forgot this. Do it too. On 13 January 2014 13:01, Erick Ocrospoma zipper1...@gmail.com wrote: I presume your index.php file uses 127.0.0.1. After login to MySQL try this: $ grant all privileges to *.* 'root'@'127.0.0.1' identified by yourpassword; $ flush privileges; Of course this could not be the best solution, it's just to skip it, you must look at the query/connection on your php file. On 13 January 2014 12:57, Érico erico...@gmail.com wrote: using localhost the coonection works ... ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h localhost -P 3306 Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 97 Server version: 5.6.15 MySQL Community Server (GPL) but using 127.0.0.1 no : ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h 127.0.0.1 -P 3306 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ericomtxmacbookpro:bin ericomtx$ 2014/1/13 Erick Ocrospoma zipper1...@gmail.com From that error I would suspect you are trying to access to a database where has not external access. And yeah, try by doing $ mysql -u root -p database -h localhost -P 3306 change localhost by 127.0.0.1 in order to test if both cases work, and see which of them (localhost/127.0.0.1) is defined in your web app as you've been told in the mail before. On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote: Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
Hi didn't work look ... isn't this something related to this : are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP ... since I am not able to stabilsh a connection even with using eclipse ... my /etc/hosts file : ## 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost #fe80::1%lo0localhost 127.0.0.1 ericomtxmacbookpro.local #127.0.0.1 mysqld 2014/1/13 Erick Ocrospoma zipper1...@gmail.com On 13 January 2014 13:25, Érico erico...@gmail.com wrote: the granting is not affecting the tables: mysql grant all privileges on *.* to root@localhost identified by 'pwd'; Query OK, 0 rows affected (0.00 sec) mysql FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) ... and if I try the granting in 127.0.0.1 : mysql grant all privileges to *.* 'root'@'127.0.0.1' identified by kernel26; ERROR 1064 (42000): 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 'to *.* 'root'@'127.0.0.1' identified by kernel26' at line 1 It's my error :) It should be: mysql grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'kernel26'; ... in eclipse using jconnector ... I get the same error : when pinging : java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169) Could not connect to New MySQL. Error creating SQL Model Connection connection to New MySQL. (Error: Access denied for user 'root'@'localhost' (using password: YES)) Access denied for user 'root'@'localhost' (using password: YES) 2014/1/13 Erick Ocrospoma zipper1...@gmail.com I forgot this. Do it too. On 13 January 2014 13:01, Erick Ocrospoma zipper1...@gmail.com wrote: I presume your index.php file uses 127.0.0.1. After login to MySQL try this: $ grant all privileges to *.* 'root'@'127.0.0.1' identified by yourpassword; $ flush privileges; Of course this could not be the best solution, it's just to skip it, you must look at the query/connection on your php file. On 13 January 2014 12:57, Érico erico...@gmail.com wrote: using localhost the coonection works ... ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h localhost -P 3306 Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 97 Server version: 5.6.15 MySQL Community Server (GPL) but using 127.0.0.1 no : ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h 127.0.0.1 -P 3306 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ericomtxmacbookpro:bin ericomtx$ 2014/1/13 Erick Ocrospoma zipper1...@gmail.com From that error I would suspect you are trying to access to a database where has not external access. And yeah, try by doing $ mysql -u root -p database -h localhost -P 3306 change localhost by 127.0.0.1 in order to test if both cases work, and see which of them (localhost/127.0.0.1) is defined in your web app as you've been told in the mail before. On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote: Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP -- ~ Happy install ! Erick. --- Cellphone : +51 950307809 Blog: http://zerick.me/ LUG: http://www.utpinux.org IRC : zerick About : http://about.me/zerick Linux User ID : 549567 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- ~ Happy install
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
Hello Érico, On 1/13/2014 1:49 PM, Érico wrote: Hi didn't work look ... isn't this something related to this : are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP ... since I am not able to stabilsh a connection even with using eclipse ... my /etc/hosts file : ## 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost #fe80::1%lo0localhost 127.0.0.1 ericomtxmacbookpro.local #127.0.0.1 mysqld The thing to remember is that 'localhost' or '127.0.0.1' is where the server believes the connection is coming from. The password you must be using in your client connection attempt must match the one used on the server for the mysql user 'root'@'localhost' or you will not authenticate. You also need to remember that if your .php page is on a different machine, it is not connecting to the same mysqld that you have running in your development machine (your personal environment). Your .PHP page is trying to connect to the one running on its host machine. That user 'root' may have an entirely different password. Your programs are connecting fine. If they didn't you would get a different message. They are failing to authenticate which means that the mysqld they are connecting to does not recognize the password you are using for the account you are trying to authenticate as. Regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
Hi no connections outside command line are being accepted . I have connected through command line , but not using eclipse for example ... it gets the same error from the web app my apache and pages are in the same computer that mysql I am not getting password issues.. otherwise I would not connect through command line Thks 2014/1/13 shawn l.green shawn.l.gr...@oracle.com Hello Érico, On 1/13/2014 1:49 PM, Érico wrote: Hi didn't work look ... isn't this something related to this : are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP ... since I am not able to stabilsh a connection even with using eclipse ... my /etc/hosts file : ## 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost #fe80::1%lo0localhost 127.0.0.1 ericomtxmacbookpro.local #127.0.0.1 mysqld The thing to remember is that 'localhost' or '127.0.0.1' is where the server believes the connection is coming from. The password you must be using in your client connection attempt must match the one used on the server for the mysql user 'root'@'localhost' or you will not authenticate. You also need to remember that if your .php page is on a different machine, it is not connecting to the same mysqld that you have running in your development machine (your personal environment). Your .PHP page is trying to connect to the one running on its host machine. That user 'root' may have an entirely different password. Your programs are connecting fine. If they didn't you would get a different message. They are failing to authenticate which means that the mysqld they are connecting to does not recognize the password you are using for the account you are trying to authenticate as. Regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
WTF - we are talking about *database connections* and *not* http-URL's the webserver is only the *messenger* Am 13.01.2014 18:54, schrieb Érico: using both urls I get the same error : http://localhost/mysql/index.php http://127.0.0.1/mysql/index.php in 127.0.0.1... after I submit the index.php ... it redirects to localhost too .. 2014/1/13 Reindl Harald h.rei...@thelounge.net Am 13.01.2014 18:28, schrieb Érico: ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.6.15 MySQL Community Server (GPL) but in the browser I get the error : Access denied for user 'root'@'localhost' (using password: YES) I can't find any information in error log and access log is there any command parameter that I should use when starting mysql so this doesn't happen? are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP signature.asc Description: OpenPGP digital signature
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
*your application* is connecting to mysql *your application* is using a hostname *your application* *may* use 127.0.0.1 *your application* should use localhost to *connect to the databse* *your application* can only use TCP *if there is* a *mysql user* with *that host* http://dev.mysql.com/doc/refman/5.5/en/can-not-connect-to-server.html Am 13.01.2014 20:38, schrieb Érico: no connections outside command line are being accepted . I have connected through command line , but not using eclipse for example ... it gets the same error from the web app my apache and pages are in the same computer that mysql I am not getting password issues.. otherwise I would not connect through command line 2014/1/13 shawn l.green shawn.l.gr...@oracle.com On 1/13/2014 1:49 PM, Érico wrote: Hi didn't work look ... isn't this something related to this : are you using localhost or 127.0.0.1 in the web-application root@localhost != root@127.0.0.1 = different users localhost: Unix-Socket 127.0.0.1: TCP ... since I am not able to stabilsh a connection even with using eclipse ... my /etc/hosts file : ## 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost #fe80::1%lo0localhost 127.0.0.1 ericomtxmacbookpro.local #127.0.0.1 mysqld The thing to remember is that 'localhost' or '127.0.0.1' is where the server believes the connection is coming from. The password you must be using in your client connection attempt must match the one used on the server for the mysql user 'root'@'localhost' or you will not authenticate. You also need to remember that if your .php page is on a different machine, it is not connecting to the same mysqld that you have running in your development machine (your personal environment). Your .PHP page is trying to connect to the one running on its host machine. That user 'root' may have an entirely different password. Your programs are connecting fine. If they didn't you would get a different message. They are failing to authenticate which means that the mysqld they are connecting to does not recognize the password you are using for the account you are trying to authenticate as. signature.asc Description: OpenPGP digital signature
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
Hello Reindl, On 1/13/2014 3:01 PM, Reindl Harald wrote: *your application* is connecting to mysql *your application* is using a hostname *your application* *may* use 127.0.0.1 *your application* should use localhost to *connect to the databse* *your application* can only use TCP *if there is* a *mysql user* with *that host* http://dev.mysql.com/doc/refman/5.5/en/can-not-connect-to-server.html ... snip ... The problem is, his other clients (his php page,his eclipse environment) is where he has problems making the connection from. His direct connection (using the mysql command line client) is having no problems. So, the issues are: * Why is a client connection, from the same host as the mysqld server, initiated from his PHP code failing to connect? * Why is a client connection, from the same host machine as his mysqld server, initiated from his Eclipse programming tool failing to connect? We are not saying he is using HTTP commands to log into his database. We have clarified that both his client and his server are on the same host. My last advice is that the password he is providing through his other clients must be incorrect. I even suggested that he may be validating his account on one instance but his tools are attempting to connect to a different instance. And whether I say mysql -u root -h localhost (via Unix socket) or I say mysql -u root -h 127.0.0.1 (via TCP socket) they may /both/ be reported as 'localhost' in the error message because of how the reverse DNS lookup happens during the user authentication process and the contents of his local /etc/hosts file. http://dev.mysql.com/doc/refman/5.6/en/connection-access.html Does that give you a better image of the current problem? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Access denied for user 'root'@'localhost' (using password:
YES) on mysql admin MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol=application/pgp-signature; boundary=wmcsLTXQx7E3jAVxUD1b39Xfw2SDoi7cu --wmcsLTXQx7E3jAVxUD1b39Xfw2SDoi7cu Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Please provide the output of the below query. Select user, host, password from mysql.user; Thanks Vikas Shukla Mail Sent from my Windows Phone From: Reindl Harald Sent: =E2=80=8E14-=E2=80=8E01-=E2=80=8E2014 01:38 To: mysql@lists.mysql.com Subject: Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin *your application* is connecting to mysql *your application* is using a hostname *your application* *may* use 127.0.0.1 *your application* should use localhost to *connect to the databse* *your application* can only use TCP *if there is* a *mysql user* with *that= host* http://dev.mysql.com/doc/refman/5.5/en/can-not-connect-to-server.html Am 13.01.2014 20:38, schrieb =C3=89rico: no connections outside command line are being accepted . I have connected through command line , but not using eclipse for example ... it gets the same error from the web app =20 my apache and pages are in the same computer that mysql =20 I am not getting password issues.. otherwise I would not connect through command line =20 2014/1/13 shawn l.green shawn.l.gr...@oracle.com =20 On 1/13/2014 1:49 PM, =C3=89rico wrote: Hi didn't work look ... isn't this something related to this : are you using localhost or 127.0.0.1 in the web-application root@localhost !=3D root@127.0.0.1 =3D different users localhost: Unix-Socket 127.0.0.1: TCP ... since I am not able to stabilsh a connection even with using eclipse ..= . my /etc/hosts file : ## 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost #fe80::1%lo0localhost 127.0.0.1 ericomtxmacbookpro.local #127.0.0.1 mysqld The thing to remember is that 'localhost' or '127.0.0.1' is where the server believes the connection is coming from. The password you must be using in your client connection attempt must match the one used on the server for the mysql user 'root'@'localhost' or you will not authenticat= e. You also need to remember that if your .php page is on a different machine, it is not connecting to the same mysqld that you have running i= n your development machine (your personal environment). Your .PHP page is trying to connect to the one running on its host machine. That user 'roo= t' may have an entirely different password. Your programs are connecting fine. If they didn't you would get a different message. They are failing to authenticate which means that the mysqld they are connecting to does not recognize the password you are us= ing for the account you are trying to authenticate as. --wmcsLTXQx7E3jAVxUD1b39Xfw2SDoi7cu-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin
Am 13.01.2014 21:47, schrieb shawn l.green: Hello Reindl, We are not saying he is using HTTP commands to log into his database we excludes obviously the OP or his overall understanding :-) Am 13.01.2014 18:54, schrieb Érico: using both urls I get the same error : http://localhost/mysql/index.php http://127.0.0.1/mysql/index.php in 127.0.0.1... after I submit the index.php ... it redirects to localhost too .. signature.asc Description: OpenPGP digital signature
User-defined variables not working ONLY on first query in chained replication
I've noticed a weird issue in our chained replication environment where when setting user-defined variables, the first time the variable is used in a session the value is NULL, but all uses thereafter work correctly. The environment is such: we have a master (master1), which has a slave which is also a master (master3), which itself has slaves (master3-slave1), i.e.: master1 - master3 - master3-slave1 I can replicate my issue with a very simple setup. I simply create a test table with one TEXT column, and I set a user-defined variable: CREATE TABLE test_table (id INT(10) PRIMARY KEY AUTO_INCREMENT, result TEXT) ENGINE=InnoDB; SET @mynewvariable = testvalue And then insert the variable into the test table: INSERT INTO test_table VALUES (NULL, @mynewvariable); The first time I run this insert, the value is correctly inserted in to master1 and its slave, master3 (as you'd expect). However, a NULL value is inserted into master3-slave1. However, if I run the INSERT a second time (just the insert, no re-declaration of the user-defined variable), the value is correctly inserted in to all three servers, so that the contents of test_table on the three servers looks as follows: master1 master3 master3-slave1 - - -- testvalue testvalue NULL testvalue testvalue testvalue I don't believe this is related to replication delay, because even if I leave a while between setting the variable and running the first INSERT, the result is always the same. The problem is agnostic of table format or how complex the table is, we can reproduce it exactly like this all of the time. Is this a known issue in MySQL with chained replication like this, or have I discovered a bug? Server version information: master1: Percona Server 5.5.28-29.1 master3: Percona Server 5.5.28-29.3 master3-slave1: Percona Server 5.5.20-55 -- Global Personals is a limited company registered in England and Wales. Registered number: 04880697. Registered office: Minton Place, Victoria Street, Windsor, Berkshire, SL4 1EG, United Kingdom.
Re: User-defined variables not working ONLY on first query in chained replication
Hi Matthew, On 16/07/2013 21:21, Matthew Ward wrote: I've noticed a weird issue in our chained replication environment where when setting user-defined variables, the first time the variable is used in a session the value is NULL, but all uses thereafter work correctly. snip The first time I run this insert, the value is correctly inserted in to master1 and its slave, master3 (as you'd expect). However, a NULL value is inserted into master3-slave1. However, if I run the INSERT a second time (just the insert, no re-declaration of the user-defined variable), the value is correctly inserted in to all three servers, so that the contents of test_table on the three servers looks as follows: snip Is this a known issue in MySQL with chained replication like this, or have I discovered a bug? Do you happen to have any table level replication filters? If so it sounds like you are affected by a bug that was fixed in 5.5.32 (https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-32.html): *Replication:*Running the server with both the|--log-slave-updates| http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_log-slave-updatesand|--replicate-wild-ignore-table| http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-wild-ignore-tableoptions in some cases caused updates to user variables not to be logged. (Note: at least replicate-ignore-table is also triggering that bug). Best regards, Jesper Krogh MySQL Support
Re: mysql Ver 14.12 Distrib 5.0.27 user privileges question
you need CREATE Privileges. http://dev.mysql.com/doc/refman/5.0/en/grant.html#grant-privileges On Wed, Feb 27, 2013 at 10:42 PM, Rajeev Prasad rp.ne...@yahoo.com wrote: Hello, currently on this version of MySQL a database has been built for me to use. and following privileges are given: I am not able to create a table on my own. what privileges I need to create and modify tables in this database? mysql Ver 14.12 Distrib 5.0.27, for sun-solaris2.9 (sparc) using EditLine wrapper mysql show grants; +---+ | Grants for myuserid@% | +---+ | GRANT USAGE ON *.* TO 'myuserid'@'%' IDENTIFIED BY PASSWORD '*4EF5..6' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'myuserid'@'%' | +---+ 2 rows in set (0.00 sec) mysql at % means I can do the operations from other hosts too? using ssh. thank you. -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: mysql Ver 14.12 Distrib 5.0.27 user privileges question
oh, osrry i was focused on the at % means I can do the operations from other hosts too? Am 27.02.2013 19:00, schrieb Stillman: OP's first question: I am not able to create a table on my own. what privileges I need to create and modify tables in this database? The answer to that question is that he/she needs CREATE to create tables and ALTER to alter them. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, February 27, 2013 12:55 PM To: mysql@lists.mysql.com Subject: Re: mysql Ver 14.12 Distrib 5.0.27 user privileges question says who? you MAY need CREATE privileges but it not uncommon have a defined scheme and not allow the user to create or drop tables, the user below is able to do anything for a common web-app to anser the OP's question % in mysql is the same as * for the bash so yes, % means any host Am 27.02.2013 18:38, schrieb Prabhat Kumar: you need CREATE Privileges. http://dev.mysql.com/doc/refman/5.0/en/grant.html#grant-privileges On Wed, Feb 27, 2013 at 10:42 PM, Rajeev Prasad rp.ne...@yahoo.com wrote: currently on this version of MySQL a database has been built for me to use. and following privileges are given: I am not able to create a table on my own. what privileges I need to create and modify tables in this database? mysql Ver 14.12 Distrib 5.0.27, for sun-solaris2.9 (sparc) using EditLine wrapper mysql show grants; +---+ | Grants for myuserid@% | +---+ | GRANT USAGE ON *.* TO 'myuserid'@'%' IDENTIFIED BY PASSWORD '*4EF5..6' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'myuserid'@'%' +---+ 2 rows in set (0.00 sec) at % means I can do the operations from other hosts too? using ssh signature.asc Description: OpenPGP digital signature
Re: mysql Ver 14.12 Distrib 5.0.27 user privileges question
says who? you MAY need CREATE privileges but it not uncommon have a defined scheme and not allow the user to create or drop tables, the user below is able to do anything for a common web-app to anser the OP's question % in mysql is the same as * for the bash so yes, % means any host Am 27.02.2013 18:38, schrieb Prabhat Kumar: you need CREATE Privileges. http://dev.mysql.com/doc/refman/5.0/en/grant.html#grant-privileges On Wed, Feb 27, 2013 at 10:42 PM, Rajeev Prasad rp.ne...@yahoo.com wrote: currently on this version of MySQL a database has been built for me to use. and following privileges are given: I am not able to create a table on my own. what privileges I need to create and modify tables in this database? mysql Ver 14.12 Distrib 5.0.27, for sun-solaris2.9 (sparc) using EditLine wrapper mysql show grants; +---+ | Grants for myuserid@% | +---+ | GRANT USAGE ON *.* TO 'myuserid'@'%' IDENTIFIED BY PASSWORD '*4EF5..6' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'myuserid'@'%' +---+ 2 rows in set (0.00 sec) at % means I can do the operations from other hosts too? using ssh signature.asc Description: OpenPGP digital signature
RE: mysql Ver 14.12 Distrib 5.0.27 user privileges question
OP's first question: I am not able to create a table on my own. what privileges I need to create and modify tables in this database? The answer to that question is that he/she needs CREATE to create tables and ALTER to alter them. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, February 27, 2013 12:55 PM To: mysql@lists.mysql.com Subject: Re: mysql Ver 14.12 Distrib 5.0.27 user privileges question says who? you MAY need CREATE privileges but it not uncommon have a defined scheme and not allow the user to create or drop tables, the user below is able to do anything for a common web-app to anser the OP's question % in mysql is the same as * for the bash so yes, % means any host Am 27.02.2013 18:38, schrieb Prabhat Kumar: you need CREATE Privileges. http://dev.mysql.com/doc/refman/5.0/en/grant.html#grant-privileges On Wed, Feb 27, 2013 at 10:42 PM, Rajeev Prasad rp.ne...@yahoo.com wrote: currently on this version of MySQL a database has been built for me to use. and following privileges are given: I am not able to create a table on my own. what privileges I need to create and modify tables in this database? mysql Ver 14.12 Distrib 5.0.27, for sun-solaris2.9 (sparc) using EditLine wrapper mysql show grants; +---+ | Grants for myuserid@% | +---+ | GRANT USAGE ON *.* TO 'myuserid'@'%' IDENTIFIED BY PASSWORD '*4EF5..6' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'myuserid'@'%' +---+ 2 rows in set (0.00 sec) at % means I can do the operations from other hosts too? using ssh Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Automatic reply: access denied to non-root@localhost null-string user in USER_PRIVILEGES
Hello Round, On 12/27/2012 5:34 AM, Round Square wrote: On 12/26/2012 01:25 PM, Igor Shevtsov wrote: You mysql.user table might be corrupted. If you have access to it as a root user, try check table mysql.user, and repair table mysql.user if table corruption was detected. Alternatively, shut down mysql server, cd /var/lib/mysql/mysql (to your $datadir/mysql directory) and run mysqlcheck -r mysql user I deleted the row with the empty user from mysql.user, then restarted the daemon, and all seems back to normal now. One lingering question is: why did mysql allow this to happen? Could this be considered a bug? After all, an inadvertent and seemingly harmless insertion leads to authentication failure for all users. Are there any other known similar gotchas? The fix for this one appears so trivial as to perhaps NOT call for a restore-from-backup. But there could be other similar glitches that might call for that? Thanks! No. Your tables were not corrupted. There is no need to restore from backup. In fact, it may have been an inappropriate restore that created this situation in the first place. What you had was either an intentional or unintentional failure in security. The system was performing appropriately for the accounts that you had configured. On 26/12/12 18:00, Round Square wrote: ... Poking around in puzzlement and comparing the current, broken state with the functioning state (from backup) I discovered that in the broken version there is this extra line in the information_schema.USER_PRIVILEGES table: | ''@'localhost' | NULL | USAGE | NO | (Note the null-string user prepended to @localhost) Again: the functional, non-broken state does NOT have this entry. Thus, my current theory is that this line is the culprit. Prior to the failure I had a surge of experimental installations, installing third-party software that created mysql tables, and can't clearly retrace everything I did, at this point, to pinpoint the installation that may have caused it. Be that as it may... (1) Is my theory correct? (2) If that line should not be there... (a) How do I remove it, properly? I don't have debian-sys-maint privileges to delete the line. (Or do I?) (b) Are there other tables, besides USER_PRIVILEGES, that would need to be updated/purged Yes. Your theory is correct. Why it had such an effect on your other logins is covered in the free, searchable, and publicly-available user manual (the only kind we have): http://dev.mysql.com/doc/refman/5.5/en/connection-access.html Removing a user account (a MySQL login) does not require debian-sys-maint privileges as this is not a Linux-level account. You need an appropriately-privileged MySQL account to do this maintenance. As you most likely discovered, we cannot directly modify the tables of the INFORMATION_SCHEMA database. Those tables are constructed dynamically (on demand) using information permanently stored in other places. In order to remove that account, you need to issue a DROP USER command or you need to be able to edit the `mysql`.`user` table. http://dev.mysql.com/doc/refman/5.5/en/drop-user.html http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Automatic reply: access denied to non-root@localhost null-string user in USER_PRIVILEGES
On 12/26/2012 01:25 PM, Igor Shevtsov wrote: You mysql.user table might be corrupted. If you have access to it as a root user, try check table mysql.user, and repair table mysql.user if table corruption was detected. Alternatively, shut down mysql server, cd /var/lib/mysql/mysql (to your $datadir/mysql directory) and run mysqlcheck -r mysql user I deleted the row with the empty user from mysql.user, then restarted the daemon, and all seems back to normal now. One lingering question is: why did mysql allow this to happen? Could this be considered a bug? After all, an inadvertent and seemingly harmless insertion leads to authentication failure for all users. Are there any other known similar gotchas? The fix for this one appears so trivial as to perhaps NOT call for a restore-from-backup. But there could be other similar glitches that might call for that? Thanks! On 26/12/12 18:00, Round Square wrote: Hi all: Suddenly, after a long, functioning run of the mysql server, all the non-root accounts went bad, with: Access denied for user 'non_root_user'@'localhost' (using password: YES) Authenticating with non_root_u...@server.ip.address still works ( the bind-address in my.cnf is mapped to server.ip.address ) Poking around in puzzlement and comparing the current, broken state with the functioning state (from backup) I discovered that in the broken version there is this extra line in the information_schema.USER_PRIVILEGES table: | ''@'localhost' | NULL | USAGE | NO | (Note the null-string user prepended to @localhost) Again: the functional, non-broken state does NOT have this entry. Thus, my current theory is that this line is the culprit. Prior to the failure I had a surge of experimental installations, installing third-party software that created mysql tables, and can't clearly retrace everything I did, at this point, to pinpoint the installation that may have caused it. Be that as it may... (1) Is my theory correct? (2) If that line should not be there... (a) How do I remove it, properly? I don't have debian-sys-maint privileges to delete the line. (Or do I?) (b) Are there other tables, besides USER_PRIVILEGES, that would need to be updated/purged My version: mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
access denied to non-root@localhost null-string user in USER_PRIVILEGES
Hi all: Suddenly, after a long, functioning run of the mysql server, all the non-root accounts went bad, with: Access denied for user 'non_root_user'@'localhost' (using password: YES) Authenticating with non_root_u...@server.ip.address still works ( the bind-address in my.cnf is mapped to server.ip.address ) Poking around in puzzlement and comparing the current, broken state with the functioning state (from backup) I discovered that in the broken version there is this extra line in the information_schema.USER_PRIVILEGES table: | ''@'localhost' | NULL | USAGE | NO | (Note the null-string user prepended to @localhost) Again: the functional, non-broken state does NOT have this entry. Thus, my current theory is that this line is the culprit. Prior to the failure I had a surge of experimental installations, installing third-party software that created mysql tables, and can't clearly retrace everything I did, at this point, to pinpoint the installation that may have caused it. Be that as it may... (1) Is my theory correct? (2) If that line should not be there... (a) How do I remove it, properly? I don't have debian-sys-maint privileges to delete the line. (Or do I?) (b) Are there other tables, besides USER_PRIVILEGES, that would need to be updated/purged My version: mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: access denied to non-root@localhost null-string user in USER_PRIVILEGES
You mysql.user table might be corrupted. If you have access to it as a root user, try check table mysql.user, and repair table mysql.user if table corruption was detected. Alternatively, shut down mysql server, cd /var/lib/mysql/mysql (to your $datadir/mysql directory) and run mysqlcheck -r mysql user On 26/12/12 18:00, Round Square wrote: Hi all: Suddenly, after a long, functioning run of the mysql server, all the non-root accounts went bad, with: Access denied for user 'non_root_user'@'localhost' (using password: YES) Authenticating with non_root_u...@server.ip.address still works ( the bind-address in my.cnf is mapped to server.ip.address ) Poking around in puzzlement and comparing the current, broken state with the functioning state (from backup) I discovered that in the broken version there is this extra line in the information_schema.USER_PRIVILEGES table: | ''@'localhost' | NULL | USAGE | NO | (Note the null-string user prepended to @localhost) Again: the functional, non-broken state does NOT have this entry. Thus, my current theory is that this line is the culprit. Prior to the failure I had a surge of experimental installations, installing third-party software that created mysql tables, and can't clearly retrace everything I did, at this point, to pinpoint the installation that may have caused it. Be that as it may... (1) Is my theory correct? (2) If that line should not be there... (a) How do I remove it, properly? I don't have debian-sys-maint privileges to delete the line. (Or do I?) (b) Are there other tables, besides USER_PRIVILEGES, that would need to be updated/purged My version: mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1
Re: user last activity and log in
Mellow greetings, Enhance your calm. Lets get our facts straight and not go off our rockers. MySQL 5.6 Enterprise edition will be able to do this natively ( https://blogs.oracle.com/MySQL/entry/new_in_mysql_enterprise_edition), but otherwise you cannot do it natively. This does not mean its impossible, though as there's a few easy work arounds. 1) Force all logins to use the PAM or AD authentication plugin -- if the authentication is success then log it in AD or PAM 2) use a init-connect to log logins but that doesn't work for users with super privileges as Keith mentioned below (thanks Keith for actually trying to help!) 3) Write your own plugin using the MySQL Plugin APIs 4) use the McAfee Audit Plugin for MySQL (Free: http://www.devshed.com/c/a/MySQL/McAfee-Releases-Audit-Plugin-for-MySQL-Users-86548/ ) Singer Wang (yes, I just watched Demolition Man) On Thu, Oct 4, 2012 at 11:29 PM, Keith Murphy bmur...@paragon-cs.comwrote: My friend Dave Holoboff wrote this up some time ago: http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html You know you people sound like children. Really unprofessional. Go ahead --- call me names. i left middle school almost 30 years ago. It won't bother me. Can we knock off the name calling and actually offer advice and possible solutions? I thought that was what this list was for. For those of us out in the field doing things ... This might be your ticket. It requires a restart of MySQL (which may or may not be acceptable) bit it's a fairly clean solution. Minimal load, easy to query for your last connection time and how often connections are made by a user. Again, requires a restart to enable (and disable) . Oh, and users with super privileges won't be logged. Thanks, Keith -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- * * (c) 850-637-3877
Re: user last activity and log in
My bad actually, the MySQL Enterprise Audit is available in MySQL 5.5 S On Fri, Oct 5, 2012 at 2:14 AM, Singer Wang w...@singerwang.com wrote: Mellow greetings, Enhance your calm. Lets get our facts straight and not go off our rockers. MySQL 5.6 Enterprise edition will be able to do this natively ( https://blogs.oracle.com/MySQL/entry/new_in_mysql_enterprise_edition), but otherwise you cannot do it natively. This does not mean its impossible, though as there's a few easy work arounds. 1) Force all logins to use the PAM or AD authentication plugin -- if the authentication is success then log it in AD or PAM 2) use a init-connect to log logins but that doesn't work for users with super privileges as Keith mentioned below (thanks Keith for actually trying to help!) 3) Write your own plugin using the MySQL Plugin APIs 4) use the McAfee Audit Plugin for MySQL (Free: http://www.devshed.com/c/a/MySQL/McAfee-Releases-Audit-Plugin-for-MySQL-Users-86548/ ) Singer Wang (yes, I just watched Demolition Man) On Thu, Oct 4, 2012 at 11:29 PM, Keith Murphy bmur...@paragon-cs.comwrote: My friend Dave Holoboff wrote this up some time ago: http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html You know you people sound like children. Really unprofessional. Go ahead --- call me names. i left middle school almost 30 years ago. It won't bother me. Can we knock off the name calling and actually offer advice and possible solutions? I thought that was what this list was for. For those of us out in the field doing things ... This might be your ticket. It requires a restart of MySQL (which may or may not be acceptable) bit it's a fairly clean solution. Minimal load, easy to query for your last connection time and how often connections are made by a user. Again, requires a restart to enable (and disable) . Oh, and users with super privileges won't be logged. Thanks, Keith -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- * * (c) 850-637-3877
Re: user last activity and log in
- Original Message - From: Singer Wang w...@singerwang.com 2) use a init-connect to log logins but that doesn't work for users with super privileges as Keith mentioned below (thanks Keith for actually trying to help!) That is indeed quite the nifty trick. Thanks, Keith :-) 3) Write your own plugin using the MySQL Plugin APIs 4) use the McAfee Audit Plugin for MySQL (Free: Hmm. Smells of McAfee, but looks interesting. Not sure what to think :-) -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
user last activity and log in
Hello, I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or anyt specific sql Aastha Gupta
Re: user last activity and log in
There is no such thing. Your application has to deal with such info. LS On Oct 4, 2012, at 11:28 AM, Aastha wrote: Hello, I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or anyt specific sql Aastha Gupta -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
Am 04.10.2012 17:28, schrieb Aastha: I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or any specific sql no - because this would mean a WRITE QUERY in the mysql-database for every connection - having a web-application with hundrets of calls per second would kill the performance this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer signature.asc Description: OpenPGP digital signature
Re: user last activity and log in
It is possible in MySQL 5.6 S On Thu, Oct 4, 2012 at 11:30 AM, List Man list@bluejeantime.com wrote: There is no such thing. Your application has to deal with such info. LS On Oct 4, 2012, at 11:28 AM, Aastha wrote: Hello, I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or anyt specific sql Aastha Gupta -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
- Original Message - From: Reindl Harald h.rei...@thelounge.net this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I notice no specification of what kind of users, so I'm assuming DB users. There *is* such a thing: you can find it in the general query log. Turning that on is a considerable performance overhead, though, and so is firmly discouraged on production systems. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
Yes, i meant DB users. On Thu, Oct 4, 2012 at 10:57 AM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Reindl Harald h.rei...@thelounge.net this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I notice no specification of what kind of users, so I'm assuming DB users. There *is* such a thing: you can find it in the general query log. Turning that on is a considerable performance overhead, though, and so is firmly discouraged on production systems. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
it does not matter what kind of users usually each application has it's own datanase and it's own user, the application makes the connection and can at this point log whatever you want using the general query log can only be a bad joke you will log EVERY query and not only logins again: it is not the job of a RDBMS to waste I/O and performance with such things - the application as example could refresh it only once per user-session the RDBMS would write blindly for each connection Am 04.10.2012 18:18, schrieb Aastha: Yes, i meant DB users. On Thu, Oct 4, 2012 at 10:57 AM, Johan De Meersman vegiv...@tuxera.be mailto:vegiv...@tuxera.be wrote: - Original Message - From: Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I notice no specification of what kind of users, so I'm assuming DB users. There *is* such a thing: you can find it in the general query log. Turning that on is a considerable performance overhead, though, and so is firmly discouraged on production systems. signature.asc Description: OpenPGP digital signature
Re: user last activity and log in
- Original Message - From: Reindl Harald rei...@thelounge.net it does not matter what kind of users I'm happy for you that you still have all the answers anyone could ever want, Harald. Regardless of having any background knowledge on the circumstance of the question, even. You truly are a gifted individual. using the general query log can only be a bad joke you will log EVERY query and not only log-ins Yes, which is why I specified explicitly that it is very much discouraged for production use. However, it can be useful at times. I recently turned it on to investigate sudden, unpredictable and above all annoyingly brief peaks in the number of connections, and I needed to know what APPLICATION INSTANCE was responsible, not which particular user - as well as have a good view of what the offending sessions did. A tcpdump would have been an option, but given that wireshark still isn't too good at decoding MySQL traffic I still opted for the full query log. There was some more tomfoolery involved, but after almost a week of logging we successfully identified the culprit. Now you may do things differently, and you may also reach a satisfactory solution; but I am absolutely sick and tired of hearing how your way is the only valid way, casually implying that the rest of the world are all bloody idiots that should just shut up and listen while you tell them every ridiculous way in which they are wrong and inferior. PLEASE, for your own sake - not to mention the nerves of the people around you - learn to accept that there are a lot of different ways to do things, and that sometimes people pick their optimal solution on quite different criteria than the ones you use. That does not necessarily make them wrong, merely different from you. As the Perl mantra goes, There's More Than One Way To Do It. /Johan -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
Am 04.10.2012 23:12, schrieb Johan De Meersman: - Original Message - From: Reindl Harald rei...@thelounge.net it does not matter what kind of users I'm happy for you that you still have all the answers anyone could ever want, Harald. not all but the one to the topic IT IS IMPOSSIBLE MYSQL CAN NOT DO WHAT THE OP WANT Regardless of having any background knowledge on the circumstance of the question, even. mysql can not an dwill not log user-logins You truly are a gifted individual. your opinion, but the answer to the question of the OP is simply NO you can't using the general query log can only be a bad joke you will log EVERY query and not only log-ins Yes, which is why I specified explicitly that it is very much discouraged for production use. it is NOT the answer to the question damned it doe NOT log the last login of a mysql user in a USEABLE form Now you may do things differently, and you may also reach a satisfactory solution; but I am absolutely sick and tired of hearing how your way is the only valid way i don't give a damn about what you are tired of the answer to I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data is SIMPLY NO and not a useless full query log casually implying that the rest of the world are all bloody idiots maybe in this case your conclusion i liked to call you a bloody idiot for bringing full query log as answer comes because you realized how useless the idea is that should just shut up and listen while you tell them every ridiculous way in which they are wrong and inferior maybe you should shut up yourself as long you are hypersensible learn to accept that there are a lot of different ways to do things again: your solution full query log is not one if you can't face the truth this is your problem and that sometimes people pick their optimal solution on quite different criteria than the ones you use. if someone does not like answers he should not ask questions There's More Than One Way To Do It. full query og is none of them if it takes SIX hours for your reply in the way you did here my conclusion is that you recently came home drunken and should go to bed signature.asc Description: OpenPGP digital signature
Re: user last activity and log in
Hi, 2012/10/4 Reindl Harald h.rei...@thelounge.net Am 04.10.2012 17:28, schrieb Aastha: I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or any specific sql no - because this would mean a WRITE QUERY in the mysql-database for every connection - having a web-application with hundrets of calls per second would kill the performance No because MySQL does not have this facility. (5.6) Saying that a feature is not present because the hypothetical implementation would impact performance doesn't make much sense in my opinion. this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I can suggest a reading here: http://www.amazon.com/Implementing-Database-Security-Auditing-Examples/dp/183342 Regards -- Claudio
RE: user last activity and log in
In looking at a couple hundred machine, I see that Connections / Uptime has a median of about 0.5 (one connection every 2 seconds) and a max of about 140. 140 writes to some audit table _might_ have a small impact on the system. -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, October 04, 2012 3:51 PM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: user last activity and log in Hi, 2012/10/4 Reindl Harald h.rei...@thelounge.net Am 04.10.2012 17:28, schrieb Aastha: I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or any specific sql no - because this would mean a WRITE QUERY in the mysql-database for every connection - having a web-application with hundrets of calls per second would kill the performance No because MySQL does not have this facility. (5.6) Saying that a feature is not present because the hypothetical implementation would impact performance doesn't make much sense in my opinion. this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I can suggest a reading here: http://www.amazon.com/Implementing-Database-Security-Auditing- Examples/dp/183342 Regards -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user last activity and log in
beside the fact that msql CAN NOT do this at all the median is not really releavt in the median you see also night hours with zero load on a typical webserver with load you have much more * a cms system * many page requests per second * no you can not use persistent connections if you have let's say 100 databases and 100 domains with 500 prefork pcroesses because these would mean in the worst case 5 connections * enable query log on machines with some hundret queriers per second would be a self DOS and fill your disks Am 05.10.2012 01:26, schrieb Rick James: In looking at a couple hundred machine, I see that Connections / Uptime has a median of about 0.5 (one connection every 2 seconds) and a max of about 140. 140 writes to some audit table _might_ have a small impact on the system. -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, October 04, 2012 3:51 PM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: user last activity and log in Hi, 2012/10/4 Reindl Harald h.rei...@thelounge.net Am 04.10.2012 17:28, schrieb Aastha: I want to find the last time the given list of users logged in. Is there any mysql table from where i can retrieve the data or any specific sql no - because this would mean a WRITE QUERY in the mysql-database for every connection - having a web-application with hundrets of calls per second would kill the performance No because MySQL does not have this facility. (5.6) Saying that a feature is not present because the hypothetical implementation would impact performance doesn't make much sense in my opinion. this makes pretty no sense and is NOT the job of a RDBMS implement it in your application / db-abstraction-layer I can suggest a reading here: http://www.amazon.com/Implementing-Database-Security-Auditing- Examples/dp/183342 signature.asc Description: OpenPGP digital signature
Re: user last activity and log in
My friend Dave Holoboff wrote this up some time ago: http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html You know you people sound like children. Really unprofessional. Go ahead --- call me names. i left middle school almost 30 years ago. It won't bother me. Can we knock off the name calling and actually offer advice and possible solutions? I thought that was what this list was for. For those of us out in the field doing things ... This might be your ticket. It requires a restart of MySQL (which may or may not be acceptable) bit it's a fairly clean solution. Minimal load, easy to query for your last connection time and how often connections are made by a user. Again, requires a restart to enable (and disable) . Oh, and users with super privileges won't be logged. Thanks, Keith -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- * * (c) 850-637-3877
Re: user last activity and log in
One small correction. Init-connect doesn't require a restart of MySQL. I was thinking of init-file. So that's even better. On Thursday, October 4, 2012, Keith Murphy wrote: My friend Dave Holoboff wrote this up some time ago: http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html You know you people sound like children. Really unprofessional. Go ahead --- call me names. i left middle school almost 30 years ago. It won't bother me. Can we knock off the name calling and actually offer advice and possible solutions? I thought that was what this list was for. For those of us out in the field doing things ... This might be your ticket. It requires a restart of MySQL (which may or may not be acceptable) bit it's a fairly clean solution. Minimal load, easy to query for your last connection time and how often connections are made by a user. Again, requires a restart to enable (and disable) . Oh, and users with super privileges won't be logged. Thanks, Keith -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- * * (c) 850-637-3877 -- Keith Murphy Senior MySQL DBA Principal Trainer Paragon Consulting Services http://www.paragon-cs.com 850-637-3877
secure user name for MySQL account?
I am considering using cryptic username for accessing and working on a database on my MySQL installation. can anyone with experience provide some suggestion pl? in documentation, i only see that it can be 16 char long. how complex it can be? any side effects/bugs of username complexity? can I use special characters in name? please advice. ty.
Re: secure user name for MySQL account?
- Original Message - From: Rajeev Prasad rp.ne...@yahoo.com I am considering using cryptic username for accessing and working on a database on my MySQL installation. can anyone with experience provide some suggestion pl? Why would you make your life hard by using cryptic usernames? Have the username (and db name) reflect the project it's used in or the user it belongs to, make sure you have a properly complex password, allow only from appropriate hosts and set up restrictive firewall rules. If you know how many simultaneous connections the application can make, you can also restrict that. in documentation, i only see that it can be 16 char long. how complex it can be? any side effects/bugs of username complexity? can I use special characters in name? Avoid reserved names, also best to avoid question marks and percent signs as those are wildcards in some situations - even though (I think) they're not actually interpreted unless the user name is a single percent sign. Apart from that, nothing much except the 16-character limit, I think. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
user not able to login from localhost
i have given select/insert/update/delete rights to a user on a specific database, from localhost. when i try to login to mysql using the uid, i get error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) what other privilege do i need to give this user?
RE: user not able to login from localhost
That says that your password in not correct. You have not gotten to specific privileges. Did you previously do GRANT ... TO myuser@localhost IDENTFIED BY '...'; ? If you can get in via root, do SHOW GRANTS FOR myuser@localhost; SELECT * FROM mysql.user WHERE user = 'myuser'; -Original Message- From: Rajeev Prasad [mailto:rp.ne...@yahoo.com] Sent: Monday, September 24, 2012 10:56 AM To: mysql list Subject: user not able to login from localhost i have given select/insert/update/delete rights to a user on a specific database, from localhost. when i try to login to mysql using the uid, i get error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) what other privilege do i need to give this user? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user not able to login from localhost
On Mon, Sep 24, 2012 at 1:55 PM, Rajeev Prasad rp.ne...@yahoo.com wrote:
i have given select/insert/update/delete rights to a user on a specific
database, from localhost. when i try to login to mysql using the uid, i get
error:
ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using
password: YES)
what other privilege do i need to give this user?
The permission set you describe should be complete, so look more
closely at what was actually granted and how you are trying to log in.
The obvious questions:
How did you create and grant privileges to that user? Did you use:
grant select,insert,update,delete on mydb.* to mysql@localhost
identified by password('password');
or what variant exactly?
When you are trying to log in, are you doing that explicitly from the
same server or are you accessing remotely?
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: user not able to login from localhost
Also ensure you issue: FLUSH PRIVILEGES; after making any changes to permissions.. On 24 September 2012 20:09, Rick James rja...@yahoo-inc.com wrote: That says that your password in not correct. You have not gotten to specific privileges. Did you previously do GRANT ... TO myuser@localhost IDENTFIED BY '...'; ? If you can get in via root, do SHOW GRANTS FOR myuser@localhost; SELECT * FROM mysql.user WHERE user = 'myuser'; -Original Message- From: Rajeev Prasad [mailto:rp.ne...@yahoo.com] Sent: Monday, September 24, 2012 10:56 AM To: mysql list Subject: user not able to login from localhost i have given select/insert/update/delete rights to a user on a specific database, from localhost. when i try to login to mysql using the uid, i get error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) what other privilege do i need to give this user? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: secure user name for MySQL account?
On this note, one thing that really bugs me about MySQL passwords is the inability to use special characters. In the SQL Server world, I let users choose their own passwords, but obeying these rules: It cannot be a dictionary word or sequence of words. It must contain at least one numeric digit. It must contain a mix of upper and lower case. It must contain at least one special character. That combination makes a password very difficult to crack. I don't know why MySQL falls so short in this respect. Arthur www.artfulsoftware.com
Re: secure user name for MySQL account?
Hello Arthur, On 9/24/2012 4:25 PM, Arthur Fuller wrote: On this note, one thing that really bugs me about MySQL passwords is the inability to use special characters. In the SQL Server world, I let users choose their own passwords, but obeying these rules: It cannot be a dictionary word or sequence of words. It must contain at least one numeric digit. It must contain a mix of upper and lower case. It must contain at least one special character. That combination makes a password very difficult to crack. I don't know why MySQL falls so short in this respect. MySQL continues to improve in this respect. While it's true that our last big security change was the enhanced password hash function introduced in 4.1 we have not been completely insensitive to the needs of our customers. For example, check out the list of account and security improvements arriving in MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html In particular, the password complexity threshold can be configured using the new Password Validation plugin: http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: user not able to login from localhost
this is what i see: | GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*829E20779862ACF47E2B4D9B7C6B1B1B1ADF7925' | | GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON `mydatabase`.* TO 'myuser'@'localhost' | +-+ I am using phpmyadmin to manage mysql, including user privileges. From: Ben Mildren ben.mild...@gmail.com To: Rajeev Prasad rp.ne...@yahoo.com Cc: mysql list mysql@lists.mysql.com Sent: Monday, September 24, 2012 2:49 PM Subject: Re: user not able to login from localhost Also ensure you issue: FLUSH PRIVILEGES; after making any changes to permissions.. On 24 September 2012 20:09, Rick James rja...@yahoo-inc.com wrote: That says that your password in not correct. You have not gotten to specific privileges. Did you previously do GRANT ... TO myuser@localhost IDENTFIED BY '...'; ? If you can get in via root, do SHOW GRANTS FOR myuser@localhost; SELECT * FROM mysql.user WHERE user = 'myuser'; -Original Message- From: Rajeev Prasad [mailto:rp.ne...@yahoo.com] Sent: Monday, September 24, 2012 10:56 AM To: mysql list Subject: user not able to login from localhost i have given select/insert/update/delete rights to a user on a specific database, from localhost. when i try to login to mysql using the uid, i get error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) what other privilege do i need to give this user? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: user not able to login from localhost
mysql SELECT * FROM mysql.user WHERE user ='myuser'; +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | localhost | myuser| *829E20779862ACF47E2B4D9B7C6B1B1B1ADF7925 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ 1 row in set (0.02 sec) mysql I am looging in from the same server. From: Rick James rja...@yahoo-inc.com To: Rajeev Prasad rp.ne...@yahoo.com; mysql list mysql@lists.mysql.com Sent: Monday, September 24, 2012 2:09 PM Subject: RE: user not able to login from localhost That says that your password in not correct. You have not gotten to specific privileges. Did you previously do GRANT ... TO myuser@localhost IDENTFIED BY '...'; ? If you can get in via root, do SHOW GRANTS FOR myuser@localhost; SELECT * FROM mysql.user WHERE user = 'myuser'; -Original Message- From: Rajeev Prasad [mailto:rp.ne...@yahoo.com] Sent: Monday, September 24, 2012 10:56 AM To: mysql list Subject: user not able to login from localhost i have given select/insert/update/delete rights to a user on a specific database, from localhost. when i try to login to mysql using the uid, i get error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) what other privilege do i need to give this user? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
(resolved) Re: user not able to login from localhost
friends, i figure that i had to give password in quotes. i was then able to login. thank you all for your help and time. From: Rajeev Prasad rp.ne...@yahoo.com To: Rick James rja...@yahoo-inc.com; mysql list mysql@lists.mysql.com Sent: Monday, September 24, 2012 4:11 PM Subject: Re: user not able to login from localhost mysql SELECT * FROM mysql.user WHERE user ='myuser'; +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | localhost | myuser| *829E20779862ACF47E2B4D9B7C6B1B1B1ADF7925 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | +---+--+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ 1 row in set (0.02 sec) mysql I am looging in from the same server. From: Rick James rja...@yahoo-inc.com To: Rajeev Prasad rp.ne...@yahoo.com; mysql list mysql@lists.mysql.com Sent: Monday, September 24, 2012 2:09 PM Subject: RE: user not able to login from localhost That says that your password in not correct. You have not gotten to specific privileges. Did you previously do GRANT ... TO myuser@localhost IDENTFIED BY '...'; ? If you can get in via root, do SHOW GRANTS FOR myuser@localhost; SELECT * FROM mysql.user WHERE user = 'myuser'; -Original Message- From: Rajeev Prasad [mailto:rp.ne...@yahoo.com] Sent: Monday, September 24, 2012 10:56 AM To: mysql list Subject: user not able to login from localhost i have given select/insert/update/delete rights to a user on a specific database, from localhost. when i try to login to mysql using the uid, i get error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) what other privilege do i need to give this user? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: secure user name for MySQL account?
Thanks for the update, Shawn. I'll check it out right now. On Mon, Sep 24, 2012 at 4:40 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Hello Arthur, On 9/24/2012 4:25 PM, Arthur Fuller wrote: On this note, one thing that really bugs me about MySQL passwords is the inability to use special characters. In the SQL Server world, I let users choose their own passwords, but obeying these rules: It cannot be a dictionary word or sequence of words. It must contain at least one numeric digit. It must contain a mix of upper and lower case. It must contain at least one special character. That combination makes a password very difficult to crack. I don't know why MySQL falls so short in this respect. MySQL continues to improve in this respect. While it's true that our last big security change was the enhanced password hash function introduced in 4.1 we have not been completely insensitive to the needs of our customers. For example, check out the list of account and security improvements arriving in MySQL 5.6 http://dev.mysql.com/doc/**refman/5.6/en/mysql-nutshell.**htmlhttp://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html In particular, the password complexity threshold can be configured using the new Password Validation plugin: http://dev.mysql.com/doc/**refman/5.6/en/validate-**password-plugin.htmlhttp://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html Yours, -- Shawn Green
Default Values for Max User and Max Connections.
Hello All, Question. In mysql 5.5 what are the defaults for these values: max_connections = ? (is this set to 151 in 5,5) max_user_connections = ?(is this set to unlimited unless you provide the exact number)? Thank you so much. Wayne Leutwyler, RHCT Open Source + Open Minds = Open Solutions Courage is being scared to death-but saddling up anyway -John Wayne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Default Values for Max User and Max Connections.
After installing, do SHOW VARIABLES LIKE 'max%'; If you don't like the values, then ad a line to my.cnf (my.ini) and restart. For max_user_connections, I think 0 means unlimited. -Original Message- From: Wayne Leutwyler [mailto:wleut...@columbus.rr.com] Sent: Friday, September 21, 2012 7:04 AM To: mysql@lists.mysql.com Subject: Default Values for Max User and Max Connections. Hello All, Question. In mysql 5.5 what are the defaults for these values: max_connections = ? (is this set to 151 in 5,5) max_user_connections = ? (is this set to unlimited unless you provide the exact number)? Thank you so much. Wayne Leutwyler, RHCT Open Source + Open Minds = Open Solutions Courage is being scared to death-but saddling up anyway -John Wayne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
user accounts using Active Directory
Hello, I want to create users and give access to different schema within the database. I also want to give them selective privileges to access the table in different schema. One way is to create users in MySQL and then grant with privileges. Is there any way to give them access to MySQL database and the tables within the schema using active directory. Also. is there a way to create group of privileges and assign users to the group required. Thanks, Aastha Gupta
Re: user accounts using Active Directory
Hi Aastha, I think that you can develop a Plugin to do it. Recently, I post a plugin that allow authenticate users through LDAP Server ( http://nafiux.com/blog/2012/08/16/mysql-ldap-authentication-plugin-clear-password-client-plugin/ ). More info about MySQL Authentication Plugins: http://dev.mysql.com/doc/refman/5.5/en/authentication-plugins.html You can build your own plugin with your needs. Best regards. On Wed, Aug 22, 2012 at 9:13 AM, Aastha aast...@gmail.com wrote: Hello, I want to create users and give access to different schema within the database. I also want to give them selective privileges to access the table in different schema. One way is to create users in MySQL and then grant with privileges. Is there any way to give them access to MySQL database and the tables within the schema using active directory. Also. is there a way to create group of privileges and assign users to the group required. Thanks, Aastha Gupta -- Ignacio Ocampo Millán
Re: Fwd: why must user variable in EXECUTE USING clause ?
it seems none of you hava answered my question.
On Tue, Mar 27, 2012 at 7:01 AM, Walter Tross wal...@waltertross.comwrote:
Harald,
keep in mind that
a) this mailing list is badly set up: unless you do a reply all, the
reply goes only to the poster (happened to me too)
b) Microsoft products have made inline (bottom) answering almost
impossible. I, like many others, am forced to used Outlook at the office,
and therefore I had to give up, and now I stick to top-posting :-(
ciao
W.
P.S. the only thing I have not given up yet is not using empty lines to
avoid the feature (?) of Outlook that glues lines together. This
feature can be disabled/reverted (although this is not widely known)
At 21.38 26/03/2012 +0800, Cifer Lee wrote:
-- Forwarded message --
From: Reindl Harald mailto:h.rei...@thelounge.net
h.rei...@thelounge.net
Date: Mon, Mar 26, 2012 at 9:17 PM
Subject: Re: why must user variable in EXECUTE USING clause ?
To: Cifer Lee mailto:mantia...@gmail.commantia...@gmail.com
would you PLEASE send to the list instead off-list
and put your answer BELOW instead to-posting?
is it really so difficult to use mailing-lists?
Am 26.03.2012 14:34, schrieb Cifer Lee:
thanks for reply
and .sorry for my poor English ...
I wrote a procedure program which contains prepare clause , please see
below
CREATE PROCEDURE `iter_table`(IN type int)
BEGIN
DECLARE tablename VARCHAR(24) DEFAULT '';
DECLARE shop_id int DEFAULT 0;
DECLARE count int DEFAULT 0;
DECLARE row_count int DEFAULT 0;
DECLARE x varchar(24);
DECLARE cur1 CURSOR FOR SELECT `id` FROM shop;
SELECT COUNT(*) INTO row_count FROM shop;
OPEN cur1;
REPEAT
FETCH cur1 INTO shop_id;
SET @shop_id := shop_id;
SET @type := type;
SET tablename= CONCAT('shop',@shop_id);
SET @sqlstr = CONCAT('SELECT
`id`,`name`,`repertory`,`photo`,`type`,`price`,@shop_id AS shop_id FROM
',tablename,' WHERE `type`=?;');
PREPARE stat FROM @sqlstr;
EXECUTE stat USING type; - Must be EXECUTE stat USING
@type ;
SET count=count+1;
UNTIL count = row_count
END REPEAT;
CLOSE cur1;
END
I got an error at the red line when I creating this procedure
and the solution is replace the 'type' with '@type'
I found here http://dev.mysql.com/doc/refman/5.5/en/execute.html
http://dev.mysql.com/doc/refman/5.5/en/execute.html
and knows that
/you must supply a |USING| clause that lists user variables containing
the values to be bound to the parameters.
Parameter values can be supplied only by user variables/,
but, I don't know why . why must parameter be user variables ? why
can't be local variables , as declared in
DECLARE clause..
thanks!
On Mon, Mar 26, 2012 at 8:15 PM, Reindl Harald mailto:
h.rei...@thelounge.neth.rei...@thelounge.net mailto:
h.rei...@thelounge.net wrote:
Am 26.03.2012 14:13, schrieb Cifer Lee:
why can't be local variable which declared in DECLARE clause?
what are you speaking about?
keep in mind that we can not read your thoughts
Content-Type: application/pgp-signature; name=signature.asc
Content-Disposition: attachment; filename=signature.asc
X-Attachment-Id: 2caae85bfd1f082d_0.1
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: why must user variable in EXECUTE USING clause ?
- Original Message - From: Cifer Lee mantia...@gmail.com it seems none of you hava answered my question. Yes, that happens :-) I'm slightly confused as to the difference between user variables and local variables, though. Are you saying you couldn't just declare @myvar and use it in your code? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: why must user variable in EXECUTE USING clause ?
Am 26.03.2012 14:13, schrieb Cifer Lee: why can't be local variable which declared in DECLARE clause? what are you speaking about? keep in mind that we can not read your thoughts signature.asc Description: OpenPGP digital signature
Fwd: why must user variable in EXECUTE USING clause ?
-- Forwarded message --
From: Reindl Harald h.rei...@thelounge.net
Date: Mon, Mar 26, 2012 at 9:17 PM
Subject: Re: why must user variable in EXECUTE USING clause ?
To: Cifer Lee mantia...@gmail.com
would you PLEASE send to the list instead off-list
and put your answer BELOW instead to-posting?
is it really so difficult to use mailing-lists?
Am 26.03.2012 14:34, schrieb Cifer Lee:
thanks for reply
and .sorry for my poor English ...
I wrote a procedure program which contains prepare clause , please see
below
CREATE PROCEDURE `iter_table`(IN type int)
BEGIN
DECLARE tablename VARCHAR(24) DEFAULT '';
DECLARE shop_id int DEFAULT 0;
DECLARE count int DEFAULT 0;
DECLARE row_count int DEFAULT 0;
DECLARE x varchar(24);
DECLARE cur1 CURSOR FOR SELECT `id` FROM shop;
SELECT COUNT(*) INTO row_count FROM shop;
OPEN cur1;
REPEAT
FETCH cur1 INTO shop_id;
SET @shop_id := shop_id;
SET @type := type;
SET tablename= CONCAT('shop',@shop_id);
SET @sqlstr = CONCAT('SELECT
`id`,`name`,`repertory`,`photo`,`type`,`price`,@shop_id AS shop_id FROM
',tablename,' WHERE `type`=?;');
PREPARE stat FROM @sqlstr;
EXECUTE stat USING type; - Must be EXECUTE stat USING
@type ;
SET count=count+1;
UNTIL count = row_count
END REPEAT;
CLOSE cur1;
END
I got an error at the red line when I creating this procedure
and the solution is replace the 'type' with '@type'
I found here http://dev.mysql.com/doc/refman/5.5/en/execute.html
and knows that
/you must supply a |USING| clause that lists user variables containing
the values to be bound to the parameters.
Parameter values can be supplied only by user variables/,
but, I don't know why . why must parameter be user variables ? why
can't be local variables , as declared in
DECLARE clause..
thanks!
On Mon, Mar 26, 2012 at 8:15 PM, Reindl Harald h.rei...@thelounge.netmailto:
h.rei...@thelounge.net wrote:
Am 26.03.2012 14:13, schrieb Cifer Lee:
why can't be local variable which declared in DECLARE clause?
what are you speaking about?
keep in mind that we can not read your thoughts
signature.asc
Description: PGP signature
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Fwd: why must user variable in EXECUTE USING clause ?
Harald,
keep in mind that
a) this mailing list is badly set up: unless you do a reply all, the reply
goes only to the poster (happened to me too)
b) Microsoft products have made inline (bottom) answering almost impossible. I,
like many others, am forced to used Outlook at the office, and therefore I had
to give up, and now I stick to top-posting :-(
ciao
W.
P.S. the only thing I have not given up yet is not using empty lines to avoid
the feature (?) of Outlook that glues lines together. This feature can be
disabled/reverted (although this is not widely known)
At 21.38 26/03/2012 +0800, Cifer Lee wrote:
-- Forwarded message --
From: Reindl Harald mailto:h.rei...@thelounge.neth.rei...@thelounge.net
Date: Mon, Mar 26, 2012 at 9:17 PM
Subject: Re: why must user variable in EXECUTE USING clause ?
To: Cifer Lee mailto:mantia...@gmail.commantia...@gmail.com
would you PLEASE send to the list instead off-list
and put your answer BELOW instead to-posting?
is it really so difficult to use mailing-lists?
Am 26.03.2012 14:34, schrieb Cifer Lee:
thanks for reply
and .sorry for my poor English ...
I wrote a procedure program which contains prepare clause , please see below
CREATE PROCEDURE `iter_table`(IN type int)
BEGIN
DECLARE tablename VARCHAR(24) DEFAULT '';
DECLARE shop_id int DEFAULT 0;
DECLARE count int DEFAULT 0;
DECLARE row_count int DEFAULT 0;
DECLARE x varchar(24);
DECLARE cur1 CURSOR FOR SELECT `id` FROM shop;
SELECT COUNT(*) INTO row_count FROM shop;
OPEN cur1;
REPEAT
FETCH cur1 INTO shop_id;
SET @shop_id := shop_id;
SET @type := type;
SET tablename= CONCAT('shop',@shop_id);
SET @sqlstr = CONCAT('SELECT
`id`,`name`,`repertory`,`photo`,`type`,`price`,@shop_id AS shop_id FROM
',tablename,' WHERE `type`=?;');
PREPARE stat FROM @sqlstr;
EXECUTE stat USING type; - Must be EXECUTE stat USING @type ;
SET count=count+1;
UNTIL count = row_count
END REPEAT;
CLOSE cur1;
END
I got an error at the red line when I creating this procedure
and the solution is replace the 'type' with '@type'
I found here
http://dev.mysql.com/doc/refman/5.5/en/execute.htmlhttp://dev.mysql.com/doc/refman/5.5/en/execute.html
and knows that
/you must supply a |USING| clause that lists user variables containing the
values to be bound to the parameters.
Parameter values can be supplied only by user variables/,
but, I don't know why . why must parameter be user variables ? why can't
be local variables , as declared in
DECLARE clause..
thanks!
On Mon, Mar 26, 2012 at 8:15 PM, Reindl Harald
mailto:h.rei...@thelounge.neth.rei...@thelounge.net
mailto:h.rei...@thelounge.net wrote:
Am 26.03.2012 14:13, schrieb Cifer Lee:
why can't be local variable which declared in DECLARE clause?
what are you speaking about?
keep in mind that we can not read your thoughts
Content-Type: application/pgp-signature; name=signature.asc
Content-Disposition: attachment; filename=signature.asc
X-Attachment-Id: 2caae85bfd1f082d_0.1
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
