Security specification in SQL.

 

Structured Query Language (SQL) is the standard query language for invoking relational databases. Here specify some of the security specification of Sql.

The SQL data-definition language includes commands to grant and revoke privileges. The SQL standard includes delete, insert, select and update privileges. The select privilege corresponds to the read privilege. SQL also includes a references privilege that restricts a user’s ability to declare foreign key when creating relations. If the relation to be created includes a foreign key that references attributes of another relation, the user must have been granted reference privilege on those attributes.

            The grant statement is mainly used to confer authorization. The basic form of the statement is

            grant<privilege list> on <relation name> to <user list>.

The privilege list allows the granting of several privileges in one command. The following grant statement grants users U11 and U12 with select privilege.

Select authorization on the branch relation is

            grant select on branch to U11,U12.

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 at the update keyword. If the list of attributes is omitted, the update privilege is granted on all attributes of the relation.

The insert privilege may also specify a list of attributes, any inserts to the relation must specify only these attributes, and each of the remaining attributes is either given default values or set to null.

The SQL references privilege is granted on specific attributes in a manner similar to that shown for the update privilege. The following grant statement allows user U11 to create relations that references the key branch name of the branch relation as a foreign key.

revoke grant option for select on branch from U11.

             

To revoke an authorization, we use the revoke statement.

            revoke<privilege list> on <relation name> from <user list>[restrict/cascade]

Eg: revoke select on branch from U11, U12 cascade.

       revoke update(amount) on loan from U11, U21.

       revoke references (branch-name) on branch from U11.

The revocation of a privilege from a user may cause other users also to lose that privilege. This behavior is called cascading of the revoke. The revoke statement may also specify restrict.

            revoke select on branch from U11,U12 restrict.

In this case an error is returned if there are any cascading revokes, and the revoke action is not carried out.

The privilege all privileges can be used as a short from for all the allowable privileges. Similarly the user name public refers to all current and future users of the system.

            By default, a user who is granted a privilege in SQL is not authorized to grant the privilege to another user. IF we wish to grant a privilege and allow the recipient to pass the privilege on to other users, we append the with grant option clause to the appropriate grant comment. For example , if we wish to allow U11 the select privilege on branch and allow U11 to grant this privilege to others, we write.

                                    grant select on branch to U11 with grant option

            Grand and Revoke are the DCL(Data Control Language) commands. The Database Administrator provide or remove privileges on a database object.