Data security is an ongoing concern for anyone managing a data warehouse. Organizations need to control access to data, down to the granular level, for secure access to data both internally and externally. With the complexity of data platforms increasing day by day, it’s become even more critical to identify and monitor access to sensitive data. In many cases, sensitive data is co-mingled with non-sensitive data, and access restrictions to sensitive data need to be enabled based on factors like data location or presence of financial information. There may also be nuances where data is sensitive for some groups of users, while for others, it is not. 

Today, we’re pleased to announce the general availability of BigQuery row-level security, which gives customers a way to control access to subsets of data in the same table for different groups of users. Row-level security (RLS) extends the principle of least privilege access and enables fine-grained access control policies in BigQuery tables. BigQuery currently supports access controls at the project-, dataset-, table- and column-level. Adding RLS to the portfolio of access controls now enables customers to filter and define access to specific rows in a table based on qualifying user conditions—providing much needed peace of mind for data professionals. 

“Our digital transformation and migration of data to the cloud magnifies the business value we can extract from our information assets. However, granular data access control is essential to comply with international regulatory and contractual requirements. BigQuery row-level security helps us comply with data residency and export restrictions,” says Jarrett Garcia, Iron Mountain’s Enterprise Data Platform Senior Director. “It enables us to manage fine-grained access controls without replicating data. What used to take months for approval and access provisioning can now be done more efficiently and effectively. We are looking forward to implementing additional data security capabilities on the BigQuery roadmap to address other critical business use cases.”

How BigQuery row-level security works

Row-level security in BigQuery enables different user personas access to subsets of data in the same table. Customers who are currently using authorized views to enable these use cases can leverage RLS for ease of management. To express the concept of RLS, we have introduced a new entity in BigQuery called row access policy. Row access policies map a group of user principals to the rows that they can see, defined by a SQL filter predicate. 

Secure logic rules created by data owners and administrators determines which user can see which rows through the creation of a row-level access policy. The row-level access policies created on a target table by administrators or data owners are applied when a query is run on the table. One table can have multiple policies applied to it.

Below is an example, where row-level access policies have been created to filter data based on users’ “region”.

In the illustrated scenario above, row-level access policies have been created to verify a querying user’s region and to give them access only to the subset of data relevant to that region. Access policies are granted to a grantee list which support all types of IAM principles such as individual users, groups, domains or service accounts. In this example, when a user queries the table, row-level access policies are evaluated to assess which, if any, policies are applicable to that user. The group ‘sales-apac’ is granted access to view a subset of rows where region = ‘APAC’ whereas the group ‘sales-us’ is granted access to view a subset of rows where the region = ’US’. Likewise, users in both groups will see rows in both regions, and users in neither group will not see any rows.

Row-level access policies can also be created using the SESSION_USER() function to restrict access only to rows that belong to the user running the query. If none of the row access policies are applicable to the querying user, the user will have no access to the data in the table.

When a user queries a table with a row-level access policy, BigQuery displays a banner notice indicating that their results may be filtered by a row-level access policy. This notice displays even if the user is a member of the `grantee_list`.

When to put BigQuery row-level security to work

Row-level access policies are useful when you have a need to limit access to data based on filter conditions. The row-access policies’ filter predicate supports arbitrary SQL, and is conceptually similar to the WHERE clause of a SQL query. Filter predicates support the SESSION_USER() function to restrict access only to rows that belong to the user running the query. If none of the row access policies are applicable to the querying user, the user will have no access to the data in the table. Currently, the column used for filtering must be in the table, but we anticipate adding support for subqueries in the filter expression, opening up access to use cases where data is filtered based on lookup tables and calculated values. Row-level access policies can be created, updated and dropped using DDL statements. You will be able to see the list of row-level access policies applied to a table using the BigQuery schema pane in the Cloud Console,  which simplifies the management of policies per table, or by using the bq command-line tool.

Row-level security is compatible with other BigQuery security features, and can be used along with column-level security for further granularity.  Since row-level access policies are applied on the source tables, any actions performed on the table will inherit the table’s associated access policies, to ensure access to secure data is protected. Row-level access policies are applicable to every method used to access BigQuery data (API, Views, etc). 

Try it out

We’re always working to enhance BigQuery’s (and Google Cloud’s) data governance capabilities, to provide more controls around managing your data. With row-level security, we are adding deeper protections for your data. You can learn more about BigQuery row-level security in our documentation and best practices.

Pierogies might just be the ultimate comfort food. But when Mrs. T’s Pierogies — the leading manufacturer of frozen pierogies in the US — learned it needed to transition its existing on-premises SAP ECC to S/4HANA, the company sought a little comfort for itself. 

Founded in 1952, Mrs. T’s Pierogies now produces more than 650 million pierogies a year. Moving that many pierogies requires a powerful ERP system — and an equally powerful IT infrastructure on which to run it. Mrs. T’s realized that the SAP-mandated transition of its ERP solution to SAP S/4HANA was an opportunity to move its SAP systems to Google Cloud and gain real-time analytics capabilities for faster sales forecasting, more effective trade promotions, and more sophisticated planning.

From necessity to opportunity

Mrs. T’s successfully ran its SAP ECC solution on its own on-premises servers for years. But after SAP decided to sunset the product, Mrs. T’s realized that it faced multiple challenges, including: 

• Migrating its SAP ECC 6.0 system from an on-premises leagcy OS to a cloud-based Linux environment
• Moving data from its SAP DB2 database to HANA
• Transitioning from ECC to SAP S/4HANA

That complex transition needed to take place with little or no downtime, since nearly all of the company’s invoices, warehouse movements, and transfer orders used the Electronic Data Interchange (EDI) protocol. Missing even a few hours of EDI transactions would put significant revenue at stake. Adding to the challenge: Some Mrs. T’s customers would not accept an invoice past five days, which left little room for error. Mrs. T’s chose Rackspace Technology, a longtime Google Cloud partner, to oversee the move. 

Mrs. T’s could have chosen to run S/4HANA on its legacy hardware but saw migration to Google Cloud as an opportunity to improve key aspects of its business, in particular data analytics. Historically, sales planning and forecasting involved time-consuming manual processes. But the speed, availability, and scalability of Google Cloud meant that Mrs. T’s could take advantage of S/4HANA’s embedded analytics capabilities. Migrating could also open the door to leveraging Google Cloud’s native integration of SAP data to power Google tools such as BigQuery, Google Cloud AI Building Blocks, and more. 

The move to Google Cloud also gave Mrs. T’s an opportunity to update its disaster recovery process. Previously, the company backed up to tape. So, if its SAP systems went down, a member of the IT department would have to drive the most recent tape backups an hour to its cold site, where the disaster recovery partner would load the SAP backup tape, boot the system up, switch network connections to that site, and cross their fingers. Not only would downtime be significant, but restored data would be limited to the periodic tape backup. 

“Everything just worked”

Once Mrs. T’s decided to migrate to Google Cloud, the company worked with Rackspace Technology to implement the system. The first phase focused on moving the SAP production environment from on-premises infrastructure to Google Cloud and updating its database to HANA, which took place over 12 weeks. The switchover occurred over a weekend and was all but invisible to users. “We came in on Monday and everything just worked,” recalls Timothy Coyle, Director of Information Systems & Technology. In a second four-month phase, the company transitioned from ECC to S/4. 

The move to Google Cloud paid dividends immediately. Batch transactions occurred twice as fast and on-screen end-user transactions rendered instantly. The upgrade also gave the finance team access to embedded analytics and monitoring for the first time. With everything now in the cloud, disaster recovery could be dynamic and nearly instantaneous, with a worst-case scenario of just 5 to 10 minutes of downtime. 

“Mrs. T’s needed a skilled and experienced partner that could move its SAP environment to Google Cloud with no negative impacts to its business. We knew this migration was a key initiative in Mrs. T’s digital transformation journey,” says Chuck Britton, Google Partner Development Manager at Rackspace. “We also knew that running SAP on Google Cloud would give the business the fast and flexible analytical capabilities it needed for its SAP data.” 

From ideation to production, Mrs. T’s migrated from its legacy on-premises infrastructure to a modern SAP S/4HANA solution on Google Cloud in only seven months, with minimal downtime and zero disruptions. Now that the migration is complete, Mrs. T’s has a flexible, highly scalable environment to run the SAP applications and data that fuel the business. Says Coyle, “Our strategic intent for IT is to make processes simpler, people more productive, and infrastructure more secure. This project fits right square in the middle of that strategic philosophy.”

Learn more about ways in which Google Cloud can transform your SAP experience and about Rackspace Google Cloud solutions for SAP customers.

Engineers that build last-mile delivery services belong to an elite order, a hallowed subcategory. Delivery customers are incredibly demanding when it comes to speed and convenience, and the services they use must take variables like increased traffic, road conditions, human error, and even driver availability into account every day.

Nuro is a company with a new approach to delivery services. Nuro has a fleet of autonomous vehicles designed to address many of the problems related to last-mile delivery. And every day, these vehicles — and their sensors — generate a lot of data before parking for the night. For Nuro engineers, that data can help them understand the impact of new on-road features, make improvements to their vehicles’ software, and ensure even better deliveries for their customers.

For Nuro, the key challenge is how to move petabytes of data as quickly, securely, and easily as possible from their edge environments, like vehicle depots, to Google’s Cloud Storage. For this delivery effort, Nuro selected Google’s Transfer Appliance with its new online transfer capability, now generally available.

Helping Nuro to speed up data delivery from the edge to the cloud

Like many Google Cloud customers, Nuro collects data from remote environments, like vehicle depots, that have different networking and storage capabilities when compared to a traditional data center. For a transfer solution to be effective moving unstructured data from these environments to the cloud, the solution needs to be easy to deploy and automate, while still providing similar performance as a more complicated alternative.

The Transfer Appliance was built for this use case. It arrives to customers as a physical appliance with a preconfigured version of Google’s Storage Transfer Service software already installed. Customers can move files to the appliance by using SFTP or SCP, or, alternately, can mount the appliance as an NFS share and copy target. Data can be stored locally on the appliance or transferred over the network, and secure encryption — at-rest and in-flight — is enabled by default.

With these new appliances, Nuro will be able to automate much of their storage transfer needs. When their autonomous vehicles return to the depot, they can move data like software logs, LIDAR data, and sensor data — all ideal fits for Google’s Cloud Storage — from parked vehicles to the Transfer Appliance. Online transfers can then be performed throughout the day, ensuring a steady stream of valuable data in the cloud for developers to analyze and use in their nightly builds. All of this will help Nuro’s engineering leaders like Jie Pan to run more productive development teams with less operational overhead.

“Our autonomous vehicles generate a tremendous amount of useful data, and our goal is to get that data to our engineers as soon as possible,” said Jie Pan, Engineering Manager at Nuro. “When vehicles return to the depot, we can move data hourly into Cloud Storage over the network. We also have the flexibility to return the Transfer Appliance back to Google Cloud. Most importantly, this rapid transfer architecture gives a meaningful boost to engineering productivity and development velocity.”

Going the extra mile

Engineering and infrastructure leaders understand the value of delivering the right data to the right teams, as fast as possible. By adding preconfigured, over-the-network transfer into a turnkey Transfer Appliance, Google Cloud customers can more easily automate these data deliveries by scheduling regular migrations of on-premises files, objects, and other unstructured data to our Cloud Storage.

As Nuro continues to grow their manufacturing and testing footprint, they plan to use Transfer Appliances to further scale and simplify their data migration from on-premises to Google Cloud. Cutting the time to migrate their data by more than half will make for happier, more productive developers, and that will help Nuro bring us all the future of delivery a little faster.

If you’d like to learn more about Transfer Appliance and its new online transfer capability, click here or reach out to your Google Cloud account team.