Database Technologies‎ > ‎Oracle‎ > ‎

User Management

  

  1. Users and Schema
  2. Creating, Altering, Dropping Users
  3. Profiles
  4. Privileges
  5. Sessions
     
     
     
     
     
    Users and Schema
     
    User is an account who could access the database, if granted
    appropriate privileges and could create objects if granted appropriate
    privileges to do so by the dba
     
    Schema is the name of the user and generally refers to set
    of objects (table,view,stored procedure) created/owned by the
    user.
     
    Objects are fully qualified as <schema_name>.<object_name>
    objects are accesed by a user by just specifying the object name,
    however to access the objects created by other user (if he has appropriate privileges) 
    he has to fully qualify him like this <other_users_schema>.<object_name>

     
     
     
     Creating, Altering, Dropping Users
     
     
    Creation Of User, below is the general syntax for creation users
    CREATE USER <username> IDENTIFIED BY <password>  | EXTERNALLY | GLOBALLY
    DEFAUTL TABLESPACE     <tablespace_name>
    TEMPORARY TABLESPACE   <temporary_tablespace_name>
    QUOTA UNLIMITED ON     <tablespace_name>
    QUOTA 100M ON          <tablespace_name>
    PROFILE regular
    PASSWORD EXPIRE
    ACCOUNT UNLOCK|LOCK
     
    Parameter in detail
     
     

     EXTERNALLY

     Refers to Operating System Authentication

     GLOBALLY

     Refers to Authentication using directory services, like Active Directory / LDAP (Light Weight Directory Access Protocol)

     DEFAULT TABLESPACE

     Refers to the tablespace where the objects would be created by user by default, he could override this and could create objects in other tablespace (if he has necessary quota on that), if this is not specified it the object would be created in the database's default tablespace if that does not exist it would be created in the system tablespace (which is bad thing)

     TEMPORARY TABLESPACE

     Refers to the tablespace where the sorting operations for the users would be created, if not specified the sorting would be done in the databases's default temporary tablespace again if that is not specified then sorting would be done in system tablespace.

     QUOTA

     Even after giving the default tablespace we have to explicitly specify the quota i.e amount of space the user is allowed.

     PROFILE

     Refers to set of rules governing the usage of resource like cpu usage, password rules, more etc

      PASSWORD EXPIRE

     Enforces user to change the password.

      ACCOUNT UNLOCK | LOCK

     Account could be locked and unlocked, by default when account is created it is unlocked

     
    Altering Users i.e modifying user's settings
     
     Normal operation that are performed on Users are like changing the
     password, selecting default tablespace for the user, granting particular
     amount of quota to user on certain tablespace.  Then we could lock the
     user account, unlock it,  change the profile of the user. 
     
    Check out the below self explanatory examples
     
    ALTER USER ron IDENTIFIED BY <new password>;
     
    ALTER USER myuser
    DEFAULT TABLESPACE <tablespace Name>
    TEMPORARY TABLESPACE <temporary tablespace Name>
    QUOTA 500M ON <tablespace name>
    QUOTA 5M ON <other tablespace Name>
     
    ALTER USER ron ACCOUNT LOCK;
    ALTER USER ron ACCOUNT UNLOCK;
    ALTER USER ron PASSWORD EXPIRE;
    ALTER USER PROFILE <NEW PROFILE>;
     
    To drop user,
     
    DROP USER ron cascade
     
    cascade option drops any object created/owned by the user here its ron
     
     
     
    Profiles
     
    Profile is collection of rules.
     
    Rules contains the follwing
     
    Kernel Limitations : This includes the following
         sessions_per_user
         cpu_per_session
         cpu_per_call
         logical_reads_per_session
         logical_reads_per_call
         idle_time
         connect_time
         private_sga
          composite_limit
      
    Password Management :  This includes the following
     
       failed_login_attempts
       password_life_time
       password_reuse_time
       password_reuse_max
       password_verify_function
       password_lock_time
       password_grace_time
     
     If no profile is specified during the account creation, user gets the default profile.
     
    To check the contents/rules of the default profile
     
    desc dba_profiles
     
    select resource_name,limit from dba_profiles where profile='DEFAULT'
     
    To assign a specific profile to user
     
    alter user <user_name> profile <profile_name>

    Lets See how to create new profiles.


    create profile SECURE
     limit
      sessions_per_user                 2
      cpu_per_session                   10000
      cpu_per_call                      1
      connect_time                      unlimited
      idle_time                         30
      logical_reads_per_session         default
      logical_reads_per_call            default
      private_sga                       20M
      failed_login_attempts             3
      password_life_time                30
      password_reuse_time               12
      password_reuse_max                unlimited
      password_lock_time                default
      password_grace_time               2
      password_verify_function       null

    /


    To enable the password verify function  execute @?/rdbms/admin/utlpwdmg.sql

    After which you could assign the password verify function to a profile
     ALTER PROFILE <profile_name> limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;
     
    Privileges
     
    After user is created, you need to give the user explicit permission to
    connect to database to create session with the database. then to
    create table he should be granted create table privilege
     
    Other way is to grant roles, roles are database objects and are collection
    of privileges  predefined roles
     
    For example 'connect' and 'resouce,'
     
    To check the components of the connect role
     
    select * from dba_sys_privs where grantee='CONNECT';
     
    To grant roles to user
     
    grant connect,resource to ron
     
    To check Privilege of existing user, i.e 
     
    To check if user has system privileges
     
    select * from dba_sys_privs where grantee='<USERNAME>';
     
    To check if user  has roles granted
     
    select * from dba_role_privs where grantee='<USERNAME>';
     
    To check if user has permission on table objects
     
    select * from dba_tab_privs where grantee='<username>';
     
     
    To check quota for user on tablespaces
     
    select username,tablespace_name,bytes/1024/1024 from dba_ts_quotas
     
     
     .
     
    Sessions
     
     Sometime it is required to kill of the session.
     
    Query the v$session view to get the information about the sessions
     
    select sid,serial#, osuser,username, from v$session
     
    After getting the required session check the sid and serial number of the session to be killed
     
    execute the following command to kill the session.
     
    alter system kill session '<sid>.<serial#>
     
     
     =============================================================================================
     
     
     
     To get the database default settings, you could query the database_properties view.

    sql>select * from database_properties
     
    PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
    DICT.BASE 2 dictionary base tables version #
    DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
    DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
    DEFAULT_EDITION ORA$BASE Name of the database default edition
    Flashback Timestamp TimeZone GMT Flashback timestamp created in GMT
    TDE_MASTER_KEY_ID    
    DST_UPGRADE_STATE NONE State of Day Light Saving Time Upgrade
    DST_PRIMARY_TT_VERSION 11 Version of primary timezone data file
    DST_SECONDARY_TT_VERSION 0 Version of secondary timezone data file
    DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
    NLS_LANGUAGE AMERICAN Language
    NLS_TERRITORY AMERICA Territory
    NLS_CURRENCY $ Local currency
    NLS_ISO_CURRENCY AMERICA ISO currency
    PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
    NLS_NUMERIC_CHARACTERS ., Numeric characters
    NLS_CHARACTERSET WE8MSWIN1252 Character set
    NLS_CALENDAR GREGORIAN Calendar system
    NLS_DATE_FORMAT DD-MON-RR Date format
    NLS_DATE_LANGUAGE AMERICAN Date language
    NLS_SORT BINARY Linguistic definition
    NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
    NLS_DUAL_CURRENCY $ Dual currency symbol
    NLS_COMP BINARY NLS comparison
    NLS_LENGTH_SEMANTICS BYTE NLS length semantics
    NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
    PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
    NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
    NLS_RDBMS_VERSION 11.2.0.1.0 RDBMS version for NLS parameters
    GLOBAL_DB_NAME SUNONE.SAMSUNG.COM Global database name
    EXPORT_VIEWS_VERSION 8 Export views revision #
    WORKLOAD_CAPTURE_MODE   CAPTURE implies workload capture is in progress
    WORKLOAD_REPLAY_MODE   PREPARE implies external replay clients can connect; REPLAY implies workload rep lay is in progress
    NO_USERID_VERIFIER_SALT 57505D68AFECC3BCECE484A1C42CC8CE  
    DBTIMEZONE 00:00 DB time zone
     
     
     
     
     
     
     
     
     
     
     
     
     
     
Comments