To check existing users in the system:
column username format a20;
select username, created from all_users order by 2;
select username, account_status, lock_date, expiry_date,
default_tablespace, temporary_tablespace, created, profile
from dba_users
order by created;
To create a user:
create user
u1 identified by u1
default tablespace users
temporary tablespace temp
quota unlimited on users
quota 5m on tbs
password expire
account lock;
Assigning the quota to the user on another
tablespace:
alter user
u1 quota 10m on tbs;
To unlock the account:
alter user
u1 account unlock;
To unlock the account and change the password at the
same time:
alter user
scott identified by tiger account unlock;
To force user to change the password:
alter user
u1 password expire;
To create the user and grant roles at the same
time
grant
connect, resource to u1 identified by u1;
Granting privileges to users seperately
grant
create session, create table, create sequence to u1;
conn u1/u1;
select *
from session_privs;
select *
from role_sys_privs order by role, privilege;
select *
from role_sys_privs order by 1;
Creating and assigning the custom roles
create role
role1;
grant
create session, create table, create sequence to role1;
grant role1
to u1;
select
role, privilege from role_sys_privs where role = 'ROLE1';
select * from
dba_role_privs where granted_role = 'ROLE1';
Assigning object privileges on a table to other
users
connect
scott/tiger
grant
select, insert, update on emp to u1;
conn u1/u1;
show user;
select *
from scott.emp;
connect /
as sysdba
show user;
column grantee
format a10
column owner format a10
column grantor format a10
column table_name format a15
column privilege format a15
select
grantee, owner, grantor, table_name, privilege
from dba_tab_privs
where owner = 'SCOTT';
Checking the privileges for the user
connect /
as sysdba
select
role, privilege from role_sys_privs where role = 'ROLE1';
select
grantee, privilege from dba_sys_privs where grantee = 'U1';
Revoking the privileges from user
conn / as
sysdba
revoke
select, insert, update on scott.emp from u1;
To confirm
the revoke:
select
grantee, owner, grantor, table_name, privilege
from dba_tab_privs
where owner = 'SCOTT';
Some of the key needed roles (predefined)
connect
resource
dba
exp_full_database
imp_full_database
To use data pump we need below roles:
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
Dropping the user Account
If the user schema contains no db objects:
drop user
u1;
If the user schema contains db objects:
drop user
u1 cascade;
Important views related to users and roles
dba_users
select
username, account_status, default_tablespace, temporary_tablespace, created
from dba_users;
user_users
select
username, account_status, lock_date, expiry_date, created, default_tablespace,
temporary_tablespace
from user_users;
all_users
select
* from all_users order by 3;
dba_ts_quotas
select
username, tablespace_name, bytes, max_bytes, blocks, max_blocks
from dba_ts_quotas;
user_ts_quotas
select
tablespace_name, bytes, blocks from user_ts_quotas;
select * from user_ts_quotas;
role_sys_privs
select
role, privilege, admin_option from role_sys_privs;
dba_role_privs
select
grantee, granted_role, admin_option, default_role from dba_role_privs;
dba_sys_privs
select
grantee, privilege, admin_option from dba_sys_privs;
dba_tab_privs
select
grantee, owner, table_name, grantor, privilege from dba_tab_privs;
select grantee, owner, table_name, grantor, privilege from dba_tab_privs
where owner='SCOTT';
role_tab_privs
select
role, owner, table_name, column_name, privilege from role_tab_privs;
dba_roles
select
role, password_required, authentication_type from dba_roles;
role_sys_privs
select
role, privilege, admin_option from role_sys_privs;
Session Management
Monitoring
desc v$session;
select
count(*) from v$session where username is not null;
select
username, sid, serial#
from v$session where username is not null;
To get session details
column
username format a15
alter
session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select username, sid, serial#, status, logon_time
from v$session
where username is not null;
Userwise Sessions
select
username, count(*) from v$session
group by username
order by username;
To see the processes by OS username:
select
username, pid, pname from v$process;
Joining v$process and v$session
select
a.sid, a.serial#, a.username, b.pid, b.spid
from v$session a, v$process b
where a.paddr = b.addr
and a.username = 'SYS';
select
username, to_char(logon_time, 'hh24:mi:ss dd-mm-yyyy'),
sid, serial#
from v$session
where sid = 183;
Killing Session at database level
alter
system kill session 'SID, SERIAL#';
alter
system kill session '23,78';
Killing session from OS level
ps -ef |
grep pmon
kill -9
3657
Creating Password file for instance
$orapwd file=orapw(sid) password=(pwd) force=y
ignorecase=y entries=(total users pwd to be stored)
$orapwd file=orapwdb1 password=manager force=y
ignorecase=y entries=5
Assigning sysdba and sysoper privileges to user
grant
sysdba to u1;
grant
sysoper to u1;
sqlplus
u1/u1 as sysdba;
show user;
SYS
sqlplus
u1/u1 as sysoper
show user;
PUBLIC
select *
from v$pwfile_users;
Creating Profile
create
profile prof1 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, in minutes)
create
profile prof1 limit
failed_login_attempts 3
password_lock_time 1
password_life_time 7
sessions_per_user 5
idle_time 15
connect_time 600;
Assigning Profile
create user
u2 identified by u2 profile prof1;
alter user
u1 profile prof1;
desc
dba_profiles;
select * from dba_profiles where profile='PROF1' order by resource_name;
select
resource_name, limit from dba_profiles where profile = 'PROF1';
select
username, profile from dba_users where profile = 'PROF1';
alter
profile prof1 limit password_lock_time 2;
select *
from dba_profiles where profile = 'PROF1';
To enforce kernel / resource parameters, the
following parameter must be set
alter
system set resource_limit = true scope = both;
Applying password restriction in profile
@$ORACLE_HOME/rdbms/admin/utlpwdmg.sql;
alter
profile default limit password_verify_function null;
alter
profile prof1 limit password_verify_function verify_function;
To remove the above function:
alter profile
prof1 limit password_verify_function null;
By default password is case sensitive, to disable
it set the following parameter to false
sec_case_sensitive_logon
= false
alter system set sec_case_sensitive_logon = true scope = both;
