Funda 1 > Rock_restrict_kill_sess - 20th May

sql> sql> -------- to lock the user sql> sql> alter user scott account lock; user altered. sql> ---- to unlock the user sql> sql> alter system scott account unlock; alter system scott account unlock * error at line 1: ora-02065: illegal option for alter system sql> alter

user scott account unlock;

user altered. sql> ------ to restrict a user sql> sql> alter system disable restricted session; system altered. sql> sql> ------to enavle it sql> sql> sql> alter system enable restricted session; system altered. sql> sql> ----------- to kill the user sql> sql> alter system kill session 20; alter system kill session 20 * error at line 1: ora-00026: missing or invalid session id sql> select account_status from dba_users where username='scott'; account_status -------------------------------open sql> desc dba_users name ----------------------------------------username user_id password account_status lock_date

null? -------not null not null

type ---------------------------varchar2(30) number varchar2(30) not null varchar2(32) date

expiry_date default_tablespace temporary_tablespace created profile initial_rsrc_consumer_group external_name

not not not not

null null null null

date varchar2(30) varchar2(30) date varchar2(30) varchar2(30) varchar2(4000)

sql> select username,user_id,lock_date,expiry_date,default_tablespace,temporary_tablespace from dba_users where user_name='scott'; select username,user_id,lock_date,expiry_date,default_tablespace,temporary_tablespace from dba_users where user_name='scott' * error at line 1: ora-00904: "user_name": invalid identifier sql> select username,user_id,lock_date,expiry_date,default_tablespace,temporary_tablespace from dba_users where username='scott'; username -----------------------------default_tablespace -----------------------------scott system

user_id lock_date expiry_da ---------- --------- --------temporary_tablespace -----------------------------59 temp

sql> set linesize 200; sql> set pagesize 200; sql> sql> / username temporary_tablespace

user_id lock_date expiry_da default_tablespace

------------------------------ ---------- --------- -------------------------------------- -----------------------------scott temp

59

sql> select name,password from users$; select name,password from users$ * error at line 1: ora-00942: table or view does not exist sql> name

select name,password from user$; password

system

------------------------------ -----------------------------sys

8a8f025737a9097a

public connect resource dba system

2d594e86f93b17a1

select_catalog_role execute_catalog_role delete_catalog_role exp_full_database imp_full_database outln

4a3ba55e08595c81

recovery_catalog_owner gather_system_statistics logstdby_administrator aq_administrator_role aq_user_role global_aq_user_role

global

oem_monitor dbsnmp

e066d214d5421ccc

hs_admin_role wkuser wmsys wm_admin_role javauserpriv javaidpriv javasyspriv javadebugpriv

7c9ba362f8314299

ejbclient java_admin java_deploy ordsys

7efa02ec7ea6b86f

ordplugins

88a2b2c183431f00

mdsys

72979a94bad2af80

ctxsys

71e687f036ad56e5

ctxapp xdb

88d8364765fce6af

anonymous

anonymous

xdbadmin authenticateduser wksys

69ed49ee1851900d

wkproxy

b97545c4dd2abe54

odm

c252e8fa117af049

odm_mtr

a7a32cd03d3ce8d5

olapsys

3fb8ef9db538647c

_next_user rman

e7b5d92911c831e1

olap_dba hr

6399f3b38edf3288

oe

9c30855e7e0cb02d

pm

72e382a52e89575a

sh

9793b3777cd3bd1a

sales_history_role qs_adm

991cddad5c5c32ca

qs

8b09c6075bdf2dc4

qs_ws

24acf617dd7d8f2f

qs_es

e6a6fa4bb042e3c2

qs_os

ff09f3eb14ae5c26

qs_cbadm

7c632afb71f8d305

qs_cb

cf9cfacf5ae24964

qs_cs

91a00922d8c0f146

scott

f894844c34402b67

62 rows selected. sql> sql> sql> desc user_histroy error: ora-04043: object user_histroy does not exist sql> desc user_history$; name null? type ---------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------user# not null number password varchar2(30) password_date date sql> sql> select u.name, h.password, h.password_date 2 from user$ u, user_history$ h 3 where u.user# = h.user#; no rows selected sql> alter user scott identified by sql; user altered. sql> sql> select u.name, h.password, h.password_date from user$ u, user_history$ h where u.user# = h.user#; no rows selected sql> select h.password, h.password_date from user_history$; select h.password, h.password_date from user_history$ * error at line 1: ora-00904: "h"."password_date": invalid identifier sql> select user#

,password,password_date from

user_history$;

no rows selected sql> --------- to see the active users in database sql> sql> select count(*) "active users" 2 from v$session where username is not null; active users -----------2 sql> select username from v$session where username is not null; username -----------------------------sys system sql> select username from v$session; username ------------------------------

sys system 10 rows selected. sql> sql> sql> -------- to identify current session

sql> sql> select user, osuser 2 from gv_$session 3 where sid = ( 4 select sid 5 from gv$mystat 6 where rownum = 1); user

osuser

------------------------------ -----------------------------sys

kgisltrust\velusamy

sql> desc gv_$session; name null? type ---------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------inst_id number saddr raw(4) sid number serial# number audsid number paddr raw(4) user# number username varchar2(30) command number ownerid number taddr varchar2(8) lockwait varchar2(8) status varchar2(8) server varchar2(9) schema# number schemaname varchar2(30) osuser varchar2(30) process varchar2(12) machine

varchar2(64) terminal varchar2(16) program varchar2(64) type varchar2(10) sql_address raw(4) sql_hash_value number prev_sql_addr raw(4) prev_hash_value number module varchar2(48) module_hash number action varchar2(32) action_hash number client_info varchar2(64) fixed_table_sequence number row_wait_obj# number row_wait_file# number row_wait_block# number row_wait_row# number logon_time date last_call_et number pdml_enabled varchar2(3) failover_type varchar2(13) failover_method varchar2(10) failed_over varchar2(3) resource_consumer_group varchar2(32) pdml_status varchar2(8) pddl_status varchar2(8) pq_status varchar2(8) current_queue_duration number client_identifier

varchar2(64) sql> sql> sql> sql> -------------- to see the connection information sql> sql> sql> select sid,authentification_type,osuser,network_service_banner from gv_$session_connect_info; select sid,authentification_type,osuser,network_service_banner from gv_$session_connect_info * error at line 1: ora-00904: "authentification_type": invalid identifier sql> select sid,authentication_type,osuser,network_service_banner from gv_$session_connect_info; sid authentication_type

osuser

---------- -------------------------- -----------------------------network_service_banner -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------7 database system

8 database

system

9 internal

kgisltrust\velusamy

10 database

kgisltrust\velusamy

windows nt tcp/ip nt protocol adapter for 32-bit windows: version 9.2.0.1.0 production

10 database

kgisltrust\velusamy

oracle advanced security: authentication service for 32-bit windows: version 9.2.0.1.0 - production

10 database

kgisltrust\velusamy

oracle advanced security: nts authentication service adapter for 32-bit windows: version 2.0.0.0.0

10 database

kgisltrust\velusamy

oracle advanced security: encryption service for 32-bit windows: version 9.2.0.1.0 - production

10 database

kgisltrust\velusamy

oracle advanced security: crypto-checksumming service for 32-bit windows: version 9.2.0.1.0 - produ

8 rows selected. sql> sql> sql> sql> --------- to see the previllaged users sql> sql> select * from gv$pwfile_users; inst_id username

sysdb sysop

---------- ------------------------------ ----- ----1 sys

true

true

sql> sql> sool off sp2-0042: unknown command "sool off" - rest of line ignored. sql> spool off