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.

See also