Do we have a Ranger 0.5 DB Schema defined somewhere?

[#1]

Audit Logs stored in Ranger Audit DB needs to be piped to SIEM system. Need to know what table(s) I can query on to pull failed policies (ie. "Denied" access). This information will eventually be pushed to SIEM.

[#2]

@rgarcia

Audit Database has only 1 table that stores all the information. Check through Mysql CLI (Assuming its mysql)

example:

select client_ip,repo_name,session_id,event_time,request_user,action,request_data,resource_path from xa_access_audit;

[#3 · Accepted solution]

Hi @rgarcia,

I started putting together the schema myself but never completed it. I think I have sufficiently documented what you need though. See the attachments for a description of a few of the tables. It is worth noting that there are two databases in a default installation. the ranger_audit database contains all of the audit information regarding the HDP-based repositories (e.g., who touch what HDFS file, when, and was it allowed). The ranger database contains several tables pertaining to the metadata about each repo and the policies around them. The audit information around who changed the policies and when is also stored here.

I also have couple example queries below to help understand them, specifically the ranger database which you need if you want to understand and audit how the policies themselves are changed:

The following query will gather all of the resources that the usr ‘hive’ has been explicitly allowed access to:

SELECT * FROM 
(select x_resource.policy_name, x_user.user_name, x_resource.res_name, x_resource.res_cols, x_resource.res_dbs, x_resource.res_col_fams from x_resource
     join x_perm_map
         on x_perm_map.res_id = x_resource.id
    join x_user
         on x_user.id = x_perm_map.user_id) as policies 
WHERE policies.user_name = 'hive’;

The next will do the same for the group ‘hadoop’:

SELECT * FROM 
(select x_resource.policy_name, x_group.group_name, x_resource.res_name, x_resource.res_cols, x_resource.res_dbs, x_resource.res_col_fams from x_resource
     join x_perm_map
         on x_perm_map.res_id = x_resource.id
    join x_group
         on x_group.id = x_perm_map.group_id) as policies 
WHERE policies.group_name = 'hadoop';

If you are looking for all users that have admin privileges somewhere within Hive, HDFS, or HBase then you can do it as follows:

SELECT * FROM 
(select x_resource.policy_name, x_user.user_name, x_resource.res_name, x_perm_map.perm_type from x_resource
     join x_perm_map
         on x_perm_map.res_id = x_resource.id
    join x_user
         on x_user.id = x_perm_map.user_id) as policies 
WHERE policies.perm_type=6;

[#4]

An extra comment with an attachment because I was limited to 2 attachments in my original answer 🙂 Note that this was derived from an instance of HDP 2.2.4 with Ranger 0.4 but should translate over to Ranger 0.5 on HDP 2.3.

[#5]

@Brandon Wilson Very nice! I think it should be in an official blog or docs. @bganesan @bdurai

[#6]

@Brandon Wilson Very nice. @Neeraj Sabharwal We are moving away from storing audits in DB, we need to guide customers to get audit from HDFS

[#7]

@rgarcia Why not pipe the data from HDFS, assuming audit is being written to HDFS as well?