Ranger is a framework to enable, monitor and manage comprehensive data security across the Hadoop platform. Ranger support is available for Big SQL service via Big SQL Ranger plugin. Big SQL Ranger plugin can store and manage access control rules for database objects in Big SQL using security policies. These policies could be created and managed using Ranger User Interface or REST APIs.
Ranger REST APIs are useful for anyone who wants to automate policy management. Ranger policies for Big SQL could be created, updated, deleted and searched using Ranger REST APIs. This blog presents some examples of creating, updating, deleting and searching policies for Big SQL service using Ranger REST APIs.
To get details about Ranger Security support for Big SQL, please check: https://www.ibm.com/support/knowledgecenter/en/SSCRJT_5.0.2/com.ibm.swg…
Creating a new Policy:
Request type ‘POST’ is used to create a new ranger policy in Big SQL. The following policy provides ‘create’ and ‘select’ permission to user user1 on all tables and views of Employees schema. A response code ‘200 Ok’ is received if create is successful. Ranger administrator privilege is required to submit the command. Ranger administrator credential admin:admin is used for this example.
curl -iv -u admin:admin -H "Content-Type: application/json" -d '{ "isEnabled":true,"service":"ranger_bigsql","name":"emp_policy","description":"Policy for employees schema access","isAuditEnabled":true,"resources":{"schema":{"values":["Employees"],"isExcludes":false,"isRecursive":false},"table":{"values":["*"],"isExcludes":false,"isRecursive":false}},"policyItems":[{"accesses":[{"type":"SELECT","isAllowed":true},{"type":"CREATE","isAllowed":true}],"users":["user1"],"groups":[],"conditions":[],"delegateAdmin":false}],"denyPolicyItems":[],"allowExceptions":[],"denyExceptions":[],"dataMaskPolicyItems":[],"rowFilterPolicyItems":[]}' -X POST http://hostname:6080/service/public/v2/api/policy
Updating an existing Policy:
Existing policies might need to be updated when a new user needs the same permission on a schema for which a policy is already defined. If we need to assign user2 create and select permission on employees schema, we can update the policy we created in our previous example to add user2. Request type ‘PUT’ is used for updating a policy. We also need to provide policy id of the existing policy which is being updated. Failure to provide a policy id or a wrong policy id will return a ‘404 Not Found’ error. A successful update will return a ‘200 OK’ message.
curl -iv -u admin:admin -H "Content-Type: application/json" -d '{ "isEnabled":true,"service":"ranger_bigsql","name":"emp_policy","description":"Policy for employees schema access","isAuditEnabled":true,"resources":{"schema":{"values":["Employees"],"isExcludes":false,"isRecursive":false},"table":{"values":["*"],"isExcludes":false,"isRecursive":false}},"policyItems":[{"accesses":[{"type":"SELECT","isAllowed":true},{"type":"CREATE","isAllowed":true}],"users":["user1","user2"],"groups":[],"conditions":[],"delegateAdmin":false}],"denyPolicyItems":[],"allowExceptions":[],"denyExceptions":[],"dataMaskPolicyItems":[],"rowFilterPolicyItems":[]}' -X PUT http://hostname:6080/service/public/v2/api/policy/policyid
Deleting Policy:
Request type ‘DELETE’ is used for deleting an existing policy. A valid policy id is required in the request URL or ‘404 Not Found’ response will be returned. Successful deletion of a policy will return ‘204 No Content’.
curl -iv -u admin:admin -X DELETE http://hostname:6080/service/public/v2/api/policy/policyid
Searching Policy:
To retrieve all information about an existing ranger policy, a policy name is required in the request URL. Request type ‘GET’ is used for this.
curl -iv -u admin:admin -H "Content-Type: application/json" -X GET "http://hostname:6080/service/public/v2/api/policy?policyName=emp_policy"
Above REST API to retrieve information about a policy will return the following information about the named policy.
[{"id":774,"guid":"dbe13789-a1e8-4774-a763-f2e32e26c8f8","isEnabled":true,"version":1,"service":"ranger_bigsql","name":"emp_policy","policyType":0,"description":"Policy for employees schema access","isAuditEnabled":true,"resources":{"schema":{"values":["Employees"],"isExcludes":false,"isRecursive":false},"table":{"values":["*"],"isExcludes":false,"isRecursive":false}},"policyItems":[{"accesses":[{"type":"select","isAllowed":true},{"type":"create","isAllowed":true}],"users":["user1"],"groups":[],"conditions":[],"delegateAdmin":false}],"denyPolicyItems":[],"allowExceptions":[],"denyExceptions":[],"dataMaskPolicyItems":[],"rowFilterPolicyItems":[]}]
To retrieve all available policies, simply omit the policyName from the request url. Following command will return complete information regarding all available policies.
curl -iv -u admin:admin -H "Content-Type: application/json" -X GET "http://hostname:6080/service/public/v2/api/policy"
Note: This has been tested with ranger version 0.7 and Big SQL version 5.0.2.