Authentication and authorisation
Authentication
The standard way to authenticate a user is to check a standard set of credentials such as a username and password against those held by the system. If the system recognises the password supplied by the user, we assume that the user is who they say they are. This is the end of the authentication process, however. Authentication in itself does not confer any particular privileges to the user; it simply succeeds or fails to identify them.
It is tempting when discussing authentication to digress into alternatives to the standard username and password. Systems can, for example, include physical tokens or biometric measurements which can be used for authentication, and the relative effectiveness of these methods is worthy of consideration if security is a particular concern. However, these subjects are not appropriate in a database module at this level simply because they are components of the hardware system, and therefore independent of the DBMS. In any case, the general model of authentication is the same for all the methods mentioned: a piece of information supplied by the user (password, fingerprint, etc.) is compared to a known value, and if it matches the system confirms the user's identity.
Authorisation
Once a user has been authenticated, the appropriate level of access is determined with reference to information stored about the authenticated user account. In most relational databases, access is controlled by a set of standard privileges which can be granted to a user. Privileges are maintained independently of the user account, so that an account can exist with no privileges at all, and privileges can be added or removed as required.
The most basic privilege is to allow the user to establish a session with the database. Thereafter the user's ability to perform basic functions can be controlled by adding further privileges such as being allowed to create tables. When you created new user accounts earlier in the module, you assigned them the two user roles CONNECT and RESOURCE as shown below.
In Oracle, roles provide a way to assign several privileges to a user in group rather than assigning them separately. The RESOURCE role includes, for example, the privilege to create and drop tables and to allow other users to access owned tables. The list of individual privileges can be seen by activating the System Privileges tab in the Create User dialog as shown below.
Each privilege is very specific which give the DBA a high degree of control over what users are permitted to do.
The dialogs in SQL Developer simply provide a GUI for the standard method for adding or removing privileges on a user account which is to use the SQL keywords GRANT and REVOKE. The table below shows some example SQL statements using these keywords and their interpretations.
| Statement | Meaning |
|---|---|
| GRANT CREATE SESSION TO bob; | Allow the user bob to start a session with the database |
| GRANT DROP USER TO bob; | Allow the user bob to drop user accounts |
| REVOKE CREATE TRIGGER FROM bob; | Remove the ability to create triggers from user bob |
A full list of Oracle system privileges can also be found under the entry for GRANT in the SQL Language Reference.
The GRANT and REVOKE commands can be used with roles as well as with privileges which makes command line administration simpler. The example below shows the creation of a user account using the GRANT command with the additional INDENTIFIED BY clause to supply a password. This same statement can be used to change the password for an existing user. Note that single quotes are not required for the password value.
1 | |