DATA CONTROL LANGUAGE
You will use DCL to create roles, permissions, and referential integrity; it is also used to con-trol access to databases by securing them. The common statements are GRANT, REVOKE, and DENY, as shown in Table 2-9.
TABLE 2-9 DCL statements
Figure 2-12 shows how to grant User1 permission to execute SELECT and INSERT operations on the Students table.
FIGURE 2-12 DCL GRANT statement
Skill 2.1: Describe relational concepts CHAPTER 2 39
Figure 2-13 shows how to revoke User1’s permission to perform INSERT operations on the Students table.
FIGURE 2-13 DCL REVOKE statement
Figure 2-14 shows how to deny User1 the permission to perform DELETE operations on the Students table. Even if another rule grants User1 the DELETE permission, this DENY rule will override it.
FIGURE 2-14 DCL DENY statement
Understanding these DCL statements is important for managing access control and ensur-ing data security in your database. Practice using these statements to gain a clear under-standing of their impact on database operations. Make sure to always carefully consider
the implications of granting, revoking, or denying permissions to maintain the integrity and security of your data.
TRANSACTION CONTROL LANGUAGE
TCL commands are used to manage transactions in the database. They include COMMIT, ROLL-BACK, and SAVEPOINT, as shown in Table 2-10.
TABLE 2-10 TCL statements
Figure 2-15 shows how you initialize a new transaction. Any SQL statements following this will be part of this transaction.
40 CHAPTER 2 Identify considerations for relational data on Azure
FIGURE 2-15 TCL BEGIN TRANSACTION statement
Figure 2-16 shows how to start a transaction, make an update to the Students table, and then commit the transaction. This means the update is saved and cannot be rolled back.
FIGURE 2-16 TCL COMMIT statement
Figure 2-17 shows a transaction started that is making an update to the Students table. However, you can decide to roll back the transaction, meaning the update to the Students table will not be saved and the data remains as it was before the transaction began.
FIGURE 2-17 DCL ROLLBACK statement
Figure 2-18 shows a transaction started that is making an update to the Students table, cre-ating a savepoint, and making another update. Then, you decide to roll back the transaction to the savepoint. The first update will be saved, but the second update will not.
FIGURE 2-18 TCL SAVEPOINT statement
These TCL commands are crucial for maintaining the integrity of your data, especially in situations where you need to perform multiple related operations as a single atomic unit. If any of these operations fails, the TCL commands allow you to roll back the entire transaction, preventing your data from ending up in an inconsistent state.
Skill 2.1: Describe relational concepts CHAPTER 241
Leave a Reply