Site Loader
Rock Street, San Francisco

In a real world situation, multiple users access the database. This creates a huge security concern. Hence, we require some mechanism to restrict the users who can access the database. Also, we need to decide in what ways these users can access the database (read or modify). This mechanism is called as Authorization.There are four kinds of authorizations which can be granted to a user. They are called as privileges. These privileges are-Authorization to read dataAuthorization to insert new data.Authorization to update data.Authorization to delete data.Granting of privilegeWe can give privileges to a user using ‘grant’ statement.The SQL syntax for granting privileges to a user is as follows:grant (privilege list)
on (relation name or view name)
to (user list);
Privilege list defines what kind of privileges are to be granted to the users mentioned in user list.These users are authorized to exercise their privileges on the relation/view specified int he SQL statement.SQL allows four kinds of privileges which are as follows:Select: This kind of authorization is required for user to read the tuples from specified relationgrant select on bank_account to Raghav;Here, we have allowed user Raghav to read the tuples from relation bank_account.Update: This privileges will allow the user to update any row of specified relation.grant update on R1 to A;Delete: This privileges is granted if we wish user to be able to delete data from the relation.grant delete on R1 to B;Insert: This will allow user to insert tuples in the relation.grant insert on R1 to C;SQl also allows following features to make authorization easy:We can grant privileges on a particular attribute of a relation too. For example-grant update (account_balance) on bank_account to Raghav; Now the user Raghav can update only the account_balance attribute of relation account_balance.All kinds privileges can be granted at once by using the keyword all privilege.Example-grant all privilege on loan to Amrit;In user list, we can use keyword public. By doing this, all the privileges granted to public will be granted to all current and future users.Example-grant select on R to public;Now all present and future users can read tuples from relation R.We can grant privileges to multiple users as follows-grant select on R to A, B;ROLESConsider a bank database. Every time a new clerk is hired, he is to be granted the same privileges as his co-workers individually. We can shorten this processby using concept of roles.Roles helps us to define a particular kind of database users. Instead of giving privileges to each clerk , we define a role ‘clerk’ and grant privileges to it. Hence, all users having the role of a clerk will be granted the authorization privileges.We can do this by using following example-Creation of role-create role clerk;Granting privileges to clerk-grant select on account to clerk;Granting role to a user-grant clerk to Amrit;Note that we can also grant multiple roles to a user. Consider the example-Grant role clerk to Raghav;
grant role manager to Raghav;Now Raghav have privileges of both manager and clerk.Transfer of PrivilegesSometimes we may want a user to be able to transfer his privileges to other users . However, by default, a user/role is not authorized to grant his privileges to other users/roles.We can give permission to a user/role to transfer his privileges by using ‘with grant option’

Post Author: admin


I'm Dora!

Would you like to get a custom essay? How about receiving a customized one?

Check it out