| Previous | Table of Contents | Next |
by Joe Greene
The last several chapters may have been tough for the more action-oriented types. You had to learn what you have control over before you could see how you control it. This chapter is designed to address these how-to topics. Again, it is impossible to guess what roles and privilege sets are best for your individual situation. Therefore, this chapter discusses the commands and concepts needed to build a privilege scheme and then shows several examples. You can then use these examples to build your own privilege schemes.
The first goal is to present the concept of roles in Oracle, which greatly simplify security maintenance. Next, security administration without roles, available in Oracle7 and Oracle8, is explored. Then several typical privilege schemes that I have run across in my consulting travels are presented. The chapter winds up with one of my favorite topics—the use of scripts to capture privilege grants for future use.
Note:
Oracle7 and 8 simplify security administration with roles, if you choose to use them. Oracle6 makes you grant privileges to each individual user.
By the end of this chapter you should be comfortable with what it takes to assign and revoke Oracle privileges to users. You should also be able to build up a security scheme for new applications as they arrive. Of course, if you purchase some off-the-shelf applications, such as Oracle Financials, the privilege scheme may be highly determined by the vendor. However, you will know the questions to ask about their security schemes so you can assign the correct privileges to your users.
Access to Oracle starts with a series of privilege grants made by the database administrators and object owners. You will probably want to plan this scheme out in advance to ensure that both users have enough access to the data, and that sensitive data and privileges are properly protected. Once you have mapped your basic access needs, it is time to consider roles. A role is a group of users that share a common privilege set.
There are no rights, only privileges. Before you can do something in Oracle, you need to have the appropriate person grant you the privilege to do it. In Oracle, DBAs typically control system privileges, and object owners control access to the objects they own. DBAs and object owners assign privileges using the SQL grant command. The format of this command is rather simple:
grant privilege to user/role;
The words grant and to are the easy parts. The user or role is fairly easy. You insert the name of the role or user. If you want to grant the same set of privileges to multiple users or roles, you separate these names with commas. The only remaining part of this command is the privilege itself. There are two forms of this command. For system privileges, you merely list one or more system privileges in this part of the command. If you wish to grant multiple privileges in a single command, you separate the privileges by commas. The privileges are exactly as you saw them in Chapter 11. For privileges that contain multiple words, you separate the words with spaces. (Oracle is smart enough to figure out what you mean.) For example, to grant the system privileges create table and create view to bsmith and jsmith, you type the following:
grant create table,create view to bsmith,jsmith;
For object privileges, you need to specify both the privilege being granted and the object on which the privilege is being granted. If you wish to grant more than one privilege in a single statement, you separate the list of privileges by commas. You separate the list of privileges from the object on which these privileges are granted by the word on. As with system privileges, you can grant the privileges to one or more users, separated by commas. For example, to grant select and delete privileges on the famous golf_scores table to bsmith and jsmith, you type the following:
grant select,delete on golf_scores to bsmith,jsmith;
Because user needs can change over time, another important capability is taking granted privileges away from the users. Perhaps the users have changed jobs, or you have been told to tighten up security in your database. Whatever the reason, the command to remove specific privileges from a given user is the following:
revoke privilege from user/role;
Here is an example of this command that revokes the select privilege granted to bsmith on the golf_scores table:
revoke select on golf_scores from bsmith;
| Previous | Table of Contents | Next |