User Management

USER

Creating the user

SQL>select tablespace_name,file_name from dba_data_files;

SQL>select tablespace_name,file_name from dba_temp_files;

SQL> Create user USER01 identified by USER01 default tablespace USER01_ts   temporary tablespace temp                            

quota unlimited on USER01_ts

quota 100m on USER01_demo_ts

password expire

account lock;

Assigning the quota to the user on other tablespace

SQL> alter user USER01 quota 10m on tbs1;

How To unlock the user account

SQL> alter user USER01 account unlock;

How To force user to change the Password

SQL> alter user USER01 password expire;

How to Grant privileges to users

SQL>grant connect, resource to user02 identified by user02;

SQL> grant create session, create table, create sequence to USER01;

SQL> conn USER01/USER01;

Views

SQL> select * from session_privs;

SQL> select * from role_sys_privs;

 

How to Assign sysdba and sysoper privilages to user

1. Creating Password file for instance

$orapwd file=orapw<sid>  password=<password> force=y ignorecase=y

Eg:

 $orapwd file=orapwdevlp password=password force=y ignorecase=y

Note:

If u don’t create password file, then you will get the error  as Below

ERROR at line 1:

ORA-01994: GRANT failed: password file missing or disabled

2. Grant using command

Sql> grant sysdba to user01;

Sql>grant sysoper to user01;

Now connect that user, as below

$sqlplus user01/user01  as sysdba

Sql>show user

       SYS

$sqlplus user01/user01 as sysoper

Sql>show user

       PUBLIC

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB  SYSOP  SYSAS

-------------------------------- ---------- -------  ---------

SYS                                    TRUE   TRUE    FALSE

USER01                                            TRUE   FALSE  FALSE

*** By default password is case sensitive, to disable it set the following parameter to false            

Sec_case_sensitive_logon=false;

SQL>alter system set sec_case_sensitive_logon=true scope=both;

Profiles

Creating profile

Sql> create profile profile1 limit

                Failed_login_attempts 3   (no of attempts)

                Password_lock_time 1       (no of days)   1/24  (for 1 hour)

                Password_life_time 7         (no of days)

                Sessions_per_user 5            (no of total sessions)

                Idle_time 1                           (in minutes)

                Connect_time 600;             (10 hours)

Assigning profile

Sql>  create user emp1 idenfied by emp1 profile profile1;

Sql> alter user emp1 profile profile1;

SQL>select * from dba_profiles where profile=’profile1’;

SQL>select username, profile from dba_users where profile=’PROFILE1’;

SQL>alter profile profile1 limit password_lock_time 2;

SQL>select * from dba_profiles where profile=’PROFILE1’;

·         To enforce kernel/resource parameters the following parameter must be set

Sql> alter system set resource_limit=true scope=both;

How to Apply password restriction in profile:

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql;

SQL>alter profile default limit password_verify_function null;

SQL> alter profile prof1 limit Password_verify_function verify_function;

SQL> alter profile prof1 limit Password_verify_function null;

Important views for Managing Users:

v$process

v$session

v$sqlarea

v$sqltext

v$lock

v$session_wait

v$sess_io