Waiver Database SecurityWe are going to accomplish the major task of creating security for our waiver database by separately creating the four key elements of the security system:
TablespacesWe'll create two tablespaces: USERS and TEMP. Here's the first one: CREATE TABLESPACE USERS DATAFILE 'D:\USERS.DAT' SIZE 300M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 500M ONLINE;
This is pretty clear. We use the CREATE TABLESPACE command, give the tablespace a name, and give the data file a name and a start size. In this case we assigned the data file 300MB to support about 20 users, told it to grow if it had to (up to 500MB), and stated that this tablespace was to be online. Now we do the same thing for the TEMP tablespace: CREATE TABLESPACE TEMP DATAFILE 'D:\TEMP.DAT' SIZE 300M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 500M TEMPORARY;
ProfilesMoving on to profiles, first we'll create the Developer profile: CREATE PROFILE WAIVER_DEV_PROFILE LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME UNLIMITED IDLE_TIME 45; Then the Business profile: CREATE PROFILE WAIVER_BUS_PROFILE LIMIT SESSIONS_PER_USER 1 CPU_PER_CALL UNLIMITED CONNECT_TIME UNLIMITED IDLE_TIME 15; And finally, the Student profile: CREATE PROFILE WAIVER_STUDENT_PROFILE LIMIT SESSIONS_PER_USER 1 CPU_PER_CALL UNLIMITED CONNECT_TIME UNLIMITED IDLE_TIME UNLIMITED; One interesting thing is that in a profile you can have quite a bit of control over logins. Let's say you want to limit the number of failed login attempts, and then lock the user account for one hour when the maximum number of attempts is reached. To do this, add the following lines to the profile: FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 60; You can go much further and control when passwords have to be changed, how long before a user can reuse a password, whether there is a grace period for changing a password before the account is locked, and so forth. Consult the Oracle documentation for details. There's enough power here to satisfy even the most demanding auditor . RolesMoving on to roles, we use the following command to create the Developer role: CREATE ROLE WAIVER_DEV_ROLE; Then we issue the GRANT statements to the role: GRANT CREATE SESSION TO WAIVER_DEV_ROLE; GRANT CREATE SYNONYM TO WAIVER_DEV_ROLE; GRANT CREATE TABLE TO WAIVER_DEV_ROLE; GRANT CREATE VIEW TO WAIVER_DEV_ROLE; GRANT CREATE PROCEDURE TO WAIVER_DEV_ROLE; GRANT CREATE TRIGGER TO WAIVER_DEV_ROLE; GRANT SELECT ANY TABLE TO WAIVER_DEV_ROLE; Note the following:
Moving on to the Business users, we have to create three roles: CREATE ROLE WAIVER_BUS_ROLE1; CREATE ROLE WAIVER_BUS_ROLE2; CREATE ROLE WAIVER_BUS_ROLE3; and then assign the grants to each one. Here are the GRANT statements for the first role: GRANT CREATE SESSION TO WAIVER_BUS_ROLE1; GRANT INSERT ON QUESTIONS TO WAIVER_BUS_ROLE1; GRANT UPDATE ON QUESTIONS TO WAIVER_BUS_ROLE1; GRANT DELETE ON QUESTIONS TO WAIVER_BUS_ROLE1; GRANT INSERT ON ANSWERS TO WAIVER_BUS_ROLE1; GRANT UPDATE ON ANSWERS TO WAIVER_BUS_ROLE1; GRANT DELETE ON ANSWERS TO WAIVER_BUS_ROLE1; GRANT INSERT ON AUTHORS TO WAIVER_BUS_ROLE1; GRANT UPDATE ON AUTHORS TO WAIVER_BUS_ROLE1; GRANT DELETE ON AUTHORS TO WAIVER_BUS_ROLE1; GRANT INSERT ON TEST_ID TO WAIVER_BUS_ROLE1; GRANT UPDATE ON TEST_ID TO WAIVER_BUS_ROLE1; GRANT DELETE ON TEST_ID TO WAIVER_BUS_ROLE1; GRANT INSERT ON TEST_TYPE_DESCRIPTION TO WAIVER_BUS_ROLE1; GRANT UPDATE ON TEST_TYPE_DESCRIPTION TO WAIVER_BUS_ROLE1; GRANT DELETE ON TEST_TYPE_DESCRIPTION TO WAIVER_BUS_ROLE1; GRANT INSERT ON TEST_QUESTIONS_LINK TO WAIVER_BUS_ROLE1; GRANT UPDATE ON TEST_QUESTIONS_LINK TO WAIVER_BUS_ROLE1; GRANT DELETE ON TEST_QUESTIONS_LINK TO WAIVER_BUS_ROLE1; GRANT INSERT ON QUESTION_TYPE_DESCRIPTION TO WAIVER_BUS_ROLE1; GRANT UPDATE ON QUESTION_TYPE_DESCRIPTION TO WAIVER_BUS_ROLE1; GRANT DELETE ON QUESTION_TYPE_DESCRIPTION TO WAIVER_BUS_ROLE1; GRANT CREATE VIEW TO WAIVER_BUS_ROLE1; GRANT SELECT TABLE TO WAIVER_BUS_ROLE1;
Here are the GRANT statements for the second role: GRANT CREATE SESSION TO WAIVER_BUS_ROLE2; GRANT INSERT ON AUTHORS TO WAIVER_BUS_ROLE2; GRANT UPDATE ON AUTHORS TO WAIVER_BUS_ROLE2; GRANT DELETE ON AUTHORS TO WAIVER_BUS_ROLE2; GRANT CREATE VIEW TO WAIVER_BUS_ROLE2; GRANT SELECT TABLE TO WAIVER_BUS_ROLE2;
And finally, here are the GRANT statements for the third role: GRANT CREATE SESSION TO WAIVER_BUS_ROLE3; GRANT CREATE VIEW TO WAIVER_BUS_ROLE3; GRANT SELECT TABLE TO WAIVER_BUS_ROLE3;
Note IMPORTANT: Again, when we create forms, procedures, and so forth later, the users will have rights to run those programs. We will modify the roles as necessary!!! The last role is the Student role. We create it like this: CREATE ROLE WAIVER_STUDENT_ROLE; And here are the GRANT statements to go along with the Student role: GRANT CREATE SESSION TO WAIVER_STUDENT_ROLE; GRANT INSERT ON STUDENTS TO WAIVER_STUDENT_ROLE; GRANT SELECT TABLE TO WAIVER_STUDENT_ROLE;
UsersThe fourth step is to create the user accounts. We're almost done. By now you're probably starting to understand how logical the process is. We're going to create accounts for ten developers, ten business users, and ten students. The ten student accounts are to control the test process. As you've seen, we could alter STUDENT_ROLE to allow ten concurrent logons , but we'd prefer not to. Creating a user involves only two steps: (1) using the CREATE USER command and (2) assigning it any role(s). Here's the syntax: CREATE USER <USER_NAME> IDENTIFIED BY <PASSWORD> DEFAULT TABLESPACE <TABLESPACE NAME> TEMPORARY TABLESPACE <TABLESPACE NAME> QUOTA <XX> ON <TABLESPACE NAME> QUOTA <XX> ON <TEMPORARY TABLESPACE NAME> PROFILE <PROFILE NAME>; After the user is created, issue the GRANT statement: GRANT <ROLE> TO <USER_NAME>; Now let's put the pieces together: CREATE USER WAIVER_DEV1 IDENTIFIED BY WAIVER_DEV_PW1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE GRANT WAIVER_DEV_ROLE TO WAIVER_DEV1;
Repeat these steps for users WAIVER_DEV2 through WAIVER_DEV10 . Now do the same for the Business users, being careful about which profile goes to whom. CREATE USER WAIVER_BUS1 IDENTIFIED BY WAIVER_BUS_PW1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE1 to WAIVER_BUS1; After creating the first Business user, create user accounts for WAIVER_BUS2 through WAIVER_BUS4 just as you did for BUS1 . Then create the BUS5 user account like this: CREATE USER WAIVER_BUS5 IDENTIFIED BY WAIVER_BUS_PW5 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE2 to WAIVER_BUS5; Now create two more Business users ” BUS6 and BUS7 ”exactly as you created BUS5. Create the eighth Business user like this: CREATE USER WAIVER_BUS8 IDENTIFIED BY WAIVER_BUS_PW8 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE3 to WAIVER_BUS8; And finally, create BUS9 and BUS10 just as you created BUS8 . Moving on to the students, create Student users 1 through 10 like this: CREATE USER WAIVER_STU1 IDENTIFIED BY WAIVER_STU_PW1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU1; Here's a quick synopsis of the entire process: 1. CREATE TABLESPACE USERS DATAFILE 'D:\USERS.DAT' SIZE 300M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 500M ONLINE; 2. CREATE TABLESPACE TEMP DATAFILE 'D:\TEMP.DAT' SIZE 300M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 500M ONLINE; 3. CREATE PROFILE WAIVER_DEV_PROFILE LIMIT SESSIONS_PER_USER 5 CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED CONNECT_TIME UNLIMITED IDLE_TIME UNLIMITED; 4. CREATE ROLE WAIVER_DEV_ROLE; 5. GRANT CREATE SESSION TO WAIVER_DEV_ROLE; GRANT CREATE SYNONYM TO WAIVER_DEV_ROLE; GRANT CREATE TABLE TO WAIVER_DEV_ROLE; GRANT CREATE VIEW TO WAIVER_DEV_ROLE; GRANT CREATE PROCEDURE TO WAIVER_DEV_ROLE; GRANT CREATE TRIGGER TO WAIVER_DEV_ROLE; GRANT SELECT ANY TABLE TO WAIVER_DEV_ROLE; 6. CREATE USER WAIVER_DEV1 IDENTIFIED BY WAIVER_DEV1_PW1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; 7. GRANT WAIVER_DEV_ROLE TO WAIVER_DEV1;
Note Here's an important point: Users cannot see tables that are owned by another user unless they have rights to those tables. (That is, the tables are in another schema.) Sometimes users find another user's table name when using ALL_TABLES , but they get an "ORA-0403" or similar error message saying that the table does not exist. The owner knows the table exists because he or she is the one who created it. Users who have rights to a table that they do not own can see the table in two ways: using either a synonym or the format "schema name.table name". After creating a synonym, an owner can either specifically give other users the rights to use that synonym or make the synonym public. Here's how we create public synonyms for tables: CREATE public synonym STUDENTS for STUDENTS; As you probably guessed, because it is a public synonym, anyone can now access the table. As mentioned, a user who has been given rights to the table can always put the schema name in front of the table name ”for example: SELECT * from SYSTEM.STUDENTS; However, this command is unwieldy, so most sites create synonyms for tables. Also be aware that the owner of the schema has to create these synonyms for tables. So if you find that your users cannot access the tables that you created with the system ID or another ID, then create public synonyms for all of the tables, and you should be all set. Use the same name as the original table. Help from the Data DictionaryEarlier in this chapter I showed you how to look at profiles using the data dictionary. Although I will discuss the data dictionary in more detail in Chapter 10, I want to include in this chapter other data dictionary views that are helpful when you're looking for information on tablespaces, roles, and password limits. Table 8.2 lists these views. Table 8.2. Data Dictionary Views Relevant to Tablespaces, Roles, and Password Limits
The Entire Waiver Database Security ScriptOften the security setup is done from a script. At the end of the script I'll give you a tip on how to have it prompt you for user name and so forth. Most security folks use such scripts to make their lives easier as staff roles change. For now, though, here is the complete script to create all the profiles, roles, and user accounts mentioned in this chapter: -- Script to build profiles, roles, and user accounts -- -- Guerrilla Oracle -- -- Run this script from the SQL> prompt: -- SQL>@SECURITY_LOAD.SQL -- where @ = path to the script -- -- When you make changes, remember to save the file as: -- TYPE = 'all files' -- and put double quotes around the name: -- "SECURITY_LOAD.SQL" ------------------------------------------------------- CREATE PROFILE WAIVER_DEV_PROFILE LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_CALL 4000 CONNECT_TIME UNLIMITED IDLE_TIME 45; CREATE PROFILE WAIVER_BUS_PROFILE LIMIT SESSIONS_PER_USER 1 CPU_PER_CALL UNLIMITED CONNECT_TIME UNLIMITED IDLE_TIME 15; CREATE PROFILE WAIVER_STUDENT_PROFILE LIMIT SESSIONS_PER_USER 1 CPU_PER_CALL UNLIMITED CONNECT_TIME UNLIMITED IDLE_TIME UNLIMITED; ALTER PROFILE WAIVER_BUS_PROFILE LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 15; ALTER PROFILE WAIVER_DEV_PROFILE LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 5; CREATE ROLE WAIVER_DEV_ROLE; GRANT CREATE SESSION TO WAIVER_DEV_ROLE; GRANT CREATE SYNONYM TO WAIVER_DEV_ROLE; GRANT CREATE TABLE TO WAIVER_DEV_ROLE; GRANT CREATE VIEW TO WAIVER_DEV_ROLE; GRANT CREATE PROCEDURE TO WAIVER_DEV_ROLE; GRANT CREATE TRIGGER TO WAIVER_DEV_ROLE; GRANT SELECT ANY TABLE TO WAIVER_DEV_ROLE; CREATE ROLE WAIVER_BUS_ROLE1; CREATE ROLE WAIVER_BUS_ROLE2; CREATE ROLE WAIVER_BUS_ROLE3; -- -- -- Grants to the WAIVER_BUS_ROLE1: -- GRANT CREATE SESSION TO WAIVER_BUS_ROLE1; GRANT INSERT ON QUESTIONS TO WAIVER_BUS_ROLE1; GRANT UPDATE ON QUESTIONS TO WAIVER_BUS_ROLE1; GRANT DELETE ON QUESTIONS TO WAIVER_BUS_ROLE1; GRANT INSERT ON ANSWERS TO WAIVER_BUS_ROLE1; GRANT UPDATE ON ANSWERS TO WAIVER_BUS_ROLE1; GRANT DELETE ON ANSWERS TO WAIVER_BUS_ROLE1; GRANT INSERT ON AUTHORS TO WAIVER_BUS_ROLE1; GRANT UPDATE ON AUTHORS TO WAIVER_BUS_ROLE1; GRANT DELETE ON AUTHORS TO WAIVER_BUS_ROLE1; GRANT INSERT ON TEST_ID TO WAIVER_BUS_ROLE1; GRANT UPDATE ON TEST_ID TO WAIVER_BUS_ROLE1; GRANT DELETE ON TEST_ID TO WAIVER_BUS_ROLE1; GRANT INSERT ON TEST_TYPE_DESC TO WAIVER_BUS_ROLE1; GRANT UPDATE ON TEST_TYPE_DESC TO WAIVER_BUS_ROLE1; GRANT DELETE ON TEST_TYPE_DESC TO WAIVER_BUS_ROLE1; GRANT INSERT ON TEST_QUESTIONS_LINK TO WAIVER_BUS_ROLE1; GRANT UPDATE ON TEST_QUESTIONS_LINK TO WAIVER_BUS_ROLE1; GRANT DELETE ON TEST_QUESTIONS_LINK TO WAIVER_BUS_ROLE1; GRANT INSERT ON QUESTIONS_TYPE_DESC TO WAIVER_BUS_ROLE1; GRANT UPDATE ON QUESTIONS_TYPE_DESC TO WAIVER_BUS_ROLE1; GRANT DELETE ON QUESTIONS_TYPE_DESC TO WAIVER_BUS_ROLE1; GRANT CREATE VIEW TO WAIVER_BUS_ROLE1; -- -- -- Grants to the WAIVER_BUS_ROLE2: -- GRANT CREATE SESSION TO WAIVER_BUS_ROLE2; GRANT INSERT ON AUTHORS TO WAIVER_BUS_ROLE2; GRANT UPDATE ON AUTHORS TO WAIVER_BUS_ROLE2; GRANT DELETE ON AUTHORS TO WAIVER_BUS_ROLE2; GRANT CREATE VIEW TO WAIVER_BUS_ROLE2; -- -- -- Grants to the WAIVER_BUS_ROLE3: -- GRANT CREATE SESSION TO WAIVER_BUS_ROLE3; GRANT CREATE VIEW TO WAIVER_BUS_ROLE3; ---- Student role and grants ----- -- CREATE ROLE WAIVER_STUDENT_ROLE; GRANT CREATE SESSION TO WAIVER_STUDENT_ROLE; GRANT INSERT ON STUDENTS TO WAIVER_STUDENT_ROLE; -- -- -- Create users and assign roles -- -- -- First build ten Developer accounts: CREATE USER WAIVER_DEV1 IDENTIFIED BY WAIVER_DEV_PW1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; GRANT WAIVER_DEV_ROLE TO WAIVER_DEV1; CREATE USER WAIVER_DEV2 IDENTIFIED BY WAIVER_DEV_PW2 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; GRANT WAIVER_DEV_ROLE TO WAIVER_DEV2; CREATE USER WAIVER_DEV3 IDENTIFIED BY WAIVER_DEV_PW3 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; GRANT WAIVER_DEV_ROLE TO WAIVER_DEV3; CREATE USER WAIVER_DEV4 IDENTIFIED BY WAIVER_DEV_PW4 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; GRANT WAIVER_DEV_ROLE TO WAIVER_DEV4; CREATE USER WAIVER_DEV5 IDENTIFIED BY WAIVER_DEV_PW5 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; GRANT WAIVER_DEV_ROLE TO WAIVER_DEV5; CREATE USER WAIVER_DEV6 IDENTIFIED BY WAIVER_DEV_PW6 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; GRANT WAIVER_DEV_ROLE TO WAIVER_DEV6; CREATE USER WAIVER_DEV7 IDENTIFIED BY WAIVER_DEV_PW7 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; GRANT WAIVER_DEV_ROLE TO WAIVER_DEV7; CREATE USER WAIVER_DEV8 IDENTIFIED BY WAIVER_DEV_PW8 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; GRANT WAIVER_DEV_ROLE TO WAIVER_DEV8; CREATE USER WAIVER_DEV9 IDENTIFIED BY WAIVER_DEV_PW9 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; GRANT WAIVER_DEV_ROLE TO WAIVER_DEV9; CREATE USER WAIVER_DEV10 IDENTIFIED BY WAIVER_DEV_PW10 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_DEV_PROFILE; GRANT WAIVER_DEV_ROLE TO WAIVER_DEV10; -- Next build four business users for BUS1_ROLE: CREATE USER WAIVER_BUS1 IDENTIFIED BY WAIVER_BUS_PW1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE1 TO WAIVER_BUS1; CREATE USER WAIVER_BUS2 IDENTIFIED BY WAIVER_BUS_PW2 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE1 TO WAIVER_BUS2; CREATE USER WAIVER_BUS3 IDENTIFIED BY WAIVER_BUS_PW3 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE1 TO WAIVER_BUS3; CREATE USER WAIVER_BUS4 IDENTIFIED BY WAIVER_BUS_PW4 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE1 TO WAIVER_BUS4; CREATE USER WAIVER_BUS6 IDENTIFIED BY WAIVER_BUS_PW6 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE2 TO WAIVER_BUS6; CREATE USER WAIVER_BUS7 IDENTIFIED BY WAIVER_BUS_PW7 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE2 TO WAIVER_BUS7; CREATE USER WAIVER_BUS8 IDENTIFIED BY WAIVER_BUS_PW8 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE3 TO WAIVER_BUS8; CREATE USER WAIVER_BUS9 IDENTIFIED BY WAIVER_BUS_PW9 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE3 TO WAIVER_BUS9; CREATE USER WAIVER_BUS10 IDENTIFIED BY WAIVER_BUS_PW10 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_BUS_PROFILE; GRANT WAIVER_BUS_ROLE3 TO WAIVER_BUS10; -- -- -- Now create the Student accounts -- CREATE USER WAIVER_STU1 IDENTIFIED BY WAIVER_STU_PW1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU1; CREATE USER WAIVER_STU2 IDENTIFIED BY WAIVER_STU_PW2 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU2; CREATE USER WAIVER_STU3 IDENTIFIED BY WAIVER_STU_PW3 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU3; CREATE USER WAIVER_STU4 IDENTIFIED BY WAIVER_STU_PW4 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU4; CREATE USER WAIVER_STU5 IDENTIFIED BY WAIVER_STU_PW5 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU5; CREATE USER WAIVER_STU6 IDENTIFIED BY WAIVER_STU_PW6 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU6; CREATE USER WAIVER_STU7 IDENTIFIED BY WAIVER_STU_PW7 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU7; CREATE USER WAIVER_STU8 IDENTIFIED BY WAIVER_STU_PW8 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU8; CREATE USER WAIVER_STU9 IDENTIFIED BY WAIVER_STU_PW9 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU9; CREATE USER WAIVER_STU10 IDENTIFIED BY WAIVER_STU_PW10 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 25M ON USERS QUOTA 25M ON TEMP PROFILE WAIVER_STUDENT_PROFILE; GRANT WAIVER_STUDENT_ROLE TO WAIVER_STU10; Here are some examples of what you will see if you run each of the commands from the SQL> prompt: SQL> CREATE TABLESPACE USERS 2 DATAFILE 'D:\USERS.DAT' SIZE 300M REUSE 3 AUTOEXTEND ON NEXT 1M MAXSIZE 500M 4 ONLINE; Tablespace created . SQL> CREATE PROFILE WAIVER_DEV_PROFILE 2 LIMIT SESSIONS_PER_USER UNLIMITED 3 CPU_PER_CALL 3000 4 CONNECT_TIME UNLIMITED 5* IDLE_TIME 45 Profile created . SQL> ALTER PROFILE WAIVER_BUS_PROFILE 2 LIMIT 3 SESSIONS_PER_USER 10; Profile altered . SQL> CREATE ROLE WAIVER_DEV_ROLE; Role created . SQL> GRANT CREATE SESSION TO WAIVER_DEV_ROLE; Grant succeeded . SQL> CREATE USER WAIVER_DEV11 2 IDENTIFIED BY WAIVER_DEV_PW11 3 DEFAULT TABLESPACE USERS 4 TEMPORARY TABLESPACE TEMP 5 QUOTA 25M ON USERS 6 QUOTA 25M ON TEMP 7 PROFILE WAIVER_DEV_PROFILE; User created . As I mentioned earlier, you can have the script prompt you for the variables . This is the more common approach, and in time you will have a folder of such scripts so that you can easily add new users, delete users, change roles, and so forth. The command you use is very simple. In your script just write the following: ACCEPT '&USER ID' ACCEPT '&PASSWORD' ACCEPT '&ROLE' and so on. When you run the script, you'll be prompted for the variables, and once they have been entered, the script will take off all by itself. In Chapter 14 I will show you how to use the same technique with reports. |