Vicentiu Ciorbaru

MariaDB allows fine grained control of access over different database objects. Granting access to a very specific resource is easy. A problem comes up when one wants to grant access to a whole database except for a particular resource (table, column, etc.). The only solution thus far is to grant access to each individual resource and omit the ones that must not be accessible. Clearly this is not maintainable, nor practical.

This is where the DENY command comes in. The gist of the feature: A user will not be able to access denied resources unless the deny is explicitly revoked. MDEV-14443 - Reverse privileges in MariaDB is an ongoing tasks that will implement it. During this talk we will go through use cases, comparison to similar features of other databases well as implementation details of the feature which will (very likely) be part of MariaDB 10.9.

MariaDB has had roles all the way back in 10.0 (2013). MySQL now supports roles as well, starting with 8.0. This talk will go through an overview of Roles in MariaDB and how they can be used. The talk will also highlight the differences, as well as the migration requirements, should you need to move to (or from) MariaDB.

Roles in MariaDB follow the SQL Standard implementation. Roles are a very useful feature for ensuring ease of use for a DBA. Not only that, but thanks to DEFAULT ROLE, applications not previously aware of roles can make use of them transparently.

In this talk we will cover the following aspects of Roles: * Creating, granting, activating. * Roles granted to roles (the role graph) * Default Roles * Information schema tables * Differences to MySQL, migration with mariadb-dump.

Whenever you write a SQL query, the database query optimizer tries to find the best possible plan to retrieve your data. The question is, what can it do and how does it do it? In this talk we will look at recent developments in query optimizers from the major database providers. With a focus on the MySQL world, we will be looking at ways to "help" the query optimizer to come up with better plans.

Window functions are an important tool for analytical queries. Window functions act as regular functions but have access to more than one row during computation. This makes them highly useful for optimising queries involving self-joins, as well as queries that need to extract data from multiple related rows from the same table. The performance speedups obtained are sometimes 100 fold. This talk will explain how that is possible, as well as use cases for window functions.

On top of that, window functions pair well with another feature that MariaDB 10.2 has introduced - Common Table Expressions. This feature was contributed by a community member and helps make queries more maintainable and sometimes faster. This feature will be covered, mostly through the use of window functions, but also introduce new use cases.

The agenda for the talk is:

• What are CTEs
• What are window functions
• Solving problems with regular SQL and window functions
• Smoothing data
• Top-N Queries
• Difference between consecutive rows