Building Your Security Schema

How does all this fit together? I've just given you a plateful of concepts, haven't I? Don't worry. Security is really not that complicated, and in the following pages I will take all the concepts just mentioned and walk you through the activities necessary to turn the concepts into realities. When we're done, you will have put a system security policy in place, created users, and protected your data.

A security system consists of three main parts : profiles, roles, and users. Each of these concepts carries with it some complexity, as the discussion that follows will illustrate .

A profile controls system resources, such as how many concurrent logons a user may have, idle time, and so forth. Users get a default profile automatically, but in the default profile all parameters are set to "unlimited". Although you can change the default profile, you are better off creating your own profiles. In most cases you need to set only a couple of parameters, so creating and managing profiles is not complex.

Roles were defined earlier in the chapter, and the user account is the unique account that you create for each user of your database.

With this quick overview, let's try to make connections to our waiver database. After that, we can march boldly into building the profiles, roles, and users.

Profiles

As already mentioned, profiles control the system resources that users need. For the waiver database, we have three user groups: developers, business users, and students. Let's look at some of the characteristics of each group , using a simple chart:

PROFIL
Profile Group	Multiple Logons	Idle Time	CPU Usage Control
Developers	Yes	High	Yes
Business users	No	Low	No
Students	No	No	No

Here's what we need to consider for the three groups:

1. Developers want more than one session, and we may want to limit their CPU usage to prevent runaway programs. You've all experienced this phenomenon , when you get a call from Operations about your program eating CPU time because of an infinite loop or something similar.

2. Business users need one logon session, and because they can be called away from their desks or sidetracked by phone calls, they should have a timeout parameter to protect the system.

3. Students will log on on only at certain workstations, and because the tests have a limited time span, we can let the testing program keep track of the time. The tests will be taken in a monitored room, so there is no risk if a student gives up and walks away from the screen.

Roles

Now that we have our three profiles, let's look at the roles that go with these profiles: Developer, Business, and Student. The Developer role is easy: full access. The Student role is also easy: just the ability to run certain programs. The Business role, however, is more complex, because we have those who can create and alter tests and questions, those who can enter only new authors, and those who will be doing queries and reports and not updating any tables.

For business users, then, we must have three subroles: one to manage tests, one to manage authors, and one to create queries and reports. So really we have five roles altogether.

Now bear with me here. Ultimately we want to control access to all the database objects, including forms, reports, and scripts. However, we have not yet written the forms, reports, and scripts, so we cannot grant any security privileges to these objects right now. Instead, we're going to start with our ten tables (which are listed in Table 8.1). After we have completed the programs, you should go back and modify the roles.

Table 8.1 shows how important roles really are. They clearly offer us a way to secure our system and data while letting our users do their jobs. Going through the preceding exercise is basically the way that you establish your system security policy.

Table 8.1. Defining Security Policies for Different Roles

		Business
Tables	Developer	B1	B2	B3	Student
AUTHORS	C	C	C	R	R
QUESTIONS	C	C	R	R	R
ANSWERS	C	C	R	R	R
STUDENTS	C	R	R	R	C [a]
STUDENT_HISTORY	C	R	R	R	C [a]
TEST_ID	C	C	R	R	R
QUESTION_TYPE_DESC	C	C	R	R	R
TEST_QUESTIONS_LINK	C	C	R	R	R
TEST_HISTORY	C	R	R	R	R
TEST_TYPE_DESC	C	C	R	R	R
Note : B1 = create tests; B2 = add authors; B3 = run queries; R = read; W = write; C = create (includes R and W).

^[a] Since we have given the students complete rights to these tables, isn't there a potential problem? Well, most systems give the rights to change the data in tables to the programs they run to take the test, not to the students. That is, the student can run the program, but the program has the power to change the tables. The problem with giving students these rights is that a student who by chance is able to get to the SQL> prompt could change the records. If we give them the rights to the program, any students going to the SQL> prompt will not be able to change their records because Oracle security will recognize that they do not have the right to change any data. Some of you have guessed that the students really don't need access to the tables, and that's right. All they need is a valid logon to run the test programs! I am keeping it simple here, however, just to show you the steps you have to go through to determine your security scheme. In Chapter 13 (Forms 6 i ), I will talk about how to give security rights to the programs instead of the users.

Users

Now that you have defined your profiles and roles, the next step is to take a look at the individual user accounts. There are a few things you will want to consider. Remember back in Chapter 6 we mentioned tablespaces? Well, they're back.

Each user will default to the SYSTEM tablespace unless you specify another tablespace. This is the area where the user can create tables and so forth. However, keep in mind two things here: We don't want the users doing too much in the SYSTEM tablespace because that activity will (1) slow the system and (2) open up important system tables, such as the data dictionary, to potential harm. Those who really need their own tablespaces will get them, but otherwise similar workers will share a particular tablespace.

We also need a TEMP tablespace. As described in Chapter 6, the TEMP tablespace is where sorts and the like take place. Again, we do not want everyone to default to the SYSTEM tablespace, so we will specify a particular TEMP tablespace for our users.

Let's start with an example before we look at our waiver database. We'll begin by creating a profile called WILD_HORSES , with a limit of two concurrent logons and 30 minutes of idle timeout and a maximum connect time of 8 hours. Here's the syntax:

 CREATE PROFILE WILD_HORSES LIMIT  SESSIONS_PER_CPU 2  CPU_P_SESSION UNLIMITED IDLE_TIME 30  CONNECT_TIME 480;   

(1) Note the keyword LIMIT

(2) Two logons maximum.

(3) Log off after 30 minutes.

(4) Log off after 8 hours.

(5) End with a semicolon.

Suppose that the staff now starts spending half the day in the corrals with the horses. To limit the connect time, you can just use the ALTER statement:

 ALTER PROFILE WILD_HORSES LIMIT CONNECT_TIME 240; 

To delete a profile that is no longer needed, use the DROP command:

 DROP PROFILE WILD_HORSES CASCADE; 

CASCADE means that all users who have this profile defined in their security will now default to the default profile.

Let's jump ahead a bit, and I'll show you how you can find out which profile an individual has by looking at all the profiles. We have mentioned one of the database's most powerful tool, the data dictionary . Here's a quick glimpse of the kind of information we can get from the data dictionary. These are data dictionary views that you can access right from the SQL*Plus prompt:

View	Description
USER_USERS	Shows user name , profile, and account status for the user
DBA_PROFILES	Shows all profiles and their limits

To run the DBA_PROFILES view, for example, just type in SQL> Select * from DBA_PROFILES; and you will get a listing of all existing profiles. You will find these data dictionary views very helpful later, when you can't find your documentation and have forgotten who has which profile and what the profiles mean.

Privileges

Now let's look at privileges . Roles define privileges, and these privileges can be SYSTEM privileges, such as the ability to update any table on the data dictionary, program privileges, or even column privileges, such as the ability to insert only certain columns in a particular table. You get the idea. Privileges are the core of a role, and you have to be very careful with them. Fortunately, creating roles is easy. First you define the role:

 SQL > CREATE ROLE WILD_HORSES_ROLE;  Role created.  

Now comes the fun part. You have to grant certain privileges to the role. There are some standard privileges, such as creating a session, without which the user would not be able to log on. Be aware that there are well over a hundred privileges that can be granted, including the abilities to create roles and alter roles.

Let's look at the privileges that the WILD_HORSES folks will need:

 GRANT     CREATE SESSION to WILD_HORSES_ROLE; GRANT     CREATE VIEW to WILD_HORSES_ROLE; GRANT     INSERT (horse-type) on WILD_HORSES to WILD_HORSES_ROLE; GRANT     SELECT on WILD_HORSES to WILD_HORSES_ROLE; 

These GRANT statements let users do the following:

• Open a database session

• Create a view

• Update the one field in the WILD_HORSES table

• Look at anything in the WILD_HORSES table

I hope that this small example has shown you what you can do with roles. Given the complexity of our waiver database, you must be aware by now that you're going to have to be very careful with what you let people do. Within the role commands, as you've just seen, are enough tools to protect your data while allowing your customers to do what they have to.

Now we're ready to move ahead and start building the security schema for our waiver database.

Note

You can assign more than one role to a user. In fact, this is usually the case, and it makes user account support much easier than if we tried to keep everything in one role or a couple of roles.