Profiles

0
58
Basic profile information
To enable us to modify the profiles we have to start with the command:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
System rules:
SESSIONS_PER_USER  – The number that determines the maximum number of user sessions.
CPU_PER_CALL  – number (hundredths of seconds) specifying the maximum CPU time allocated to the session,
CONNECT_TIME  – specifies the maximum length of the user session, which is expressed in minutes,
IDLE_TIME  – maximum idle time in minutes, (if the user waits for “idle” for
long query is not included in IDLE_TIME)
LOGICAL_READS_PER_SESSION  – maximum number of blocks (in RAM and disk space)
hard) that can be used for one query (where the query is divided into parsing,
execution, return results)
LOGICAL_READS_PER_CALL  – maximum number of blocks needed per query (where the query is split
to parse, execute, return results),
PRIVATE_SGA – Maximum number of bytes (in bytes) a session can consume from an SGA shared pool.
You can add the prefix “K” or “M” to the number in kilobytes or megabytes,
Password rules:
FAILED_LOGIN_ATTEMPTS  – The maximum number of unsuccessful login attempts after it has been exceeded
will be blocked,
PASSWORD_LIFE_TIME  – maximum password lifetime after login is not possible until
password will not be changed
PASSWORD_REUSE_TIME  – time in days after which the password can be reused (when changing
password)
PASSWORD_REUSE_MAX  – minimum number of password changes after which the password can be reused, after assigning this option to any number, PASSWORD_REUSE_TIME must be set to “unlimited”
PASSWORD_LOCK_TIME  – Number of days after which your account will be unlocked, after the maximum number of unsuccessful logins,
PASSWORD_GRACE_TIME  – maximum password lifetime, in days,
PASSWORD_VERIFY_FUNCTION  – a password validation script, it is interesting to put any program written in PL / SQL and this gives you great verification capabilities. Exemplary scenarios “the user can only log in from 8 to 16”, or “if the user’s login is different than Bartek then return a random exception and break the session”;
DEFAULT Profile
Each database has a profile named DEFAULT that is associated with each user by default. Each subsequent profile inherits from the DEFAULT profile. For example, if a new profile does not have a defined IDLE_TIME parameter, then it is UNLIMITED from the DEFAULT profile. The DEFAULT profile of each rule assigns an UNLIMITED value, which is an undefined parameter, ie a constraint bar. The DEFAULT profile is assigned to each user by default. The command returning the list of profiles installed in the Oracle database looks like this:
SELECT * FROM DBA_PROFILES;
Create Profiles
A profile can be created, associated with the user, and received by the user at any time. Changed or new profiles are valid since the new session has been established. The sample command creating a new profile named “profile1” looks like this:
CREATE PROFILE profile1 LIMIT
SESSIONS_PER_USER 3
FAILED_LOGIN_ATTEMPTS 3
IDLE_TIME 30;
Once you create a profile, you can associate it with the user and it will take effect once the new session has started. You may only have one profile at a time. Every one overwrites the previous one. The following is a command that assigns a profile to the user:
ALTER USER <user> PROFILE; </ user>
Deleting a profile
The profile can be deleted (provided it is not associated with the user) using the command:
DROP PROFILE;
To delete a profile associated with a user, add the CASCADE option:
DROP PROFILE CASCADE;
When you receive a profile, for example, by deleting it, the DEFAULT profile will automatically be assigned to it, which will take effect once the new session is established. The default profile cannot be deleted.