Managing Users and Resources

 Managing Users and Resources

Creating users :
      You can create a new database user by using the create user dialog box in SQL*DBA or the SQL command creates users .when you create a new user giving username and password is mandatory , where as following will take default values if not provited

    1.Default tablespace
    2.temporary tablespace
    3.tables space quotas
    4.Profile


1.Default tablespace :

       Each user is associated with a default tablespace.the default setting for every users default tablespace is system tablespace.if a user creates any type of object you should specifically assaign the user a default tablespace


2.Temporary tablespace:

      Each user is associated with a temporary tablespace , when a user execute the SQL statement that requires the creation of temporary segments,the user temporary tablespace contains the emporary segments.

 To create the new database user(ramu)

sys>>create user ramu identified by ramu
         default tablespace tbs1
         quota 5m ON tbs1
         quota 3m ON system
         profile clerk;

 sys>>alter user ramu profile clerk;

 To change the password for the user

 sys>> alter user ramu identified by <newpassword>;

To drop the user
 sys>> drop user ramu cascade;

 Creating and alter a Profiles:

Session_per_user         --Limits the number of concurrent session for the user
Cpu_per_session         --Limits the CPU time for session. This is expressed in hundredths of seconds
Cpu_per_call               --Limits CPU time for a call.This is expressed in hundredths of seconds
Connect_time              --Limits the total elapsed connect time a session
Failed_login_attempts --no of failed attempts after which accounts is going to locked
Password_life_time     --no of days password is valid
Password_reuse_max   --no of time password can be changed
Paswword_verify_function--Function with which it is going to verify the password
Password_lock_time    -- no of days password going to be locked
Password_grace_time   --no of days it s going to prompt for password expiry
Idle_time                      -- defines the maximum amount of continuos inactive time span.
Logical_reads_per_session--limits the number of data blocks read ina session
Logical_reads_per_call   -- limits the number of data blocks read for a call to process a SQL 

Private_sga
Composite_limit

Creating the profile :

SYS>>create PROFILE ramu limit
         SESSIONS_PER_USER   1
        CPU_PER_CALL    6000 
        CONNECT_TIME   560
        PASSWORD_LIFE_TIME  60 
        PASSWORD_GRACE_TIME 10
        IDLE_TIME   15
        FAILED_LOGIN_ATTEMPTS 3;

Changing the values for profile :

sys>>alter profile ramu limit
        LOGICAL_READS_PER_SESSION  20000
       CPU_PER_CALL  default
       LOGICAL_READS_PER_CALL 100;

To drop the profile

sys>>drop profile ramu cascade;

SESSIONS and user licensing:

LICENCE_MAX_SESSIONS
LICENCE_SESSION_WARNING
LICENCE_MAX_USERS

  To set the maximum sessions for an Instance:

sys>>ALTER system set licence_max_sessions=100;

 To set both WARNING limit and maximun limit

sys>>alter system set licence_max_session=64
                      licence_sessions_warning=54;

 To set USER limit :

sys>>alter system set licence_max_users=30;

Password file Athentication:

$cd $ORACLE_HOME/dbs
$orapwd file=<filename> password=<password> entries=<max_users>

Example:
$orapwd file=orapwDEMO password=oracle entries=3


Viewing Information about database users and profiles:

DBA_USERS
ALL_USERS
USER_USERS
DBA_TS_QUOTAS
USER_TS_QUOTAS
USER_PASSWORD_LIMITS
USER_RESOURCE_LIMITS
DBA_PROFILES
RESOURCE_COST
v$session
v$sessat
v$statname



examples:

sys>>create PROFILE prof_clerk limit
         session_per_user 1
        idle_time 30
        connect_time 600;


sys>>create user ramu identified by ramu
                  default tablespace ts1
          temporary tablespace tmp1
          quota 50m on ts1
          profile ramu;

  Listing all users and associated information :

sys>>select username,account_status,default_tablespace,temporary_tablespace,
                                             created,profile from dba_users;

  Viewing memory use for each session :

sys>>select username,value || 'BYTES' "current UGA memory"
            from v$SESSION sess,v$SESSTAT stat,v$STATNAME name where
        sess.SID=stat.SID AND stat.STATISTIC# = name.STATISTIC#
        and name.NAME = 'session uga memory';

  To see the current licensing limits;

sys>> select sessions_max,sessions_warning,sessions_current s_current,sessions_highwater s_high,
      users_max from V$License;