Missing grants on tools.labsdb
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	DamianZaremba
	May 22 2016, 3:06 PM

Description

Using the schema as described @ https://wikitech.wikimedia.org/wiki/Help:Tool_Labs/Database#Creating_new_databases, creating a new schema failed for the tools.cluebotng user (s52585 MySQL user) with an access denied:

Access denied for user 's52585'@'%' to database 's52585__interface'

jynus in Cloud-Services tracked it down to a grants issue.

[16:58] <jynus> Damianz_web, it seems there is something wrong with the grants, has this user (tool) been created recently?
[17:01] <Damianz_web> It was created at least a year ago, just not used :)
[17:02] <jynus> I can fix this for you, but could you file a ticket meanwhile with the issue while I do it?
[17:02] <jynus> you are not the only user affected

Tools account was created > 1 year ago.

Related Objects
Search...

		Status	Subtype	Assigned	Task
		Resolved		jcrespo	T136319 Labs users missing grants on replicas
		Resolved		jcrespo	T135947 Missing grants on tools.labsdb

Event Timeline

DamianZaremba created this task.May 22 2016, 3:06 PM

Restricted Application added subscribers: Zppix, Aklapper. · View Herald TranscriptMay 22 2016, 3:06 PM

DamianZaremba updated the task description. (Show Details)May 22 2016, 3:08 PM

It seems that s52585 (now manually fixed) and other users lack the:

GRANT ALL PRIVILEGES ON `s52585\_\_%`.* TO 's52585'@'%'

privileges.

I can fix this, but @yuvipanda, could you check first the script to check that this is not an ongoing issue (it will not happen for newly created accounts).

jcrespo renamed this task from Unable to create database on tools.labsdb to Missing grants on tools.labsdb.May 22 2016, 3:09 PM

jcrespo triaged this task as Low priority.

jcrespo moved this task from Triage to Blocked external/Not db team on the DBA board.May 23 2016, 8:41 AM

The relevant SQL is here: https://phabricator.wikimedia.org/diffusion/OPUP/browse/production/modules/labstore/files/create-dbusers;774873e507ca990399f49f370957f33635a0803f$137

CREATE USER '{user_name}'@'%' IDENTIFIED BY '{user_pass}';
GRANT SELECT, SHOW VIEW ON `%\_p`.* TO '{user_name}'@'%';
GRANT ALL PRIVILEGES ON `{user_name}\_\_%`.* TO '{user_name}'@'%';

which is being run through a single cursor.execute() -- so it seems unlikely that this would be an issue for new accounts.

valhallasw added a parent task: T136319: Labs users missing grants on replicas.May 26 2016, 3:40 PM

jcrespo moved this task from Blocked external/Not db team to Pending comment on the DBA board.May 26 2016, 3:56 PM

jcrespo raised the priority of this task from Low to Medium.May 26 2016, 4:24 PM

jcrespo merged a task: T136247: Cannot create database with s52590.

jcrespo added a subscriber: tom29739.

@tom29739 Try now.

(The task should continue being open for other missing users)

@jcrespo, s52589 is also missing grants, 'Access denied for user 's52589'@'%' to database 's52589__laravelapi''.

jcrespo claimed this task.May 26 2016, 4:44 PM

jcrespo moved this task from Pending comment to In progress on the DBA board.

Running:

GRANT ALL PRIVILEGES ON `s52587\_\_%`.* TO 's52587'@'%';
GRANT ALL PRIVILEGES ON `s52589\_\_%`.* TO 's52589'@'%';
GRANT ALL PRIVILEGES ON `s52592\_\_%`.* TO 's52592'@'%';
GRANT ALL PRIVILEGES ON `s52593\_\_%`.* TO 's52593'@'%';
GRANT ALL PRIVILEGES ON `s52594\_\_%`.* TO 's52594'@'%';
GRANT ALL PRIVILEGES ON `s52595\_\_%`.* TO 's52595'@'%';
GRANT ALL PRIVILEGES ON `s52596\_\_%`.* TO 's52596'@'%';
GRANT ALL PRIVILEGES ON `s52597\_\_%`.* TO 's52597'@'%';
GRANT ALL PRIVILEGES ON `s52598\_\_%`.* TO 's52598'@'%';
GRANT ALL PRIVILEGES ON `s52599\_\_%`.* TO 's52599'@'%';
GRANT ALL PRIVILEGES ON `s52600\_\_%`.* TO 's52600'@'%';
GRANT ALL PRIVILEGES ON `s52602\_\_%`.* TO 's52602'@'%';
GRANT ALL PRIVILEGES ON `s52603\_\_%`.* TO 's52603'@'%';
GRANT ALL PRIVILEGES ON `s52604\_\_%`.* TO 's52604'@'%';
GRANT ALL PRIVILEGES ON `s52605\_\_%`.* TO 's52605'@'%';
GRANT ALL PRIVILEGES ON `s52609\_\_%`.* TO 's52609'@'%';
GRANT ALL PRIVILEGES ON `s52610\_\_%`.* TO 's52610'@'%';
GRANT ALL PRIVILEGES ON `s52611\_\_%`.* TO 's52611'@'%';
GRANT ALL PRIVILEGES ON `s52612\_\_%`.* TO 's52612'@'%';
GRANT ALL PRIVILEGES ON `s52613\_\_%`.* TO 's52613'@'%';
GRANT ALL PRIVILEGES ON `s52615\_\_%`.* TO 's52615'@'%';
GRANT ALL PRIVILEGES ON `s52616\_\_%`.* TO 's52616'@'%';
GRANT ALL PRIVILEGES ON `s52617\_\_%`.* TO 's52617'@'%';
GRANT ALL PRIVILEGES ON `s52620\_\_%`.* TO 's52620'@'%';
GRANT ALL PRIVILEGES ON `s52621\_\_%`.* TO 's52621'@'%';
GRANT ALL PRIVILEGES ON `s52624\_\_%`.* TO 's52624'@'%';
GRANT ALL PRIVILEGES ON `s52625\_\_%`.* TO 's52625'@'%';
GRANT ALL PRIVILEGES ON `s52626\_\_%`.* TO 's52626'@'%';
GRANT ALL PRIVILEGES ON `s52627\_\_%`.* TO 's52627'@'%';
GRANT ALL PRIVILEGES ON `s52628\_\_%`.* TO 's52628'@'%';
GRANT ALL PRIVILEGES ON `s52630\_\_%`.* TO 's52630'@'%';

@yuvipanda, the creation date of these users, according to some users match last years' NFS failure. It is strange that because of that some grants worked and not some others (maybe the LDAP failed and the user strinf could not be retrieved). I am closing this, but I am pinging you in case you want to double check some extra checks/exceptions/failure states for the script (is this variable NULL/empty string?).

The script was also completely rewritten after that crash, so I think if it was due to that it's probably fixed by now.

jcrespo mentioned this in T130595: labsdb accounts being created without grants to create personal databases.May 31 2016, 3:44 PM

jcrespo mentioned this in T136319: Labs users missing grants on replicas.

• Phabricator_maintenance removed a subscriber: yuvipanda.Jun 7 2017, 6:42 PM

Missing grants on tools.labsdbClosed, ResolvedPublicActions

Description

Related ObjectsSearch...

Event Timeline

Missing grants on tools.labsdb
Closed, ResolvedPublic
Actions

Related Objects
Search...