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;