Security and Access

Data Control Statements are used for authorizing data access, in order to preserve the security of the data within the database. These statements can ensure that unauthorized users don’t change or view data accidentally or deliberately. Data access authorizations are provided using the GRANT statement.

The Concept of USER #

• In the SQL environment, every user is identified by what is known as an Authorization Identifier.

• All data access authorization is associated with a user through the user’s authorization identifier.

• In most RDBMSs, a user has to log-on to the RDBMS to be able to use any of its facilities.

• During this log-on process, the authorization identifier is made known to the RDBMS and subsequent activities that the user can undertake on the database depend upon the data access authorization associated with the authorization identifier.

• The process of log-on itself varies from RDBMS to RDBMS as SQL does not lay down any standard guidelines.

• The SQL standard assumes that whenever an SQL command is submitted there is an Authorization Identifier associated with it.

• In most RDBMSs, the log-on process requires the user to specify the users authorization identifier along with a password.

• The authorization identifier is also called the usercode or log-on id.

• Facilities are normally available for a user to change his or her password.

• RDBMSs also provide for different types of users, whose access to RDBMS facilities vary.

Example

• A super-user (a user with DBA authority) can create new users, insert, update and delete from table, remove existing users, create base tables and views using any table. In essence, a super-user can use any of the facilities in the RDBMS. This user can create other users (including users of type DBA), and also change the password of the default super-user.

• Users with an authorization to resource facilities of the RSBMS can create tables and views (objects) and give privileges on them to other users. These users do not have access to any objects (tables and views) unless they are owned by them, or access to them has been explicitly granted.

• Users with an authorization to only connect facilities cannot create new base tables. They can however, access exiting base tables and views provided access to these tables and views has been explicitly granted. These users can create views using the tables and views which are accessible to them.

Schemas and Qualifiers #

Table creation is a part of schema creation as given in the following example

SQL> CREATE SCHEMA AUTHORIZATION USER1;

SQL> CREATE TABLE DEPARTMENT (DEPT_NO NUMBER(3) NOT NULL, DEPT_NAME
VARCHAR2(30), LOC VARCHAR2(30), PRIMARY KEY (DEPT_NO));

SQL> CREATE TABLE EMPLOYEE (EMP_NO NUMBER(5) NOT NULL PRIMARY KEY,
EMP_SURNAME VARCHAR2(35) NOT NULL, EMP_FNAME VARCHAR2(35) NOT NULL,
DEPT_NO NUMBER(3) NOT NULL, SAL NUMBER(7,2), UNIQUE(EMP_SURNAME, EMP_FNAME),
CHECK(DEPT_NO<20 OR SAL > 5000), FOREIGN KEY (DEPT_NO) REFERENCES
DEPARTMENT(DEPT_NO));

Note: If we don’t specify column name in references table (DEPARTMENT) by default it will take PRIMARY KEY column of references table (DEPARTMENT).

A scheme is a piece of the database that is owned by some specific user. The complete database definition will typically consist of multiple schemas. The CREATE SCHEMA contains table creation, view and grant operations.

Important aspects associated with schema definition are

• AUTHORIZATION USER1 specifies that user USER1 is the owner of this schema.

• The CRAETE SCHEMA can contain many (or zero) statements for creating table and/or statements for creating views and/or statements for granting privileges to other users.

• USER1 is said to own all the tables and views created in this schema.

• A user is not allowed to own more than one schema.

• A schema cannot be owned by more than one user.

• Tables and views in different schemas can have the same (unqualified) name. Hence USER1.EMPLOYEE and USER2.EMPLOYEE refer to two different tables.

GRANT Statement #

The Syntax of the GRANT command which is used to provide data access is:

SQL> GRANT <privilege specification> ON <table> TO
<grantee list>
[WITH GRANT OPTION]

• <privilege specification> is either a comma delimited list of specific privileges (SELECT, INSERT, UPDATE, DELETE, REFERENCES) with an optional parenthesized comma delimited list of column names in case of UPDATE and REFERENCE or the word[s] ALL [PRIVILEGES].

• <table> is a base table or a view.

• <grantee> is either a comma delimited list of valid authorization identifiers or the word PUBLIC.

The GRANT command is further explained with the help of examples which assumes a user with authorization identifier USER3 and a table RESIGNATIONS (columns : SUBMITTED_TO, RESIGNED_AS, STATED_REASON and ACTUAL_REASON).

SQL> GRANT INSERT ON RESIGNATRATION TO USER3;

Will all USER3 to ass rows to the RESIGNATIONS table. GRANT of multiple privileges can be combined as follows as:

SQL> GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON RESIGNATION TO USER3;

Since the above SQL statement gives USER3 a free hand with RESIGNATIONS, it is equivalent to:

SQL> GRANT ALL PRIVILEGES ON RESIGNATION TO USER3;
OR
SQL> GRANT ALL ON RESIGNATIONS TO USER3;

The REVOKE Statement #

The base standard of SQL does not specify any means of taking away privileges once they have been granted to users. Fortunately, most implementations of SQL have standardized on the REVOKE command.

SQL> REVOKE SELECT ON RESIGNATIONS FROM USER3;

This just the opposite of the GRANT statement. The <privilege specification> and <grantee list> are to be used just as in the GRANT statement. The lack of standardization has given rise to one ambiguity.

In case a USER3 had been given a privilege WITH GRANT OPTION, an USER3 in turn passed the privilege to USER3 automatically REVOKE the privilege from USER3? Though logically this should be so, there may be difference in this aspect across implementation of SQL.