DROP PROFILE

The DROP PROFILE statement removes a profile from the database.

Required privilege

ADMIN

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

DROP PROFILE profile [CASCADE]

Parameters

Parameter Description

profile

Name of the profile to be dropped.

CASCADE

Specify CASCADE to de-assign the profile from any users to whom the profile is assigned. TimesTen reassigns the DEFAULT profile to such users. You must specify CASCADE to drop a profile that is currently assigned to users.

Description

  • Use this statement to drop an existing profile. You cannot drop the DEFAULT profile. See "CREATE PROFILE" for information on the DEFAULT profile.

  • If you create a profile that is not currently assigned to a user, you do not need to specify CASCADE to drop the profile. If, however, the profile is currently assigned to a user, you must specify CASCADE to drop the profile.

Example

This example creates the test_profile profile and the test_profile_assign_to_user profile. It then creates the test_user user and assigns the test_profile_assign_to_user profile to that user. The example attempts to drop the test_profile profile. The operation succeeds as there are no users assigned to this profile. The example then attempts to drop the test_profile_assign_to_user profile. The operation succeeds if CASCADE is specified. After the test_profile_assign_to_user profile is dropped, the test_user user is assigned the DEFAULT profile.

  1. Create the test_profile profile. Set FAILED_LOGIN_ATTEMPTS to a value of 5.

    Command> CREATE PROFILE test_profile LIMIT FAILED_LOGIN_ATTEMPTS 5;
     
    Profile created.
    
  2. Create the test_profile_assign_to_user profile. Set FAILED_LOGIN_ATTEMPTS to a value of 3.

    Command> CREATE PROFILE test_profile_assign_to_user 
             LIMIT FAILED_LOGIN_ATTEMPTS 3;
     
    Profile created.
    
  3. Create the test_user user and assign the test_profile_assign_to_user profile to this user.

    Command> CREATE USER test_user identified by test_user_pwd 
             PROFILE test_profile_assign_to_user;
     
    User created.
    
  4. Drop the test_profile profile. The DROP PROFILE operation succeeds. There are no users assigned to this test_profile profile.

    Command> DROP PROFILE test_profile;
     
    Profile dropped.
    
  5. Attempt to drop the test_profile_assign_to_user profile. The DROP PROFILE operation fails. There is a user assigned to this profile. Repeat the DROP PROFILE operation again, but this time specify CASCADE. The DROP PROFILE operation succeeds.

    Command> DROP PROFILE test_profile_assign_to_user;
    15178: Profile TEST_PROFILE_ASSIGN_TO_USER has users assigned, cannot drop without CASCADE
    The command failed.
    
    Command> DROP PROFILE test_profile_assign_to_user CASCADE;
     
    Profile dropped.
    
  6. Query the DBA_USERS system view to verify that the test_user user has been assigned the DEFAULT profile.

    Command> SELECT profile FROM dba_users WHERE username = 'TEST_USER';
     
    PROFILE
    < DEFAULT >
    1 row found.