DCL STATEMENTS (COMMIT, ROLLBACK, SAVEPOINT, GRANT, REVOKE)

Any relational database must be able to pass the ACID test: it must guarantee atomicity, consistency, isolation, and durability. The principle of atomicity states that all parts of a transaction must complete or none of them. The principle of consistency states that the results of a query must be consistent with the state of the database at the time the query started. The principle of isolation states that an incomplete (that is, uncommitted) transaction must be invisible to the rest of the world. The principle of durability states that once a transaction completes, it must be impossible for the database to lose it.

After DML commands we have to commit transaction in order , for the changes to take effect in the DB.

TRANSACTIONS

COMMIT

TO undo the changes we have made with the COMMIT statement we can use

ROLLBACK

We can also make certain points in our code that will let us ROLLBACK only to that point.

SAVEPOINT name_of_savepoint;

ROLLBACK TO name_of_savepoint;

FOR UPDATE

There is an option to do select on some table and lock it for changes, so that we can do update on it.

SELECT * FROM table_name FOR UPDATE;

PRIVILEGES

GRANT : gives user and roles rights and privileges on database objects or schema.

GRANT privilege_name TO user_name_or_role_name;

Privileges:
SESSION – allows user to connect to the DB
RESOURCE – allows user to work with objects on his schema
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE

We can also use the command to GRANT a role to a user

GRANT role_name TO user_name;

REVOKE : removes or restricts user rights or privileges on database objects.