Authorization - Gate CSE - UPSCFEVER

Authorization

Authorizations on data include:

Authorization to read data

Authorization to insert new data.

Authorization to update data

Authorization to delete data

Each of these types of authorizations is called a privilege. We may authorize the user all, none, or a combination of these types of privileges on speciﬁed parts of a database, such as a relation or a view.

When a user submits a query or an update, the SQL implementation ﬁrst checks if the query or update is authorized, based on the authorizations that the user has been granted. If the query or update is not authorized, it is rejected.

A user who has some form of authorization may be allowed to pass on (grant) this authorization to other users, or to withdraw (revoke) an authorization that was granted earlier.

Granting and Revoking of Privileges

The SQL standard includes the privileges select, insert, update, and delete. The privilege all privileges can be used as a short form for all the allowable privileges.

A user who creates a new relation is given all privileges on that relation automatically.

The grant statement is used to confer authorization.

grant <privilege list>
on <relation name or view name>
to <user/role list>;

grant select on department to Amit, Satoshi;

The privilege list allows the granting of several privileges in one command.

The update authorization may be given either on all attributes of the relation or on only some.

If update authorization is included in a grant statement, the list of attributes on which update authorization is to be granted optionally appears in parentheses immediately after the update keyword.

If the list of attributes is omitted, the update privilege will be granted on all attributes of the relation.

grant update (budget) on department to Amit, Satoshi;

The insert authorization on a relation allows a user to insert tuples into the relation.

The insert privilege may also specify a list of attributes; any inserts to the relation must specify only these attributes, and the system either gives each of the remaining attributes default values (if a default is deﬁned for the attribute) or sets them to null.

The delete authorization on a relation allows a user to delete tuples from a relation.

The user name public refers to all current and future users of the system. Thus, privileges granted to public are implicitly granted to all current and future users.

By default, a user/role that is granted a privilege is not authorized to grant that privilege to another user/role. SQL allows a privilege grant to specify that the recipient may further grant the privilege to another user.

The SQL authorization mechanism grants privileges on an entire relation, or on speciﬁed attributes of a relation. However, it does not permit authorizations on speciﬁc tuples of a relation.

To revoke an authorization, we use the revoke statement. It takes a form almost identical to that of grant:

revoke <privilege list>
on <relation name or view name>
from <user/role list>;

revoke select on department from Amit, Satoshi;
revoke update (budget) on department from Amit, Satoshi;

Roles

Consider the real-world roles of various people in a university. Each instructor must have the same types of authorizations on the same set of relations. Whenever a new instructor is appointed, she will have to be given all these authorizations individually.

A better approach would be to specify the authorizations that every instructor is to be given, and to identify separately which database users are instructors.

When a new instructor is hired, a user identiﬁer must be allocated to him, and he must be identiﬁed as an instructor. Individual permissions given to instructors need not be speciﬁed again.

The notion of roles captures this concept.

Any authorization that can be granted to a user can be granted to a role. Roles are granted to users just as authorizations are.

```
create role instructor;
```

Roles can then be granted privileges just as the users can, as illustrated in this statement:

```
grant select on takes
to instructor;
```

Roles can be granted to users, as well as to other roles, as these statements show:

grant dean to Amit;
create role dean;
grant instructor to dean;
grant dean to Satoshi;

Note that there can be a chain of roles; for example, the role teaching assistant may be granted to all instructors. In turn the role instructor is granted to all deans. Thus, the dean role inherits all privileges granted to the roles instructor and to teaching assistant in addition to privileges granted directly to dean.

Transfer of Privileges

A user who has been granted some form of authorization may be allowed to pass on this authorization to other users.

By default, a user/role that is granted a privilege is not authorized to grant that privilege to another user/role.

If we wish to grant a privilege and to allow the recipient to pass the privilege on to other users, we append the with grant option clause to the appropriate grant command.

grant select on department to Amit with grant option;

Revoking of Privileges

Suppose that the database administrator decides to revoke the authorization of user 1. If any user has authorization from user 1, that authorization should be revoked as well.

But if a user has got authorization from more than 1 users, he would continue to have the authorization as long as atleast one of the grantors holds the authority.

Revocation of a privilege from a user/role may cause other users/roles also to lose that privilege. This behavior is called cascading revocation.

However, the revoke statement may specify restrict in order to prevent cascading revocation:

revoke select on department from Amit, Satoshi restrict;

Although the default behavior is cascading revocation we can also explicitly specify it by using 'cascade':

revoke select on department from Amit, Satoshi cascade;

To grant a privilege to the role instead of the user we use:

```
granted by current_role
```

upscfever.com