GRANT and REVOKE

Giving and taking away privileges (SELECT, INSERT, UPDATE, DELETE, ALL) on a table or on columns, from a role or from PUBLIC, WITH GRANT OPTION and REVOKE with CASCADE.

25 cards
6 topics
Free

Study this deck

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

Privilege: what is it?
Show the answer
A specific right given to a role on a database object: reading, inserting, changing or deleting a table's rows, for instance.
A table's privileges include SELECT, INSERT, UPDATE and DELETE. They are granted one by one.
After these statements, can bob still read the client table?

```sql
GRANT SELECT, INSERT, UPDATE ON commande TO bob;
GRANT SELECT ON client TO PUBLIC;
REVOKE UPDATE ON commande FROM bob;
REVOKE SELECT ON client FROM bob;
`
- Yes, through the right granted to PUBLIC
- No, the last REVOKE took that right away from him
- No, because he lost UPDATE on commande
- Only if bob owns client
Show the answer
Yes, through the right granted to PUBLIC
The REVOKE only removes the right granted to bob himself. PUBLIC's right stays, and bob is part of it like every role. To close access: REVOKE SELECT ON client FROM PUBLIC.
To take away bob's right to read client: REVOKE SELECT ON client … bob (not TO).
Show the answer
To take away bob's right to read client: REVOKE SELECT ON client FROM bob (not TO).
GRANT goes with TO, REVOKE with FROM. In both, ON names the object.
GRANT: what is it?
Show the answer
The statement that gives a role one or several privileges on an object, for example reading the client table for the statistics tool.
The form is GRANT privileges ON object TO role. Without it, a role that does not own the object has no right on it.

Study this deck