All relational database management systems provide some sort of intrinsic security mechanisms that are designed to minimise security threats as stated in the previous section. These mechanism range from the simple password protection offered in Microsoft Access to the complex user/role structure supported by advanced relational databases like Oracle and Microsoft SQL Server. But can we define access control for all these DBMS using a single mechanism?
SQL provides that interface for access control. Let us discuss the security mechanisms common to all databases using the Structured Query Language (SQL).
An excellent practice is to create individual user accounts for each database user. Although, sharing of user accounts among various users is possible or even one user account can be created for each type of user, however, such a practice should be discouraged.
Why? It could be because of the following reasons:
It will eliminate individual accountability: If any one of the users make a change in the database, we will not be able to trace it back to a specific individual even after going through audit logs. Imagine what would happen when a specific user leaves the organisation and his or her access from the database is to be removed? It will require change in the password and this will cause inconvenience to other users.
Thus, it is important that we provide separate user accounts for separate users.
Does this mechanism have any drawbacks? If the expected number of database users are small then it is all right to give them individual user name and passwords and all the database access privileges that they need to have on the database items.
However, consider a situation when there are a large number of users. Specification of access rights to all these users individually will take a long time. That is still manageable as it may be a one time effort, however, the problem will be compounded if we need to change the access right for a particular type of users. Such an activity would require a huge maintenance cost. This cost can be minimised if we use a specific concept called “Roles”. A database may have hundreds of users but their access rights may be categorised in specific roles for example, teachers, student in a university database. Such roles would require specification of access rights only once for the role. The users then can be assigned username, password and specific role. Thus, the maintenance of user accounts becomes easier as now we have limited roles to be maintained.
Granting PermissionsYou would need to create the users or roles before you grant them permissions. Then permissions can be granted to a user or a role. This can done with the use of the
SQL GRANT statement.
The syntax of this statement is:
GRANT <permissions>
[ON <table>]
TO <user/role>
[WITH GRANT OPTION]
Now, let us define this statement line-by-line. The first line,
GRANT <permissions>, allows you to specify the specific permissions on a table. These can be either relation-level data manipulation permissions (such as
SELECT,
INSERT,
UPDATE and
DELETE) or data definition permissions (such as
CREATE TABLE,
ALTER DATABASE and
GRANT). More than one permission can be granted in a single
GRANT statement, but data manipulation permissions and data definition permissions may not be combined in a single statement.
The second line, ON <table>, is used to specify the table on which permissions are being given. This line is not needed if we are granting data definition permissions.
The third line specifies the user or role that are being granted permissions.
Finally, the fourth line,
WITH GRANT OPTION, is optional. If this line is included in the statement, the user is also permitted to grant the same permissions that s/he has received to other users. Please note that the
WITH GRANT OPTION cannot be specified when permissions are assigned to a role.
Example 1: Assume that you have recently hired a group of 25 data entry operators who will be adding and maintaining student records in a university database system. They need to be able to access information in the
STUDENT table, modify this information and add new records to the table. However, they should not be able to entirely delete a record from the database Solution: First, you should create user accounts for each operator and then add them all to a new role-Dataentry. Next, we would need to use the following SQL statement to grant them the appropriate permissions:
GRANT SELECT, INSERT, UPDATE
ON STUDENT
TO Dataentry
And that is all that you need to do. Let us now examine a case where we are assigning data definition permissions.Example 2: We want to allow members of the DBA role to add new tables to our database. Furthermore, we want them to be able to grant other users permission to do the same.
Solution: The SQL statement to do so is:
GRANT CREATE TABLE
TO DBA
WITH GRANT OPTION
Notice that we have included the
WITH GRANT OPTION line to ensure that our DBAs can assign this permission to other users. At this point, we have discussed how to assign permissions to users and roles as necessary. We will now look at the methods for removing permissions from users.
Removing Permissions Once we have granted permissions, it may be necessary to revoke them at a later date.
SQL provides us with the
REVOKE command to remove granted permissions. The following is the syntax of this command:
REVOKE [GRANT OPTION FOR] <permissions>
ON <table>
FROM <user/role>
Please notice that the syntax of this command is almost similar to that of the
GRANT command. Please also note that the
WITH GRANT OPTION is specified on the
REVOKE command line and not at the end of the command as was the case in
GRANT. As an example, let us imagine we want to revoke a previously granted permission to the user Usha, such that she is not able to remove records from the
STUDENT database. The following commands will solve the problem:
REVOKE DELETE
ON STUDENT
FROM Usha
There is one additional mechanism supported by some commercial
DBMS that is worth discussing − the DENY command. This command can be used to explicitly deny permission to a user that s/he might otherwise have received because of his/her membership of a role. Here is the syntax:
DENY <permission>
ON <table>
TO <user/role>
Consider the last problem again, let us imagine that Usha was also a member of the Teachers role that also had access to the
STUDENT table. The previous
REVOKE statement would not be sufficient to deny her access to the table. It will remove the permission granted to her through a
GRANT statement, but would not affect the permissions gained through her membership in the Teachers role. However, if we use a
DENY statement it will block permission for any role. Here is the
command:
DENY DELETE
ON STUDENT
TO Usha
Thus
DENY command creates a “
NOT PERMITTED” statement in the database access controls. If we later want to give Usha permission to remove student records again from the
STUDENT table, we cannot simply use the
GRANT command. This is because of the fact that the
GRANT command permission to
DELETE record would be overridden by the existing
DENY. Thus, first we use the
REVOKE command to remove the
DENY Not permission as:
REVOKE DELETE
ON STUDENT
FROM Usha
Please notice that this command is exactly the same as the
REVOKE used to remove a granted permission. Thus, the
DENY and
GRANT commands both work in a similar fashion -- they both create permissions in the database access control mechanism. The
REVOKE command removes all such permissions for the specified user. Once this command has been issued, Usha will be able to delete student records from the table if she is a member of a role that possesses that permission. You can also issues a
GRANT command to provide the
DELETE permission to Usha’s account.
The access control mechanisms supported by the
Standard Query Language is a good starting point, but you must look into the
DBMS documentation to locate the enhanced security measures supported by your system. You will find that many
DBMS support more advanced access control mechanisms, such as granting permissions on specific attributes.
