Waiver Database Security


Waiver Database Security

We 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:

  1. Tablespaces

  2. Profiles

  3. Roles

  4. Users

Tablespaces

We'll create two tablespaces: USERS and TEMP. Here's the first one:

 CREATE TABLESPACE  USERS  DATAFILE 'D:\USERS.DAT' SIZE 300M REUSE graphics/1_icon.gif AUTOEXTEND ON NEXT 1M MAXSIZE 500M   ONLINE; 

(1) Use single quotes around the data file name .

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 graphics/1_icon.gif AUTOEXTEND ON NEXT 1M MAXSIZE 500M   TEMPORARY; graphics/2_icon.gif 

(1) Use single quotes around the data file name.

(2) Notice TEMPORARY.

Profiles

Moving 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 .

Roles

Moving 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:

  • ANY means that this role can perform the named function in the schema(s) to which it has rights, not just its own. Hence, we want to be careful not to grant blanket rights such as UPDATE ANY TABLE because then everything would be opened up to the developers. What we do want to give them, however, is the right to look at any table, as the SELECT ANY TABLE statement indicates. In other words, in the test database you would give the developers UPDATE and CREATE rights, but not in the production database.

  • SESSION means the ability to start a database session.

  • SYNONYM means creating synonyms for objects.

  • TABLE and VIEW are self-explanatory.

  • Procedures are programs.

  • Triggers are pieces of code that are fired off under certain conditions.

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; graphics/1_icon.gif 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; 

(1) This role can update the QUESTIONS, ANSWERS, AUTHORS, AND TEST_ID tables. Hence it needs rights to add, modify, and delete entries in those tables, along with all the description tables.

Here are the GRANT statements for the second role:

 GRANT CREATE SESSION TO WAIVER_BUS_ROLE2; graphics/1_icon.gif 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; 

(1) This role can update only the AUTHORS table.

And finally, here are the GRANT statements for the third role:

 GRANT CREATE SESSION TO WAIVER_BUS_ROLE3; graphics/2_icon.gif GRANT CREATE VIEW TO WAIVER_BUS_ROLE3; GRANT SELECT TABLE TO WAIVER_BUS_ROLE3; 

(2) This role can run reports and views only, on any table.

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; graphics/3_icon.gif GRANT INSERT ON STUDENTS TO WAIVER_STUDENT_ROLE; GRANT SELECT TABLE TO WAIVER_STUDENT_ROLE; 

(3) This role can add a record to the STUDENTS table. The programs will have rights to update all the other tables such as TEST_HISTORY.

Users

The 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 graphics/1_icon.gif DEFAULT TABLESPACE USERS   TEMPORARY TABLESPACE TEMP graphics/2_icon.gif QUOTA 25M ON USERS   QUOTA 25M ON TEMP   PROFILE WAIVER_DEV_PROFILE graphics/3_icon.gif GRANT WAIVER_DEV_ROLE TO WAIVER_DEV1; graphics/4_icon.gif 

(1) We have included the password WAIVER_DEV_PW1,

(2) then assigned this user to the two tablespaces we created,

(3) and last, tied the user to the Developer Profile.

(4) The last step is to tie the user to the Developer role.

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 graphics/1_icon.gif DATAFILE 'D:\USERS.DAT' SIZE 300M REUSE     AUTOEXTEND ON NEXT 1M MAXSIZE 500M     ONLINE; 2. CREATE TABLESPACE TEMP graphics/2_icon.gif DATAFILE 'D:\TEMP.DAT' SIZE 300M REUSE     AUTOEXTEND ON NEXT 1M MAXSIZE 500M     ONLINE; 3. CREATE PROFILE WAIVER_DEV_PROFILE graphics/3_icon.gif 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; graphics/4_icon.gif 5. GRANT CREATE SESSION TO WAIVER_DEV_ROLE; graphics/5_icon.gif 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 graphics/6_icon.gif 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; graphics/7_icon.gif 

(1) Create the default tablespace.

(2) Create the default TEMP tablespace.

(3) Create the profile.

(4) Create the role.

(5) Grant privileges to the role.

(6) Create user account.

(7) Give the user a role.

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 Dictionary

Earlier 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

View

Description

DBA_TS_QUOTAS

Shows the tablespace quotas for all users.

USER_TS_QUOTAS

Shows the tablespace quotas for the current user only.

USER_PASSWORD_LIMITS

Shows any password restrictions in the user's profile.

USER_RESOURCE_LIMITS

Shows any resource limits for the current user.

USER_COL_PRIVS

Shows any particular columns in tables that the user has special rights to and what the user can do to those columns .

ALL_COL_PRIVS

Shows all columns in all tables for all users that have special column privileges.

USER_TAB_PRIVS

Shows all object privileges for a particular user.

SESSION_PRIVS

Lists all the system privileges for a particular user.

DBA_SYS_PRIVS

Shows all system privilege grants made to roles and users.

DBA_ROLE_PRIVS

Shows all roles that have been granted both to users and to other roles.

DBA_ROLES

Lists all the roles for a database administrator.

The Entire Waiver Database Security Script

Often 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.



Guerrilla Oracle
Guerrilla Oracle: The Succinct Windows Perspective
ISBN: 0201750775
EAN: 2147483647
Year: 2003
Pages: 84

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net