This design might seem to force you to execute a tedious number of GRANT statements when you first set up the database. Furthermore, privileges require constant maintenance, as people change jobs. For example, if a clerk in Human Resources is terminated, you want to revoke the Update privilege as soon as possible, otherwise the unhappy employee might execute a statement such as the following one:
UPDATE hr_data SET (emp_name, hire_date, dept_num) = (NULL, NULL, 0)
Less dramatic, but equally necessary, privilege changes are required daily, or even hourly, in any model that contains sensitive data. If you anticipate this need, you can prepare some automated tools to help maintain privileges.
Your first step should be to specify privilege classes that are based on the jobs of the users, not on the structure of the tables. For example, a first-line manager needs the following privileges:
When a manager is promoted to a staff position or sent to a field office, you must revoke all those privileges and grant a new set of privileges.
Define the privilege classes you support, and for each class specify the databases, tables, and columns to which you must give access. Then devise two automated routines for each class, one to grant the class to a user and one to revoke it.
Your operating system probably supports automatic execution of command scripts. In most operating environments, interactive SQL tools such as DB-Access accept commands and SQL statements to execute from the command line. You can combine these two features to automate privilege maintenance.
The details depend on your operating system and the version of the interactive SQL tool that you are using. You must create a command script that performs the following functions:
In this way, you can reduce the change of the privilege class of a user to one or two commands.
Another way to avoid the difficulty of changing user privileges on a case-by-case basis is to use roles. The concept of a role in the database environment is similar to the group concept in an operating system. A role is a database feature that lets the DBA standardize and change the privileges of many users by treating them as members of a class.
For example, you can create a role called news_mes that grants connect, insert, and delete privileges for the databases that handle company news and messages. When a new employee arrives, you need only add that person to the role news_mes. The new employee acquires the privileges of the role news_mes. This process also works in reverse. To change the privileges of all the members of news_mes, change the privileges of the role.
To start the role creation process, determine the name of the role and the connections and privileges you want to grant. Although the connections and privileges are strictly in your domain, you need to consider some factors when you name a role. Do not use any of the following words as role names:
|
alter |
default |
index |
null |
resource |
|
connect |
delete |
insert |
public |
select |
|
DBA |
execute |
none |
references |
update |
A role name must be different from existing role names in the database. A role name must also be different from user names that are known to the operating system, including network users known to the server computer. To make sure your role name is unique, check the names of the users in the shared memory structure who are currently using the database as well as the following system catalog tables:
When the situation is reversed and you are adding a user to the database, check that the user name is not the same as any of the existing role names.
After you approve the role name, use the CREATE ROLE statement to create a new role. After the role is created, all privileges for role administration are, by default, given to the DBA.
As DBA, you can use the GRANT statement to grant role privileges to users. You can also give a user the option to grant privileges to other users. Use the WITH GRANT OPTION clause of the GRANT statement to do this. You can also use the WITH GRANT OPTION clause when granting privileges to roles as in this example:
GRANT rol1 TO usr1 WITH GRANT OPTION;
When you grant role privileges, you can substitute a role name for the user name in the GRANT statement. You can grant a role to another role. For example, say that role A is granted to role B. When a user enables role B, the user gets privileges from both role A and role B.
However, a cycle of role granting cannot be transitive. If role A is granted role B, and role B is granted role C, then granting C to A returns an error.
If you need to change privileges, use the REVOKE statement to delete the existing privileges and then use the GRANT statement to add the new privileges.
After the DBA grants privileges and adds users to a role, there are two possible ways to enable roles. The DBA can specify a default role for PUBLIC or for individual users using the GRANT DEFAULT ROLE statement. This will be the initial role setting when connecting to the database. The role can also be changed by the user using the SET ROLE statement. When a role has been enabled, all privileges that have been granted to the role will be available as well as all privileges explicitly granted to you and to PUBLIC.
You can find yourself in a situation where you are uncertain which user is included in a role. Perhaps you did not create the role, or the person who created the role is not available. Issue queries against the sysroleauth and sysusers system catalog tables to find who is authorized for which table and how many roles exist.
After you determine which users are members of which roles, you might discover that some roles are no longer useful. To remove a role, use the DROP ROLE statement. Before you remove a role, the following conditions must be met: