User Management

  1. Users and Schema

  2. Creating, Altering, Dropping Users

  3. Profiles

  4. Privileges

  5. Sessions

    1. .

    2. Users and Schema

    3. User is an account who could access the database, if granted

    4. appropriate privileges and could create objects if granted appropriate

    5. privileges to do so by the dba

    6. Schema is the name of the user and generally refers to set

    7. of objects (table,view,stored procedure) created/owned by the

    8. user.

    9. Objects are fully qualified as <schema_name>.<object_name>

    10. objects are accesed by a user by just specifying the object name,

    11. however to access the objects created by other user (if he has appropriate privileges)

    12. he has to fully qualify him like this <other_users_schema>.<object_name>

    13. .

    14. Creating, Altering, Dropping Users

    15. Creation Of User, below is the general syntax for creation users

    16. CREATE USER <username> IDENTIFIED BY <password> | EXTERNALLY | GLOBALLY

    17. DEFAUTL TABLESPACE <tablespace_name>

      1. TEMPORARY TABLESPACE <temporary_tablespace_name>

      2. QUOTA UNLIMITED ON <tablespace_name>

      3. QUOTA 100M ON <tablespace_name>

      4. PROFILE regular

      5. PASSWORD EXPIRE

      6. ACCOUNT UNLOCK|LOCK

    18. Parameter in detail

    1. Altering Users i.e modifying user's settings

    2. Normal operation that are performed on Users are like changing the

    3. password, selecting default tablespace for the user, granting particular

    4. amount of quota to user on certain tablespace. Then we could lock the

    5. user account, unlock it, change the profile of the user.

    6. Check out the below self explanatory examples

    7. ALTER USER ron IDENTIFIED BY <new password>;

    8. ALTER USER myuser

    9. DEFAULT TABLESPACE <tablespace Name>

    10. TEMPORARY TABLESPACE <temporary tablespace Name>

    11. QUOTA 500M ON <tablespace name>

    12. QUOTA 5M ON <other tablespace Name>

    13. ALTER USER ron ACCOUNT LOCK;

    14. ALTER USER ron ACCOUNT UNLOCK;

    15. ALTER USER ron PASSWORD EXPIRE;

    16. ALTER USER PROFILE <NEW PROFILE>;

    17. To drop user,

    18. DROP USER ron cascade

    19. cascade option drops any object created/owned by the user here its ron

    20. .

    21. Profiles

    22. Profile is collection of rules.

    23. Rules contains the follwing

    24. Kernel Limitations : This includes the following

      1. sessions_per_user

      2. cpu_per_session

      3. cpu_per_call

      4. logical_reads_per_session

      5. logical_reads_per_call

      6. idle_time

      7. connect_time

      8. private_sga

      9. composite_limit

    25. Password Management : This includes the following

      1. failed_login_attempts

      2. password_life_time

      3. password_reuse_time

      4. password_reuse_max

      5. password_verify_function

      6. password_lock_time

      7. password_grace_time

    1. If no profile is specified during the account creation, user gets the default profile.

    2. To check the contents/rules of the default profile

    3. desc dba_profiles

    4. select resource_name,limit from dba_profiles where profile='DEFAULT'

    5. To assign a specific profile to user

    6. alter user <user_name> profile <profile_name>

    7. Lets See how to create new profiles.

    8. create profile SECURE

    9. limit

    10. sessions_per_user 2

    11. cpu_per_session 10000

    12. cpu_per_call 1

    13. connect_time unlimited

    14. idle_time 30

    15. logical_reads_per_session default

    16. logical_reads_per_call default

    17. private_sga 20M

    18. failed_login_attempts 3

    19. password_life_time 30

    20. password_reuse_time 12

    21. password_reuse_max unlimited

    22. password_lock_time default

    23. password_grace_time 2

    24. password_verify_function null

    25. /

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

    27. After which you could assign the password verify function to a profile

    28. ALTER PROFILE <profile_name> limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;

    29. Privileges

    30. After user is created, you need to give the user explicit permission to

    31. connect to database to create session with the database. then to

    32. create table he should be granted create table privilege

    33. Other way is to grant roles, roles are database objects and are collection

    34. of privileges predefined roles

    35. For example 'connect' and 'resouce,'

    36. To check the components of the connect role

    37. select * from dba_sys_privs where grantee='CONNECT';

    38. To grant roles to user

    39. grant connect,resource to ron

    40. To check Privilege of existing user, i.e

    41. To check if user has system privileges

    42. select * from dba_sys_privs where grantee='<USERNAME>';

    43. To check if user has roles granted

    44. select * from dba_role_privs where grantee='<USERNAME>';

    45. To check if user has permission on table objects

    46. select * from dba_tab_privs where grantee='<username>';

    47. To check quota for user on tablespaces

    48. select username,tablespace_name,bytes/1024/1024 from dba_ts_quotas

    49. .

    50. Sessions

    51. Sometime it is required to kill of the session.

    52. Query the v$session view to get the information about the sessions

    53. select sid,serial#, osuser,username, from v$session

    54. After getting the required session check the sid and serial number of the session to be killed

    55. execute the following command to kill the session.

    56. alter system kill session '<sid>.<serial#>'

    57. =============================================================================================

    58. To get the database default settings, you could query the database_properties view.

    59. sql>select * from database_properties

    1. select u.username

    2. ,'alter user '||u.username||' identified by values '''||s.spare4||''';' cmd

    3. from dba_users u

    4. join sys.user$ s

    5. on u.user_id = s.user#

    6. where u.username = upper('&used');