Press "Enter" to skip to content

Category: Security

Apache Ranger On ElasticMapReduce

Published 2016-12-06 by Kevin Feasel

Varun Rao explains role-based access control using Apache Ranger on Amazon ElasticMapReduce:

Using the HUE SQL Editor, execute the following query.

These queries use external tables, and Hive leverages EMRFS to access the data stored in S3. Because HiveServer2 (where Hue is submitting these queries) is checking with Ranger to grant or deny before accessing any data in S3, you can create fine-grained SQL-based permissions for users even though there is a single EC2 role specified for the cluster (which is used by all requests the cluster makes to S3). For more information, see Additional Features of Hive on Amazon EMR.

If your job includes securing a Hadoop cluster, this is a nice read, even if you don’t use EMR.

Comments closed

Row-Level Security With Power BI

Published 2016-12-02 by Kevin Feasel

Callum Green shows how to use row-level security with Power BI Desktop:

In the June 2016 monthly Power BI release, Row Level Security (RLS) was introduced into Power BI desktop. This is great news for people using the application, especially as the configuration is stored within the Power BI model.  Previously, you had to create the security in the web environment, which could easily be overwritten when publishing multiple times from a desktop workbook.

In this blog, I will show you how to set up RLS in Power BI desktop and how to test it works. My example uses the AdventureWorksDW2014 database (download here), specifically applying permissions for a manager. Each manager will only be able to see data for the Sales Representatives that report to them.

This is different from the SQL Server 2016 feature of the same name, but the concept is the same.

Comments closed

Checking File Permissions

Published 2016-11-25 by Kevin Feasel

Andrew Peterson runs chmod 664 on a database backup:

You’re attempting to RESTORE a SQL Server database backup to your Linux installation and you get the message:

Msg 3201, Level 16, State 2, Line 17
Cannot open backup device ‘C:\home\user\Downloads\AdventureWorks2012.bak’.
Operating system error 2(The system cannot find the file
specified.).
Msg 3013, Level 16, State 1, Line 17
RESTORE HEADERONLY is terminating abnormally.

If you’re going to administer SQL Server on Linux, it’s a good idea to check out the Unix-style permissions model.  It’s a bit different than what we’re used to on Windows, though it does make sense with a bit of practice.

Comments closed

Viewing Power BI Audit Logs

Published 2016-11-25 by Kevin Feasel

Ginger Grant shows how to give a Power BI Administrator rights to view the audit logs:

The Audit Logs are the third menu item in the Power BI Admin Portal. As you can tell by looking at a copy of the screen below, Audit Logs are not really part of Power BI. Yes the ability to log all of the content in Power BI exists in the Audit Logs, but so does the ability to review the audit logs for things like Exchange Mailbox Activities and User Administration Activities.

If the Office 365 Administrator has granted a user Power BI Administration rights, this is what the newly minted Power BI Administrator will see when trying to access any search activities. It appears that you the user has rights, until that user tries to do anything on the screen. At that point, this error window appears.

Click through to see how to grant audit log access.

Comments closed

SQL Server R Service Users

Published 2016-11-16 by Kevin Feasel

John Pertell shows how to figure out which user account is running SQL Server R Services code:

You’re not running as yourself, even though that’s the account you signed into SSMS as.

You’re not running under the server account that SQL or SQL Launchpad run under.

You’re running as a new account created when you installed SQL R Service In Database for the purpose of running R code.

John also looks at a couple ways of showing which user is running this code and notes that this solves his file share issue.

Comments closed

Bulk Administration

Published 2016-11-11 by Kevin Feasel

Kenneth Fisher discusses the bulk administration right:

So as with all permissions we only grant them if there is an actual need right? And the best practice of least privilege says that if someone has to be able to do a bulk load on a table then we should grant the bulk load to that one table right? There’s the rub. Bulk admin permissions are at the instance level and are not granular in any way. Ie you can’t grant it specifically to a single database or table. It’s all or nothing.

Read on for Kenneth’s thoughts.

Comments closed

Always Encrypted And Memory-Optimized Tables

Published 2016-11-01 by Kevin Feasel

Joey D’Antoni tests whether Always Encrypted works on memory-optimized tables in SQL Server 2016:

Last week was the PASS Summit, which is the biggest confab of SQL Server professionals on the planet (and educational as ever), Denny Cherry  (b|t) and I ran into Bob Ward (b|t) of Microsoft and of 500 level internals presentations. And for the first time ever, Bob asked us a question about SQL Server—of course we didn’t know the answer of the top of our heads, but we felt obligated to research it like we’ve made Bob do so many times. Anyone, the question came up a Bob’s internals session on Hekaton (In-Memory OLTP) and whether it supported the new Always Encrypted feature in SQL Server 2016. I checked books online, but could not find a clear answer, so I fired up SSMS and setup a quick demo.

Click through for scripts and the answer.

Comments closed

Cloudera, Polybase, And Active Directory

Published 2016-10-25 by Kevin Feasel

Ajay Jagannathan shows how to integrate a SQL Server instance + Polybase with a Cloudera Hadoop cluster, all using Active Directory for accounts:

For all usernames and principals, we will use the suffixes like Cluster14 for name-scalability.

  1. Active Directory setup:

  1. Install OpenLDAP utilities (openldap-clients on RHEL/Centos) on the host of Cloudera Manager server. Install Kerberos client (krb5-workstation on RHEL/Centos) on all hosts of the cluster. This step requires internet connection in Hadoop server. If there is no internet connection in the server, you can download the rpm and install.

This is absolutely worth the read.

Comments closed

Securing Solr Collections

Published 2016-10-13 by Kevin Feasel

Jan Kunigk and Paul Wilkinson show how to secure Solr collections:

The policy shown below establishes four Sentry roles based on the admin, operators, users, and techusersgroups.

  • Administrators are entitled to all actions.

  • Operators are granted update and query privileges.

  • Users are granted query privileges.

  • Tech users are granted update privileges.

These are pretty straightforward role-based access controls.  The authors also look at accessing the data via Flume and a couple other technologies.

Comments closed