Security: MySql - Perl

Hi!

Sorry, I am a beginner with Perl.

I try to connect to a MySQL database whith a perl script but I always
get the following error:
"DBD::mysql::st execute failed: SELECT command denied to user:
'logger@localhost' for table 'category'..."

I have granted all of the priviledges to the user logger by this mysql
console command:
GRANT ALL PRIVILEGES ON logger
TO 'logger'@'localhost'
IDENTIFIED BY 'logger';

GRANT ALL PRIVILEGES ON logger
TO 'logger'@'%'
IDENTIFIED BY 'logger';

use mysql
update user set select_priv='Y' where user="logger";


With the bin/mysql client I can connect and do everything on the above
database as "logger", but from the PERL script I cannot.

Thank you!
Akos Tomasits



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Security: MySql - Perl

--2oS5YaxWCcQjTEyO
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Sep 27, 2005 at 01:54:04PM +0200, ?kos Tomasits wrote:
> Hi!
>=20
> Sorry, I am a beginner with Perl.
>=20
> I try to connect to a MySQL database whith a perl script but I always=20
> get the following error:
> "DBD::mysql::st execute failed: SELECT command denied to user:=20
> 'logger@localhost' for table 'category'..."
>=20
> I have granted all of the priviledges to the user logger by this mysql=20
> console command:
> GRANT ALL PRIVILEGES ON logger
> TO 'logger'@'localhost'
> IDENTIFIED BY 'logger';
>=20
> GRANT ALL PRIVILEGES ON logger
> TO 'logger'@'%'
> IDENTIFIED BY 'logger';
>=20
> use mysql
> update user set select_priv=3D'Y' where user=3D"logger";
>=20
>=20
> With the bin/mysql client I can connect and do everything on the above=20
> database as "logger", but from the PERL script I cannot.

Could you post the part of the Perl script that actually connects to
the database? There could be some trivial error...

Besides, how exactly have you established that you can do everything
with the MySQL client? Have you tried it with the "-h localhost" and
"-h 127.0.0.1" options?

G'luck,
Peter

--=20
Peter Pentchev roam@ringlet.net roam@cnsys.bg roam@FreeBSD.org
PGP key: http://people.FreeBSD.org/~roam/roam.key.asc
Key fingerprint FDBA FD79 C26F 3C51 C95E DF9E ED18 B68D 1619 4553
I am jealous of the first word in this sentence.

--2oS5YaxWCcQjTEyO
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (FreeBSD)

iD8DBQFDOUjH7Ri2jRYZRVMRAlO1AJ9DVApS/h/jdUGwjKnET72MR9wrdwCb BeCN
lr6+/YZkrqGCsQMihcvxl3A=
=jkG4
-----END PGP SIGNATURE-----

--2oS5YaxWCcQjTEyO--

Re: Security: MySql - Perl

Ákos Tomasits wrote:
> Hi!
>
> Sorry, I am a beginner with Perl.
>
> I try to connect to a MySQL database whith a perl script but I always
> get the following error:
> "DBD::mysql::st execute failed: SELECT command denied to user:
> 'logger@localhost' for table 'category'..."

OK, logger@localhost doesn't have permission to work with the table named
category in some db.

> I have granted all of the priviledges to the user logger by this mysql
> console command:
> GRANT ALL PRIVILEGES ON logger
> TO 'logger'@'localhost'
> IDENTIFIED BY 'logger';
>
> GRANT ALL PRIVILEGES ON logger
> TO 'logger'@'%'
> IDENTIFIED BY 'logger';

These grant the logger users (@localhost and @%) all privileges on the
logger table in whatever db was in use at the time. It does not grant any
permissions for the category table. That's probably not what you meant.

Is logger the name of the db? If you want the logger users to be able to
work with all tables in the logger db, you need to

GRANT ALL PRIVILEGES ON logger.*
TO 'logger'@'localhost'
IDENTIFIED BY 'logger_passwd';

See the manual for details on using GRANT
.

> use mysql
> update user set select_priv='Y' where user="logger";

This sets a GLOBAL privilege, which you probably don't want. In any case,
it won't take effect until you FLUSH PRIVILEGES or restart mysqld. Still,
the solution is to get the GRANT right, not to edit the mysql table.

> With the bin/mysql client I can connect and do everything on the above
> database as "logger", but from the PERL script I cannot.

Based on what you've shown us, I'm skeptical. If that's really true, we'll
need more info. Show us the connection command you use for the mysql
client, the lines of your perl code which make the connection and the
select, and show us the output of

SHOW GRANTS FOR logger@localhost;
SHOW GRANTS FOR logger@%;

> Thank you!
> Akos Tomasits

It would probably be a good idea for you to read about the mysql privilege
system .

Michael

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Security: MySql - Perl

And don't forget the usual dance of FLUSH PRIVILEGES.

On 9/27/05, Michael Stassen wrote:
> =C1kos Tomasits wrote:
> > Hi!
> >
> > Sorry, I am a beginner with Perl.
> >
> > I try to connect to a MySQL database whith a perl script but I always
> > get the following error:
> > "DBD::mysql::st execute failed: SELECT command denied to user:
> > 'logger@localhost' for table 'category'..."
>
> OK, logger@localhost doesn't have permission to work with the table named
> category in some db.
>
> > I have granted all of the priviledges to the user logger by this mysql
> > console command:
> > GRANT ALL PRIVILEGES ON logger
> > TO 'logger'@'localhost'
> > IDENTIFIED BY 'logger';
> >
> > GRANT ALL PRIVILEGES ON logger
> > TO 'logger'@'%'
> > IDENTIFIED BY 'logger';
>
> These grant the logger users (@localhost and @%) all privileges on the
> logger table in whatever db was in use at the time. It does not grant an=
y
> permissions for the category table. That's probably not what you meant.
>
> Is logger the name of the db? If you want the logger users to be able to
> work with all tables in the logger db, you need to
>
> GRANT ALL PRIVILEGES ON logger.*
> TO 'logger'@'localhost'
> IDENTIFIED BY 'logger_passwd';
>
> See the manual for details on using GRANT
> .
>
> > use mysql
> > update user set select_priv=3D'Y' where user=3D"logger";
>
> This sets a GLOBAL privilege, which you probably don't want. In any case=
,
> it won't take effect until you FLUSH PRIVILEGES or restart mysqld. Still=
,
> the solution is to get the GRANT right, not to edit the mysql table.
>
> > With the bin/mysql client I can connect and do everything on the above
> > database as "logger", but from the PERL script I cannot.
>
> Based on what you've shown us, I'm skeptical. If that's really true, we'=
ll
> need more info. Show us the connection command you use for the mysql
> client, the lines of your perl code which make the connection and the
> select, and show us the output of
>
> SHOW GRANTS FOR logger@localhost;
> SHOW GRANTS FOR logger@%;
>
> > Thank you!
> > Akos Tomasits
>
> It would probably be a good idea for you to read about the mysql privileg=
e
> system .
>
> Michael
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=3Dmediis@gmail.com
>
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Security: MySql - Perl

Mark Ahlstrom wrote:
> And don't forget the usual dance of FLUSH PRIVILEGES.

which is unnecessary if you use GRANT (or any other account management
command, such as REVOKE or SET PASSWORD). FLUSH PRIVILEGES is only
necessary if you directly modify the mysql db tables, which I recommended
against. See the manual for more
.

Michael

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org