Administering an analytics schema
Running an analytics schema in a relational DBMS: schema, roles and read rights, views and materialised views, indexes, partitioning of the fact table and evolving it without breaking reports.
- 21 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
- Understand
A few cards from the deck
Database schema: what is it?
Show the answer
A namespace in a database that groups tables, views and other objects: ventes.commande and compta.commande are two different tables.
The warehouse goes in its own schema so it can get separate rights and be kept apart from the production tables, without creating a second database.
After these statements, which query can the role analyste run?
```sql
GRANT USAGE ON SCHEMA ventes TO analyste;
GRANT SELECT ON ventes.commande, ventes.client TO analyste;
REVOKE SELECT ON ventes.client FROM analyste;`SELECT * FROM ventes.commande;SELECT * FROM ventes.client;DELETE FROM ventes.commande;SELECT * FROM ventes.produit;
Show the answer
SELECT * FROM ventes.commande;SELECT on commande was granted and never revoked. On client it was granted then revoked. On produit it was never given, and DELETE would require a DELETE right the role does not have.
For a role to read a table in a schema, it needs … on the schema and … on the table.
Show the answer
For a role to read a table in a schema, it needs USAGE on the schema and SELECT on the table.
The right on the table is not enough: without USAGE, PostgreSQL answers “permission denied for schema”. Both rights are given with GRANT and removed with REVOKE.
Role: what is it?
Show the answer
An entity of the database to which rights are granted, which can stand for a person or for a group of people.
Rights are given to a group role (analyst), then people are added to or removed from it: a new hire does not mean redoing every GRANT.