Managing users and profiles on Oracle DB

Users:

List all users

SELECT *
FROM dba_users u
WHERE u.username LIKE 'WEBUSER';

Unlock user

ALTER USER WEBUSER ACCOUNT UNLOCK;

Change user password

ALTER USER WEBUSER IDENTIFIED BY strongpassword;

Profiles

Before you start

Remember the parameter RESOURCE_LIMIT determines whether resource limits are enforced in database profiles. chek this parameter value and only then handle re profiles when needed. tou can chec the value by using show parameter RESOURCE_LIMIT in sqlplus.

List profiles

List only profiles

SELECT * FROM dba_profiles dp

List profiles and users and resource_name

  SELECT du.username,
         du.profile,
         dp.resource_name,
         dp.resource_type,
         dp.LIMIT
    FROM dba_profiles dp INNER JOIN dba_users du ON dp.profile = du.profile
   WHERE     dp.resource_type = 'KERNEL'
         AND dp.LIMIT <> 'DEFAULT'
         AND dp.LIMIT <> 'UNLIMITED'
ORDER BY 2, 1

Change user profile

An Example - prevent lock

CREATE PROFILE umlimited_attempts LIMIT
  FAILED_LOGIN_ATTEMPTS UNLIMITED;

ALTER USER WEBUSER PROFILE  umlimited_attempts;

SELECT username, profile, account_status
  FROM dba_users
 WHERE username = 'WEBUSER';

  SELECT du.username,
         du.profile,
         dp.resource_name,
         dp.resource_type,
         dp.LIMIT
    FROM dba_profiles dp INNER JOIN dba_users du ON dp.profile = du.profile
   WHERE     dp.resource_type = 'KERNEL'
         AND dp.LIMIT <> 'DEFAULT'
         AND dp.LIMIT <> 'UNLIMITED'
ORDER BY 2, 1;

SELECT DBMS_METADATA.get_ddl ('PROFILE', profile) AS profile_ddl
  FROM (SELECT DISTINCT profile FROM dba_profiles)
 WHERE profile LIKE UPPER ('%TIME_LIMIT%');

← Previous Post Next Post →