Again, this is a rather simple scheme. It avoids using the nasty “any” privileges and still should enable the developers to get their jobs done. I would like to emphasize the importance of testing new software with accounts that will have the same security scheme as the end users. As mentioned earlier, I once received an application that was tested with operating system accounts that, in effect, had system administrator privileges and Oracle accounts that had full DBA privileges. Surprisingly, nothing worked when the application was transferred to the real world where users were just users, not DBAs. Trust me, testing using only developer or object owner Oracle IDs is inviting the opportunity for you to shoot yourself in the foot.

Finally, on the other end of the spectrum from most of the systems listed previously is the laboratory or research system. In this case, every group wants to be able to create and freely manipulate its own data. I always suggest setting up roles for each project team so that they can share data with one another freely (that is, grant to the role as opposed to giving grants to all the members of that group individually). I suggest giving each of these roles the same privilege sets that were given to the developers in the test data warehouse system. Of course, there may be lab environments (for example, pharmaceutical testing) that need to exercise stringent controls over lab data. However, this example covers a number of installations wherein the database is a general-purpose tool to store data as opposed to a repository of controlled corporate business application data.

Summary

This chapter took on the rather ambitious task of covering the privileges associated with an Oracle8 database. These privileges are the key to security for the database. The Oracle8 system allows you a fine level of control over access to both the database internal functionality and to data stored in the database. In the next chapter I discuss how roles can be used to simplify the process of granting access to the database and its data.