Concerned : Developer getting There is no "root"@"%" registered errormessage

--------------090503030303050905080004
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hiya

Ill be up front my saying my knowledge of store procedures is limited.

A Developer gave me a procedure to load.

It starts off with:

CREATE DEFINER=`root`@`%` PROCEDURE ....


But now, the developer informs me that he gets the following message.
There is no 'root'@'%' registered

Googling reveals the following link :
http://forums.mysql.com/read.php?10,237843,238950#msg-238950

And that I can run:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mysql' WITH
GRANT OPTION;

Im hesitant to do it.

My Mysql root acess details are:

mysql> select user, host from user WHERE user = 'root';
+------+-------------+
| user | host |
+------+-------------+
| root | 127.0.0.1 |
| root | localhost |
+------+-------------+

If someone can share their opinion, thoughts or share the same concerns
it would be appreciated.

Kind Regards
Brent Clark


--------------090503030303050905080004--

Re: Concerned : Developer getting There is no "root"@"%" registerederror message

--001485f774f2f6461e04aab3a935
Content-Type: text/plain; charset=ISO-8859-1

Change the definer to one of your registered root accounts. Root@127 or
root@localhost.

On Aug 17, 2011 8:39 AM, "Brent Clark" wrote:

Hiya

Ill be up front my saying my knowledge of store procedures is limited.

A Developer gave me a procedure to load.

It starts off with:

CREATE DEFINER=`root`@`%` PROCEDURE ....


But now, the developer informs me that he gets the following message. There
is no 'root'@'%' registered

Googling reveals the following link : http://forums.mysql.com/read.**
php?10,237843,238950#msg-**238950

And that I can run:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mysql' WITH GRANT
OPTION;

Im hesitant to do it.

My Mysql root acess details are:

mysql> select user, host from user WHERE user = 'root';
+------+-------------+
| user | host |
+------+-------------+
| root | 127.0.0.1 |
| root | localhost |
+------+-------------+

If someone can share their opinion, thoughts or share the same concerns it
would be appreciated.

Kind Regards
Brent Clark

--001485f774f2f6461e04aab3a935--

Re: Concerned : Developer getting There is no "root"@"%" registerederror message

--------------040708000001040709010506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hiya

Thank you so much for replying. I really appreciate it.

I know the answer (well I think I do :) ), but im still going to ask.
What is the risk if do the "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
IDENTIFIED BY 'mysql' WITH GRANT OPTION;"
To satisfy the developer.

Thank you again.
Brent

On 17/08/2011 15:42, Johnny Withers wrote:
>
> Change the definer to one of your registered root accounts. Root@127
> or root@localhost.
>
>> On Aug 17, 2011 8:39 AM, "Brent Clark" >> > wrote:
>>
>> Hiya
>>
>> Ill be up front my saying my knowledge of store procedures is limited.
>>
>> A Developer gave me a procedure to load.
>>
>> It starts off with:
>>
>> CREATE DEFINER=`root`@`%` PROCEDURE ....
>>
>>
>> But now, the developer informs me that he gets the following message.
>> There is no 'root'@'%' registered
>>
>> Googling reveals the following link :
>> http://forums.mysql.com/read.php?10,237843,238950#msg-238950
>>
>> And that I can run:
>>
>> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mysql' WITH
>> GRANT OPTION;
>>
>> Im hesitant to do it.
>>
>> My Mysql root acess details are:
>>
>> mysql> select user, host from user WHERE user = 'root';
>> +------+-------------+
>> | user | host |
>> +------+-------------+
>> | root | 127.0.0.1 |
>> | root | localhost |
>> +------+-------------+
>>
>> If someone can share their opinion, thoughts or share the same
>> concerns it would be appreciated.
>>
>> Kind Regards
>> Brent Clark
>>


--------------040708000001040709010506--

Re: Concerned : Developer getting There is no "root"@"%" registerederror message

--001485f774f243595804aab3d5be
Content-Type: text/plain; charset=ISO-8859-1

It would allow anyone from anywhere to access your server as root with full
access to all databases using the password 'mysql'. Not very secure. I don't
recommend having a root@% defined.

On Aug 17, 2011 8:50 AM, "Brent Clark" wrote:

Hiya

Thank you so much for replying. I really appreciate it.

I know the answer (well I think I do :) ), but im still going to ask. What
is the risk if do the "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED
BY 'mysql' WITH GRANT OPTION;"
To satisfy the developer.

Thank you again.
Brent



On 17/08/2011 15:42, Johnny Withers wrote:

> >
> >
> > Change the definer to one of your registered root accounts. Root@127 or
> root@localhost.
> >
>
> >> On Aug 17, 2011 8:39 AM, "Brent Clark" > brentgclarklist@gmail.c...
>

--001485f774f243595804aab3d5be--

Re: Concerned : Developer getting There is no "root"@"%" registerederror message

--bcaec51a7d461259ac04aab423cc
Content-Type: text/plain; charset=ISO-8859-1

Perhaps he could make the stored procedure functional by creating a user
with the EXECUTE permission (somebody please fell free to correct me if I am
mistaken) if it is to be used to execute a stored procedure. You don't want
someone to attempt using the 'root' username with a typical 'mysql' password
to be granted ALL PRIVILEGES. Just my opinion.


On Wed, Aug 17, 2011 at 9:54 AM, Johnny Withers wrote:

> It would allow anyone from anywhere to access your server as root with full
> access to all databases using the password 'mysql'. Not very secure. I
> don't
> recommend having a root@% defined.
>
> On Aug 17, 2011 8:50 AM, "Brent Clark" wrote:
>
> Hiya
>
> Thank you so much for replying. I really appreciate it.
>
> I know the answer (well I think I do :) ), but im still going to ask. What
> is the risk if do the "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
> IDENTIFIED
> BY 'mysql' WITH GRANT OPTION;"
> To satisfy the developer.
>
> Thank you again.
> Brent
>
>
>
> On 17/08/2011 15:42, Johnny Withers wrote:
>
> > >
> > >
> > > Change the definer to one of your registered root accounts. Root@127or
> > root@localhost.
> > >
> >
> > >> On Aug 17, 2011 8:39 AM, "Brent Clark" > > > brentgclarklist@gmail.c...
> >
>

--bcaec51a7d461259ac04aab423cc--

Re: Concerned : Developer getting There is no "root"@"%" registered error message

----- Original Message -----
> From: "Brent Clark"
>
> I know the answer (well I think I do :) ), but im still going to ask.
> What is the risk if do the "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
> IDENTIFIED BY 'mysql' WITH GRANT OPTION;"
> To satisfy the developer.


That said developer then has full privileges with that account, perhaps? :-)

Check what privileges the stored procedure REALLY needs, and create an account with a strong, random password that has only those privileges. Do not give out the password to the developer. Any developer that demands full admin privileges is not to be left within a hundred miles of a production server.


Developers get full DML rights on the dev environment. They're free to fuck up their own database as much as they want, there.

If you have the comfort of a staging environment, they get select/insert/update/delete/execute to start off. More may be added depending on what is needed. Document the strict minimum privileges that make stuff work. Repeated requests for more privileges will be met with a frown, but granted. You *should* know what privileges you need, you wrote the damn thing.

If you have the luxury of a preproduction environment, only the privileges documented from staging will be granted. Requests for additional privileges will be met with a thorough LARTing and a nuking of the account and data. Redo from start.

On production, installs are performed strictly as per procedure; no exceptions; and only from the scripts that I have documented as working on preprod. You don't even get access to a mysql client on the app server. If a dump to dev is *really* not good enough for you to debug an issue, you can sit next to me while I query the database for you.


Also, @John Siebert, DEFINER privileges define with which privileges the SP will execute, whereas it is the user invoking the SP who needs execute privileges on it.

--
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?unsub=gcdmg-mysql-2@m.gmane.org

RE: Concerned : Developer getting There is no "root"@"%" registered error message

Correct me if I am wrong, but my impression is that usage of
"DEFINER=3D" in stored procedures has no impact on runtime and is
actually optional.
(not to be confused with the clause SQL SECURITY INVOKER which is
crucial.)
Note: I use 5.1.32-enterprise-gpl-advanced-log.

David.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Concerned : Developer getting There is no "root"@"%" registered error message

----- Original Message -----
> From: "David Lerer"
>
> Correct me if I am wrong, but my impression is that usage of
> "DEFINER=" in stored procedures has no impact on runtime and
> is actually optional.
> (not to be confused with the clause SQL SECURITY INVOKER which is
> crucial.)

You can also say SQL SECURITY DEFINER, which makes the procedure run with the privileges of the user who defined it. At that point, the definer becomes very relevant indeed :-)


--
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?unsub=gcdmg-mysql-2@m.gmane.org

RE: Concerned : Developer getting There is no "root"@"%" registered error message

VGhhbmtzIEJpZXIuIEkgc2VlIHdoYXQgeW91IG1lYW4uDQooQXMgYSBydWxl IHdlIGFsd2F5cyB1
c2UgU1FMIFNFQ1VSSVRZIElOVk9LRVIpDQpEYXZpZC4NCg0KLS0tLS1Pcmln aW5hbCBNZXNzYWdl
LS0tLS0NCkZyb206IEpvaGFuIERlIE1lZXJzbWFuIFttYWlsdG86dmVnaXZh bXBAdHV4ZXJhLmJl
XSANClNlbnQ6IFRodXJzZGF5LCBBdWd1c3QgMTgsIDIwMTEgNDoyMCBBTQ0K VG86IERhdmlkIExl
cmVyDQpDYzogbXlzcWxAbGlzdHMubXlzcWwuY29tDQpTdWJqZWN0OiBSZTog Q29uY2VybmVkIDog
RGV2ZWxvcGVyIGdldHRpbmcgVGhlcmUgaXMgbm8gJ3Jvb3QnQCclJyByZWdp c3RlcmVkIGVycm9y
IG1lc3NhZ2UNCg0KLS0tLS0gT3JpZ2luYWwgTWVzc2FnZSAtLS0tLQ0KPiBG cm9tOiAiRGF2aWQg
TGVyZXIiIDxETGVyZXJAdXMudW5pdmlzaW9uLmNvbT4NCj4gDQo+IENvcnJl Y3QgbWUgaWYgSSBh
bSB3cm9uZywgYnV0IG15IGltcHJlc3Npb24gaXMgdGhhdCB1c2FnZSBvZg0K PiAiREVGSU5FUj08
dXNlcj4iICBpbiBzdG9yZWQgcHJvY2VkdXJlcyBoYXMgbm8gaW1wYWN0IG9u IHJ1bnRpbWUgYW5k
DQo+IGlzIGFjdHVhbGx5IG9wdGlvbmFsLg0KPiAobm90IHRvIGJlIGNvbmZ1 c2VkIHdpdGggdGhl
IGNsYXVzZSBTUUwgU0VDVVJJVFkgSU5WT0tFUiB3aGljaCBpcw0KPiBjcnVj aWFsLikNCg0KWW91
IGNhbiBhbHNvIHNheSBTUUwgU0VDVVJJVFkgREVGSU5FUiwgd2hpY2ggbWFr ZXMgdGhlIHByb2Nl
ZHVyZSBydW4gd2l0aCB0aGUgcHJpdmlsZWdlcyBvZiB0aGUgdXNlciB3aG8g ZGVmaW5lZCBpdC4g
QXQgdGhhdCBwb2ludCwgdGhlIGRlZmluZXIgYmVjb21lcyB2ZXJ5IHJlbGV2 YW50IGluZGVlZCA6
LSkNCg0KDQotLSANCkJpZXIgbWV0IGdyZW5hZHluDQpJcyBhbHMgbW9zdGVy ZCBieSBkZW4gd3lu
DQpTeSBkaWUndCBkcmlua3QsIGlzIGVlbmUga3dlemVsDQpIeSBkaWUndCBk cmlua3QsIGlzIHJh
cyBlZW4gZXplbA0K

Re: Concerned : Developer getting There is no "root"@"%" registered error message

----- Original Message -----
> From: "David Lerer"
>
> Thanks Bier. I see what you mean.
> (As a rule we always use SQL SECURITY INVOKER)

Both have their use, although I would very much prefer MySQL to default to INVOKER if not specified, instead of the extremely annoying-when-migrating DEFINER.

--
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?unsub=gcdmg-mysql-2@m.gmane.org