Application software would fail to meet its objective if there is no controlled access to information in its database. Database is a repository of data. The information contained is not meant for the consumption of just anybody and should not be accessible by just any user. Those that are highly sensitive and confidential should be viewable and updatable only by trusted users deemed worthy of such privilege while those that are less confidential should be viewable and updatable by users deserving lower privilege. Thus, it is reasonable to grant users different levels of access to database information so that while some users can only view and search certain records, those with higher privilege can insert, update and delete records and even alter table structure. This situation is called multi-level access security. The result is giving different levels of authority to users over the records in the database for effective control, accountability and security enforcement purposes. This is achieved by granting users certain privileges based on available user groups.
On your stand-alone personal computer, you can afford to create only one user account for a user who will have limited or no access to the database. However, in a multi-user system, it is a different ballgame. There are multiple users accessing the database and due to this, there must be some varying levels of restrictions among the authorized users to the database depending on the level of authority the user is entitled to. The responsibility to authorize use of Database Management System (DBMS) rests with the Database Administrator (DBA) staff or the Database Administrator who must set up individual user identifiers and create individual accounts in the process. Each user identifier is associated with a password that should be known only to the user and DBMS uses this information to authenticate the user. The administrator can also set up group identifiers and create user members for each group.
Authorization is the granting of a right or privilege which enables a user to have legitimate access to database objects. The privileges are granted by an SQL GRANT statement. Authentication is a mechanism that determines whether a user is, who he or she claims to be.
Privileges are the actions that the user is permitted to carry out on a given base relation (table) or view. Examples are:
ALL PRIVILEGES – user is allowed to do anything.USAGE – user is only allowed to log in. He cannot do any other thing.ALTER – user can modify existing tables structure and indexes.CREATE – user can create new databases and tables.DELETE – user can delete table entries.DROP – user is allowed to delete tables and/or databases.INDEX – user is allowed to create and/or delete indexes.INSERT – user can add new table entries.SELECT – user can view/search table entries.UPDATE – user is allowed to modify table entries.
Consider the following SQL statement:
GRANT ALL PRIVILEGES ON assets.*TO admin identified by “adminpass”;
When the SQL is run, it would grant all privileges to a user created with username admin and adminpass password. The user will have all privileges on all the tables in the assets database. In this way, you can create an admin user group which will be for the database administrator(s). There is an omission of the KEYWORD WITH GRANT OPTION in the SQL statement. This prevents user admin from automatically passing all its special privileges on to other users when he (the administrator) is creating other user accounts for various use. When the administrator connects to the DBMS with his username admin and password adminpass using a script also having an SQL statement like the following:
GRANT SELECT, UPDATE ON assets.*TO marketing identified by “mkt”WITH GRANT OPTION;
A user account marketing is created with username marketing and mkt password and with the capability of doing only 2 things on all tables in the assets database- select and update records. This is a way of creating another user group called marketing. There is the addition of the WITH GRANT OPTION clause. This enables the user group marketing to pass privileges SELECT and UPDATE on to other users like members of the marketing user group. The users receiving the privileges may in turn grant them still to other users. With the WITH GRANT OPTION clause omitted in SQL GRANT statement creating the admin account, the administrator can maintain tight control over who has permission to use the object and over what forms of access are allowed.
Creation of user accounts can be done manually by the administrator directly in the database or programmatically using a script that connects to the DBMS with his credentials and that runs SQL GRANT statement that contains variables that can hold new usernames and passwords passed to them. He can, for instance, add more users to the user group marketing by running such script or do it manually. The SQL GRANT statement will grant same privileges granted to the marketing user group.
