[sshproxy-dev] Changes to sshproxy/SSHproxy/pwdb/mysql.py

[Pierre-Yves Rofes]

Index: sshproxy/SSHproxy/pwdb/mysql.py
diff -u sshproxy/SSHproxy/pwdb/mysql.py:1.6 sshproxy/SSHproxy/pwdb/mysql.py:1.7
--- sshproxy/SSHproxy/pwdb/mysql.py:1.6 Thu Oct 27 14:14:36 2005
+++ sshproxy/SSHproxy/pwdb/mysql.py     Fri Nov 18 09:39:36 2005
@@ -100,10 +100,19 @@
 
 ################ functions for scripts/add_group ########################
 
-    def list_groups(self):
+    def list_groups(self, site=None):
         q_getgroup = """
-            select id, name from sgroup
-        """
+            select sgroup.id,
+                   sgroup.name
+                from sgroup
+        """
+        if site:
+            q_getgroup = q_getgroup.strip() + """,
+                     sgroup_site,
+                     site
+                where site.name = '%s' and
+                      site.id = sgroup_site.site_id and
+                      sgroup_site.sgroup_id = sgroup.id""" % site
         group = db.cursor()
         group.execute(q_getgroup)
         p = []
@@ -137,21 +146,44 @@
 
 ################ functions for scripts/add_site #######################
 
-    def list_sites(self):
+    def list_sites(self, group=None):
         q_listsite = """
-            select id, name, ip_address, port, location from site
-        """
+            select site.id,
+                   site.name,
+                   site.ip_address,
+                   site.port,
+                   site.location
+                from site
+        """
+        if group:
+            q_listsite = q_listsite.strip() + """,
+                     sgroup_site,
+                     sgroup
+                where sgroup.name = '%s' and
+                      sgroup_site.sgroup_id = sgroup.id and
+                      sgroup_site.site_id = site.id""" % group
         site = db.cursor()
         site.execute(q_listsite)
         p = []
         for id, name, ip_address, port, location in site.fetchall():
-            p.append({ 'id': id, 'name': name, 'ip': ip_address, 'port': port, 
'location': location })
+            p.append({ 'id': id,
+                       'name': name,
+                       'ip': ip_address,
+                       'port': port,
+                       'location': location })
         site.close()
         return p
 
+    # XXX: rename this method
     def get_site_for_script(self, name):
         q_getsite = """
-            select id, name, ip_address, port, location from site where name = 
'%s'
+            select id,
+                   name,
+                   ip_address,
+                   port,
+                   location
+                from site 
+                where name = '%s'
         """
         site = db.cursor()
         site.execute(q_getsite % name)
@@ -159,11 +191,20 @@
         if not p or not len(p):
             return None
         site.close()
-        return { 'id': p[0], 'name': p[1], 'ip_address': p[2], 'port': p[3], 
'location': p[4] }
+        return { 'id': p[0],
+                 'name': p[1],
+                 'ip': p[2],
+                 'port': p[3],
+                 'location': p[4] }
 
     def add_site(self, name, ip_address, port, location):
         q_addsite = """
-            insert into site (name, ip_address, port, location) values 
('%s','%s',%d,'%s')
+            insert into site (
+                    name,
+                    ip_address,
+                    port,
+                    location)
+                values ('%s','%s',%d,'%s')
         """
         if self.get_site_for_script(name):
             return None
@@ -174,21 +215,30 @@
 
 ################## functions for scripts/add_user #####################
 
-    def list_users(self):
+    def list_users(self, site_id=None):
         q_listuser = """
             select site_id, uid, password, `primary` from user
         """
+        if site_id:
+            q_listuser = q_listuser + " where site_id = " + str(site_id)
         site = db.cursor()
         site.execute(q_listuser)
         p = []
         for site_id, uid, password, primary in site.fetchall():
-            p.append({ 'uid': uid, 'site_id': site_id, 'password': password, 
'primary': primary })
+            p.append({ 'uid': uid,
+                       'site_id': site_id,
+                       'password': password,
+                       'primary': primary })
         site.close()
         return p
 
     def get_users(self, uid):
         q_getuser = """
-            select site_id, uid, password, `primary` from user where uid = '%s'
+            select site_id,
+                   uid,
+                   password,
+                   `primary`
+                from user where uid = '%s'
         """
         user = db.cursor()
         user.execute(q_getuser % uid)
@@ -196,11 +246,24 @@
         if not p or not len(p):
             return None
         user.close()
-        return { 'site_id': p[0], 'uid': p[1], 'password': p[2], 'primary': 
p[3] }
+        return { 'site_id': p[0],
+                 'uid': p[1],
+                 'password': p[2],
+                 'primary': p[3] }
+
+    def add_user_to_site(self, uid, site, password, primary):
+        site_id = self.get_id('site', site)
+        self.add_user(uid, site_id, password, primary)
+        return 1
 
     def add_user(self, uid, site_id, password, primary):
         q_adduser = """
-            insert into user (uid, site_id, password, `primary`) values 
('%s','%s','%s',%d)
+            insert into user (
+                    uid,
+                    site_id,
+                    password,
+                    `primary`)
+                values ('%s',%d,'%s',%d)
         """
         if self.get_users(uid):
             return None
@@ -213,19 +276,19 @@
 
     def list_logins(self):
         q_listlogin = """
-            select uid, password, `key` from login
+            select name, password, `key` from login
         """
         site = db.cursor()
         site.execute(q_listlogin)
         p = []
-        for uid, password, key in site.fetchall():
-            p.append({ 'uid': uid, 'password': password, 'key': key })
+        for login, password, key in site.fetchall():
+            p.append({ 'login': login, 'password': password, 'key': key })
         site.close()
         return p
 
     def get_logins(self, uid):
         q_getlogin = """
-            select uid, password, `key` from login where uid = '%s'
+            select name, password, `key` from login where uid = '%s'
         """
         login = db.cursor()
         login.execute(q_getlogin % uid)
@@ -233,22 +296,22 @@
         if not p or not len(p):
             return None
         login.close()
-        return { 'uid': p[0], 'password': p[1], 'key': p[2] }
+        return { 'login': p[0], 'password': p[1], 'key': p[2] }
 
-    def add_login(self, uid, password, key):
+    def add_login(self, login, password, key):
         q_addlogin = """
-            insert into login (uid, password, `key`) values ('%s','%s','%s')
+            insert into login (name, password, `key`) values ('%s','%s','%s')
         """
         if self.get_logins(uid):
             return None
         login = db.cursor()
-        login.execute(q_addlogin % (uid, password, key))
+        login.execute(q_addlogin % (login, password, key))
         login.close()
         return 1
 
 ######### functions for link scripts/add_login_profile ###############
 
-    def list_login_prof(self):
+    def list_login_profile(self):
         q_list = """
              select login_id, profile_id from login_profile
         """
@@ -256,11 +319,13 @@
         lists.execute(q_list)
         p = []
         for login_id,profile_id in lists.fetchall():
-            p.append({ 'login_id': login_id, 'profile_id': profile_id })
+            profile = self.get_name('profile', profile_id)
+            login = self.get_name('login', login_id)
+            p.append({ 'login': login, 'profile': profile })
         lists.close()
         return p
 
-    def add_login_prof(self, login_id, profile_id):
+    def add_login_profile(self, login_id, profile_id):
         q_addlogin = """
             insert into login_profile (login_id, profile_id) values (%d,%d)
         """
@@ -272,7 +337,7 @@
 
 ######### functions for link scripts/add_profile_sgroup ###############
 
-    def list_prof_group(self):
+    def list_profile_group(self):
         q_list = """
              select profile_id, sgroup_id from profile_sgroup
         """
@@ -280,11 +345,13 @@
         lists.execute(q_list)
         p = []
         for profile_id,sgroup_id in lists.fetchall():
-            p.append({ 'profile_id': profile_id, 'sgroup_id': sgroup_id })
+            profile = self.get_name('profile', profile_id)
+            sgroup = self.get_name('sgroup', sgroup_id)
+            p.append({'prof': profile, 'sgroup': sgroup})
         lists.close()
         return p
 
-    def add_prof_group(self, profile_id, sgroup_id):
+    def add_profile_group(self, profile_id, sgroup_id):
         q_addlogin = """
             insert into profile_sgroup (profile_id,sgroup_id) values (%d,%d)
         """
@@ -294,7 +361,7 @@
         return 1
 
 
-######### functions for link scripts/add_sgroup_site ###############
+######### functions for link scripts/add_group_site ###############
 
     def list_group_site(self):
         q_list = """
@@ -304,7 +371,9 @@
         lists.execute(q_list)
         p = []
         for sgroup_id,site_id in lists.fetchall():
-            p.append({ 'sgroup_id': sgroup_id, 'site_id': site_id })
+            sgroup = self.get_name('sgroup', sgroup_id)
+            site = self.get_name('site', site_id)
+            p.append({'sgroup': sgroup, 'site': site})
         lists.close()
         return p
 
@@ -317,8 +386,31 @@
         login.close()
         return 1
 
+######################################################################
+
+    def get_name(self, table, id):
+        name = None
+        query = """
+        select name from `%s` where id = '%d'
+        """
+        c= db.cursor()
+        c.execute(query % (table, int(id)))
+        name = c.fetchone()
+        c.close()
+        return name[0]
+
+
+    def get_id(self, table, name):
+        id = None
+        query = """
+        select id from `%s` where name = '%s' 
+        """
+        c = db.cursor()
+        c.execute(query % (table, name))
+        id = c.fetchone()
+        c.close()
+        return id[0]
 
-#######################################################################
 
     def is_allowed(self, username, password=None, key=None):
         """Check is a user is allowed to connect to the proxy."""
@@ -349,8 +441,10 @@
             user, sid = sid.split('@')
         if not user:
             q_user = """
-            select uid from site,user where site.id = user.site_id and 
site.name = '%s'
- order by `primary` desc limit 1            
+            select uid
+                from site, user
+                where site.id = user.site_id and site.name = '%s'
+                order by `primary` desc limit 1            
 """
             users = db.cursor()
             users.execute(q_user % sid)
@@ -360,7 +454,8 @@
             user = user[0]
             users.close()
         if not self.can_connect(user, sid):
-            print 'User \'%s\' is not allowed to connect to \'%s\'' % (user, 
sid)
+            print 'User \'%s\' is not allowed to connect to \'%s\'' % (user,
+                                                                       sid)
             return None, None
         return self.sites[sid].default_user(), self.sites[sid]
 
@@ -390,7 +485,7 @@
         group = db.cursor()
         group.execute(q_group % (self.login, site, user))
         gr = group.fetchone()[0]
-        print 'requete can_connect: %d' % gr
+        print '\ncan_connect matches for user \'%s\' and site \'%s\' = %d' % 
(user, site, gr)
         group.close()
         return gr