The SQL specifies that a transaction begins automatically when an SQL statement is executed. The following four statements show the different ways to end the transaction and the next two statements show different privilages on database to users.
1. Commit Statement
A transaction is completed successfully after commit. Commit statement is used to make data changes permanent to database.
Syntax : COMMIT;
2. Rollback
Rollback statement is used to terminate current transaction and discarding all data changes pending due to that transaction.
Syntax : ROLLBACK;
3. Savepoint
It is used to partially commit the current transaction and put a savepoint at that position.
Syntax : SAVEPOINT <name>;
If second savepoint will be created within same transaction then earlier savepoint is automatically discarded by database.
4. Rollback to Savepoint
It is used to partially rollback any transaction and pending data changes upto specified savepoint are discarded.
Syntax : ROLLBACK To <SAVEPOINT name>;
5. Grant Statement
Grant statement is used to give different permissions on different portions of database to different users. In a multi-user database management system, it is required to grant different permissions for security purposes.
Syntax : GRANT <privilege-list>| ALL
ON <object>
TO <user-list>| PUBLIC
[WITH GRANT OPTION]
where
Privilege list–specifies the permissions to be granted to users like ALTER, DELETE, CREATE, UPDATE etc.
ALL–specifies all the permissions.
Object–specifies name of tables or their columns.
User-list–specifies name of users to which permissions are granted.
PUBLIC–specifies all users.
WITH GRANT OPTION–specifies that the user in user-list can give permissions to any other user that were granted to him [If specified].
Ex. : Grant all the permissions on table Emp to all users
GRANT ALL
ON Emp
TO PUBLIC;
Ex. : Grant UPDATE and DELETE permissions on table Dept to users Nick and Rohn
GRANT UPDATE, DELETE
ON Dept
TO Nick, Rohn;
Ex. : Grant ALTER authority on table Emp to user Nick with the capability to grant those authorities to other users
GRANT ALTER
ON Emp
TO Nick
WITH GRANT OPTION;
Ex. : Grant INSERT authority on EID column of table Emp to user Nick.
GRANT INSERT
ON Emp EID
TO Nick;
Note: In table emp, eID is primary key so you cannot insert NULL value. Before granting insert authority on a particular column you must sure that column has a default value otherwise you cannot insert any new value.
6. REVOKE Statement
REVOKE statement is used to take away any authority from a user that was granted earlier.
Syntax : REVOKE <privilege-list>| ALL
ON <table name> [(column-comma-list)]
FROM <user-list>| PUBLIC
Ex. : REVOKE the UPDATE permission on table Dept from Rohan.
REVOKE UPDATE
ON Dept
FROM Rohn;
Ex. : REVOKE INSERT and UPDATE permission on Name and EID columns of
table Emp from all users.
REVOKE INSERT, UPDATE (Name, EID)
ON Emp
FROM PUBLIC;
Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)