CREATE USER FZAFAR IDENTIFIED BY VALUES ’2BC657B017E6006B’ DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; — 5 Roles for ITSM GRANT SELECT_CATALOG_ROLE TO FZAFAR; GRANT EXECUTE_CATALOG_ROLE TO FZAFAR; GRANT DELETE_CATALOG_ROLE TO FZAFAR; GRANT RESOURCE TO FZAFAR; GRANT CONNECT TO FZAFAR; ALTER [...]
Archive for the ‘Security’ Category
13 May
Dropping User
To drop a user you must have the DROP USER system privilege To check if user has privilege to drop user. select * from session_privs where privilege=’DROP USER’; Dropping User without having objects: SQL> drop user test; SQL> drop user a cascade; Dropping a Connected User Find out the connected user sid and [...]
24 Apr
Locks and Queries
Query to check blocking and waiting sessions SELECT holding_session bsession_id, waiting_session wsession_id, b.username busername, a.username wusername, c.lock_type type, mode_held, mode_requested, lock_id1, lock_id2 FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a WHERE c.waiting_session=a.sid and c.holding_session=b.sid To Find which session is blocking other sessions with some system info select s1.username || ‘@’ || s1.machine || ‘ [...]
26 Mar
Constraints
Several types of Oracle constraints can be applied to Oracle tables to enforce data integrity, including: Oracle "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to [...]
