·  SQL – Profiles

 

- Info:

  - Profile is set of restrictions which can be given to user or role.

  - Each user or role gets default profile if no profile was specified at creation time.

  - Additional info can be found at http://www.psoug.org/reference/profiles.html.

 

- Syntax – Create:

  CREATE PROFILE MyProfile

  LIMIT  SESSIONS_PER_USER     2

        CPU_PER_SESSION       DEFAULT

 

- Syntax – Change:

  ALTER PROFILE MyProfile

  LIMIT idle_time             20;        --minutes

        FAILED_LOGIN_ATTEMPTS DEFAULT

 

- Syntax – Delete:

  DROP PROFILE MyProfile

 

- Example:

  CREATE PROFILE MyProfile LIMIT

    SESSIONS_PER_USER         2

    CPU_PER_SESSION           DEFAULT

    CPU_PER_CALL              UNLIMITED

    CONNECT_TIME              DEFAULT

    IDLE_TIME                 DEFAULT

    LOGICAL_READS_PER_SESSION DEFAULT

    LOGICAL_READS_PER_CALL    DEFAULT

    COMPOSITE_LIMIT           DEFAULT

    PRIVATE_SGA               DEFAULT

    FAILED_LOGIN_ATTEMPTS     DEFAULT

    PASSWORD_LIFE_TIME        DEFAULT

    PASSWORD_REUSE_TIME       DEFAULT

    PASSWORD_REUSE_MAX        DEFAULT

    PASSWORD_LOCK_TIME        DEFAULT

    PASSWORD_GRACE_TIME       DEFAULT

    PASSWORD_VERIFY_FUNCTION  DEFAULT

 

- Comments:

  - Non password restrictions will take efect only if system parameter RESOURCE_LIMIT is set to TRUE as   described in [SQL – System].

  - When IDLE_TIME is set in the users' profiles or the default profile.

  - This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually         disconnect.

  - It does not always clean up the Unix session (LOCAL=NO sessions).

  - At this time all oracle resources are released but the shadow processes remains and OS resources are not released.

  - This shadow process is still counted towards the parameters of init.ora.

  - This process is killed and entry from v$session is released only when user again tries to do something.

  - Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client       machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session.

 

  - What is your init.ora parameter resource_limit set to?  If FALSE (the default) profiles are not enforced. 

  - Only when set to TRUE will it work.

  - The way the idle time logout works is:

    - User logs in.  Session status will be active or inactive in V$SESSION

    - After X minutes in the inactive status, their session will be KILLED. 

      Their status in v$session will be turned into KILLED. 

      All locks and resources they had will be released but their CONNECTION will remain.

    - The very next time the client contacts the database, they will receive the error message "your session has been         killed". 

    - At that point in time, their entry in V$SESSION will go away. (but not before that).

    - So, that is how the client will be notified, as soon as they attempt to use the database, the database will tell them         "sorry, you've been logged out". 

    - Then and only then their physical connection to the database will "go away". 

      Prior to that, they are effectively logged out (no longer holding locks and such) but still connected.