from Framework import Context
from Framework.Logger import Logger
from Tools.SiteDBCore import SiteDBApi
context = Context()
context.addService(Logger("sitedbtest"))
api = SiteDBApi(context)
api.connect()
print api.getTierList()
from Framework import Context
from Framework.Logger import Logger
from Tools.SiteDBCore import SiteDBApi
context = Context ()
context.addService (Logger ("sitedbtest"))
api = SiteDBApi (context)
api.connect ()
print api.getTierList ()
class SecurityDBApi(object):
def __init__(self, context):
"""The constructor of the SecurityDBApi creates a SiteDBApi, connects to the sitedb instance
and puts it on the context so that it is available for others to use
"""
self.api = SiteDBApi (context)
self.api.connect ()
context.addService (self.api)
def _first(self, cur):
row = cur.fetchone()
if row: return row[0]
else: return None
def getDNFromUsername(self, username):
fields = ("dn")
return self.api.getDataObject (fields,
'''select dn from contact where username = :username''',
{ 'username' : username })
def getPasswordFromUsername(self, username):
fields = ("passwd")
return self.api.getDataObject (fields,
'''select passwd from user_passwd where username = :username''',
{ 'username' : username })
def getUsernameFromDN(self, dn):
fields = ("username")
return self.api.getDataObject (fields, '''select username from contact where dn = :dn''',
{ 'dn' : dn })
def getAllFromID(self, id):
fields = ("dn", "username", "passwd")
return self.api.getDataObject (fields,
'''select c.dn, c.username, p.passwd
from contact c left join user_passwd p on p.username = c.username
where c.id = :id''',
{ 'id' : id })
def getUsernameFromID(self, id):
fields = ("username")
return self.api.getDataObject (fields,
'''select username from contact where id = :id''',
{ 'id' : id })[0]["username"]
def getDNFromID(self, id):
fields = ("dn")
return self.api.getDataObject (fields,
'''select dn from contact where id = :id''',
{ 'id' : id })[0]["dn"]
def getIDFromUsername(self, username):
fields = ("id")
return self.api.getDataObject (fields,
'''select id from contact where username = :username''',
{ 'username' : username })[0]["id"]
def getIDFromDN(self, dn):
fields = ("id")
return self.api.getDataObject (fields,
'''select id from contact where dn = :dn''',
{ 'dn' : dn })[0]["id"]
def getCryptoKey(self, id): #id comes from cookie
fields = ("timestamp", "key")
keyinfo = self.api.getDataObject(fields,
'''select time, cryptkey
from crypt_key
where id = :id''', {"id": id})
return keyinfo[0]
def addCryptoKey(self, key):
#store key to database, return the key id:
if self.api.connectionType() == "sqlite":
self.api.editDataObject("""insert into crypt_key (cryptkey) values (:key)""",
{"key":key})
elif self.api.connectionType() == "oracle" or self.api.connectionType() == "SQLAlchemy":
self.api.editDataObject("""insert into crypt_key (cryptkey, time, id) values (:key, systimestamp, crypt_key_sq.nextval)""",
{"key":key})
fields = ("id")
keyinfo = self.api.getDataObject(fields,
'''select id from crypt_key where cryptkey = :key''', {"key": key})
return keyinfo[0]['id']
def hasGroupResponsibility (self, username, group, role):
self.api.context.Logger().debug( "Does %s have %s for group %s" % (username, role, group) )
fields = ("count")
try:
self.api.context.Logger().debug ( "Connection type = %s" % self.api.connectionType() )
data = ''
if self.api.connectionType() == "sqlite":
self.api.context.Logger().debug( "Has group responsibility" )
groupsplit = group.replace("|", "', '")
self.api.context.Logger().debug( groupsplit )
rolesplit = role.replace("|", "', '")
self.api.context.Logger().debug( rolesplit )
sql = """SELECT count (contact.id)
FROM group_responsibility
JOIN contact on contact.id = group_responsibility.contact
JOIN role on role.id = group_responsibility.role
JOIN user_group on user_group.id = group_responsibility.user_group
WHERE contact.username = :sdb_username
AND role.title in ('%s')
AND user_group.name in ('%s')"""% (rolesplit, groupsplit)
data = self.api.getDataObject (fields, sql, {"sdb_username": username})
self.api.context.Logger().debug( "count = %s" % data )
elif self.api.connectionType() == "oracle":
data = self.api.getDataObject (fields,
"""SELECT count (contact.id)
FROM group_responsibility
JOIN contact on contact.id = group_responsibility.contact
JOIN role on role.id = group_responsibility.role
JOIN user_group on user_group.id = group_responsibility.user_group
WHERE contact.username = :sdb_username
AND REGEXP_LIKE(role.title, :sdb_role)
AND REGEXP_LIKE(user_group.name, :sdb_group)""", {"sdb_username": username,
"sdb_group": group,
"sdb_role": role})
if data[0]["count"]:
return True
except:
return False
return False
# site can be either the site name or the site id
def hasSiteResponsibility (self, username, site, role):
self.api.context.Logger().debug( "Does %s have %s for site %s" % (username, role, site) )
fields = ("count")
try:
data = {}
if site:
if not site.isdigit():
if self.api.connectionType() == "sqlite":
sitesplit = site.replace("|", "', '")
self.api.context.Logger().debug( sitesplit )
rolesplit = role.replace("|", "', '")
self.api.context.Logger().debug( rolesplit )
sql = """SELECT count (contact.id)
FROM site_responsibility
join contact on contact.id = site_responsibility.contact
join role on role.id = site_responsibility.role
join site on site.id = site_responsibility.site
WHERE contact.username = :sdb_username
AND role.title in ('%s')
AND site.name in ('%s')""" % (rolesplit, sitesplit)
data = self.api.getDataObject (fields, sql, {"sdb_username": username})
elif self.api.connectionType() == "oracle":
data = self.api.getDataObject (fields,
"""SELECT count (contact.id)
FROM site_responsibility
join contact on contact.id = site_responsibility.contact
join role on role.id = site_responsibility.role
join site on site.id = site_responsibility.site
WHERE contact.username = :sdb_username
AND REGEXP_LIKE(role.title, :sdb_role)
AND REGEXP_LIKE(site.name, :sdb_site)""", {"sdb_username": username,
"sdb_site": site,
"sdb_role": role})
else:
if self.api.connectionType() == "sqlite":
sitesplit = site.replace("|", "', '")
self.api.context.Logger().debug( sitesplit )
rolesplit = role.replace("|", "', '")
self.api.context.Logger().debug( rolesplit )
sql = """SELECT count (contact.id)
FROM site_responsibility
join contact on contact.id = site_responsibility.contact
join role on role.id = site_responsibility.role
join site on site.id = site_responsibility.site
WHERE contact.username = :sdb_username
AND role.title in ('%s')
AND site.id in ('%s')""" % (rolesplit, sitesplit)
data = self.api.getDataObject (fields, sql, {"sdb_username": username})
elif self.api.connectionType() == "oracle":
data = self.api.getDataObject (fields,
"""SELECT count (contact.id)
FROM site_responsibility
join contact on contact.id = site_responsibility.contact
join role on role.id = site_responsibility.role
join site on site.id = site_responsibility.site
WHERE contact.username = :sdb_username
AND REGEXP_LIKE(role.title, :sdb_role)
AND REGEXP_LIKE(site.id, :sdb_site)""", {"sdb_username": username,
"sdb_site": site,
"sdb_role": role})
if data[0]["count"]:
self.api.context.Logger().debug( "%s has role %s for site %s" % (username, role, site) )
return True
except Exception, e:
self.api.context.Logger().debug( e )
return False
return False