Intesting requiremnet

I have been asked to give user A account lock and unlock so that it can lock and unlock only user B but if I happen to give it to user A then it can be used against.

did some research and came to conclusion that following looks promising to me.


create or replace procedure MYLOCK as
begin
execute immediate 'alter user A account lock';
end;


create or replace procedure MYUNLOCK as
begin
execute immediate 'alter user A account unlock';
end;

grant execute on mlock,munlock to B;


connect as B

SQL> exec sys.mlock

SQL> conn A/A


SQL> conn B/B

SQL > exec sys.munlock

SQL>conn A/A

set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr

statspack erroring out

1. Disable the primary key constraint STATS$MUTEX_SLEEP_PK and create a non-unique index on the columns.
alter table perfstat.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;
create index perfstat.STATS$MUTEX_SLEEP_PK on
STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);
2. Restarting the database would relieve the situation temporarily as v$mutex_sleep would be cleaned up.

3. There is a similar issue with standby database statspack. The same workaround applies with slightly different syntax:

Disable the primary key constraint STDBYPERF.STATS$MUTEX_SLEEP_PK and create a non-unique index on
the columns.
alter table stdbyperf.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;
create index stdbyperf.STATS$MUTEX_SLEEP_PK on
STATS$MUTEX_SLEEP(SNAP_ID, DBID, INSTANCE_NUMBER, MUTEX_TYPE, LOCATION);

However, after you apply the 11.1.0.7 BLR patch (Bug 8887151), the workaround will be a little
different:
Disable the primary key constraint STDBYPERF.STATS$MUTEX_SLEEP_PK and create a non-unique index on
the columns.
alter table stdbyperf.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;

CREATE INDEX STATS$MUTEX_SLEEP_PK ON STATS$MUTEX_SLEEP(SNAP_ID, DBID, INSTANCE_NUMBER, MUTEX_TYPE, LOCATION);

select password from dba_users where username = 'NARESH'
copied the hash to notepad
alter user NARESH identified by NARESH
connect NARESH/NARESH
...did stuff...
connect / as sysdba
alter user NARESH identified by values 'password hash from earlier'

DO NOT DO IT

Priviledges

Roles
==========
Users to roles and system privileges

select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;

System privileges to roles and users
========================================
select
lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
(
/* THE PRIVILEGES */
select
null p,
name c
from
system_privilege_map
where
name like upper('%&enter_privliege%')
/* THE ROLES TO ROLES RELATIONS */
union
select
granted_role p,
grantee c
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
privilege p,
grantee c
from
dba_sys_privs
)
start with p is null
connect by p = prior c;


Object privileges
===================
select
case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
end
from
(
/* THE OBJECTS */
select
null p1,
null p2,
object_name obj,
owner own,
object_type typ
from
dba_objects
where
owner not in
('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN','MDSYS','ORDSYS','XDB', 'WKSYS', 'EXFSYS',
'OLAPSYS', 'DBSNMP', 'DMSYS','CTXSYS','WK_TEST', 'ORDPLUGINS', 'OUTLN')
and object_type not in ('SYNONYM', 'INDEX')
/* THE OBJECT TO PRIVILEGE RELATIONS */
union
select
table_name p1,
owner p2,
grantee,
grantee,
privilege
from
dba_tab_privs
/* THE ROLES TO ROLES/USERS RELATIONS */
union
select
granted_role p1,
granted_role p2,
grantee,
grantee,
null
from
dba_role_privs
)
start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;

c8

@f
column oracle_process_id format A8
column request_id format 9999999999
column conc_prog_name format A40
column requestor format A17

select fcr.request_id,
substr(decode (fcr.description, null, fcp.user_concurrent_program_name, fcr.description||' ('||fcp.user_concurrent_program_name||')'),1,40) conc_prog_name,
fu.user_name requestor
, sid
, vs.process
, vp.spid
from
v$session vs,
v$process vp,
apps.fnd_user fu,
apps.fnd_concurrent_programs_vl fcp,
apps.fnd_concurrent_requests fcr
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcr.status_code = 'R'
and fcr.phase_code = 'R'
and fcr.requested_by = fu.user_id
and fcr.oracle_process_id = vp.spid(+)
and vp.addr = vs.paddr (+)
;

frm

set echo off
set feedback off
col email_address format a35 heading 'EMAIL ADDRESS'
col user_name format a35 heading 'USER NAME'
col pid format 9999 heading 'PID'
col spid format a6 heading 'SERVER|PID'
col sid format 9999 heading 'SID'
col serial# format 99999 heading 'SERIAL'
col process format a6 heading 'CLIENT|PID'
col osuser format a8 heading 'OS|USERNAME'
col log_per_sec format 99999 heading 'LOG|PER|SEC'
col logical format b999999999 heading 'LOGICAL|READS'
col phy_per_sec format b9999 heading 'PHY|PER|SEC'
col physical_reads format b99999999 heading 'PHYSICAL|READS'
col audsid format b9999999 heading 'AUDIT|SESSION'
col program format a30 heading 'PROGRAM NAME'
col module format a12 heading 'FORM NAME'
col logon_time format a8 heading 'LOGON|TIME'
col duration format a8 heading 'DURATION'
col last_call_min format 999 heading 'LAST|CALL|MIN'
col orcl_user format a6 heading 'ORACLE|USERID'
col status format a8 heading 'Status'
col enqueue format a8 heading 'Enqueue'

set linesize 132
set pagesize 0
set heading on
ttitle 'Long Running Active Form Sessions'
select distinct user_name,
decode( s.status, 'ACTIVE', 'ACTIVE', 'INACTIVE', 'INACTIVE', 'KILLED', 'KILLED', '?' ) status,
decode( s.lockwait, null, null, 'Enqueue' ) enqueue,
s.last_call_et/60 last_call_min,
s.module,
s.sid,
s.serial#,
-- s.username orcl_usr,
-- s.osuser osuser,
s.process,
p.spid,
to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration,
( i.block_gets + i.consistent_gets ) /
( ( sysdate - s.logon_time ) * 86400 ) log_per_sec,
i.block_gets + i.consistent_gets logical,
physical_reads /
( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec,
i.physical_reads,
-- s.action,
email_address
from applsys.fnd_logins l,
applsys.fnd_user u,
v$session s,
v$sess_io i,
v$process p
where l.user_id = u.user_id
and s.sid = i.sid
and p.pid = l.pid
-- and s.process = l.spid
and p.spid = l.process_spid(+)
and l.end_time is null
and s.paddr = p.addr(+)
and substr( s.action, 1, 4 ) = 'FRM:'
and s.last_call_et >= 300
and s.status in ( 'ACTIVE', 'KILLED' )
order by last_call_min desc
/


exit
/