Archive for the ‘Security’ Category

Standard User Creation Procedure

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 USER FZAFAR DEFAULT ROLE ALL;    –Dont forget to enable default role all
  — 2 System Privileges for FZAFAR
  GRANT CREATE SESSION TO FZAFAR;
  GRANT SELECT ANY TABLE TO FZAFAR;
  — 1 Tablespace Quota for FZAFAR
  ALTER USER FZAFAR QUOTA UNLIMITED ON USERS;

 

Creating User in TOAD 

1. User Info
Select User Name Password  Resource Profile(Default)

2. TableSpace
Default TableSpace and Temporary Tablespace

3. ROLE
Select CONNECT, RESOURCE, EXECUTE_CATALOG_ROLE, SELECT_CATALOG_ROLE

4. SYSTEM Privileges
Leave Blank

5. Object Grants
Leave Blank (if user asks for specific grants then check the checkboxes)

6. Quotas
Leave Blank

7. Resource Groups
Leave Blank

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 serial# by,

SQL> select sid, serial# from v$session where username = 'TEST';



SID SERIAL#
---------- ----------
268 1268
315 1223

Before killing the user , lock its account so that it cannot attempt
further connections.

SQL> Alter user test account lock;

Now kill the connected session.

SQL> alter system kill session ‘268,1268’;

SQL> alter system kill session ‘315,1223’;

Finally drop the user.

SQL> drop user test cascade;

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   || ‘ ( SID=’ || s1.sid || ‘ )  is blocking ‘
  || s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
  from v$lock l1, v$session s1, v$lock l2, v$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1   and l2.id2 = l2.id2 ;

 

Lock type and the ID1 / ID2 columns

The first place to look is the TYPE column. There are only three types of user locks, TX, TM and UL. UL is a user-defined lock — a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it’s acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.

The TM lock is a DML lock. It’s acquired once for each object that’s being changed. The ID1 column identifies the object being modified.

Selecting locking information using v$lock, v$session, dba_objects. First look up for rows with block > 0. Pick the sid and look for rows having same id1(one row should have sid that has another row with block > 0). Create rowid using sid identified earlier as blocking session. Using rowid look up the row in the table.

select * from v$lock

select object_name from dba_objects where object_id=68046

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=135

select do.object_name,
   row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
    dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,  ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
    from v$session s, dba_objects do
    where sid=135
    and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

    select * from sys.tstlock where rowid = ‘AAAQnOAABAAAQ9yAAA’

For RAC

SELECT substr(DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid,1,12) sess,
       id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
   (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
     ORDER BY id1, request;

Isolation Levels are how Oracle executes SQL statements in regards to read consistency and is directly related to what lockmay be ignored.

  • Read Committed (Default)
  • Serializable Transactions
  • Read-only

Read Committed: Each query executed by a transaction sees only data that was committed before the query (not the transaction) began.

Serializable Transaction: See only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements.

Read-Only: See only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.

Oracle Lock Modes

  • Exclusive Lock Mode
  • Share Lock Mode

Exclusive Lock Mode: Prevents the associates resource from being shared. This lockmode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.

Share Lock Mode: Allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.

Oracle Lock Types

  • DML locks (data locks)
  • DDL locks (dictionary locks)
  • Oracle Internal Locks/Latches
  • Oracle Distributed Locks
  • Oracle Parallell Cache Management Locks

Oracle DML Lock Types

  • Row Level Locks
  • Table Level Locks

        Oracle Row Locks [TX]

  • All DML locks Oracle acquires automatically are row-level locks.
  • No limit to the number of row locks held by a transaction.
  • Oracle does not escalate locks from the row level.
  • Row locking provides the lowest level of locking possible provides the best possible transaction concurrency.
  • Readers of data do not wait for writers of the same data rows.
  • A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back.
  • If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

        Oracle  Table Level Lock [TM]

  • A transaction acquires a table lock for DML statements such as INSERT/UPDATE/DELETE, SELECT with the FOR UPDATE, and LOCK TABLE.  Reasons are to reserve DML access to the table on behalf of a transaction and prevent DDL operations
  • Table locks prevent the an exclusive DDL lock on the same table which prevents DDL operations.  Example, a table cannot be altered or dropped if any uncommitted transaction holds a table lock for it.
  • A table lock can be held in several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X).

Oracle DDL Lock Modes

  • Exclusive DDL Locks
  • Shared DDL Locks
  • Breakable Parse Locks

Oracle Latches

  • Latches are low-level serialization mechanisms to protect shared data structures in the system global area (SGA). Latches protect the oracle lists like list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. The implementation of latches is operating system dependent, particularly in regard to whether and how long a process will wait for a latch.

Oracle Internal Locks

  • Data Dictionary Locks
    • Held on entries in dictionary caches while the entries are being modified or used. They guarantee that statements being parsed do not see inconsistent object definitions.
  • File and Log Management Locks
    • Protect various files like control files, redo log files so that only one process at a time can change it. Datafiles are locked to ensure that multiple instances mount a database in shared mode or that one instance mounts it in exclusive mode.
  • Tablespace and Rollback Segment Locks
    • Protect tablespaces and rollback segments. Example, all instances accessing a database must agree on if s tablespace is online or offline. Rollback segments are locked so that only one instance can write to a segment.

Data Dictionary Tables

DBA_LOCK_INTERNAL
1 row for every lock or latch held or being requested

DBA_LOCKS
All locks or latches held or being requested

DBA_DML_LOCKS
All DML locks held or being requested in DB

DBA_DDL_LOCKS
All DDL locks held or being requested in DB

DBA_BLOCKERS
Non-waiting sessions holding locks being waited on

DBA_WAITERS
All sessions waiting on, but not holding waited for locks

 

Good Link for Locks

ORAFAQ on Locks

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 the table definition to ensure the validity of the region column.
  • Not Null Constraint: This Oracle constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time.
  • Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key.
  • References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times.  At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.
  • Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.

Important Constraint Views

dba_cons_columns
dba_constraints

 

CREATE TABLE Dept_tab (
    Deptno  NUMBER(3) CONSTRAINT Dept_pkey PRIMARY KEY, 
    Dname   VARCHAR2(15), 
    Loc     VARCHAR2(15))
CREATE TABLE Emp_tab ( 
    Empno    NUMBER(5) CONSTRAINT Emp_pkey PRIMARY KEY, 
    Ename    VARCHAR2(15) NOT NULL, 
    Job      VARCHAR2(10), 
    Mgr      NUMBER(5) CONSTRAINT Mgr_fkey REFERENCES Emp_tab,
    Hiredate DATE, 
    Sal      NUMBER(7,2), 
    Comm     NUMBER(5,2), 
    Deptno   NUMBER(3) NOT NULL 
             CONSTRAINT dept_fkey REFERENCES Dept_tab ON DELETE CASCADE);

alter table  table_name add constraint constraint_name;

Primary and Foreign Key Constraints
ALTER TABLE Dept_tab 
    ADD CONSTRAINT PK_Dept PRIMARY KEY (deptno); 
ALTER TABLE Emp_tab 
    ADD CONSTRAINT FKEY_Dept FOREIGN KEY (Deptno) REFERENCES Dept_tab;
Unique Check and Not Null Constraints
ALTER TABLE Dept_tab CONSTRAINT UQ_Dname UNIQUE (Dname, Loc);
ALTER TABLE Dept_tab CONSTRAINT Loc_check1
CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'));
ALTER TABLE Emp_tab MODIFY (Ename VARCHAR2(15) NOT NULL);
Enabling Disabling Dropping Constraints

ALTER TABLE Dept  ENABLE CONSTRAINT UQ_Dname;

ALTER TABLE Dept_tab  DISABLE CONSTRAINT UQ_Dname;

ALTER TABLE Dept_tab  DROP CONSTRAINT  UQ_Dname;

 

Constraint State

DEFERRABLE Clause

The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

  • Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The checking of a NOT DEFERRABLE constraint can never be deferred to the end of the transaction.

    If you declare a new constraint NOT DEFERRABLE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

  • Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

The following statement creates table games with a NOT DEFERRABLE INITIALLY IMMEDIATE constraint check (by default) on the scores column:

CREATE TABLE games (scores NUMBER CHECK (scores >= 0));

CREATE TABLE games (scores NUMBER, CONSTRAINT unq_num UNIQUE (scores) INITIALLY DEFERRED DEFERRABLE);

VALIDATE | NOVALIDATE

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default.

ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)
ENABLE VALIDATE;

 

RELY Constraints

The ETL process commonly verifies that certain constraints are true. For example, it can validate all of the foreign keys in the data coming into the fact table. This means that you can trust it to provide clean data, instead of implementing constraints in the data warehouse. You create a RELY constraint as follows:

ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id) 
RELY DISABLE NOVALIDATE;

This statement assumes that the primary key is in the RELY state. RELY constraints, even though they are not used for data validation, can:

  • Enable more sophisticated query rewrites for materialized views. See Chapter 18, " Query Rewrite" for further details.

  • Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it.

DYNAMIC SQL FOR CONSTRAINTS

To disable constraints in a schema for all tables

SELECT ‘alter table ‘||owner||’.’||table_name||’ disable constraint ‘||constraint_name||’ ;’

FROM dba_constraints where owner = ‘HR’

To enable constraints, make sure that referential integrity constraints are enabled after primary key constraints.

SELECT ‘alter table ‘||owner||’.’||table_name||’ enable constraint ‘||constraint_name||’ ;’

FROM dba_constraints  where owner = ‘HR’

order by constraint_type

QUERIES FOR CHECKING CONSTRAINTS

  SELECT Constraint_name, Search_condition  
    FROM User_constraints  
    WHERE (Table_name = 'DEPT_TAB' OR Table_name = 'EMP_TAB') AND  
        Constraint_type = 'C';

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE

FROM DBA_CONSTRAINTS

WHERE TABLE_NAME = ‘EMPLOYEES’

SELECT Owner,Constraint_name, Table_name, Column_name 
FROM dba_cons_columns

where  table_name = ‘EMPLOYEES’

and owner = ‘HR_DUP’