User Admin- MySQL Administrator- SELECT command denied to user

User Admin- MySQL Administrator- SELECT command denied to user

Post by DanWeave » Sat, 22 Mar 2008 21:10:30


Using Windows Vista, running MySQL Administrator (as administrator)
and connecting successfully to MySQL db on a shared remote host (IP
adress in below changed) I cannot modify the user privileges eg Add
New User.
Cant find any straightforward answer though searches through this and
similar newsgroups show lots of similar questions the solutions to
which are above my head- eg - logging on as 'root'- with what
password? The username I am logged in as is the one given to me by my
host and allows me to change tables etc...

SELECT command denied to user 'dan'@'22.112.33.44 for table
'user' (1142)
 
 
 

User Admin- MySQL Administrator- SELECT command denied to user

Post by J.O. Ah » Sat, 22 Mar 2008 21:26:24


IMHO it's a quite straight forward error message, that the user dan, when he
connects from the host 22.112.33.44 don't have the accesses to use SELECT for
the table user.

1. You aren't logged into the MySQL server as administrator, but as dan, who
won't have the same rights as the root user who connects from the localhost,
as the MySQL is on a remote machine, you won't be able to login from localhost
as long as you use your machine directly, you have to connect to the remote
machine, say using putty, then invoke the mysql client and login as root.

If you don't have a remote login to the server running the mysql, then you
can't do this, and if you would have, you still will need to have the root
password, but as the mysql server is a shared one, I doubt you have any root
password.

If you are lucky, you may have the privilege with dan to create new users for
the database you have on the shared mysql server, you log in to the database
as you normally do and use the GRANT command to create the new user. If you
lack the privilege to do this, then contact the company that hosts the mysql
server and ask them to create the user for you.

--

//Aho

 
 
 

User Admin- MySQL Administrator- SELECT command denied to user

Post by DanWeave » Sat, 22 Mar 2008 23:12:32

Thanks JOAho...
I just logged into my account at Fasthosts (who host the MySQL db)...
It says that...
"The DBO (database owner) has full administrator access to the
database and should be used to set permissions for other users."
One can add a user using the GUI at Fasthosts and then: "configure
their permissions within the database by logging in as your database
owner user." - I have added another user which I cannot 'see' in SQL
Administrator.
In MySQL Administrator I have the same problems as described in first
post ie the user dan supposedly has all permissions but cant see the
user list or modify anything related to permissions. I prefer not to
have to delve into command line GRANTing etc (it seems that that is
the kind of command the SQL Administrator GUI assembles no?)... I
cant see why my username and pass (which allow me to create and change
tables etc) dont function for user admin through SQL Administrator.
 
 
 

User Admin- MySQL Administrator- SELECT command denied to user

Post by Jerry Stuc » Sun, 23 Mar 2008 00:28:44


MySQL keeps track of userids and IP addresses. For instance, you can
have one set of permissions for someone accessing the database directly
from the server, and a completely different set of permissions (or deny
any access) when accessing the database from a remote system. It can
even be carried down to a specific IP address.

It could be that your userid has those permissions when accessing the
database from the server - but not when accessing from any other machine.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
XXXX@XXXXX.COM
==================
 
 
 

User Admin- MySQL Administrator- SELECT command denied to user

Post by DanWeave » Wed, 26 Mar 2008 21:26:35

Hi Jerry,

Thanks for that and sorry for delay in response - what I dont get is
that I can access the db on the server from my remote machine (this
one) using SQL Administrator with full permissions- I can change
tables etc- without logging on through the hosts www website
(fasthost)- yet I cannot seem to add users. The host must expect this
to be accomplished via an external program as its new user setup on
the website only allows a username and password to be set and has no
functionality for setting read write permissions etc... Using SQL
administrator I cannot even see another user as set up with the host
despite being connected and logged in as the DBowner -Aghhhh!
 
 
 

User Admin- MySQL Administrator- SELECT command denied to user

Post by Peter H. C » Wed, 26 Mar 2008 22:20:34


Okay, your next step now is to find where in the documentation that
fasthost provides that it says you're allowed to add users to the MySQL
instance. Then you go to the fasthost support people and ask them where
to do what the documentation says you can do. We've already covered how
MySQL does the add-users functionality, it all depends on permissions
you don't have and we wouldn't really expect a user to have. We're about
out of help we can give. It's in fasthost's hands now.

--
'Cluids' - def: "Fluids having the effect of restoring or imparting
Clue. Eg; beer, coffee, sulphuric acid, etc."
-- Lionel, in the Monastery
 
 
 

User Admin- MySQL Administrator- SELECT command denied to user

Post by Jerry Stuc » Thu, 27 Mar 2008 02:14:58


> Hi Jerry,
>
> Thanks for that and sorry for delay in response - what I dont get is
> that I can access the db on the server from my remote machine (this
> one) using SQL Administrator with full permissions- I can change
> tables etc- without logging on through the hosts www website
> (fasthost)- yet I cannot seem to add users. The host must expect this
> to be accomplished via an external program as its new user setup on
> the website only allows a username and password to be set and has no
> functionality for setting read write permissions etc... Using SQL
> administrator I cannot even see another user as set up with the host
> despite being connected and logged in as the DBowner -Aghhhh!
>
>

(Top posting fixed)

It means you don't have CREATE USER (and possibly GRANT) privileges.
It's quite common to NOT give users these privileges but only one or two
users for the database (all that is generally required for a web page).

As Peter indicated, talk to your hosting company to ensure you should be
allowed to do this.

P.S. Please don't top post. Thanks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
XXXX@XXXXX.COM
==================
 
 
 

User Admin- MySQL Administrator- SELECT command denied to user

Post by DanWeave » Thu, 27 Mar 2008 03:19:01


Thanks gents, Ill give it another go and if cant fix I'll brave the
customer service and see how it is!


Dan
 
 
 

User Admin- MySQL Administrator- SELECT command denied to user

Post by Michael Au » Thu, 27 Mar 2008 13:07:12


What most hosts like fasthost expect is that you do not add users at the
database instance layer, but you - the developer - role-your-own user
access. See PHPBB on how this can be accomplished from the application
/php/web-layer and you would use the username they provided you for
creating the schema (tables/indexes etc...) and web-layer access - as I
believe Jerry mentioned.

I would not want just anyone to have that level of access to my database
- could be a huge security risk especially if you are not sure of all of
the ramifications of what you are doing...