(DCL & TCL) IN SQL SERVER

(DCL & TCL) IN SQL SERVER

DATA CONTROL LANGUAGE & TRANSACTION CONTROL LANGUAGE

                                            

DATA CONTROL LANGUAGE: This is the 3^rd sub language available in T-SQL. It is used to control the data between different user accounts. It includes the following statements.

1. GRANT Statement
2. REVOKE Statement

Grant Statement: This statement is used to grant the permissions (INSERT, SELECT, UPDATE, DELETE) on a specific table to different user accounts.

Syntax:

GRANT {ALL/SPECIFIC PERMISSIONS} ON TABLENAME

                                                TO USER ACCOUNT (S) [WITH GRANT OPTION]

 WITH GRANT OPTION: When any user got the permissions on a specific table from other user with this option, then that user can grant the permissions on that same table to another user account. At that time sub user acts as owner.

E.g.:

GRANT ALL ON EMP TO DURGAPRASAD WITH GRANT OPTION

From the above statement DURGA PRASAD user account got all permissions on EMP table from SA user account. Mean time DURGAPRASAD can give the permissions on EMP to another user account because he got the permissions WITH GRANT OPTION.

E.g.:

GRANT INSERT, SELECT ON EMP TO KARTHIK

Now KARTHIK can perform select and insert operations on EMP table.

But KARTHIK cannot perform update and delete operations on EMP table because he does not have the corresponding permissions.

Revoke Statement: This statement is used to revoke the permissions (INSERT, SELECT, UPDATE, DELETE) on a specific table from different user accounts.

Syntax:

REVOKE {ALL/SPECIFIC PERMISSIONS} ON TABLENAME

                                                FROM USER ACCOUNT (S) [CASCADE]

CASCADE: Using this option we can destroy the communication link between user accounts more over from the main user it self we can revoke the permissions from all sub users.

E.g.:

REVOKE ALL ON EMP FROM BABBU CASCADE

The above statement revokes the permissions on EMP table from BABBU and KARTHIK. Now BABBU and KARTHIK users cannot access EMP table.

TRANSACTIONS CONTROL LANGUAGE {TCL}

Transaction is nothing but a unit of work. We can control these transactions using the following statements

1. ROLLBACK Statement
2. COMMIT Statement
3. SAVE TRAN [SACTION]

1. ROLLBACK Statement: This statement is used to cancel a particular performed transaction. To perform this statement in SQL Server we have to follow any one of the below 2 approaches.

Approach 1: SET IMPLICIT_TRANSATIONS ON: This approach is only to cancel a single recently performed operation.

E.g.:

SET IMPLICIT_TRANSATIONS ON

SELECT * FROM EMP

DELETE FROM EMP

SELECT * FROM EMP

ROLLBACK

SELECT * FROM EMP

 

Approach 2: Explicit Transactions: To approach is to cancel recently performed multiple operations.

Syntax:  BEGIN TRAN

               -------------

               GO

               -------------

               GO

               -------------

            ROLLBACK TRAN

 GO is query separator

E.g.: 

BEGIN TRAN

INSERT INTO DEPT VALUES (50,’TRA’,’AUS’)

GO

UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11

GO

DELETE FROM STUDENT WHERE SNO=101

Select the entire transaction and press F5 for one time

ROLLBACK TRAN

The ROLLBACK statement cancels INSERT on Dept, UPDATE on EMP and DELETE on Student tables.

2. COMMIT Statement: This statement makes a transaction permanent. It is not possible to rollback the committed transaction.

           

E.g.:

SELECT * FROM EMP

DELETE FROM EMP

SELECT * FROM EMP

COMMIT

SELECT * FROM EMP

The COMMIT statement deletes the data from EMP permanently. It is not possible to ROLLBACK the delete operation.

E.g.:

BEGIN TRAN

INSERT INTO DEPT VALUES (50,’TRA’,’AUS’)

GO

UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11

GO

DELETE FROM STUDENT WHERE SNO=101

Select the entire transaction and press F5 for one time

COMMIT TRAN

The above COMMIT TRAN makes all three transactions permanent. We cannot ROLLBACK the transactions.

3. SAVE TRAN Statement: This statement is used to COMMIT/ROLLBACK a particular performed transaction from the set of transactions. It is associated with alphabets in order to save the transactions.

BEGIN TRAN

SAVE TRAN A

INSERT INTO DEPT VALUES (50,’TRA’,’AUS’)

SAVE TRAN B

UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11

SAVE TRAN C

DELETE FROM STUDENT WHERE SNO=101

ROLLBACK TRAN C (The delete operation will be cancelled)

COMMIT TRAN B (The update operation performed permanently we cannot rollback)