1) To
create oracle user account in the database
The following creates user named Pavan
SQL>create
user Pavan identified by Pavan default tablespace users default temporary
tablespace temp quota unlimited on users;
2) To
change the password of the oracle user
The
following changes the password of Pavan user to Kumar from Pavan
SQL>alter
user Pavan identified by Kumar;
3) To
lock the user account
SQL>alter
user Pavan account lock;
4) To
unlock the user account
SQL>alter
user Pavan account unlock;
5) To
expire account password
SQL>alter
user Pavan password expire;
6) To
drop the oracle user account that contains no objects
SQL>drop
user Pavan;
7) To
drop the oracle user account that contains objects
SQL>drop
user Pavan cascade;
8) To
check the default tablespace, temporary tablespace and account status of the
oracle user
SQL>select
username, account_status, default_tablespace, temporary_tablespace from
dba_users where username=’PAVAN’;
9) To
assign quota on tablespace to oracle user while creating user itself
The
following creates user named Pavan with quota 15m on users tablespace
SQL>create
user Pavan identified by Pavan quota 15m on users;
10) To
assign quota on tablespace after creation of oracle user
SQL>alter
user Pavan quota unlimited on users;
11) To
check the quotas on tablespaces for oracle user
SQL>select
username, tablespace_name, max_bytes from dba_ts_quotas where username=’PAVAN’;
12) To
grant system privilege to oracle user account
SQL>grant
create session to Pavan;
13) To
grant system privilege to oracle user account with administrative privilege
SQL>grant
create table to Pavan with admin option;
14) To
grant object privilege to oracle user account
SQL>connect
Scott/tiger
SCOTT>grant
update on emp to Pavan;
15) To
grant object privilege to oracle user account with administrative privilege
SQL>connect
Scott/tiger
SCOTT>grant
select on dept to Pavan with grant option;
16) To
revoke system privilege from oracle user account
SQL>revoke
create session, create table from Pavan;
17) To
revoke object privilege from oracle user account
SQL>connect
Scott/tiger;
SCOTT>revoke
update on emp, select on dept from Pavan;
18) To
check what privileges that oracle user
has
SQL>connect
Pavan/Kumar
Pavan>select
* from session_privs;
19) To
check all system privileges
SQL>select
privilege from dba_sys_privs;
20) To
check all object privileges
SQL>select
privilege from dba_tab_privs;
21) To
create a role
The
following creates a role named r1
SQL>create
role r1;
22) To
assign privileges to role r1
SQL>grant
create session, create table, create tablespace to r1;
23) To
assign roles to oracle user with and without administrative privilege
SQL>grant
r1 to Pavan;
SQL>grant
r1 to Pavan with admin option;
24) To
assign default role to oracle user
SQL>alter
user Pavan default role r1;
25) To
assign default role as none to oracle user
SQL>alter
user Pavan default role none;
26) To
enable a non default role r1
SQL>set
role r1;
27) To
revoke a role from oracle user
SQL>revoke
r1 from Pavan;
28) To
drop a role
SQL>drop
role r1;
29) To
create a profile
The
following creates a profile named prf_1 containing named set of password limits
such as failed login attempts, password lifetime, password reuse time, password
verify function etc.,
SQL>create
profile prf_1 limit
Failed_login_attempts
3
Password_lock_time
unlimited
Password_life_time
30
Password_reuse_time
30
Password_verify_function
verify_function_11g
Password_grace_time
5;
30) To
enforce resource limits using profiles
Set
resource_limit parameter to TRUE
SQL>alter
system set resource_limit=true scope=both;
SQL>create profile prf_resource limit
Cpu_per_session 10000
Idle_time 60
Connect_time 480;
31) To
alter an existing profile
SQL>alter profile prf_1 limit
Failed_login_attempts 2
Password_life_time 60
Password_verify_function null;
32) To
assign a profile to oracle user
SQL>alter
user Pavan profile prf_1;
33) To
drop an unassigned profile
SQL>drop
profile prf_resource;
34) To
drop an assigned profile
SQL>drop
profile prf_1 cascade;