aws-samples
ORACLE MultiUser doesn't support if the GET_GRANTED_DDL contains multiple sqls instead of one
As we have https://github.com/aws-samples/aws-secrets-manager-rotation-lambdas/blob/master/SecretsManagerRDSOracleRotationMultiUser/lambda_function.py#L196,
for grant_type in ['ROLE_GRANT', 'SYSTEM_GRANT', 'OBJECT_GRANT']:
try:
cur.execute("SELECT DBMS_METADATA.GET_GRANTED_DDL('%s', '%s') FROM DUAL" % (grant_type, current_dict['username'].upper()))
results = cur.fetchall()
for row in results:
sql = row[0].read().strip(' \n\t').replace("\"%s\"" % current_dict['username'].upper(), "\"%s\"" % pending_dict['username'])
cur.execute(sql)
except cx_Oracle.DatabaseError:
# If we were unable to find any grants skip this type
pass
Basically this code is to check which privileges the current user has, and then grant them to the pending user. However if the row[0].read returns multiple sqls, it will never commit successfully because cx_Oracle can be used to execute individual statements, one at a time.
In our test situation, DUMMY_USER contains multiple privileges and the sql of SYSTEM_GRANT would become: GRANT "CONNECT" TO "DUMMY_USER_CLONE" GRANT "RESOURCE" TO "DUMMY_USER_CLONE" Then the cur.execute(sql) would raise exception of (We have added a traceback for debugging propose): Traceback (most recent call last): File "/var/task/lambda_function.py", line 204, in set_secret cur.execute(sql) cx_Oracle.DatabaseError: ORA-00933: SQL command not properly ended
So I suggest to do below modification to make it compatible for above situation.
for grant_type in ['ROLE_GRANT', 'SYSTEM_GRANT', 'OBJECT_GRANT']:
try:
cur.execute("SELECT DBMS_METADATA.GET_GRANTED_DDL('%s', '%s') FROM DUAL" % (grant_type, current_dict['username'].upper()))
results = cur.fetchall()
for row in results:
sqls = row[0].read().strip(' \n\t').replace("\"%s\"" % current_dict['username'].upper(), "\"%s\"" % pending_dict['username'])
for sql in sqls.split('\n'):
cur.execute(sql)
except cx_Oracle.DatabaseError:
# If we were unable to find any grants skip this type
pass
