def set_password_for_login()

in lambda_functions/secretsmanager/RDSSQLServer-Multiuser.py [0:0]

• 26 lines of code
• 14 McCabe index (conditional complexity)

def set_password_for_login(cursor, current_db, current_login, pending_dict):
    """Runs various SQL statements in order to set the login password to that of the pending secret dictionary

    This helper function runs SQL statements in order to set the login password to that of the pending secret dictionary

    Args:
        cursor (pymssql.Cursor): The pymssql Cursor object

        current_db (string): The current database that we are connected to

        current_login (string): The current user login

        pending_dict (dict): The Secret Dictionary for the pending secret

    Raises:
        pymssql.OperationalError: If there are any errors running the SQL statements

    """
    # Check if the login exists, if not create it and grant it all permissions from the current user
    # If the user exists, just update the password
    cursor.execute("SELECT name FROM sys.server_principals WHERE name = %s", pending_dict['username'])
    if len(cursor.fetchall()) == 0:
        # Create the new login
        create_login = "CREATE LOGIN %s" % pending_dict['username']
        cursor.execute(create_login + " WITH PASSWORD = %s", pending_dict['password'])

        # Only handle server level permissions if we are connected the the master DB
        if current_db == 'master':
            # Loop through the types of server permissions and grant them to the new login
            query = "SELECT state_desc, permission_name FROM sys.server_permissions perm "\
                    "JOIN sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id "\
                    "WHERE prin.name = '%s'" % current_login
            cursor.execute(query)
            for row in cursor.fetchall():
                if row['state_desc'] == 'GRANT_WITH_GRANT_OPTION':
                    cursor.execute("GRANT %s TO %s WITH GRANT OPTION" % (row['permission_name'], pending_dict['username']))
                else:
                    cursor.execute("%s %s TO %s" % (row['state_desc'], row['permission_name'], pending_dict['username']))

        # We do not create user objects in the master database
        else:
            # Get the user for the current login and generate the alt user
            cursor.execute("SELECT dbprin.name FROM sys.database_principals dbprin JOIN sys.server_principals sprin ON dbprin.sid = sprin.sid WHERE sprin.name = %s", current_login)
            cur_user = cursor.fetchall()[0]['name']
            alt_user = get_alt_username(cur_user)

            # Check if the user exists. If not, create it
            cursor.execute("SELECT name FROM sys.database_principals WHERE name = %s", alt_user)
            if len(cursor.fetchall()) == 0:
                cursor.execute("CREATE USER %s FOR LOGIN %s" % (alt_user, pending_dict['username']))

            apply_database_permissions(cursor, cur_user, pending_dict['username'])

    else:
        alter_stmt = "ALTER LOGIN %s" % pending_dict['username']
        cursor.execute(alter_stmt + " WITH PASSWORD = %s", pending_dict['password'])