Tim Landscheidt has uploaded a new change for review. https://gerrit.wikimedia.org/r/234934
Change subject: Add list-user-databases command ...................................................................... Add list-user-databases command Bug: T91231 Change-Id: I951bce6043f78d33d5fd409fee855fc5bb34abc9 --- M debian/changelog M debian/control M debian/misctools.install M debian/misctools.manpages M misctools/Makefile.am A misctools/list-user-databases A misctools/list-user-databases.1 7 files changed, 89 insertions(+), 4 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/labs/toollabs refs/changes/34/234934/1 diff --git a/debian/changelog b/debian/changelog index ecfebc7..fd74501 100644 --- a/debian/changelog +++ b/debian/changelog @@ -1,9 +1,10 @@ toollabs (1.6~dev) unstable; urgency=medium + * list-user-databases: List all databases a user has access to. * Bump Standards-Version to 3.9.5. * Fix lintian warning about maintainer also being in uploaders. - -- Tim Landscheidt <[email protected]> Sun, 05 Apr 2015 04:25:45 +0000 + -- Tim Landscheidt <[email protected]> Mon, 31 Aug 2015 02:51:53 +0000 toollabs (1.5) unstable; urgency=low diff --git a/debian/control b/debian/control index 5fc993d..9d4ca94 100644 --- a/debian/control +++ b/debian/control @@ -11,7 +11,7 @@ Package: misctools Architecture: any -Depends: ${misc:Depends}, ${shlibs:Depends} +Depends: python3, python3-mysql.connector, ${misc:Depends}, ${shlibs:Depends} Description: Miscellaneous Labs-specific tools Miscellaneous Labs-specific Tools used on Tool Labs diff --git a/debian/misctools.install b/debian/misctools.install index 6df9429..14249fe 100644 --- a/debian/misctools.install +++ b/debian/misctools.install @@ -1,4 +1,5 @@ usr/bin/become +usr/bin/list-user-databases usr/bin/take usr/bin/webservice usr/sbin/rmtool diff --git a/debian/misctools.manpages b/debian/misctools.manpages index 8f9d8cc..dbe0b8a 100644 --- a/debian/misctools.manpages +++ b/debian/misctools.manpages @@ -1,4 +1,5 @@ debian/tmp/usr/share/man/man1/become.1 +debian/tmp/usr/share/man/man1/list-user-databases.1 debian/tmp/usr/share/man/man8/rmtool.8 debian/tmp/usr/share/man/man1/take.1 debian/tmp/usr/share/man/man8/toolwatcher.8 diff --git a/misctools/Makefile.am b/misctools/Makefile.am index 87b590d..448a22a 100644 --- a/misctools/Makefile.am +++ b/misctools/Makefile.am @@ -1,3 +1,3 @@ -man_MANS = become.1 rmtool.8 toolwatcher.8 -bin_SCRIPTS = become webservice +man_MANS = become.1 list-user-databases.1 rmtool.8 toolwatcher.8 +bin_SCRIPTS = become list-user-databases webservice sbin_SCRIPTS = rmtool toolwatcher diff --git a/misctools/list-user-databases b/misctools/list-user-databases new file mode 100755 index 0000000..e281baa --- /dev/null +++ b/misctools/list-user-databases @@ -0,0 +1,62 @@ +#!/usr/bin/python3 + +import argparse +import configparser +import os +import re + +import mysql.connector + +# Servers to search databases on. +DATABASE_SERVERS = ('10.64.37.4', '10.64.37.5', '10.64.37.9', '10.64.4.11') + +if __name__ == '__main__': + # Get list of defaults files to extract user information from. + parser = argparse.ArgumentParser(description='List databases owned by users.') + parser.add_argument('mycnffilenames', + metavar='MYCNFFILENAME', + nargs='+', + help='name of MySQL defaults file') + args = parser.parse_args() + + for mycnffilename in args.mycnffilenames: + c = configparser.ConfigParser() + c.read(mycnffilename) + username = c['client']['user'].strip("'") + password = c['client']['password'].strip("'") + databases_found = [] + for server in DATABASE_SERVERS: + conn = mysql.connector.connect(user=username, + password=password, + host=server, + database='mysql') + cur = conn.cursor() + cur.execute('SHOW GRANTS') + database_patterns = [] + for (grantstatement, ) in cur.fetchall(): + # Ignore general grants for replica views. + if grantstatement == 'GRANT SELECT, SHOW VIEW ON `%%\\_p`.* TO \'%s\'@\'%%\'' % username: + continue + if re.match('^GRANT SHOW VIEW ON \*\.\* TO \'%s\'@\'%%\'(?: IDENTIFIED BY PASSWORD \'\*[0-9A-F]{40}\')?$' % re.escape(username), grantstatement): + continue + + # Gather grants for user-specific databases. + m = re.match('^GRANT ALL PRIVILEGES ON `([^`]+)`\.\* TO \'%s\'@\'%%\'(?: WITH GRANT OPTION)?$' % re.escape(username), grantstatement) + if m: + database_patterns.append(m.group(1)) + else: + raise Exception('Cannot parse ' + repr(grantstatement)) + + for database_pattern in database_patterns: + cur.execute('SHOW DATABASES LIKE %s', (database_pattern, )) + for r in cur.fetchall(): + databases_found.append([server, r[0]]) + + conn.close() + + if databases_found: + print('Databases found for user %s ...' % username) + for database_found in databases_found: + print('... on server %s database %s' % (database_found[0], database_found[1])) + else: + print('No databases found for user %s' % username) diff --git a/misctools/list-user-databases.1 b/misctools/list-user-databases.1 new file mode 100644 index 0000000..c6089c7 --- /dev/null +++ b/misctools/list-user-databases.1 @@ -0,0 +1,20 @@ +.TH LIST-USER-DATABASES 1 2015-08-31 1.6~dev "Wikimedia Labs Tools misctools" +.SH NAME +list-user-databases \- list databases a MySQL account has access to +.SH SYNOPSIS +.B list-user-databases +.I filename +.RI [filename ...] +.SH DESCRIPTION +.B list-user-databases +lists all databases on the replica servers and +.I tools.labsdb +that the user has access to whose +.I replica.my.cnf +file has been given as a command line argument. +.SH EXIT STATUS +In case of success, +.B list-user-databases +returns 0. +.SH AUTHORS +Tim Landscheidt -- To view, visit https://gerrit.wikimedia.org/r/234934 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I951bce6043f78d33d5fd409fee855fc5bb34abc9 Gerrit-PatchSet: 1 Gerrit-Project: labs/toollabs Gerrit-Branch: master Gerrit-Owner: Tim Landscheidt <[email protected]> _______________________________________________ MediaWiki-commits mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
