#!/usr/bin/python
# -*- coding: utf-8 -*-

DOCUMENTATION = '''
---
module: oracle_user
short_description: Manage users/schemas in an Oracle database
description:
    - Manage users/schemas in an Oracle database
    - Can be run locally on the controlmachine or on a remote host
version_added: "1.9.1"
options:
    hostname:
        description:
            - The Oracle database host
        required: false
        default: localhost
    port:
        description:
            - The listener port number on the host
        required: false
        default: 1521
    service_name:
        description:
            - The database service name to connect to
        required: true
    user:
        description:
            - The Oracle user name to connect to the database
        required: false
    password:
        description:
            - The Oracle user password for 'user'
        required: false
    mode:
        description:
            - The mode with which to connect to the database
        required: false
        default: normal
        choices: ['normal','sysdba']
    schema:
        description:
            - The schema that you want to manage
        required: false
        default: None
    schema_password:
        description:
            - The password for the new schema. i.e '..identified by password'
        required: false
        default: null
    schema_password_hash:
        description:
            - The password hash for the new schema. i.e '..identified by values 'XXXXXXX'
        required: false
        default: None
    default_tablespace:
        description:
            - The default tablespace for the new schema. The tablespace must exist
        required: false
        default: None
    default_temp_tablespace:
        description:
            - The default tablespace for the new schema. The tablespace must exist
        required: false
        default: None
    update_password:
        description:
            - always will update passwords if they differ. on_create will only set the password for newly created users.
        required: false
        default: always
        choices: ['always','on_create']
    authentication_type:
        description:
            - The type of authentication for the user.
        required: false
        default: password
        choices: ['password','external','global']
    profile:
        description:
            - The profile for the user
        required: false
        default: None
    grants:
        description:
            - The privileges granted to the new schema
        required: false
        default: None
    state:
        description:
            - Whether the user should exist. Absent removes the user, locked/unlocked locks or unlocks the user
        required: False
        default: present
        choices: ['present','absent','locked','unlocked']
notes:
    - cx_Oracle needs to be installed
requirements: [ "cx_Oracle" ]
author: Mikael Sandström, oravirt@gmail.com, @oravirt
'''

EXAMPLES = '''
# Create a new schema on a remote db by running the module on the controlmachine  (i.e: delegate_to: localhost)
oracle_user: hostname=remote-db-server service_name=orcl user=system password=manager schema=myschema schema_password=mypass default_tablespace=test state=present grants="'create session', create any table'"

# Create a new schema on a remote db
oracle_user: hostname=localhost service_name=orcl user=system password=manager schema=myschema schema_password=mypass default_tablespace=test state=present grants=dba

# Drop a schema on a remote db
oracle_user: hostname=localhost service_name=orcl user=system password=manager schema=myschema state=absent


'''

try:
    import cx_Oracle
except ImportError:
    cx_oracle_exists = False
else:
    cx_oracle_exists = True


import hashlib
import string
from binascii import unhexlify
    
def clean_string(item):
    item = item.replace("'","").replace(", ",",").lstrip(" ").rstrip(",").replace("[","").replace("]","")
    return item

def clean_list(item):
    item = [p.replace("'","").replace(", ",",").lstrip(" ").rstrip(",").replace("[","").replace("]","") for p in item]
    return item

# Check if the user/schema exists
def check_user_exists(module, cursor, schema):
    sql = 'select count(*) from dba_users where username = upper(\'%s\')' % schema
    try:
            cursor.execute(sql)
            result = cursor.fetchone()[0]
    except cx_Oracle.DatabaseError as exc:
            error, = exc.args
            msg = error.message+ 'sql: ' + sql
            module.fail_json(msg=msg)
    return result > 0


# Create the user/schema
def create_user(module, cursor, schema, schema_password, schema_password_hash, default_tablespace, default_temp_tablespace, profile, authentication_type, state, container, container_data, grants):
    grants_list=[]
    total_sql = []
    if not (schema):
        msg = 'Error: Missing schema name'
        return False

    if not (schema_password) and authentication_type == 'password':
        if not (schema_password_hash):
            msg = 'Error: Missing schema password or password hash'
            module.fail_json(msg=msg, Changed=False)

    if authentication_type == 'password':
        if (schema_password_hash):
            sql = '''create user %s identified by values '%s' ''' % (schema, schema_password_hash)
        else:
            sql = '''create user %s identified by "%s" ''' % (schema, schema_password)
    elif authentication_type == 'global':
        sql = 'create user %s identified globally ' % (schema)
    elif authentication_type == 'external':
        sql = 'create user %s identified externally ' % (schema)
    elif authentication_type == 'none':
        sql = 'create user %s ' % (schema)

    if (default_tablespace):
        sql += 'default tablespace %s '% default_tablespace
        sql += 'quota unlimited on %s '% default_tablespace

    if (default_temp_tablespace):
        sql += 'temporary tablespace %s '% default_temp_tablespace

    if (profile):
        sql += ' profile %s' % profile

    if container:
        sql += ' container=%s' % (container)

    if state == 'locked':
        sql += ' account lock'

    if state == 'expired':
        sql += ' password expire'

    if state == 'expired & locked':
        sql += ' account lock password expire'

    if container_data:
        altersql = 'alter user %s set container_data=%s container=current' % (schema, container)
        total_sql.append(altersql)

    total_sql.append(sql)

    if module.check_mode:
        msg = 'About to execute: %s' % sql
        module.exit_json(msg=msg, changed=True)

    # module.exit_json(msg=total_sql, changed=True)
    for a in total_sql:
        #module.warn(sql)
        execute_sql(module, cursor, a)

    return True


# Get the current password hash for the user
def get_user_password_hash(module, cursor, schema):
    sql = 'select spare4 from sys.user$ where name = upper(\'%s\')' % schema
    retval = None
    try:
        cursor.execute(sql)
        pwhashresult = cursor.fetchone()[0]
        return pwhashresult
    except cx_Oracle.DatabaseError as exc:
        error, = exc.args
        msg = error.message+ ': sql: ' + sql
        module.fail_json(msg=msg)

    return retval


# Check plaintext password against retrieved hash
# currently works with S:/T: hashes only, returns false otherwise
def password_matches_hash(module, password, password_hash):
    # Based on OPITZ commit 4056d76c67cf4f3da75011fcdcc52c458f410a56
    # S: style hash
    if ('S:' in password_hash):
        h = password_hash.split('S:')[1][:60]
        if not set(h).issubset(string.hexdigits) or len(h) % 2 != 0:
            return False # not a valid hex string character found, should not happen
        hash=password_hash.split('S:')[1][:40]
        salt=password_hash.split('S:')[1][40:60]
        sha1 = hashlib.sha1()
        sha1.update(password.encode('utf-8'))
        sha1.update(unhexlify(salt))
        return hash.upper() == sha1.hexdigest().upper()

    # Based on
    # https://www.trustwave.com/en-us/resources/blogs/spiderlabs-blog/changes-in-oracle-database-12c-password-hashes/
    # T: style hash
    try:
        import pbkdf2
        if ('T:' in password_hash):
            h = password_hash.split('T:')[1][:160]
            if not set(h).issubset(string.hexdigits) or len(h) % 2 != 0:
                return False # not a valid hex string character found, should not happen

            hash=password_hash.split('T:')[1][:128]

            AUTH_VFR_DATA=password_hash.split('T:')[1][128:160].encode('utf-8')
            AUTH_VFR_DATA=unhexlify(AUTH_VFR_DATA)
            salt = AUTH_VFR_DATA + b'AUTH_PBKDF2_SPEEDY_KEY'
            key = pbkdf2.PBKDF2(password, salt, 4096, hashlib.sha512) # Password
            key_64bytes = key.read(64)

            t = hashlib.sha512()
            t.update(key_64bytes)
            t.update(AUTH_VFR_DATA)
            return hash.upper() == t.hexdigest().upper()
    except:
        pass
    
    # no supported hashes found
    return False


# Modify the user/schema
def modify_user(module, cursor, schema, schema_password, schema_password_hash, default_tablespace, default_temp_tablespace, update_password, profile, authentication_type, state, container_data):

    sql_get_curr_def = 'select lower(account_status) as account_status'
    sql = 'alter user %s' % schema

    old_pw_hash = get_user_password_hash(module, cursor, schema)
    wanted_set = set()
    
    if update_password == 'always':
        if authentication_type == 'password':
            if schema_password_hash and schema_password_hash != old_pw_hash:
                sql += ''' identified by values '%s' ''' % (schema_password_hash)
                wanted_set.add(('PASSWORD_HASH', schema_password_hash))
            elif schema_password and not password_matches_hash(module, schema_password, old_pw_hash):
                sql += ''' identified by "%s" ''' % (schema_password)
                wanted_set.add(('PASSWORD_HASH', '********'))
        elif authentication_type == 'external':
            sql += ' identified externally '
            sql_get_curr_def += ' ,lower(authentication_type) as authentication_type'
        elif authentication_type == 'global':
            sql += ' identified globally '
            sql_get_curr_def += ' ,lower(authentication_type) as authentication_type'
        elif authentication_type == 'none':
            sql_get_curr_def += ' ,lower(authentication_type) as authentication_type'

    if default_tablespace:
        sql += ' default tablespace %s' % default_tablespace
        sql += ' quota unlimited on %s '% default_tablespace
        sql_get_curr_def += ' ,lower(default_tablespace) as default_tablespace'

    if default_temp_tablespace:
        sql += ' temporary tablespace %s ' % default_temp_tablespace
        sql_get_curr_def += ' ,lower(temporary_tablespace) as temporary_tablespace'

    if profile:
        sql += ' profile %s ' % profile
        sql_get_curr_def += ' ,lower(profile) as profile'

    want_account_status = None
    if state == 'present' or state == 'unlocked':
        want_account_status = ('ACCOUNT_STATUS', 'open')
        sql += ' account unlock'

    elif state == 'locked':
        want_account_status = ('ACCOUNT_STATUS', state.lower())
        sql += ' account lock'

    elif state == 'expired':
        want_account_status = ('ACCOUNT_STATUS', state.lower())
        sql += ' password expire'

    elif state == 'expired & locked':
        want_account_status = ('ACCOUNT_STATUS', state.lower())
        sql += ' account lock password expire'

    if want_account_status:
        wanted_set.add(want_account_status)

    if authentication_type != 'password' and update_password == 'always':
        wanted_set.add(('AUTHENTICATION_TYPE', authentication_type.lower()))

    if default_tablespace:
        wanted_set.add(('DEFAULT_TABLESPACE', default_tablespace.lower()))

    if default_temp_tablespace:
        wanted_set.add(('TEMPORARY_TABLESPACE', default_temp_tablespace.lower()))

    if profile:
        wanted_set.add(('PROFILE', profile.lower()))

    sql_get_curr_def += ' from dba_users where username = upper(\'%s\')' % schema

    current_set = execute_sql_get_as_set(module, cursor, sql_get_curr_def)

    # wanted list is subset of current settings, do not do anything
    if wanted_set.issubset(current_set):
        module.exit_json(msg='The schema (%s) is in the intented state' % (schema), changed=False)
        
    if module.check_mode:
        msg = 'About to execute: %s' % sql
        module.exit_json(msg=msg, changed=True)

    # do the complete change -> exit with change=True
    # module.warn(msg=sql)
    execute_sql(module, cursor, sql)
    module.exit_json(msg='Successfully altered the user (%s) / %s => %s' % (schema, str(current_set), str(wanted_set)), changed=True)

    return True

# Run the actual modification
def execute_sql(module, cursor, sql):

    try:
        cursor.execute(sql)
    except cx_Oracle.DatabaseError as exc:
        error, = exc.args
        msg = 'Blergh, something went wrong while executing sql - %s sql: %s' % (error.message, sql)
        module.fail_json(msg=msg, changed=False)

    return True

def execute_sql_get(module, cursor, sql):

    try:
            cursor.execute(sql)
            result = cursor.fetchall()
    except cx_Oracle.DatabaseError as exc:
            error, = exc.args
            msg = error.message+ ': sql: ' + sql
            module.fail_json(msg=msg)

    return result


def execute_sql_get_as_set(module, cursor, sql):

    try:
            cursor.execute(sql)
            columns = [column[0] for column in cursor.description]
            results = set()
            for row in cursor.fetchall():        
                results.update(set(zip(columns, row)))
    except cx_Oracle.DatabaseError as exc:
            error, = exc.args
            msg = error.message+ ': sql: ' + sql
            module.fail_json(msg=msg)

    return results


# Drop the user
def drop_user(module, cursor, schema):
    black_list = ['sys','system','dbsnmp']
    if schema.lower() in black_list:
        msg = 'Trying to drop an internal user: %s. Not allowed' % schema
        module.fail_json(msg=msg, changed=False)

    sql = 'drop user %s cascade' % schema

    if module.check_mode:
        msg = 'About to execute: %s' % sql
        module.exit_json(msg=msg, changed=True)

    try:
        cursor.execute(sql)
    except cx_Oracle.DatabaseError as exc:
        error, = exc.args
        msg = 'Blergh, something went wrong while dropping the schema - %s sql: %s' % (error.message, sql)
        module.fail_json(msg=msg)

    return True


def main():

    msg = ['']
    module = AnsibleModule(
        argument_spec = dict(
            oracle_home   = dict(required=False, aliases=['oh']),
            hostname      = dict(default='localhost'),
            port          = dict(default=1521, type="int"),
            service_name  = dict(required=False, aliases = ['tns']),
            user          = dict(required=False),
            password      = dict(required=False, no_log=True),
            mode          = dict(default='normal', choices=["normal","sysdba"]),
            schema        = dict(default=None,aliases=['name']),
            schema_password  = dict(default=None, no_log=True),
            schema_password_hash  = dict(default=None, no_log=True),
            state         = dict(default="present", choices=["present", "absent", "locked", "unlocked", "expired", "expired & locked"]),
            default_tablespace = dict(default=None),
            default_temp_tablespace = dict(default=None),
            update_password = dict(default='always', choices=['on_create','always'], no_log=False),
            profile        = dict(default=None),
            authentication_type = dict(default='password', choices=['password','external','global', 'none']),
            container      = dict(default=None),
            container_data = dict(default=None),
            grants         = dict(default=None, type="list")

        ),
        mutually_exclusive=[['schema_password', 'schema_password_hash']],
        supports_check_mode=True
    )

    oracle_home = module.params["oracle_home"]
    hostname = module.params["hostname"]
    port = module.params["port"]
    service_name = module.params["service_name"]
    user = module.params["user"]
    password = module.params["password"]
    mode = module.params["mode"]
    schema = module.params["schema"]
    schema_password = module.params["schema_password"]
    schema_password_hash = module.params["schema_password_hash"]
    state = module.params["state"]
    default_tablespace = module.params["default_tablespace"]
    default_temp_tablespace = module.params["default_temp_tablespace"]
    update_password = module.params["update_password"]
    profile  = module.params["profile"]
    authentication_type = module.params["authentication_type"]
    container = module.params["container"]
    container_data = module.params["container_data"]
    grants = module.params["grants"]

    if not cx_oracle_exists:
        module.fail_json(msg="The cx_Oracle module is required. 'pip install cx_Oracle' should do the trick. If cx_Oracle is installed, make sure ORACLE_HOME is set")

    conn = oracle_connect(module)
    cursor = conn.cursor()

    if state not in ('absent'):
        if not check_user_exists(module, cursor, schema):
            if create_user(module, cursor, schema, schema_password, schema_password_hash, default_tablespace, default_temp_tablespace, profile, authentication_type, state, container, container_data, grants):
                msg = 'The schema %s has been created successfully' % (schema)
                module.exit_json(msg=msg, changed=True)
        else:
            modify_user(module, cursor, schema, schema_password, schema_password_hash, default_tablespace, default_temp_tablespace, update_password, profile, authentication_type, state, container_data)

    # elif state in ('unlocked','locked', ''):
    #     if not check_user_exists(module, cursor, schema):
    #         # if create_user(module, cursor, schema, schema_password, schema_password_hash, default_tablespace, default_temp_tablespace, profile, authentication_type, state, container, grants):
    #         msg = 'The schema %s doesn\'t exist' % schema
    #         module.fail_json(msg=msg, changed=False)
    #     else:
    #         modify_user(module, cursor, schema, schema_password, schema_password_hash, default_tablespace, default_temp_tablespace, update_password, profile, authentication_type, state)


    elif state == 'absent':
        if check_user_exists(module, cursor, schema):
            if drop_user(module, cursor, schema):
                msg = 'The schema (%s) has been dropped successfully' % schema
                module.exit_json(msg=msg, changed=True)
        else:
            module.exit_json(msg='The schema (%s) doesn\'t exist' % schema, changed=False)


    module.exit_json(msg='Undhandled exit', changed=False)


from ansible.module_utils.basic import *

# In thise we do import from local project project sub-directory <project-dir>/module_utils
# While this file is placed in <project-dir>/library
# No colletions are used
try:
    from ansible.module_utils.oracle_utils import oracle_connect
except:
    pass

# In thise we do import from collections
try:
    from ansible_collections.ibre5041.ansible_oracle_modules.plugins.module_utils.oracle_utils import oracle_connect
except:
    pass


if __name__ == '__main__':
    main()
