Practice with User Profiles and User Roles in Oracle

HANDS-ON #1: User profiles

Your company has hired several new people for a new department project in your regions. This department will have some different requirements from regular works, and the DBA wants to ensure that the new hires will not be getting stepped on by current users in the database and vise versa. To help insulate the new hires, it has been decided to create a specific profile for them. The profile name will be NEWREG1 and it will need to have the following properties:

• After three login attempts, the account should be locked.
• The password should expire after 30 days.
• The same password should not be reused again for at 31 days.
• The account should have a grace period of five days to change an expired password.
• Each user can have 3 concurrent sessions open.

Write the SQL commands that would implement this profile. Execute your script to create the new profile and capture the results in a spool file. Next, write the query that would list the profile, the profile resource name, and the limit value for the new profile. Execute this query in your database and capture the results in a spool file.

HANDS-ON #2: User roles

You need to create a new ROLE named JRDBA1 that can be used for temporary and assistant DBA personnel. You want the new role to contain all of the current roles that the SYSTEM user has with ADMIN OPTION (when it pertains to the current SYSTEM user setting). You also want the new role to be password-protected with a password of MJOAG2T. Once the role is created, write the statements to assign the role to the user ASSOCDBA1, but do not make it the default role. (HINT: To do this you will need to create the user ASSOCDBA1 first. Be sure you define both a default and a temporary tablespace other than SYSTEM when doing this).
Execute your script to create the new user and ROLE and the assignment of the ROLE to the user, and capture the results in a spool file.

Related Documents

Basic Oracle Sql Exercise

Practice Exercise for Oracle PL/SQL

Oracle Procedure and Function Practice

Exercise Oracle package and package body

Database design practice for a Movie Company

Oracle Enterprise Manager Excercises

Oracle Database Installation Exercises

Oracle Database Creation Exercises

Database Administration Exercises - Week 3

Temporary Tables and Data Types Exercises

Exercises with Oracle Create Table Add Columns

Practice with User Profiles and User Roles in Oracle

Practice Oracle Joins Examples

Oracle Object type Exercises - VARRAY

Audit and Profile Management Exercises in Oracle