# HG changeset patch # User Daniele Nicolodi # Date 1322643263 -3600 # Node ID a198738e3187177388d694b94b3e7f2b1e761b05 # Parent 5c5f6d93d9d44d9f14a2af0b9b42fa29e651c9d1 Fix 'transactions' table privileges. Bump schema version to 30. diff -r 5c5f6d93d9d4 -r a198738e3187 src/ltpdarepo/__init__.py --- a/src/ltpdarepo/__init__.py Wed Nov 30 09:41:29 2011 +0100 +++ b/src/ltpdarepo/__init__.py Wed Nov 30 09:54:23 2011 +0100 @@ -24,7 +24,7 @@ from .views.users import module as users -SCHEMA = 29 +SCHEMA = 30 class datetimeutc(datetime): diff -r 5c5f6d93d9d4 -r a198738e3187 src/ltpdarepo/admin.py --- a/src/ltpdarepo/admin.py Wed Nov 30 09:41:29 2011 +0100 +++ b/src/ltpdarepo/admin.py Wed Nov 30 09:54:23 2011 +0100 @@ -248,7 +248,11 @@ curs = conn.cursor() for priv in privs: - curs.execute('''GRANT %s ON %s.* TO %%s@%%s''' % (priv, database), (username, '%')) + curs.execute("GRANT %s ON %s.* TO %%s@%%s" % + (priv, database), (username, '%')) + # explicitly grant privileges on transactions table + curs.execute("GRANT INSERT ON %s.transactions TO %%s@%%s" % + (database, ), (username, '%')) conn.commit() conn.close() diff -r 5c5f6d93d9d4 -r a198738e3187 src/ltpdarepo/install.py --- a/src/ltpdarepo/install.py Wed Nov 30 09:41:29 2011 +0100 +++ b/src/ltpdarepo/install.py Wed Nov 30 09:54:23 2011 +0100 @@ -110,7 +110,7 @@ end repeat; END;""") - curs.execute("""INSERT INTO `options` VALUES ('version', '29')""") + curs.execute("""INSERT INTO `options` VALUES ('version', '30')""") conn.commit() conn.close() diff -r 5c5f6d93d9d4 -r a198738e3187 src/ltpdarepo/tests/test_schema_upgrade.py --- a/src/ltpdarepo/tests/test_schema_upgrade.py Wed Nov 30 09:41:29 2011 +0100 +++ b/src/ltpdarepo/tests/test_schema_upgrade.py Wed Nov 30 09:54:23 2011 +0100 @@ -71,10 +71,15 @@ dbs = [row[0] for row in curs.fetchall()] self.assertEqual(dbs, ['db1']) - # no explicit privileges on transactions table - curs.execute("SELECT * FROM mysql.tables_priv") - tables = curs.fetchall() - self.assertEqual(tables, ()) + # privileges on transactions table + curs.execute("SELECT Db, Table_priv FROM mysql.tables_priv " + "WHERE Table_name='transactions' AND User = 'u1'") + privs = curs.fetchall() + self.assertEqual(privs, ()) + curs.execute("SELECT Db, Table_priv FROM mysql.tables_priv " + "WHERE Table_name='transactions' AND User = 'u2'") + privs = curs.fetchall() + self.assertEqual(privs, (('db1', u'Insert'),)) # dump database structure upgraded = StringIO() diff -r 5c5f6d93d9d4 -r a198738e3187 src/ltpdarepo/upgrade.py --- a/src/ltpdarepo/upgrade.py Wed Nov 30 09:41:29 2011 +0100 +++ b/src/ltpdarepo/upgrade.py Wed Nov 30 09:54:23 2011 +0100 @@ -431,3 +431,41 @@ for db in databases: # add index on "submitted" column to speed up activity view curs.execute("""ALTER TABLE `%s`.objmeta ADD INDEX (submitted)""" % db) + + +@register(29, 30) +def upgrade_29_30(conn, **kwargs): + + # removing privileges explicitly granted on the 'transactions' + # table in upgrade step from version 2.4 to 2.5 was a mistacke. + # add them back + curs = conn.cursor() + curs.execute("SELECT db_name FROM available_dbs") + databases = [row[0] for row in curs.fetchall()] + for db in databases: + _update_transactions_privileges(conn, db) + conn.commit() + + +def _update_transactions_privileges(conn, database): + curs = conn.cursor() + # build a table with user name, boolean signaling if the user + # has any privilege on the database, boolean signaling if the user + # has insert privilege on transations table + curs.execute("""SELECT username, + Select_priv='Y' OR Insert_priv='Y' OR + Update_priv='Y' OR Delete_priv='Y', + FIND_IN_SET('Insert', Table_priv)>0 FROM users + LEFT JOIN mysql.db AS md ON md.Db=%s AND md.User=username + LEFT JOIN mysql.tables_priv AS mt ON mt.Db=%s AND + mt.Table_name='transactions' AND mt.User=username + ORDER BY username""", (database, database)) + for row in curs.fetchall(): + if row[1] and not row[2]: + # user has privileges on the database but not on the transactions table + curs.execute("""GRANT INSERT ON `%s`.transactions + TO %%s@%%s""" % database, (row[0], '%')) + if row[2] and not row[1]: + # user has privileges on the transactions table but not on the database + curs.execute("""REVOKE INSERT ON `%s`.transactions + FROM %%s@%%s""" % database, (row[0], '%')) diff -r 5c5f6d93d9d4 -r a198738e3187 src/ltpdarepo/views/databases.py --- a/src/ltpdarepo/views/databases.py Wed Nov 30 09:41:29 2011 +0100 +++ b/src/ltpdarepo/views/databases.py Wed Nov 30 09:54:23 2011 +0100 @@ -124,6 +124,30 @@ curs.execute(cmd, (user, '%')) +def _update_transactions_permissions(database): + curs = g.db.cursor() + # build a table with user name, boolean signaling if the user + # has any privilege on the database, boolean signaling if the user + # has insert privilege on transations table + curs.execute("""SELECT username, + Select_priv='Y' OR Insert_priv='Y' OR + Update_priv='Y' OR Delete_priv='Y', + FIND_IN_SET('Insert', Table_priv)>0 FROM users + LEFT JOIN mysql.db AS md ON md.Db=%s AND md.User=username + LEFT JOIN mysql.tables_priv AS mt ON mt.Db=%s AND + mt.Table_name='transactions' AND mt.User=username + ORDER BY username""", (database, database)) + for row in curs.fetchall(): + if row[1] and not row[2]: + # user has privileges on the database but not on the transactions table + curs.execute("""GRANT INSERT ON `%s`.transactions + TO %%s@%%s""" % database, (row[0], '%')) + if row[2] and not row[1]: + # user has privileges on the transactions table but not on the database + curs.execute("""REVOKE INSERT ON `%s`.transactions + FROM %%s@%%s""" % database, (row[0], '%')) + + @app.route('//permissions', methods=['GET', 'POST']) @require('admin') def permissions(database): @@ -137,6 +161,7 @@ if request.method == 'POST' and form.validate(): updates = _permissions_updates(permissions, request.form) _update_permissions(database, updates) + _update_transactions_permissions(database) flash('Permissions updated.') return redirect(url_for('manage.databases.view', database=database)) return render_template('databases/permissions.html', database=db, permissions=permissions, form=form) diff -r 5c5f6d93d9d4 -r a198738e3187 src/ltpdarepo/views/users.py --- a/src/ltpdarepo/views/users.py Wed Nov 30 09:41:29 2011 +0100 +++ b/src/ltpdarepo/views/users.py Wed Nov 30 09:54:23 2011 +0100 @@ -161,14 +161,38 @@ return updates -def _update_permissions(user, updates): +def _update_permissions(username, updates): curs = g.db.cursor() for database, priv, value in updates: if value: cmd = "GRANT %s ON `%s`.* TO %%s@%%s""" % (priv, database) else: cmd = "REVOKE %s ON `%s`.* FROM %%s@%%s""" % (priv, database) - curs.execute(cmd, (user, '%')) + curs.execute(cmd, (username, '%')) + + +def _update_transactions_permissions(username): + curs = g.db.cursor() + # build a table with database name, boolean signaling if the user + # has any privilege on the database, boolean signaling if the user + # has insert privilege on transations table + curs.execute("""SELECT db_name, + Select_priv='Y' OR Insert_priv='Y' OR + Update_priv='Y' OR Delete_priv='Y', + FIND_IN_SET('Insert', Table_priv)>0 FROM available_dbs + LEFT JOIN mysql.db AS md ON md.Db=db_name AND md.User=%s + LEFT JOIN mysql.tables_priv AS mt ON mt.Db=db_name AND + mt.Table_name='transactions' AND mt.User=%s + ORDER BY db_name""", (username, username)) + for row in curs.fetchall(): + if row[1] and not row[2]: + # user has privileges on the database but not on the transactions table + curs.execute("""GRANT INSERT ON `%s`.transactions + TO %%s@%%s""" % row[0], (username, '%')) + if row[2] and not row[1]: + # user has privileges on the transactions table but not on the database + curs.execute("""REVOKE INSERT ON `%s`.transactions + FROM %%s@%%s""" % row[0], (username, '%')) @app.route('//permissions', methods=('GET', 'POST')) @@ -184,6 +208,7 @@ if request.method == 'POST' and form.validate(): updates = _permissions_updates(permissions, request.form) _update_permissions(username, updates) + _update_transactions_permissions(username) flash('Permissions updated.') return redirect(url_for('manage.users.view', username=username)) return render_template('users/permissions.html', user=user, permissions=permissions, form=form)