In your lab for this week you are going to work with three different areas and processes within the Oracle Database that can be used to control data security. Each of these three processes has its own distinctive application to providing levels of security. In each case the individual processes deal with either limiting a users access to the database, limiting access to processes within the database, or keeping track of what the user is doing while in the database.
For the lab you will be using the SCOTT user which is already created in your instance. In Step 4 you will also be asked to shutdown you instance, make some edits to the init.ora file for your instance and then restart the instance.
 | L A B S T E P S |  |
| STEP 1: Define a New Profile |
Oracle provides the ability to set expirations, limit the reuse, and define the complexity of passwords. In addition, accounts can be locked if the password is entered incorrectly too many times. In this section of the lab we are going to create a custom profile that will then be applied to the SCOTT user.
- To begin, log into your instance as the SYS user.
- Write SQL script that will create a new profile named DBM449_SCOTT_PROFILE that will do the following:
- Limit the number of failed login attempts to 3 in a row.
- Limit the overall connection time to 10 hours (we will give him a little leeway incase he has to work overtime).
- Allow a session to be idle no more than 1 hour.
- Change the password every 60 days.
- Allow the user 3 days to change the password after it expires.
- Not allow a previous password be reused before there have been three password changes.
- Execute your pfile script and verify that the profile has been created by running a query against the DBA_PROFILES view in the data dictionary. Limit your output to ONLY the DBM449_SCOTT_PROFILE parameters.
| STEP 2: Testing the New Profile |
Now that we have a new profile for the SCOTT user we need to verify that it works properly. For obvious reasons there are going to be parts of the profile that we cannot test within the confines of this lab due to time constraints, but we can test to verify that the SCOTT user is being controlled by the profile.
- The first things we need to do is assign the profile to the SCOTT user. While still logged into your instance as the SYS user write and execute the SQL command that will assign the new SBM449_SCOTT_PROFILE profile to the SCOTT user.
- Now log into SCOTT (password is TIGER). Remember that you must supply the database instance name when logging in from the SQL> prompt just as you do when using the login window, i.e. CONN SCOTT/TIGER@DB####.WORLD.
- There are several things that we can test related to the logging in and changing a password so here we go.
- You should now be successfully connect to the SCOTT user. Write the connect command again on this time use an incorrect password. NOTE: you should get a warning message stating that you are no longer connected to Oracle. That is fine, just keep trying to log in.
- Repeat the above process until you get the ORA-28000: the account is lockederror which will indicate that the profile is working here.
- At this point we need to get the account unlocked so you will need to login to your instance as the SYS user and unlock the SCOTT account BUT DO NOT LOG BACK INTO THE SCOTT USER YET.
- Now we can test the password reuse parameter. To do this we must EXPIRE the current password. Write and execute the SQL command to expire the password for the SCOTT user.
- Now log back into the SCOTT user. You should receive a message stating that the password has expired (ORA-28001: the password has expired) and then prompting you to change the password.
- Try to reuse the TIGER password. You should receive the following - ORA-28007: the password cannot be reused.
- Now log into the SCOTT user again and this time change the password to LION to complete this step of the lab.
| STEP 3: Using the PRODUCT_USER_PROFILE table |
As the owner of a schema a user has certain inherited privileges that would allow the user to pass access to his/her own objects on to other users. Often times this can open up data to scrutiny by individuals who probably do not need to have access to it. These types of decisions should always be made by the DBA in charge of the database. One mechanism the DBA has to keeping users from using these inherited privileges is by excluding those commands using the PRODUCT_USER_PROFILE (PUP) table. In this section of the lab we are going to do this to the SCOTT user by setting up the scenario that will prohibit him from giving the user GEORGE (created in lab 2) access to the EMP table.
- To begin, copy the pupbld.sql file from $ORACLE_HOME/rdbms/admin to the C drive of your local computer or place it in the local F drive of your Citrix environment. Once you have downloaded it you will need to open the file and make two edits to the login strings; one at the top and one at the bottom. The login at the top of the script is for the SYSTEM user. The password is already set to MANAGER which is correct. You need to change the reference to the database instance to match your instance name (do not add the AS SYSDBA to this connection string). The other connection string is at the bottom and you also need to change the instance name here to match yours. Once you have made these changes then save the file.
- Now login to your database instance as the SYS user. Run the PUPBLD.SQL script from the SQL> prompt (DO NOT copy and paste the script). Remember that at the end of the script you should be connect as the SYS user. You can test this by issuing a SHOW USER command.
- Now we need to limit SCOTT from being able to use the GRANT command.
- Insert the proper values into the PRODUCT_USER_PROFILE table that will keep the SCOTT user from using the GRANT command. Remember that some of the values in your insert statement must be in upper case and some will need to be in mixed case. Once you have done this then query the table to verify the insert (REMEMBER: you cannot query the table as the SYS user, only as the SYSTEM user).
- Now we need to test our above settings and make sure they are working.
- Connect to the SCOTT user (remember that you changed the password to LION).
- Write and execute the statement that would GRANT the user GEORGE the ability to write a select statement and see the data in the EMP table owned by SCOTT. You should receive the following message - SP2-0544: Command "grant" disabled in Product User Profile.
- This verifies that you have now disabled the ability of the SCOTT user to allow another user to access any of the data in his schema.
| STEP 4: Setting up the Database to use Auditing |
Being able to audit what, when and where people are doing things in the database can be a very enlightening thing for a DBA. It can also be a very important tool in working with Data Security. Oracle provides the ability to do various types of auditing, but it takes some special setting up of the environment for this to work. In this step we are going to make the necessary adjustments to the current Oracle instance so that we can enable auditing and make some tests. If you need to review the processes to be used here then refer to the iLab Manual in week 1.
- First you need to make sure that you are logged into your instance as the SYS user.
- At this point issue a SHUTDOWN IMMEDIATE command to shut down you database instance.
- Once the instance is shut down you need to go into your Citrix Windows Explorer application, find your database instance set of directory folders, drill down to the pfile directory folder and open your init.ora file found in that folder.
- Under the section titled "Security and Auditing" you need to add the parameter AUDIT_TRAIL and set the parameter to DB_EXTENDED. This will allow the SQL_TEXT column of the DBA_AUDIT_OBJECT view to be populated. Save and close the file and then go back to your SQL*Plus session.
- Now using the init.ora file, start your instance back up to an OPEN status. You can do this by issuing a STARTUP PFILE= statement and pointing to your init.ora file.
- Once you have completed this process you are ready to begin setting up the database to audit some activity.
| STEP 5: Creating an Audit Trail |
Oracle permits audit trails to be generated for session login attempts, access to objects, and activity performed on objects. Again using the SCOTT user we are going to set up several scenarios for auditing what SCOTT does while in a session. NOTE: if you need to work through this process several times you can delete the values in the AUD$ base table by issuing the TRUNCATE TABLE AUD$ command while logged in as the SYS user.
- Make sure that you are connected as user SYS.
- Display value of the parameter AUDIT_TRAIL. For the VALUE column you should have a value of DB_EXTENDED.
- Now we can set up auditing to track what goes on in the database.
- Write SQL statements to audit successful and unsuccessful login attempts by SCOTT.
- Write SQL statement to audit any successful INSERT, UPDATE or DELETE performed on table DEPT in scott's schema.
- Now we need to test the audits to verify that they work.
- Log into the SCOTT user (remember that the password is LION) and perform the following:
- write and execute an UPDATE statement that will change the value in the LOC column of the DEPT table to MIAMI where the DEPTNO value is 10.
- Write and execute the INSERT statement that will in insert the following values into DEPT - (50, 'LEGAL', 'HOUSTON').
- Write and execute the DELETE statement that will delete the row from the DEPT table that was just inserted.
- Try to reconnect to the SCOTT user with an invalid password.
- Now connect back to the SYS user.
- While logged into your instance as the SYS user, query the DBA_AUDIT_OBJECT view of the data dictionary for the user name of the account (Not the OS), the object owner, the object name, the action name and the SQL command (text) from the DBA_AUDIT_OBJECT view in the Data Dictionary.
- Did you notice that the entries for successful logon and unsuccessful logon attempts were not there. Now query the user name, action name and return code values in the DBA_AUDIT_SESSION view. You should find that information here.
Related Documents
No comments:
Post a Comment