Database Connector- Error in Grant Access Json

[Mortal]

Hi,

We have MySQL DB 5.7 version which supports saviynt 23.8 version and I'm trying to create and grant access to a db account. I'm able to create account but grant access is failing in the same request. 

GrantAccessJSON: 

{
"GrantAccess" : [
"INSERT INTO
user_access(username, entitlementkey, entitlement_value) VALUES(${users.firstname},${entitlement_values.entitlement_valuekey},${entitlement_values.entitlement_value})" ,
" call GrantPrivilegesByEntitlement('${accountName}','${entitlement_values.entitlement_value}')"
]
}

ERROR: error while converting jsonstringtomap.

We created a stored procedure GrantPrivilegesByEntitlement, we are able to grant access in db itself with same stored procedure . but from saviynt this same query is not getting executed.

PFA screenshot of the stored procedure.

[Dhruv_S]

Hi @Mortal 

Which connector you are using?

If you are using DB connector then you can follow the below links for correct syntax.

Configuring the Integration for Provisioning and Deprovisioning (saviyntcloud.com)

Solved: DB connector- GrantaccessJSON syntax error - Saviynt Forums - 48864

Solved: DB Connector GrantAccessJSON - Saviynt Forums - 14413

If you still face an issue, please share log snippet.

Regards,

Dhruv Sharma

[Mortal]

Hi @Dhruv_S ,

I checked those documents and used query mentioned in the above forums:

{"Roles" : "${task.entitlement_valueKey.entitlement_value.toString().contains('#Y#Y#Y#Y')?'GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO \"'+accountName+'\";':'GRANT SELECT ON *.* TO \"'+accountName+'\";'}"}

but still getting "Error while converting JsonStringToMap" in logs and permissions are not granted to that user account. We are using MySQL 5.7 version so does it affect grant json because of that?  I have attached the log file for that account and access request. 

Thanks.

[Dhruv_S]

Hi @Mortal 

As per the attached logs I can see below errors

The key value in JSON is Roles=${task.entitlement_valueKey.entitlement_value.toString().contains('#Y#Y#Y#Y')?'GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO "'+accountName+'";':'GRANT SELECT ON *.* TO "'+accountName+'";'}"
"2023-12-05T09:32:31.048+00:00","ecm-worker","provisoning.DBProvisioningService","quartzScheduler_Worker-8","DEBUG","Exit fetchQueries"
"2023-12-05T09:32:31.048+00:00","ecm-worker","provisoning.DBProvisioningService","quartzScheduler_Worker-8","ERROR","Assigning Access null to account John"

Please try with actual entitlement in highlighted contains block and see if it works.

{"Roles" : "${task.entitlement_valueKey.entitlement_value.toString().contains('#Y#Y#Y#Y')?'GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO \"'+accountName+'\";':'GRANT SELECT ON *.* TO \"'+accountName+'\";'}"}

Regards,

Dhruv Sharma

[Mortal]

Hi @Dhruv_S,

I made changes according to what you have ask in the above query. But facing same error "error while converting JsonStringToMap" and I didn't understand why it's taking grant access query for two times. First it will show which entitlement we selected and for second time it will show null value.

I have attached the log file of that requested user. Please do check and let me know.

Thanks.

[Dhruv_S]

Hi @Mortal 

As per the logs, it seems it is not even able to fetch the account and entitlement which may happen if the add access task is getting executed before the account creation. 

Could you please try to execute the grant access on an existing account in DB and see if it works.

Based on the results, we can advise further.

Regards,

Dhruv Sharma

[Mortal]

Hi @Dhruv_S ,

The add access task is getting execute after the account creation. I have created a stored procedure as well but granting access getting executed from DB but unable to triggered from Saviynt. We were able to provide access/ privileges to a user from DB. But when a user trying to request for that entitlement/ privileges the query is not getting triggered. I have checked the DB logs as well but the process is quitting before taking any query.

I have provided the DB logs for the reference as well.

[ Connect    offshore_user@ec2-54-210-37-27.compute-1.amazonaws.com on offshore_db using TCP/IP

8096 Query    SHOW GLOBAL STATUS

8114 Query    /* mysql-connector-java-5.1.32 ( Revision: jess.balint@oracle.com-20140716155848-mlwabor66widht1n ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'

8114 Query    /* mysql-connector-java-5.1.32 ( Revision: jess.balint@oracle.com-20140716155848-mlwabor66widht1n ) */SELECT @@session.auto_increment_increment

8114 Query    SET NAMES latin1

8114 Query    SET character_set_results = NULL

Query    SET autocommit=1

8114 Quit ]

[Dhruv_S]

Hi @Mortal 

From the previous logs, we could see that the possibly the below highlighted portion of syntax is not getting resolved.

{"Roles" : "${task.entitlement_valueKey.entitlement_value.toString().contains('#Y#Y#Y#Y')?'GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO \"'+accountName+'\";':'GRANT SELECT ON *.* TO \"'+accountName+'\";'}"}

I would suggest you try once with a hardcoded value and see if it works. It will help us identify if the issue is in that portion of syntax.

{"Roles" : "${<Actual Entitlement value here>.contains('#Y#Y#Y#Y')?'GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO \"'+accountName+'\";':'GRANT SELECT ON *.* TO \"'+accountName+'\";'}"}

Regards,

Dhruv Sharma

[Mortal]

Hi @Dhruv_S ,

I tried using above syntax for grant access by hardcoding the entitlement value but getting the same error. I am sharing the logs as well.

[Dhruv_S]

Hi @Mortal 

Thanks for your patience and co-operation. Could you please try once with below syntax as well and let me know if any of them works.

Query1

{"DBGRANT" : ["GRANT ${task?.entitlement_valueKey?.entitlement_value} ON *.* TO '${accountName}';","FLUSH PRIVILEGES;"]}

Query2

{
"entitlement_type1": "call grantent1('${accountName}', '${task.entitlement_valueKey.customproperty7}', '${task.entitlement_valueKey.entitlement_value}')",
"entitlement_type2": "call grantent2('${accountName}', '${task.entitlement_valueKey.customproperty7}', '${task.entitlement_valueKey.entitlement_value}')"
}