Finally, it is important to create dummy Oracle user IDs that have roles and privileges similar to those of the actual users of the application. You prove nothing if you do all your testing from the object owner account, which has full access to all the objects that it owns. The developer accounts presented in Figure 14.3 have some additional privileges, such as select any, that speed the development process; they are also not valid for testing. You could grant all these roles to the developers and trust that they would set their roles accordingly before testing. However, I do not trust schemes where there is a lot of setup work that needs to be done before conducting tests, and therefore I create these accounts that simulate real-world users. The test cases developed then specifically state that they need to be run as a certain user.

Figure 14.3.  Sample development instance privilege scheme.

This section has provided you with three samples to consider when you develop your own security scheme. It is important to remember that the security scheme should be tightly tied to the business rules and needs. Therefore, your own individual schemes may be different from those presented in this section. I have tried several means to help develop my security schemes. My current favorite is illustrated in Figure 14.4. It has the advantage of including the common roles and grants I would use, while fitting on a single sheet of paper. Feel free to modify it into something that fits your own style.

Figure 14.4.  Sample role security checklist.

Summary

This chapter has covered the Oracle role and grant scheme used in Oracle8. It is the key to Oracle database security and therefore cannot be treated lightly. Users start without any privileges (not even the privilege to connect to the database). You then have to add privileges to users to allow them to perform their jobs. These privilege grants can take the form of Enterprise Manager property page modifications, commands issued at the command line, or scripts containing the grant commands that are executed.