BigSQL and Ranger - stanislawbartkowski/hdpactivedirectory GitHub Wiki
Enabling
Integrating BigSQL with Ranger is easy and straightforward. Follow the instruction under the link.
Keep in mind, that BigSQL security bypasses Hive security. So if the user access is restricted in BigSQL but not in Hive, the user can still access sensitive data through Hive SQL engine. In Hadoop, the security policy should be consistent across all cluster.
After successful installation, the BigSQL Ranger panel should be visible.
Test
Test scenario
Follow the pattern https://github.com/stanislawbartkowski/hdpactivedirectory#ad-users-and-groups-used-for-testing.
| User | Group | Description |
|---|---|---|
| user1 | (no group) | malicious user, access forbidden |
| user2 | dataadmin | full access, can create and update tables |
| user3 | datascience | read-only access, SELECT |
Create test database
As bigsql user (BigSQL administrator) create schema testdb. BigSQL allows a single database only, so the BigSQL schema maps to Hive database.
db2 connect to bigsql user bigsql db2 create schema testdb
DB20000I The SQL command completed successfully.
Create an appropriate policy in Ranger UI
Malicious user user1 does not belong to any group, therefore is not covered by this policy setting.
As user2
Expected result: full administrative access to TESTDB schema.
db2 connect to bigsql user user2
Enter current password for user2:
Database Connection Information
Database server = DB2/LINUXX8664 11.1.9.0
SQL authorization ID = USER2
Local database alias = BIGSQL
db2 set current schema testdb db2 "create hadoop table test (id int, name varchar(100))" db2 "insert into test values (1,'name1')" db2 "insert into test values (2,'name2')" db2 "select * from test"
ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 name1
2 name2
2 record(s) selected.
As user3
Expected result: only SELECT access, any other access type is not allowed.
db2 connect to bigsql user user3 db2 set current schema testdb db2 "select * from test"
ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 name1
2 name2
2 record(s) selected.
Try to modify data.
db2 "insert into test values (3,'name3')"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "USER3". Operation: "INSERT". Object: "TESTDB.TEST". SQLSTATE=42501
db2 drop table test
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "USER3". Operation: "DROP TABLE". Object: "TESTDB.TEST". SQLSTATE=42501
As user1
Expected result: malicious user, any access to data denied.
db2 connect to bigsql user user1 db2 set current schema testdb db2 "select * from test"
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "USER1". Operation: "SELECT". Object: "TESTDB.TEST". SQLSTATE=42501
Ranger auditing
Ranger audit option allows monitoring BigSQL access and detecting inappropriate behaviour.
For instance, the malicious user1 should leave the following traces.
Troubleshooting
https://www.ibm.com/support/knowledgecenter/en/SSCRJT_6.0.0/com.ibm.swg.im.bigsql.doc/doc/admin_ranger_audit_enable.html If Ranger BigSQL entries are not visible in Ranger audit, verify Infra Solr->Configs->Advanced->Advanced infra-solr-security-json->Ranger audit service users. Add bigsql user if not included and restart Infra Solr component.
