Grants Without Roles

For those of you who have Oracle6 or just do not want to use roles, there is the old-fashioned way of granting every privilege to each individual user. You have to grant the appropriate system privileges to each user. You also have to grant the appropriate object privileges to each user. If you have an average user with five system privileges and five access privileges on 10 tables, you would have a total of 55 privileges to grant per user. A system with just 20 users would have 1100 individual grants. Such a system would include grants such as the following:

grant select,insert on golf_scores to jsmith;

grant select,insert on golf_scores to bsmith;

grant select on golf_courses to jsmith;

grant select on golf_courses to bsmith;

and so on. There are a few things you can do to make this easier. First, build scripts for each type of user that enable you to input a user name and the script, and then performs all the grants appropriate to that type of user. This can be thought of as implementing roles through scripts. Another way to make things easier is to grant as many privileges as possible to public. You do not want to compromise security, but if it is a privilege that everyone has, save yourself some time.

Use of Scripts to Capture Privilege Sets

After a few weeks on the job, most database administrators come to the conclusion that change is eternal. Developers find glitches in their software and need to make changes, new applications are constantly being installed, and every now and then you run into a problem where data is lost and you need to restore it. One of my favorite concepts in Oracle7 and 8, and especially in Oracle6, is storing all the grant commands used to set up the instance in a script file on disk. These scripts come in handy when you have to drop and re-create tables, or when you want to compare actual privilege grants to those you originally created. It also is easier to copy an existing grant line and make modifications to it than to type the new command from scratch at the SQL prompt.

It is relatively simple to create these scripts. You can use whatever text editor you prefer on your system to create a file that contains one or more lines just as you would type them at the SQL prompt, terminated by semicolons (;). The last line in this SQL script file should contain a backslash (\). The back-slash is automatically inserted when you use the edit command within SQL*Plus. Here is an example of such a file:

grant create session to golfer;

grant create session to golf_pro;

grant select,insert on golf_scores to golfer;

grant select,insert,update,delete on golf_scores to golf_pro;

To execute a multiple-line SQL script file such as this, you use either the start command or the at sign (@). The following is an example of executing the previous SQL script:

SQL @\odbasg\grants.sql



Grant succeeded.



Grant succeeded.



Grant succeeded.



Grant succeeded.



SQL>

Enterprise Manager Role and Grant Management

Enterprise Manager (covered in more detail in Chapter 21) makes role and grant management much easier than it was in olden days. You use the standard tree view to access a list of either users (who have a property page that contains their roles) or roles (which has a property page for users). You also have a property page to assign object privileges to roles or users. Refer to Chapter 21, “Administration Using Enterprise Manager,” for a more detailed discussion of this process.

Command Line Role and Grant Management

For those of you who have not set up Enterprise Manager or who use dumb terminals (or PCs emulating dumb terminals), you can still perform all of the privilege management functions using Server Manager or SQL*Plus. The basic process is to connect to the database using an account with the correct privileges (remember only the object owner can grant access to its objects). Once connected, you issue the grant commands described earlier or run a script that contains all of the grants needed.