def AddClientLabels(self, client_id, owner, labels, cursor=None):
"""Attaches a list of user labels to a client."""
cid = db_utils.ClientIDToInt(client_id)
labels = set(labels)
args = [(cid, mysql_utils.Hash(owner), owner, label) for label in labels]
args = list(collection.Flatten(args))
query = """
INSERT IGNORE INTO client_labels
(client_id, owner_username_hash, owner_username, label)
VALUES {}
""".format(", ".join(["(%s, %s, %s, %s)"] * len(labels)))
try:
cursor.execute(query, args)
except MySQLdb.IntegrityError as e:
raise db.UnknownClientError(client_id, cause=e)
def AddClientKeywords(self, client_id, keywords, cursor=None):
"""Associates the provided keywords with the client."""
cid = db_utils.ClientIDToInt(client_id)
keywords = set(keywords)
args = [(cid, mysql_utils.Hash(kw), kw) for kw in keywords]
args = list(collection.Flatten(args))
query = """
INSERT INTO client_keywords (client_id, keyword_hash, keyword)
VALUES {}
ON DUPLICATE KEY UPDATE timestamp = NOW(6)
""".format(", ".join(["(%s, %s, %s)"] * len(keywords)))
try:
cursor.execute(query, args)
except MySQLdb.IntegrityError as e:
raise db.UnknownClientError(client_id, cause=e)
def WriteSignedBinaryReferences(self, binary_id, references, cursor=None):
"""Writes blob references for a signed binary to the DB."""
args = {
"binary_type": binary_id.binary_type.SerializeToWireFormat(),
"binary_path": binary_id.path,
"binary_path_hash": mysql_utils.Hash(binary_id.path),
"blob_references": references.SerializeToBytes()
}
query = """
INSERT INTO signed_binary_references {cols}
VALUES {vals}
ON DUPLICATE KEY UPDATE
blob_references = VALUES(blob_references)
""".format(cols=mysql_utils.Columns(args),
vals=mysql_utils.NamedPlaceholders(args))
cursor.execute(query, args)
def ReadApprovalRequest(self,
requestor_username,
approval_id,
cursor=None):
"""Reads an approval request object with a given id."""
query = ("""
SELECT
ar.approval_id,
ar.timestamp,
ar.approval_request,
u.username,
ag.timestamp
FROM approval_request ar
LEFT JOIN approval_grant ag USING (username_hash, approval_id)
LEFT JOIN grr_users u ON u.username_hash = ag.grantor_username_hash
WHERE ar.approval_id = %s AND ar.username_hash = %s
""")
cursor.execute(query, [
_ApprovalIDToInt(approval_id),
mysql_utils.Hash(requestor_username)
])
res = cursor.fetchall()
if not res:
raise db.UnknownApprovalRequestError("Approval '%s' not found." %
approval_id)
approval_id_int, timestamp, approval_request_bytes, _, _ = res[0]
approval_request = mysql_utils.StringToRDFProto(
rdf_objects.ApprovalRequest, approval_request_bytes)
approval_request.approval_id = _IntToApprovalID(approval_id_int)
approval_request.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp)
for _, _, _, grantor_username, timestamp in res:
if not grantor_username:
continue
# Note: serialized approval_request objects are guaranteed to not
# have any grants.
approval_request.grants.append(
rdf_objects.ApprovalGrant(
grantor_username=grantor_username,
timestamp=mysql_utils.MysqlToRDFDatetime(timestamp)))
return approval_request
def ReadAPIAuditEntries(self,
username=None,
router_method_names=None,
min_timestamp=None,
max_timestamp=None,
cursor=None):
"""Returns audit entries stored in the database."""
query = """SELECT details, timestamp
FROM api_audit_entry
WHERE_PLACEHOLDER
ORDER BY timestamp ASC
"""
conditions = []
values = []
where = ""
if username is not None:
conditions.append("username_hash = %s")
values.append(mysql_utils.Hash(username))
if router_method_names:
placeholders = ["%s"] * len(router_method_names)
placeholders = ", ".join(placeholders)
conditions.append("router_method_name IN (%s)" % placeholders)
values.extend(router_method_names)
if min_timestamp is not None:
conditions.append("timestamp >= %s")
values.append(mysql_utils.RDFDatetimeToMysqlString(min_timestamp))
if max_timestamp is not None:
conditions.append("timestamp <= %s")
values.append(mysql_utils.RDFDatetimeToMysqlString(max_timestamp))
if conditions:
where = "WHERE " + " AND ".join(conditions)
query = query.replace("WHERE_PLACEHOLDER", where)
cursor.execute(query, values)
return [
_AuditEntryFromRow(details, timestamp)
for details, timestamp in cursor.fetchall()
]
def UpdateUserNotifications(self,
username,
timestamps,
state=None,
cursor=None):
"""Updates existing user notification objects."""
query = ("UPDATE user_notification "
"SET notification_state = %s "
"WHERE username_hash = %s AND timestamp IN {}").format(
mysql_utils.Placeholders(len(timestamps)))
args = [
int(state),
mysql_utils.Hash(username),
] + [mysql_utils.RDFDatetimeToMysqlString(t) for t in timestamps]
cursor.execute(query, args)
def WriteUserNotification(self, notification, cursor=None):
"""Writes a notification for a given user."""
# Copy the notification to ensure we don't modify the source object.
args = {
"username_hash":
mysql_utils.Hash(notification.username),
"notification_state":
int(notification.state),
"notification":
notification.SerializeToString(),
}
query = "INSERT INTO user_notification {columns} VALUES {values}".format(
columns=mysql_utils.Columns(args),
values=mysql_utils.NamedPlaceholders(args))
try:
cursor.execute(query, args)
except MySQLdb.IntegrityError:
raise db.UnknownGRRUserError(notification.username)
def AddClientKeywords(self, client_id, keywords, cursor=None):
"""Associates the provided keywords with the client."""
cid = mysql_utils.ClientIDToInt(client_id)
now = datetime.datetime.utcnow()
keywords = set(keywords)
args = [(cid, mysql_utils.Hash(kw), kw, now) for kw in keywords]
args = list(itertools.chain.from_iterable(args)) # Flatten.
query = """
INSERT INTO client_keywords
(client_id, keyword_hash, keyword, timestamp)
VALUES {}
ON DUPLICATE KEY UPDATE timestamp = VALUES(timestamp)
""".format(", ".join(["(%s, %s, %s, %s)"] * len(keywords)))
try:
cursor.execute(query, args)
except MySQLdb.IntegrityError as e:
raise db.UnknownClientError(client_id, cause=e)
def WriteYaraSignatureReference(
self,
blob_id,
username,
cursor,
):
"""Marks specified blob id as a YARA signature."""
query = """
INSERT IGNORE INTO yara_signature_references
VALUES (%(blob_id)s, %(username_hash)s, NOW(6))
"""
args = {
"blob_id": blob_id.AsBytes(),
"username_hash": mysql_utils.Hash(username),
}
try:
cursor.execute(query, args)
except MySQLdb.IntegrityError:
raise db.UnknownGRRUserError(username=username)
def ReadSignedBinaryReferences(self, binary_id, cursor):
"""Reads blob references for the signed binary with the given id."""
cursor.execute(
"""
SELECT blob_references, timestamp
FROM signed_binary_references
WHERE binary_type = %s AND binary_path_hash = %s
""", [
binary_id.binary_type.SerializeToDataStore(),
mysql_utils.Hash(binary_id.path)
])
row = cursor.fetchone()
if not row:
raise db.UnknownSignedBinaryError(binary_id)
raw_references, timestamp = row
references = rdf_objects.BlobReferences.FromSerializedString(
raw_references)
return references, mysql_utils.MysqlToRDFDatetime(timestamp)
def ListClientsForKeywords(self, keywords, start_time=None, cursor=None):
"""Lists the clients associated with keywords."""
keywords = set(keywords)
hash_to_kw = {mysql_utils.Hash(kw): kw for kw in keywords}
result = {kw: [] for kw in keywords}
query = """
SELECT keyword_hash, client_id
FROM client_keywords
WHERE keyword_hash IN ({})
""".format(", ".join(["%s"] * len(result)))
args = list(iterkeys(hash_to_kw))
if start_time:
query += " AND timestamp >= %s"
args.append(mysql_utils.RDFDatetimeToMysqlString(start_time))
cursor.execute(query, args)
for kw_hash, cid in cursor.fetchall():
result[hash_to_kw[kw_hash]].append(mysql_utils.IntToClientID(cid))
return result
def WriteApprovalRequest(self, approval_request, cursor=None):
"""Writes an approval request object."""
# Copy the approval_request to ensure we don't modify the source object.
approval_request = approval_request.Copy()
# Generate random approval id.
approval_id_int = random.UInt64()
now_str = mysql_utils.RDFDatetimeToMysqlString(
rdfvalue.RDFDatetime.Now())
grants = approval_request.grants
approval_request.grants = None
args = {
"username_hash":
mysql_utils.Hash(approval_request.requestor_username),
"approval_type":
int(approval_request.approval_type),
"subject_id":
approval_request.subject_id,
"approval_id":
approval_id_int,
"timestamp":
now_str,
"expiration_time":
mysql_utils.RDFDatetimeToMysqlString(
approval_request.expiration_time),
"approval_request":
approval_request.SerializeToString()
}
query = (
"INSERT INTO approval_request {columns} VALUES {values}".format(
columns=mysql_utils.Columns(args),
values=mysql_utils.NamedPlaceholders(args)))
cursor.execute(query, args)
for grant in grants:
self._GrantApproval(approval_request.requestor_username,
approval_id_int, grant.grantor_username,
now_str, cursor)
return _IntToApprovalID(approval_id_int)
def ReadUserNotifications(self,
username,
state=None,
timerange=None,
cursor=None):
"""Reads notifications scheduled for a user within a given timerange."""
query = ("SELECT UNIX_TIMESTAMP(timestamp), "
" notification_state, notification "
"FROM user_notification "
"WHERE username_hash = %s ")
args = [mysql_utils.Hash(username)]
if state is not None:
query += "AND notification_state = %s "
args.append(int(state))
if timerange is not None:
time_from, time_to = timerange # pylint: disable=unpacking-non-sequence
if time_from is not None:
query += "AND timestamp >= FROM_UNIXTIME(%s) "
args.append(mysql_utils.RDFDatetimeToTimestamp(time_from))
if time_to is not None:
query += "AND timestamp <= FROM_UNIXTIME(%s) "
args.append(mysql_utils.RDFDatetimeToTimestamp(time_to))
query += "ORDER BY timestamp DESC "
ret = []
cursor.execute(query, args)
for timestamp, state, notification_ser in cursor.fetchall():
n = rdf_objects.UserNotification.FromSerializedString(
notification_ser)
n.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp)
n.state = state
ret.append(n)
return ret
def ReadApprovalRequests(
self,
requestor_username,
approval_type,
subject_id=None,
include_expired=False,
cursor=None) -> Sequence[rdf_objects.ApprovalRequest]:
"""Reads approval requests of a given type for a given user."""
query = """
SELECT
ar.approval_id,
UNIX_TIMESTAMP(ar.timestamp),
ar.approval_request,
u.username,
UNIX_TIMESTAMP(ag.timestamp)
FROM approval_request ar
LEFT JOIN approval_grant AS ag USING (username_hash, approval_id)
LEFT JOIN grr_users u ON u.username_hash = ag.grantor_username_hash
WHERE ar.username_hash = %s AND ar.approval_type = %s
"""
args = [mysql_utils.Hash(requestor_username), int(approval_type)]
if subject_id:
query += " AND ar.subject_id = %s"
args.append(subject_id)
query += " ORDER BY ar.approval_id"
ret = []
now = rdfvalue.RDFDatetime.Now()
cursor.execute(query, args)
for approval_request in _ResponseToApprovalsWithGrants(
cursor.fetchall()):
if include_expired or approval_request.expiration_time >= now:
ret.append(approval_request)
return ret
def WriteSignedBinaryReferences(self, binary_id, references, cursor):
"""Writes blob references for a signed binary to the DB."""
args = {
"binary_type":
binary_id.binary_type.SerializeToDataStore(),
"binary_path":
binary_id.path,
"binary_path_hash":
mysql_utils.Hash(binary_id.path),
"timestamp":
mysql_utils.RDFDatetimeToMysqlString(rdfvalue.RDFDatetime.Now()),
"blob_references":
references.SerializeToString()
}
query = """
INSERT INTO signed_binary_references {cols}
VALUES {vals}
ON DUPLICATE KEY UPDATE
timestamp = VALUES(timestamp),
blob_references = VALUES(blob_references)
""".format(cols=mysql_utils.Columns(args),
vals=mysql_utils.NamedPlaceholders(args))
cursor.execute(query, args)
def WriteGRRUser(self,
username,
password=None,
ui_mode=None,
canary_mode=None,
user_type=None,
email=None,
cursor=None):
"""Writes user object for a user with a given name."""
values = {
"username": username,
"username_hash": mysql_utils.Hash(username)
}
if password is not None:
values["password"] = password.SerializeToBytes()
if ui_mode is not None:
values["ui_mode"] = int(ui_mode)
if canary_mode is not None:
# TODO(amoser): This int conversion is dirty but necessary with
# the current MySQL driver.
# TODO: We can remove this once the bug is fixed.
values["canary_mode"] = int(bool(canary_mode))
if user_type is not None:
values["user_type"] = int(user_type)
if email is not None:
values["email"] = email
query = "INSERT INTO grr_users {cols} VALUES {vals}".format(
cols=mysql_utils.Columns(values),
vals=mysql_utils.NamedPlaceholders(values))
updates = ", ".join("{0} = VALUES({0})".format(col) for col in values)
query += " ON DUPLICATE KEY UPDATE " + updates
cursor.execute(query, values)
def WriteApprovalRequest(self, approval_request, cursor=None):
"""Writes an approval request object."""
# Copy the approval_request to ensure we don't modify the source object.
approval_request = approval_request.Copy()
# Generate random approval id.
approval_id_int = random.UInt64()
grants = approval_request.grants
approval_request.grants = None
expiry_time = approval_request.expiration_time
args = {
"username_hash":
mysql_utils.Hash(approval_request.requestor_username),
"approval_type": int(approval_request.approval_type),
"subject_id": approval_request.subject_id,
"approval_id": approval_id_int,
"expiration_time": mysql_utils.RDFDatetimeToTimestamp(expiry_time),
"approval_request": approval_request.SerializeToString()
}
query = """
INSERT INTO approval_request (username_hash, approval_type,
subject_id, approval_id, expiration_time,
approval_request)
VALUES (%(username_hash)s, %(approval_type)s,
%(subject_id)s, %(approval_id)s, FROM_UNIXTIME(%(expiration_time)s),
%(approval_request)s)
"""
cursor.execute(query, args)
for grant in grants:
self._GrantApproval(approval_request.requestor_username,
approval_id_int, grant.grantor_username,
cursor)
return _IntToApprovalID(approval_id_int)
def AddUser(self, connection, user, password):
cursor = connection.cursor()
cursor.execute(
"INSERT INTO grr_users (username, username_hash, password) "
"VALUES (%s, %s, %s)", (user, mysql_utils.Hash(user), bytes(password)))
cursor.close()
