User Management

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

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

Password Management : This includes the following

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