The World of a Penguin

Managing data access policies in Hive

2019-10-01T20:38:00.000+02:00

Managing data access policies in Hive

To efficiently manage access policies in our Hive Hadoop cluster at Svenska Spel we developed a tool Cobra-policytool. Instead of managing our policies in Apache Rangers web interface and tagging information in Apache Atlas it is now integrated into our CI/CD pipeline. We can use our normal development process for our polices too.

In this post I will describe the background to Cobra-policytool and demonstrate how to use it. We recently open sourced Cobra-policytool to be able to collaborate with others.

Earlier this year Svenska Spel migrated our Hadoop cluster to a new cluster using Hortonworks HDP. At the same time we decided to use Kerberos, Apache Atlas, and Apache Ranger to get good security and powerful access control. If you do not know what Atlas and Ranger does I recommend to take a look at my talk from DataWorks Summit Europe 2018.

With Atlas and Ranger in place, next step was to implement and enforce our policies. Soon we realised it was not feasible to sit and tag our tables and columns in Atlas UI and enter policies into Ranger UI. It would be too time consuming and error prone. Neither could we peer-review our changes nor version control them. Finally, as a Hive focused organisation and already had our data model in Oracle SQL Developer Data Modeler, why add another place to enter our metadata and how to keep them in sync?

With these four insights:

time consuming
error prone
lack of version control
scattered metadata

One question was raised: We already generating much of our ETL code and deploy it with an automated deployment pipeline so why not handle tags and policies the same way?

We did a proof of concept where we added metadata into our data model. This metadata became tags in Atlas. The tags were complemented with a file of policies to be applied to Ranger. It worked well. The BI developer tag the data while modelling, within the same tool. A few people in the team needed to learn write policy files. The rest of the process looks the same for the developer, everything happens in the background.

Over the last few months we have iterated and improved our proof of concept to a production ready tool called Cobra-policytool. After presenting our work at Data Works Summit Europe there were a lot of interest. We therefore decided to open source it.

Cobra-policytool is independent of our use of Oracle SQL Developer Data Modeler. It reads CSV files with tag information and a json-file with our policies and applies these to Atlas and Ranger. It makes it easy for others to use it with their tools of choice or when maintaining them by hand.

We have to this date used Cobra-policytool for our data in Hive but intend to use it for more aspects of our Hadoop cluster later.

The rest of this post I will walk through a simple example how to use Cobra-policytool. It is not intended to cover all aspects but to get you started.

Assume we have a table called customer in the schema example_dev.


Customer table in schema example

We have two groups of users we want to allow work this table. First group are our analysts, they need to read it but are not allowed to see personal identifiable information (PII). Second group are our marketing team. They are allowed to see personal identifiable information but only for users opted-in for marketing, in our case true in the column marketing.

Lets start tag our data to mark what is PII. This is done adding a tag PII to our columns with information that can identify people. To do this we create one CSV-file for tables and columns respectively.

Create the CSV for tables name it table_tags.csv:

schema;table;tags
example;customer;PII_table

Not that we only write “example” as schema, but we said earlier the name was “example_dev”. Cobra-policytool uses the convention to suffix our schema with environment name. This way we can have several environments in one Hive installation.

Create the CSV for columns name it column_tags.csv:

schema;table;attribute;tags
example;customer;customer_id;PII
example;customer;name;PII
example;customer;city;
example;customer;marketing;

We list all columns in the customer table, this way cobra-policytool can determine if tags have been removed from a column. You get a warning of columns not listed. The same is true for tables. If one table or column have multiple tags we separate them with comma(,).

To deploy the tags to Atlas we use the command cobra-policy. Provide the directory of the CSV files, name of our environment and our config file. Look in the documentation how to configure where your servers are. Remember our environment is the suffix to our schema, in this case dev.

cobra-policy tags_to_atlas --srcdir example-dir --environment dev -c myconfig

If you go to your Atlas UI you can search for table customer and see that it have the tag PII_table. So far nothing happens with our data. We must provide policies for our cases.

We start with a policy for our analyst users. When an analyst ask a query in Hive all fields tagged with PII shall be anonymised with an UDF udf.anonymize. The UDF can be any UDF in Hive. Ranger provides som simple masking UDFs. In this example the columns customer_id and name shall be anonymised.

Policies are defined in the file ranger_policies.json that must be stored in the same directory as the CSV files. This is how our ranger_policies.json will look like, turn to the documentation for details.

[{
    "command": "apply_rule",
    "policy": {
        "service": "dev_tag",
        "name": "load_etl_mask_pii_data_analysts",
        "policyType": 1,
        "description": "Mask all column data tagged PII",
        "isAuditEnabled": true
        "resources": {
            "tag": {
                "values": ["PII"],
                "isExcludes": false,
                "isRecursive": false
            }
        },
       "dataMaskPolicyItems": [{
           "dataMaskInfo": {
               "dataMaskType": "hive:CUSTOM",
               "conditionExpr": "udf.anonymize({col})",
               "valueExpr": "udf.anonymize({col})"
           },
           "accesses": [{
               "type": "hive:select",
               "isAllowed": true
           }],
           "users": [],
           "groups": ["analyst"],
           "conditions": [],
           "delegateAdmin": false
       }],
       "isEnabled": true
   }
}]

Note that we are not listing neither columns nor tables. We rely on the tag information. This makes the policies easier to read and maintain.

We deploy our policy with cobra-policytool in a similar way as we applied the tags:

cobra-policy rules_to_ranger --srcdir example-dir --environment dev --project-name post

Now, when a member of the group analyst run a query on the customer table she will only see anonymised data.


Analysts view of customer table

In our second use-case our CRM users shall only see customers opted in for marketing. This means we want to only show rows where marketing is true. For this we use row based filtering. Unfortunately one cannot do row based filtering using tags in Ranger, one rule per table is needed. Cobra-policytool add the possibility to have one rule for all tables with a tag. This requires that your filter expression works in all the tables with that tag. When deploying Cobra-policytool will create one rule for each table with a tag.

We extend ranger_policies.json with an apply_tag_row_rule command. This command has next to the policy a filter section used to define what tables, rows, users and groups to match the policy for.

[{
  "command": "apply_tag_row_rule",
  "filters": [{
    "groups": ["crm"],
    "users": [],
    "tagFilterExprs": [{
      "tags": ["PII_table"],
      "filterExpr": "marketing = true"
    }]
  }],
  "policy": {
    "service": "dev_hive",
    "name": "${project_name}_${environment}_${schema}_${table}",
    "description": "Row filtering policy for CRM",
    "policyType": 2,
    "isEnabled": true,
    "resources": {
      "database": {
        "isExcludes": false,
        "values": ["${schema}_${environment}"],
        "isRecursive": false
      },
      "table": {
        "isExcludes": false,
        "values": ["${table}"],
        "isRecursive": false
      }
    },
    "isAuditEnabled": true
  }
},
...]

The policy part will be applied to every table with the PII_table tag. Which tables have the tag is read from the CSV-files. A number of variables are referred in the policy. These will be expanded for each matching table. The variable table will be changed to a matching table name, same for schema. Project_name and environment are taken from the command line arguments. You can also define your own variables in the configuration file.

To apply this rule we once again run our command:

cobra-policy rules_to_ranger --srcdir example-dir --environment dev --project-name post

If we take a look at customer table as a CRM user we only have half of the users there, those with marketing equals true.

CRM users view

Our two groups of users; analyst and CRM, have now only access to the data they need. Data is anonymised for analysts and CRM users only see customers opted in for marketing. When having a few rules it is straight forward to use this on all our data in the warehouse. Our experience is that getting our intention into crisp policies is the hard part. When that is in place is creating the policy file straight forward.

Enforcing of our data policies are achieved creating three files and using Cobra-policytool. Two CSV files defining tags to mark important aspects off tables and columns. One json file including our policies. These files are managed in our source repository and deployed like other DDL-scripts. Cobra-policytool can be run over and over agin to guarantee no changes are made manually.

This post have demonstrated how we use cobra-policytool to apply tags and policies to a Hadoop and Hive environment. Thanks to cobra-policytool can we:

version control our changes to policies
review changes before deployed
easily revert when issues appears
consume tag-information produced from our code generator
have rule based filtering based on tags instead on individual tables.

To learn more visit Cobra-policytools Github pages. You can find more details and examples there.

Upgrade to Ubuntu 10.10 and ATI-graphics

2010-10-11T19:22:00.000+02:00

If you read this before you upgrade Ubuntu to 10.10. Remove/inactivate all your binary drivers before you upgrade.

If you read this after you upgrade Ubuntu to 10.10 and get a messy screen so you do not see anything and can't login. Reboot your computer in failsafe mode. Select to become root and do:

apt-get install fglrx

I also remembered that I got in the last installation phase at the upgrade a complaint about problem removing fglrx. Why do I suggest to install fglrx when we actually have a problem to remove it? Well it is easy. Since it was half installed and I could not remove it, then I tried to install it again. I saw it installed dkms and compiled a new version of the driver for my kernel.

I will now try to run the open source variant instead. I hate closed source, it is only problems.

The generalization of the day

2009-08-22T18:01:00.004+02:00

I use the web to find solutions of my problems, as most people do today. I have noted a huge difference between two communities I am using, the Java and the Linux community. With the Linux community I mean it in a broad sense like Linux distributions and common software running on Linux like Postgres, Apache, Evolution, F-spot, Python etc. I.e classic open source software.

When I search on a problem in the Linux area I most of the time get good hits with people having the same problem and good answers from people who knows how to solve it. The hits are on mailing lists, forums, and blogs.

On the other hand when searching on problems around Java I most of the time find people who have the same problem, but no answers.

I know this is a very big generalization but are anybody else experience the same? Why are people in classic open source more willing to help than in the Java world?

Rule of thumbs for API designing

2009-07-27T08:53:00.004+02:00

I have just read a very interesting article "API Design Matters" by Michi Henning. He discusses the problem with bad designed and gives some rules of thumb. We all have some time used an API that does not feel right and makes the coding hard. It is very interesting to read Michis analyze of this and why. My feeling is that he had put the hammer right on the nail. Here is a summary of the rules he discuss. I assume you will feel like me when you read them; yeah thats obvious. But thats the nice thing and the important is to get the list.

An API :

must provide sufficient functionality for the caller to achieve its task.
should be minimal without imposing undue inconvenience on the caller.
should be policy free if it is general purpose.
should be policy rich if it is special purpose.
should be designed from the perspective of the caller.
don't let the caller configure "everything".
should be documented before it is implemented.

If you want to read the discussion behind the rules, go to the article.

Moved to France

2009-07-11T14:22:00.004+02:00

Not me but my mail server has moved to France or to be more correct is now hosted by the French hosting provider Gandi.net. According to GeoIP it is in Paris. I rent a small virtual machine from them to host my web and mail server. This makes me less dependent of my broad band connection and I skip the problem that my IP-address may change without notice.

At Gandi I have an Ubuntu 9.04 Linux machine with 256kb of memory and 8GB of disk. This is more than enough for me to hosting private mail and web. The mailsever is postfix and dovecot. I have my own CA-authority to handle secure connections from my mail clients.

Spring framework to Python

2009-07-05T19:10:00.004+02:00

I just noticed that Springsource has released the Spring framework for Python. Interesting, especially since i like the Spring framework in Java and I prefer Python as language in a lot of cases. I must try it some day....

IPhone - not for me

2009-06-09T18:42:00.004+02:00

The new IPhone looks nice. The only (?) problem is that, as far as I know, it cannot be fully used from an Linux/Ubuntu only environment. They assume you have a Mac or MS Windows machine.

I think I have to wait until some one starts selling Android phones in Sweden and not require purchase by installments for the phone.

The next big wave is Wave

2009-05-29T17:15:00.002+02:00

Looking at Googles demo of Wave. It is impressing and my imagination fly and demonstrates solutions to some of my everyday problems like entering an e-mail conversation late, mixing mail and chat etc.

Wave will be the next or second to next big thing on the Net. I also looking forward to integration with my Ubuntu Gnome-desktop.

The future is cloudy

2009-04-28T20:31:00.006+02:00

It is obvious most vendors are very focused on supporting the cloud. Ubuntu 9.04 focus on provide so you can have your "own" Amazon cloud. Canonical provides images for Amazon EC2. SpringSource makes it simpler to deploy spring applications on the cloud, both own Google AppEngine and Amazon. Vmware helps you to build your inhouse cloud.
Then we have all SaaS services like Google Apps, Salesforce, sugarcrm.

The big question is: How do we get single sign on on the cloud?

Enough about clouds for today.

One of the bigest news from SpringOne here in Amsterdam is that SpringSource development environment, STS, will be free as in no fees. Today it was mentioned in one conversation that a new minor release of STS will be released next thursday.

Now @ twitter too

2009-04-28T17:03:00.002+02:00

I am now at twitter too. I will probably only twitt at special occasions like conferences.

SpringOne Europe 2009 day2

2009-04-28T12:49:00.002+02:00

SpringSource is really pushing Groovy and Grails. Hope they start pushing Jython too.

Heard a nice talk just before lunch about transactions. Transactions engines are hard to write. Recomendation is to skip XA transactions.

Time for some The before the next session.

What's in Spring 3?

2009-04-27T21:11:00.003+02:00

One session talked about the upcoming Springframework 3.0. It is more annotations, support for validation in JPA/Hibernate beans. Most interesting is that Spring will solve the REST-problem in Java. Python and Ruby has already solved it. JAX-RS does not solve it in Java. It does not support a way to negotiating what format the result should be in (JSON, XML, HTML etc). Spring 3.0 solves this. So this will be very usefull. Spring 3 will be available early this summer.

The conference has ben good so far. Two days to go. I have also attended a talk about performance tuning of Tomcat and a more detailed talk about Roo wich I mentioned from the keynote.

SpringOne Europe 2009 Keynote monday

2009-04-27T15:20:00.003+02:00

Some interesting notes from the keynote by Rod Johnson.
SpringSource modified Eclipse environment STS for make it easier to develop Spring applications will be free from now.

Their new tool Roo (may change name) to kickstart web development looks very promising. Finally something in the java world that makes simple forms development as easy as VB or Delphi.

SpringOne Europe 2009

2009-04-27T12:48:00.004+02:00

I am attending the SpringOne conference in Amsterdam. The conference will start any minute now. The program looks very interesting.
the morning here in Amsterdam was a bit cloudy but the sun is now shining. For you that did not know that; you can be full on one pancake. I just eat one with mushrooms and cheese.

Netbook Remix

2009-04-24T08:09:00.004+02:00

Some people have complained on the Ubuntu 9.04 Netbook Remix release. I installed it on my Asus EeePC 900 last night at the Ubuntu Release party. It fits my needs on the EeePC perfectly. I do not want it on my normal desktop PC but on a a smal device is it very good.

Time to party!!! 9.04 is here

2009-04-23T17:07:00.002+02:00

Ubuntu 9.04 is here. I have already downloaded the netbookremix image. It is soon time to take Remy, my EeePC, to the release party at The Champ here in Linköping.

Do you have some data?

2009-03-13T17:55:00.004+01:00

Are you sitting on some data and not sharing it? Is it collected by tax-funding? Why do you not share it?

Tim Berners-Lee talk at TED about the problem people not sharing data. Take 15 minutes and listen to his talk. I wait when you doing it............

OK? Are you still sitting on your data? Have you talked to you boss about share it? Show the talk for him/her. Think! Help mankind!

Actually, Tim Berners-Lee talks about the semantic web but not using the word.

The next swedish superstar

2009-03-13T10:39:00.007+01:00

Normally I write about tech related things on this blog. Since there a massive interest in todays topic I write about it here instead of in my Swedish blog.

The other day the Swedish evening paper Expressen had a full page article about Erik Hassle and that he had signed a world wide record contract. Erik has only released one single in Sweden earlier. His first album will be released in April in Sweden. According to the record company boss will Erik be one of Sweden's greatest artist.

Well, last night did I attend a concert organized by the Swedish UN-organization to collect money to remove mines. There were several bands playing, but for me Erik Hassle was the main event. The crowd were screaming and dancing. The sound were terrific. The music and the lyrics, good as ever. It was a great gig and it is exciting to know that it is Sweden's next big artist. (Sorry for the very bad picture taken with my cellphone.)

The music on Eriks myspace-page is good, but he is even better live.

Problem with Gmail and Gtalk

2009-03-10T10:19:00.002+01:00

Seems like Google has problems again with Gmail and Gtalk. Thats bad! I have had problem for about an hour with Gmail and a few minutes ago did Gtalk stop working. I hope that the problems are solved soon.

IE-free Windows 7?

2009-03-05T07:32:00.003+01:00

If this is true, that Internet Explorer will be optional in Windows 7, then it is a huge victory for the Open Source movement and for free competition on the software market.

A piece of shit

2009-02-11T13:05:00.003+01:00

Here is a fantastic description of how new technology is hyped and just irritating everybody but everybody must have it. Everything is better, but we announce that we will soon release a better version, but you can't wait. Take a look on the video and laugh.

French roads is opened

2009-01-21T09:30:00.003+01:00

France will allow the OpenStreetMap project to access their national data with all roads, names, etc. I assume this is a part of the EU Inspire directive.

When will Sweden follow?

I welcome SoundBridge to my network

2009-01-06T14:40:00.003+01:00

I have bought a new toy that is connected to the computer. It is a Pinnacle SoundBridge, which is the same as Roku SoundBridge. It download and plays music from my Ubuntu Server and can also play internet radio. I have installed Fire fly media server on my server to stream music using the daap-protocol. There is an Ubuntu/Debian package in the default repositories that is called mt-daapd, which is the old name of Fire fly. A nice thing with Fire fly is that it converts music files that are in formats that SoundBridge doesn't understand so they can be played anyway.

The migration has begun

2008-12-31T11:19:00.003+01:00

I have finally started migrate my VHS tapes to hard drive. Last summer, I reconnected my Audio/Video-equipment so it became possible to record videos from the VHS-player on my KISS EPG. Now when I had a few days of between X-mas and New Year it was time to start the migration. I can say this will take some time. My flow is:

Play a VHS-tape on the VCR and record it on the KISS.
Transfer the video file from the KISS-player to the Ubuntu file-server.
Cut the video with Avidemux to remove garbage and some times split in episodes etc.
Put the file in the right place in the directory structure.

Well, it will take some time to migrate all VHS-tapes. Fortunately, it does not require any manual work most of the time.

Subversion and SSL

2008-12-14T20:41:00.003+01:00

Have you tried to access an subversion-server using SSL in Ubuntu 8.10 (Intrepid)? I guess you get complains about certificate even if they are correct. It looks like this:

svn list https://www.linuxalert.org/svn
Error validating server certificate for 'https://www.linuxalert.org:443':
- The certificate is not issued by a trusted authority. Use the
fingerprint to validate the certificate manually!
Certificate information:
- Hostname: www.linuxalert.org
- Valid: from Wed, 25 Jun 2008 13:30:25 GMT until Thu, 25 Jun 2009 13:30:25 GMT
- Issuer: Linux alert, Linkoping, SE
- Fingerprint: e4:8f:b9:1b:f3:6c:f4:83:2a:1f:c6:9c:6d:cf:6a:4b:99:9e:22:1d
(R)eject, accept (t)emporarily or accept (p)ermanently?

I have my certificate installed. The solution was to install subversion and libsvn1 from backports in 8.04 (Hardy) .

$ sudo dpkg -i subversion_1.5.1dfsg1-1ubuntu2~hardy2_amd64.deb libsvn1_1.5.1dfsg1-1ubuntu2~hardy2_amd64.deb
(Reading database ... 226839 files and directories currently installed.)
Preparing to replace subversion 1.5.1dfsg1-1ubuntu2~hardy2 (using subversion_1.5.1dfsg1-1ubuntu2~hardy2_amd64.deb) ...
Unpacking replacement subversion ...
dpkg - warning: downgrading libsvn1 from 1.5.1dfsg1-1ubuntu2 to 1.5.1dfsg1-1ubuntu2~hardy2.
Preparing to replace libsvn1 1.5.1dfsg1-1ubuntu2 (using libsvn1_1.5.1dfsg1-1ubuntu2~hardy2_amd64.deb) ...
Unpacking replacement libsvn1 ...
Setting up libsvn1 (1.5.1dfsg1-1ubuntu2~hardy2) ...

Setting up subversion (1.5.1dfsg1-1ubuntu2~hardy2) ...
Processing triggers for man-db ...
Processing triggers for libc6 ...
ldconfig deferred processing now taking place
$

After this it looks better.

$ svn list https://www.linuxalert.org/svn
Authentication realm: LinuxAlert
Password for 'magru':

The version of subversion is the same but it is linked using other libraries.