It is part of the function of the database administrator (DBA) to manage the use of data by multiple users.

Each user of a database will have a unique name, an authorisation identifier (aka user id).

It is part of the function of the database administrator to create such authorisation identifiers for each database user, and for each database that a user needs to access (there is no connection between databases).

Accessing a database requires that a database connection is enabled through an SQL statement, via an authorisation identifier. This can be achieved:

• Explicitly - the user provides the authorisation identifier.
• Implicitly - the authorisation identifier is acquired by the DBMS without user involvement (connection through a user process).

All database access requires an authorisation identifier. Without it a database connection cannot be made. An authorisation identifier is not a personal id. Different authorisation identifiers can be used by one person for many databases or just one database.

Security of the data is achieved using passwords, other authentication routines built into the system or as an inherent part of the operating system. These are not SQL matters.

Table definition

The implementation of a DBMS allows for the administrator to specify what user can do what in regards to the database. Not all users can access all data/tables nor can all users create tables etc.

The creator of a base table becomes the owner of that base table. And there can be only one (table owner)! This owner is all powerful. Inserting, updating, deleting, definition altering, removing - there is no limit to the owners control.

Tables created are not automatically shared. The database is partitioned according to the user. Definitions of tables are held in an SQL Schema with each table owner having their own schema. Many SQL schemas make one database. Each schema will have a name. Schemas are defined by CREATE SCHEMA statements.

In order to distinguish between tables created with identical names by different users it is necessary to use dot notation (qualified name) whereby the schema name is placed before the table name. This is normal where one user wishes to access another users table. A user wishing to access his own tables can either use the qualified or unqualified name.

Using shared tables

Using qualified names allows users to access other users data tables. Once a table has been identified in the FROM clause, a user can employ aliases to shorten the amount of text requiring input:

SELECT s.student_id, name, region             //aliases used
FROM dba.student s, dba.enrolment e           //aliases established
WHERE s.student_id = e.student_id             //aliases used
AND counsellor_no = tutor_no

It is important to remember to use qualified table or column names where naming conflicts exist (same names in different tables).

Access control

For a user to be able to have access to another users table/data, the user must have the necessary permissions. This helps in maintaining the quality and security of data.

Privileges

Access rights granted by the table owner to other users. Achieved by a GRANT statement once the table is defined:

GRANT <privileges> ON <table> TO <users>

The users are identified by authorisation identifiers. The privileges can be one or more of the following:

• SELECT - to enable the retrieval of data from the named table
• DELETE - to enable the removal of rows from the named table
• INSERT - to enable the addition of rows to the named table
• UPDATE - to enable the change of column values in the named table
• REFERENCE - to create foreign key references to the named table

A user who is granted the REFERENCE privilege is allowed to create foreign keys within their own tables referencing another users named table. Without this referential action, a table owner would lose control of the ability to do what they want with their own table. REFERENCE provides some control over this.

There is also the GRANT ALL PRIVILEGES statement. This grants all the above privileges and allows for the table owner to include a list of column names limiting the privileges.

If a table is to be made available to all users, this can be done using a PUBLIC statement:

GRANT SELECT, UPDATE (quota) ON course TO PUBLIC

Privileges can be removed using a REVOKE statement:

REVOKE UPDATE (quota) ON course FROM PUBLIC

The grant option

In addition to being granted any or all of the above privileges, a user, who is granted privileges to a table, may also be allowed to grant these privileges to other users:

GRANT SELECT ON student TO admin WITH GRANT OPTION

This action may be limited to specific privileges as in the above statement. The table name will have to be qualified with the table owners schema name. We now have a situation where there are three user roles identified as:

• Owner - the table creator
• Grantee - has a privilege to a table
• Grantor - a user who can use a GRANT statement to grant a privilege to a grantee

A grantor can use a GRANT ALL PRIVILEGES statement to a grantee. However, only those privileges that have been granted by the owner to the grantor will be granted to the grantee.

Limiting access with views

One problem with granting privileges is that all data within a table is accessed by another user(s). This may cause problems with data that may be confidential and as such should be restricted from some/all other users.

The SQL solution (at this level) is the creation first of a view, limiting the columns of data chosen, followed by the granting of privileges to the view.

Comments, suggestions, ideas to
Stuart Banner