Troubleshooting
Problem
SQL0551 (-551) returned for non-DBADM users when they try using JSON via db2nosql.sh
Symptom
Trying to use db2nosql.sh receives SQL0551 / -551 (authorization error).
/home/snoopy/sqllib/json/bin: ./db2nosql.sh -user snoopy -url
"jdbc:db2://localhost:50100/jsondb:traceLevel=-1;traceFile=/tmp/jsontrac
e.txt;" -password xxxx
JSON Command Shell Setup and Launcher.
This batch script assumes your JRE is 1.5 and higher. 1.6 will mask your
password.
Type db2nosql.sh -help to see options
IBM DB2 NoSQL JSON API 1.1.0.0 build 1.4.173
Licensed Materials - Property of IBM
(c) Copyright IBM Corp. 2013 All Rights Reserved.
nosql>Type your JSON query and hit <ENTER>
nosql>Type help() or help for usage information. All commands are case
sensitive.
[nosql][1.4.173] CDJSN1155E Verification failed for DB2 JSON enablement.
Reason "10", SQLCode "-551"
Resolving The Problem
Grant the non-DBADM user, in our example "snoopy" the required privileges for JSON objects by running the script below
db2 -tvf grantperm.db2
grantperm.db2
-- Change to your JSON database name
connect to jsondb;
grant select on systools.sysjson_index to user snoopy;
grant insert on systools.sysjson_index to user snoopy;
grant delete on systools.sysjson_index to user snoopy;
grant execute on function systools.json_val2 to user snoopy;
grant execute on function systools.json_binary to user snoopy;
grant execute on function systools.json_binary2 to user snoopy;
grant execute on function systools.json_table to user snoopy;
grant execute on function systools.json_table_binary to user snoopy;
grant execute on function systools.json_len to user snoopy;
grant execute on function systools.json_type to user snoopy;
grant execute on function systools.json2bson to user snoopy;
grant execute on function systools.bson2json to user snoopy;
grant execute on function systools.bson_validate to user snoopy;
grant execute on function systools.json_get_pos_arr_index to user
snoopy;
grant execute on function systools.json_update(BLOB(16M), VARCHAR(256),
VARCHAR(32672), VARCHAR(32672) FOR BIT DATA, INTEGER) to user snoopy;
grant execute on function systools.json_update(BLOB(16M),
VARCHAR(32672)) to user snoopy;
grant execute on specific function systools.REG_MATCHES_NOFLAGS to user
snoopy;
grant execute on specific function systools.REG_MATCHES to user snoopy;
connect reset;
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21982359