Week 4 Written Assignment: Stored Procedures As A Security M

Week 4 Written Assignment Stored Procedures as a Security Measure

In our readings we have learned the importance of connections and the use of stored procedures and functions. For this assignment we will look at stored procedure and function methods related to securing connections and data in the database. Note: Don’t forget your Oracle 11g documentation resource for the how to examples. For this assignment you are required to do BOTH Part A and Part B.

Part A: Short Answer Questions

1. What are the advantages of using stored procedures and functions in Oracle?

2. Oracle automatically tracks all object interdependencies for every PL/SQL stored object. What does this include? When does Oracle automatically recompile a stored procedure?

3. What is gained when using stored procedures in a client/server environment? What is the difference between stored procedures and functions? When you create a procedure or function, you may define parameters. What types of parameters can be declared?

Part B: Developing a Password Policy

Your task is to develop a single SQL script that will perform all the following tasks of enforcing password complexity:

  1. Connect to the SYS user.
  2. Create a password complexity function (named as "is481_password") to enforce the following requirements:
    • Is at least six (6) characters long
    • Differs from the user name
    • Has at least one alphabet, one numeric, and one punctuation mark character
    • Is not simple or obvious, such as welcome, account, oracle, database, or user
    • Differs from the previous password by at least 3 characters

    Please note the exact spelling on the function name is REQUIRED. Please note the double quotation mark (") was not a part of the specified function name.

  3. Create a profile named is481_prof with the following specifications:
    • Number of days the password is valid before it is aged out: 60 days
    • Number of days ahead of expiration the user is warned: 1 day
    • Number of days before a password can be reused: 30 days
    • Number of times a password can be reused: UNLIMITED
    • Number of failed login attempts allowed before the account is locked: 3
    • Number of days an account is locked due to failed login attempts: 13 days
    • Set PASSWORD_VERIFY_FUNCTION to is481_password
  4. Assign the above profile to the user DBSEC that you have already created.

Tips:

  • Use one SQL (PL/SQL) script to complete all tasks.
  • Connect to SYS with: "conn sys/password as sysdba;"
  • Submit the SQL script and a screenshot of the creation process.

Related Assignments