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.

As software-defined vehicles continue to advance and the quantity of digital services grows to meet consumer demand, the data required to provide these services continue to grow as well. This makes automotive manufacturers and suppliers look for capabilities to log and analyze data, update applications, and extend commands to in-vehicle software.

The challenges the automotive sector faces can be quantified. A modern vehicle contains upwards of 70 electronic control units (ECUs), most of which are connected to one or more sensors. Not only is it now possible to exactly measure many aspects of vehicle performance, but new options become available. Using LIDAR (light detection and ranging), for example, vehicles are achieving higher levels of autonomy; this leads to a data stream from such demanding applications that may reach 25 GB per hour. For the in-vehicle processing of data, 100 million lines of software code may be present — more than a fighter jet. This in-vehicle code will have to be maintained with updates and new functionalities.

Access to the data will allow manufacturers to gain valuable insights into operational details of their vehicles. The use of this data can help to reduce costs and risks, increase ROI, support ESG initiatives, and provide valuable insights to develop innovative solutions and shorten the time to value for Electric Vehicle innovations.

Sibros’ Deep Connected Platform (DCP) makes it possible for these manufacturers to build and launch new connected vehicle use cases from production to post-sale at scale by connecting and managing all software and data throughout every life cycle stage. A key component of this platform is the Sibros Deep Logger that provides capabilities like the following:

• Full configurability of what to record, when to record it, and how fast to record it.
• High resolution timestamps of all Controller Area Network (CAN) messages.
• Dynamic application of live log configurations to receive new data points without deploying new software.

For example, properly analyzed engine data enables true predictive maintenance for the first time, which creates the option to repair or replace components before failure happens. Another example would be the evaluation of data regarding the use of certain in-car features with the goal to redesign its interior.

Two other components of the DCP are software updates and remote commands to ECUs. The DCP on Google Cloud enables seamless integration with any vehicle architecture and provides OEMs and suppliers with the platform to manage connected vehicle data at rest and in transit using a proven and secure way on a global scale.

OEMs can pull data through APIs provided by Sibros into Google Data Cloud (including BigQuery) to gain access to the rich information data sets provided by the DCP within their environment and blend this data with their first party data sets to provide value insights for their business. Some of the Connected Vehicle insights that DCP information enables are:

• Damage prevention, improved operation, or development of the next generation of engines with insights from complex analyses that could consider parameters like model, engine type, mileage, overall speed, temperature, air pressure, load, services, and more.
• The combination of electric vehicle battery usage data like charging cycles, engine performance, and battery age with contributing factors as the use of the air conditioning to determine if such factors contribute to hazardous battery conditions and for improved battery development.
• Cross-organization collaboration in R&D by the provision of information on all these metrics and more from real-world driving, like engine knock data and even tire pressure.
• Google Cloud’s unified data cloud offering provides a complete platform for building data-driven applications like those from Sibros — from simplified data ingestion, processing, and storage to powerful analytics, AI, ML, and data sharing capabilities — integrated with Google Cloud. With a diverse partner ecosystem and support for multi-cloud, open-source tools and APIs, Google Cloud provides Sibros the portability and the extensibility they need to avoid data lock-in.

“Software has an ever increasing importance in the automotive world, even more so with electric vehicles and new mobility services. Google Cloud is partnering with Sibros to bring their award winning Deep Connected Platform to deliver high frequency, low latency over-the-air software updates, data logging & diagnostics capabilities to our automotive customers, leveraging the security and scale of Google Cloud. This is revolutionizing everything from development cycles to business models and customer relationships.” — Matthias Breunig, Director, Global Automotive Solutions, Google Cloud

Through Built with BigQuery, Google Cloud is helping tech companies like Sibros build innovative applications on Google’s Data Cloud with simplified access to technology, helpful and dedicated engineering support, and joint go-to-market programs.

“Sibros is looking forward to partnering with Google Cloud, which will enable vehicle manufacturers and suppliers to reach the next level in their use of data. Sibros solutions for Deep Data Logging and Updating on the Google Data Cloud, combined with Google BigQuery, will help them to mitigate risks, reduce costs, add innovative products, and introduce value-added use cases.” — Xiaojian Huang, Chief Digital Officer, Software, Sibros

Sibros and Google Cloud are driving Connected Mobility transformation to help our customers accelerate R&D innovation, power efficient operations, and unlock software-defined vehicle use cases with a full stack connected vehicle platform. Click here to learn more about Sibros on Google Cloud.

Picking the right database for your application is not easy. The choice depends heavily on your use case—transactional processing, analytical processing, in-memory database, and so on—but it also depends on other factors. This post covers the different database options available within Google Cloud across relational (SQL) and non-relational (NoSQL) databases and explains which use cases are best suited for each database option. 

Relational databases 

In relational databases information is stored in tables, rows and columns, which typically works best for structured data. As a result they are used for applications in which the structure of the data does not change often. SQL (Structured Query Language) is used when interacting with most relational databases. They offer ACID consistency mode for the data, which means:

• Atomic: All operations in a transaction succeed or the operation is rolled back.
• Consistent: On the completion of a transaction, the database is structurally sound.
• Isolated: Transactions do not contend with one another. Contentious access to data is moderated by the database so that transactions appear to run sequentially.
• Durable: The results of applying a transaction are permanent, even in the presence of failures.

Because of these properties, relational databases are used in applications that require high accuracy and for transactional queries such as financial and retail transactions. For example: In banking when a customer makes a funds transfer request, you want to make sure the transaction is possible and it actually happens on the most up-to-date account balance, in this case an error or resubmit request is likely fine.

There are three relational database options in Google Cloud: Cloud SQL, Cloud Spanner, and Bare Metal Solution.

Cloud SQL: Provides managed MySQL, PostgreSQL and SQL Server databases on Google Cloud. It reduces maintenance cost and automates database provisioning, storage capacity management, back ups, and out-of-the-box high availability and disaster recovery/failover. For these reasons it is best for general-purpose web frameworks, CRM, ERP, SaaS and e-commerce applications.

Cloud Spanner: Cloud Spanner is an enterprise-grade, globally-distributed, and strongly-consistent database that offers up to 99.999% availability, built specifically to combine the benefits of relational database structure with non-relational horizontal scale. It is a unique database that combines ACID transactions, SQL queries, and relational structure with the scalability that you typically associate with non-relational or NoSQL databases. As a result, Spanner is best used for applications such as gaming, payment solutions, global financial ledgers, retail banking and inventory management that require ability to scale limitlessly with strong-consistency and high-availability. 

Bare Metal Solution: Provides hardware to run specialized workloads with low latency on Google Cloud. This is specifically useful if there is an Oracle database that you want to lift and shift into Google Cloud. This enables data center retirements and paves a path to modernize legacy applications. 

Non-relational databases

Non-relational databases (or NoSQL databases) store compex, unstructured data in a non-tabular form such as documents. Non-relational databases are often used when large quantities of complex and diverse data need to be organized. Unlike relational databases, they perform faster because a query doesn’t have to access several tables to deliver an answer, making them ideal for storing data that may change frequently or for applications that handle many different kinds of data. 

For example, an apparel store might have a database in which shirts have their own document containing all of their information, including size, brand, and color with room for adding more parameters later such as sleeve size, collars, and so on.

Qualities that make NoSQL databases fast:

• Eventual consistency: stores usually exhibit consistency at some later point (e.g., lazily at read time)
• Horizontal scaling, usually using hashed distributions
• Typically, they are optimized for a specific workload pattern (i.e., key-value, graph, wide-column)
• Typically, they don’t support cross shard transactions or flexible isolation modes.

Because of these properties, non-relational databases are used in applications that require large scale, reliability, availability, and frequent data changes.They can easily scale horizontally by adding more servers, unlike some relational databases, which scale vertically by increasing the machine size as the data grows. Although, some relations databases such as Cloud Spanner support scale-out and strict consistency.

Non-relational databases can store a variety of unstructured data such as documents, key-value, graphs, wide columns, and more. Here are your non-relational database options in Google Cloud: 

• Document databases: Store information as documents (in formats such as JSON and XML). For example: Firestore
• Key-value stores: Group associated data in collections with records that are identified with unique keys for easy retrieval. Key-value stores have just enough structure to mirror the value of relational databases while still preserving the benefits of NoSQL. For example: Datastore, Bigtable, Memorystore
• In-memory database: Purpose-built database that relies primarily on memory for data storage. These are designed to attain minimal response time by eliminating the need to access disks. They are ideal for applications that require microsecond response times and can have large spikes in traffic. For example: Memorystore
• Wide-column databases: Use the tabular format but allow a wide variance in how data is named and formatted in each row, even in the same table. They have some basic structure while preserving a lot of flexibility. For example: Bigtable
• Graph databases: Use graph structures to define the relationships between stored data points; useful for identifying patterns in unstructured and semi-structured information. For example: JanusGraph

There are three non-relational databases in Google Cloud:

• Firestore: Is a serverless document database which scales on demand and acts as a backend-as-a-service. It is DBaaS that increases the speed of building applications. It is perfect for all general purpose uses cases such as ecommerce, gaming, IoT and real time dashboards. With Firestore users can interact with and collaborate on live and offline data making it great for real-time application and mobile apps.  
• Cloud Bigtable: Cloud Bigtable is a sparsely populated table that can scale to billions of rows and thousands of columns, enabling you to store terabytes or even petabytes of data. It is ideal for storing very large amounts of single-keyed data with very low latency. It supports high read and write throughput at sub-millisecond latency, and it is an ideal data source for MapReduce operations. It also supports the open-source HBase API standard to easily integrate with the Apache ecosystem including HBase, Beam, Hadoop and Spark along with Google Cloud ecosystem.
• Memorystore: Memorystore is a fully managed in-memory data store service for Redis and Memcached at Google Cloud. It is best for in-memory and transient data stores and automates the complex tasks of provisioning, replication, failover, and patching so you can spend more time coding. Because it offers extremely low latency and high performance, Memorystore is great for web and mobile, gaming, leaderboard, social, chat, and news feed applications.

Conclusion

Choosing a relational or a non-relational database largely depends on the use case. Broadly, if your application requires ACID transactions and your data structure is not going to change much, select a relational database. 

In Google Cloud use Cloud SQL for any general-purpose SQL database and Cloud Spanner for large-scale globally scalable, strongly consistent use cases. In general, if your data structure may change later and if scale and availability is a bigger requirement than consistency then a non-relational database is a preferable choice.  Google Cloud offers Firestore, Memorystore, and Cloud Bigtable to support a variety of use cases across the document, key-value, and wide column database spectrum.

For more comparison resources on each database check out the overview. For more hands-on experience with Bigtable, check out our on-demand training here and learn about migrating databases to managed services check out this whitepaper.  

https://youtube.com/watch?v=2TZXSnCTd7E%3Fenablejsapi%3D1%26

For more #GCPSketchnote, follow the GitHub repo. For similar cloud content follow me on Twitter @pvergadia and keep an eye out on thecloudgirl.dev.