Oracle Flashback Technology (III) - Flashback Data Archive



Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:



Using Flashback Data Archive (Oracle Total Recall)
  • With Data Archive you can store and track transactional changes to a record over its lifetime.
  • It permanently stores undo information on flashback archives, allowing you to keep the transactional history of a object since its creation.
  • Flashback archives are enabled on individual tables and are located in tablespaces, and have a name, a specified retention period and a space quota on the tablespace.
  • A database can have multiple flashback archives.
  • when a DML transaction commits on a flashback archive enabled table, the Flashback Data Archiver (FBDA) process stores the pre-image of the rows into a flashback archive.
  • FBDA also manages the data within the flashback archives (purging data beyond retention period).
  • Historical data can be queried using the Flashback Query AS OF clause.
  • Useful for compliance with record stage policies and audit requirements.


To enable flashback archiving for a table:
  • You need FLASHBACK ARCHIVE privilege on a flashback data archive
  • Table cannot be clustered, nested, temporary, remote or external
  • Table cannot have LONG nor nested columns


Create a Flashback Data Archive
(1) Create a new tablespace (you may also use an existing one)

SQL> create tablespace fda_ts
   datafile '/u01/app/oracle/oradata/test112/fda1_01.dbf'
   size 1m autoextend on next 1m;

SQL> select tablespace_name, status, contents, retention
  from dba_tablespaces
  where tablespace_name ='FDA_TS';

TABLESPACE_NAME                STATUS    CONTENTS  RETENTION   
------------------------------ --------- --------- ----------- 
FDA_TS                         ONLINE    PERMANENT NOT APPLY   

(2) Create Flashback archvies:

SQL> create flashback archive default fda_1m tablespace fda_ts   -- Must be SYSDBA to create DEFAULT FDA
  quota 1G retention 1 month;                                -- To change use ALTER FLASHBACK ARCHIVE...SET DEFAULT

SQL> create flashback archive fda_2yr tablespace fda_ts retention 2 year;
  
SQL> create flashback archive fda_10d tablespace fda_ts retention 10 day;

Managing Flashback Data Archives:
(1) Manage FDA tablespaces:

ALTER FLASHBACK ARCHIVE...
   ...SET DEFAULT;
   ... ADD TABLESPACE... QUOTA...;
   ... MODIFY TABLESPACE...
   ... REMOVE TABLESPACE...

(2) Manage retention period:

ALTER FLASHBACK ARCHIVE fda_name MODIFY RETENTION n [Year | Month | day ];

(3) Purge historical data

ALTER FLASHBACK ARCHIVE...
   ...PURGE ALL;                          -- Purge ALL historical data
   ...PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL 'n' DAY);
   ...PURGE BEFORE SCN scn_num;

(4) Drop FDA:

DROP FLASHBACK ARCHIVE fda_name;           -- Drops FDA. Keeps tablespace.

Enabling FDAs on objects:
  • FDA is disabled by default
  • User needs FLASHBACK ARCHIVE privilege to create enable flashback archive on object.
SQL> conn sys/pwd as sysdba;

SQL> grant flashback archive on fda_1m to userA;

SQL> conn userA/pwd;

SQL> Create table emp 
  (empno number primary key,
   ename varchar2(20),
   salary number) 
  flashback archive fda_1m;


-- To Disable Flashback archive on table
SQL> ALTER TABLE emp NO flashback archive;

Information about Flashback data Archives:
DBA_FLASHBACK_ARCHIVE, DBA_FLASHBACK_ARVHIE_TS and DBA_FLASHBACK_ARCHIVE_TABLES
SQL> select owner_name, flashback_archive_name, retention_in_days, status, 
       to_char(last_purge_time, 'dd-mon-yy hh24:mi:ss')
from dba_flashback_archive;

OWNER_NAME    FLASHBACK_ARCHIVE_NAME  RETENTION_IN_DAYS      STATUS  LAST_PURGE_TIME           
------------- ----------------------- ---------------------- ------- ------------------------- 
SYS           FDA_1M                  30                     DEFAULT 25-oct-11 13:34:14 
SYS           FDA_2YR                 730                            25-oct-11 13:34:54 
SYSTEM        FDA_10D                 10                             25-oct-11 13:38:05

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME  FLASHBACK_ARCHIVE#     TABLESPACE_NAME                QUOTA_IN_MB 
----------------------- ---------------------- ------------------------------ ------------
FDA_1M                  1                      FDA_TS                         1024        
FDA_2YR                 2                      FDA_TS                                     
FDA_10D                 3                      FDA_TS                                     


SQL> select * from dba_flashback_archive_tables;

TABLE_NAME  OWNER_NAME                     FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME   STATUS   
----------- ------------------------------ ---------------------- ------------------- -------- 
EMP         SYSTEM                         FDA_1M                 SYS_FBA_HIST_75434  ENABLED  


Example: Viewing table history.
(1) Insert data on emp
(2) Keep record of some points in time
(3) Query the historical data on emp
SQL> select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') start_time, 
       current_scn start_scn
     from v$database;

START_TIME         START_SCN              
------------------ ---------------------- 
25-oct-11 14:22:25 1498655       

SQL> select * from emp;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 

-- PL/SQL block performs a number of DMLs on emp and prints timestamps
set serveroutput on
declare
 procedure get_timestamp
 is
   v_time varchar2(25);
   v_scn  integer;
 begin
   select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') start_time, 
        current_scn start_scn into v_time, v_scn
   from v$database;
   dbms_output.put_line('timestamp: ' || v_time);
   dbms_output.put_line('SCN:       ' || v_scn);
end;
 
begin
  insert into emp values (1, 'John', 2000);
  commit;
  dbms_lock.sleep(2);
  get_timestamp();
  for i in 1 .. 10 
  loop
   update emp set salary =salary*1.05 where empno=1;
   commit;
   dbms_lock.sleep(2);
   if i=5 then
     insert into emp values (2, 'Mary', 3000);
     update emp set salary = 2500 where empno =1;
     commit;
     dbms_lock.sleep(2);
     update emp set ename = initcap(ename);
     commit;
     insert into emp values (3, 'Gary', 1500);
     delete from emp where empno=2;
     commit;
     get_timestamp();   
   end if;
  end loop;
  dbms_lock.sleep(2);
  get_timestamp();
end;
/

anonymous block completed
timestamp: 25-oct-11 14:22:27
SCN:       1498659
timestamp: 25-oct-11 14:22:39
SCN:       1498683
timestamp: 25-oct-11 14:22:51
SCN:       1498700

SQL> select * from emp;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
3                      Gary                 1500         

SQL> select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') end_time, 
       current_scn end_scn
     from v$database;
END_TIME           END_SCN                
------------------ ---------------------- 
25-oct-11 14:22:51 1498701


(a) Select data at a point in time
SQL> select *  from emp as of scn 1498683;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 2500                   
3                      Gary                 1500       

SQL> select * 
     from emp as of timestamp to_timestamp('25-oct-11 14:22:51', 'dd-mon-yy hh24:mi:ss');
EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
3                      Gary                 1500  


(b) select all versions of a row betwen two points in time

SQL> select *
     from emp
       versions between scn 1498659 and 1498700
     where empno =1;
EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
1                      John                 2000                   
1                      John                 2100                   
1                      John                 2205                   
1                      John                 2315.25                
1                      John                 2431.0125              
1                      John                 2552.563125            
1                      John                 2500                   
1                      John                 2500                   
1                      John                 2625                   
1                      John                 2756.25                
1                      John                 2894.0625              
1                      John                 3038.765625            
1                      John                 3190.70390625          

 14 rows selected 


SQL> select versions_xid xid, versions_startscn start_scn,
            versions_endscn end_scn, versions_operation operation,
            empno, ename, salary
     from emp
        versions between scn 1498659 and 1498700
     where empno =1;

XID              START_SCN   END_SCN                OPERATION EMPNO   ENAME                SALARY                 
---------------- ----------- ---------------------- --------- ------- -------------------- ---------------------- 
03000F008B040000 1498633     1498674                I         1       John                 3190.70390625          
05001F00AA040000 1498657     1498661                I         1       John                 2000                   
030003008B040000 1498661     1498664                U         1       John                 2100                   
02000A007E040000 1498664     1498667                U         1       John                 2205                   
01000D003A030000 1498667     1498670                U         1       John                 2315.25                
0400090075030000 1498670     1498672                U         1       John                 2431.0125              
06000B0094040000 1498672     1498674                U         1       John                 2552.563125            
0900080096040000 1498674     1498678                U         1       John                 2500                   
03001F008B040000 1498678     1498685                U         1       John                 2500                   
09001F0097040000 1498685     1498688                U         1       John                 2625                   
080010006C050000 1498688     1498691                U         1       John                 2756.25                
0700190078030000 1498691     1498694                U         1       John                 2894.0625              
03001A008B040000 1498694     1498697                U         1       John                 3038.765625            
05001E00AB040000 1498697                            U         1       John                 3190.70390625          

 14 rows selected 







Oracle Flashback Technology (II) - Flashback Transactions




Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:





Oracle Flashback Transaction
  • Allow you to rollback changes made by one or more transactions.
  • Flashback Transaction generates and executes the DMLs necessary to undo a given transactions and (optionally) all its dependent transactions.
  • Requisites:
    • ARCHIVELOG mode
    • At least one archive log already generated
    • Minimal and PK (plus FK) supplemental logging enabled
    Privileges required:
    • FLASHBACK and SELECT on the object or
    • FLASHBACK ANY TABLE.

Configuring the database to use Flashback Transaction

(1) Check the database log mode

SQL> Select log_mode from v$database;

LOG_MODE     
------------ 
NOARCHIVELOG

(2) Change Log mode to archivelog:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

SQL> Select log_mode from v$database;

LOG_MODE     
------------ 
ARCHIVELOG

(3) Switch logfile

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

(4) Check log files

SQL> select group#, thread#, bytes/1024/1024 Mb, members, archived, status
     from v$log;

GROUP#                 THREAD#                MB                     MEMBERS                ARCHIVED STATUS           
---------------------- ---------------------- ---------------------- ---------------------- -------- ---------------- 
1                      1                      50                     1                      YES      ACTIVE           
2                      1                      50                     1                      NO       CURRENT          
3                      1                      50                     1                      YES      INACTIVE       

SQL> select name, archived, applied, status, completion_time, compressed
     from v$archived_log;

NAME                                                      ARCHIVED APPLIED   STATUS COMPLETION_TIME           COMPRESSED 
--------------------------------------------------------- -------- --------- ------ ------------------------- ---------- 
/u01/.../flash_recovery_area/SID/archivelog/date/name.arc YES      NO        A      08-DEC-10                 NO         

(5) Enable Minimal and PK (and FK) supplemental logging 

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

(3) Check the current setting for supplemental logging
  (V$DATABASE, DBA_LOG_GROUPS, DBA_LOG_GROUP_COLUMNS)

SQL> Select supplemental_log_data_min, supplemental_log_data_all,
            supplemental_log_data_UI,  supplemental_log_data_FK
     from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK 
------------------------- ------------------------- ------------------------ ------------------------ 
YES                       NO                        NO                       YES                     

Using Flashback Transaction
  • Use DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure to rollback a transaction and its dependent transactions.
  • Can be done with the database online.
Restrictions
  • DDL operations cannot be flashed back
  • Transactions that use LOB datatypes (BFILE, BLOB, CLOB, NCLOB) cannot be flashed back
  • Transactions cannot use features or datatypes not supported by the Log Miner
  • Ensure that undo segments are large enough to maintain undo information for the period you may need to flashback.

Example 1: Flashback transactions that have no dependent transactions.
  1. Drop and create tables dept and emp
  2. Transaction 1 and Transaction 2 insert into dept and emp
  3. To flashback Transaction 2 you need:
    • Find out the transaction ID (XID): you can use OEM and Log Miner, or use a flashback transaction query.
    • Identify the transaction to be flashed back and use dbms_flashback.transaction_backout

(1) Drop and recreate dept and emp tables.
SQL> drop table emp;
SQL> drop table dept;

-- Check the current database SCN
SQL> select current_scn scn_before_tblcreation 
     from v$database;

SCN_BEFORE_TBLCREATION 
---------------------- 
1473663               


SQL> create table dept
    (deptno number primary key,
     deptname varchar2(20));
  
SQL> create table emp
     (empno number primary key, 
      empname varchar2(10),
      deptno number, 
      salary number,
      constraint fk_dept foreign key(deptno)
                         references dept(deptno));

-- Check the current database SCN
SQL> select current_scn scn_before_transactions 
     from v$database;

SCN_BEFORE_TRANSACTIONS 
----------------------- 
1473743   
(2) Insert data into emp and dept
-- Transaction 1: Insert new row (id=1)
insert into dept values (1, 'accounting');
insert into dept values (2, 'sales');
insert into emp values (1, 'John', 1, 2000);
commit;

-- Transaction 2: Insert new rows (ids=2,3)
insert into emp values (2, 'Mary', 1, 2500);
insert into emp values (3, 'Jack', 2, 2000);
insert into dept values (3, 'IT');
commit;

-- Check the current database SCN
SQL> select current_scn scn_after_transactions 
     from v$database;

SCN_AFTER_TRANSACTIONS 
---------------------- 
1473781 

-- Check inserted data
SQL> select * from dept;

DEPTNO                 DEPTNAME             
---------------------- -------------------- 
1                      accounting           
2                      sales                
3                      IT                   

SQL> select * from emp;

EMPNO                  EMPNAME    DEPTNO                 SALARY                 
---------------------- ---------- ---------------------- ---------------------- 
1                      John       1                      2000                   
2                      Mary       1                      2500                   
3                      Jack       2                      2000  
(3) Now use the flashback transaction query to find information about all transactions in a time or SCN interval
SQL> select versions_xid xid, versions_startscn start_scn,
            versions_endscn end_scn, versions_operation operation,
            empno, empname, deptno, salary
     from emp
        versions between scn 1473743 and 1473781;

XID              START_SCN   END_SCN  OPERATION EMPNO   EMPNAME    DEPTNO   SALARY  
---------------- ----------- -------- --------- ------- ---------- -------- -------- 
05000D0099040000 1473777              I         3       Jack       2         2000   
05000D0099040000 1473777              I         2       Mary       1         2500   -- Transaction 2
07000A006A030000 1473747              I         1       John       1         2000   

Identify the transaction you need to flashback. For example, if you choose to flashback transaction 2 only its three insert statements will be flashed back. Also, transaction 2 has no dependents, since:
  • No other subsequent transaction modified any data touched by transaction 2
  • No other subsequent transaction modified data linked through referential integrity with the rows touched by transaction 2.

To flashback a transaction, use DBMS_FLASHBACK.TRANSACTION_BACKOUT (numtxns, xids, options).
The procedure accepts one or more transaction IDs (XIDs).
It has also an OPTIONS parameter to indicate how dependent transactions should be treated. The possible values are:
  • NOCASCADE - The transaction should have no dependencies. If a dependency is found, this raises an error.
  • NOCASCADE_FORCE - The transaction is backed out without affecting dependent transactions. This option succeeds only if no constraints are violated in the process.
  • NONCONFLICT_ONLY - This option backout only the changes to the non-conflicting rows of the given transactions.
  • CASCADE - The transaction and all it's dependencies will be removed.
SQL> conn / as sysdba;

SQL> declare
 v_xid sys.xid_array;
begin
 v_xid := sys.xid_array('05000D0099040000');
 dbms_flashback.transaction_backout( numtxns => 1,
                                     xids    => v_xid,
                                     options => dbms_flashback.cascade);
end;
/
anonymous block completed
  • TRANSACTION_BACKOUT analyzes the transactional dependencies, performs DML operations, and generates reports.
  • It also DOES NOT commit the actions performed.
  • To make the transaction backout permanent, you need to explicitly commit the transaction.
(4) Check the effects of the flashback. Query the table contents. Commit the flashback transaction.
SQL> select * from dept;

DEPTNO                 DEPTNAME             
---------------------- -------------------- 
1                      accounting           
2                      sales                


SQL> select * from emp;

EMPNO                  EMPNAME    DEPTNO                 SALARY                 
---------------------- ---------- ---------------------- ---------------------- 
1                      John       1                      2000           

SQL> commit;

TRANSACTION_BACKOUT Reports
The backout procedure populates two static data dictionary views.
  • DBA_FLASHBACK_TXN_STATE
  • Shows information about the transactions backedout by the procedure.
    • COMPENSATING_XID - Transaction of ID of the compensating transaction. You will need to explictly commit this transaction in order to make the backout permanent.
    • XID - Backedout transaction
    • DEPENDENT_XID - Dependent transaction
(a) Query DBA_FLASHBACK_TXN_STATE view

SQL> select *
from dba_flashback_txn_state
where xid = '05000D0099040000';

COMPENSATING_XID XID              DEPENDENT_XID    BACKOUT_MODE     USERNAME                       
---------------- ---------------- ---------------- ---------------- ------------------------------ 
0800160056050000 05000D0099040000                  CASCADE          SYSTEM                

(b) Use the compensating_xid returned to query the DBA_FLASHBACK_TXN_REPORT view:

sql> select xid_report
     from dba_flashback_txn_report
     where compensating_xid = '0800160056050000';

XID_REPORT   
------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?> 
<COMP_XID_REPORT XID="0800160056050000" NAME="_SYS_COMP_TXN_1114255_TIM_1319522919"> 
 <TRANSACTION XID="05000D0099040000"> 
 <CHARACTERISTICS> 
 </CHARACTERISTICS> 
 <UNDO_SQL> 
  <USQL\="yes"> 
   delete from "SYSTEM"."DEPT" where "DEPTNO" = '3' and "DEPTNAME" = 'IT' 
  </USQL>
  <USQL exec="yes">
   delete from "SYSTEM"."EMP" where "EMPNO" = '3' and "EMPNAME" = 'Jack' and "DEPTNO" = '2' and "SALARY" = '2000'
  </USQL> 
  <USQL exec="yes"> 
   delete from "SYSTEM"."EMP" where "EMPNO" = '2' and "EMPNAME" = 'Mary' and "DEPTNO" = '1' and "SALARY" = '2500' 
  </USQL>  
 </UNDO_SQL> 
 <DEPENDENT_XIDS> 
 </DEPENDENT_XIDS>
 </TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>delete from "SYSTEM"."DEPT" where "DEPTNO" = '3' and "DEPTNAME" = 'IT' 
</EXEC_USQL> 
<EXEC_USQL>delete from "SYSTEM"."EMP" where "EMPNO" = '3' and "EMPNAME" = 'Jack' and "DEPTNO" = '2' and "SALARY" = '2000' 
</EXEC_USQL>
<EXEC_USQL>delete from "SYSTEM"."EMP" where "EMPNO" = '2' and "EMPNAME" = 'Mary' and "DEPTNO" = '1' and "SALARY" = '2500'
</EXEC_USQL>             
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>  


Structured Storage approaches

(Extracts from a good discussion posted by James Hamilton in his blog. You can check the original article and comments here.)

Although a couple of years old, James Hamilton provided a good requisite-based breakdown of data storage systems. These are some of his points:
  • The world of structured storage extends far beyond relational (Oracle, DB2, SQL Server, MySQL, NoSQL, etc) systems.
  • Many applications do not need the rich programming model of relational systems and some are better seviced by lighter-weight, easier-to-administers, and easier-to-scale solutions.

  • Structured storage approaches can be classified based on customer major requirements.
  • These are Feature-first, scale-first, simple structured storage and purpose-optimized stores.

(1) Feature-First
  • Traditional Relational database management systems (RDBMS) are the structured storage system of choice here.
  • Driven by requirements for Enterprise financial systems, human resource systems, customer relationship management systems (FIN, HR, CRMs)
    • Examples here include Oracle, MySQL, SQL Server, PostgreSQL, Sybase, DB2.
  • Cloud solutions here include:

(2) Scale-First
  • This is the domain of very high scale website (i.e. facebook, Gmail, Amazon, Yahoo, etc)
  • Scaling capabilities are more important than more features and none could run on a single rdbms.
  • The problem here is that the full relational database model (including joins, aggregations, use of stored procedures) is difficult to scale (especially in distributed contexts).
  • Distributing data across tens to thousands of rdbms instances and still maintain support for the distributed data as if it were under a single rdbms engine is difficult.
  • As an alternative, very high scale may be supported with the use of key-value store solutions. These include HBase, Amazon SimpleDB (cloud-based), Project Valdemort, Cassandra, Hypertable, etc

(3) Simple Structure storage
  • Applications that have a structure storage requirement but do not need features, cost and complexity of RDBMSs neither have very high scalability requirements.
  • Some implementations include:
  • Facebook: email inbox search (Cassandra)
  • Amazon: retail shopping card (Dynamo)
  • Berkeey DB

(4) Purpose-Optimized stores
  • Mike Stonebraker argued that the existing commercial RDBMS offerings do not meet the needs of many important market segments
  • Some special purpose real-time, stream processing solutions (StreamBase, Vertica, VoltDB) have beat the RDBMS benchmart by +30x...


Readings:
Mike Stonebraker, One Size fits all

Oracle Database Security


source: xkcd

Oracle default security features
User accounts
  • naming convention, password profiles, password policies
  • Unlike SQL Server (2005/2008), there is no clear separation between database user and database schema
Authentication methods
  • database level, OS level, network level
Privileges and roles
  • restrict data access
Application security
  • User session information using Application CONTEXT
  • Application Context: name-value pair that holds session info.
  • You can retrieve info about a user (i.e., username/terminal, username/deptid) and restrict database and application access based on this information.
Virtual Private Database: restrict database access on the row and column levels.
  • VPD policy: dynamically imbeds a WHERE clause into SQL statements
Encryption: DBMS_CRYPTO and DBMS_SQLHASH
Audit database





Additional database security resources
  1. Oracle Advanced Security
    • TDE, Wallet Mgmt, Network Encryption, RADIUS, Kerberos, Secure Sockets Layer authentication
  2. Oracle Label Security
    • Secures database tables at the row level
  3. Oracle Database Vault
    • Provides fine-grained access control to sensitive data
  4. Oracle Audit Vault
    • Collect data from audit trail tables, OS audit files and redo logs.
  5. Oracle Enterprise User Security
  6. Oracle Total Recall
  7. Oracle Database Firewall
  8. Oracle Data Masking


Managing Oracle Security
Database Security involves Authentication, Authorization, Data Access, and Audit


(1) Authentication Methods
  • Database Authentication (Default)
    • Oracle implements authentication through a user ID and password.
  • Operating System authentication
    • Sometimes referred as OPS$ accounts.
    • Oracle looks for a user ID that matches your OS login ID and prepends an OPS$ to it.
    • Additional benefit: OS scripts can log into the database without hard coding user names and passwords.
  • Third Party Authentication
    • You can tie authentication to third-party providers like Kerberos or DCE (called network authentication) (Needs EE+Security Pack)
    • or provide it from the middle tier (called multitier authentication).


(2) Authorization
  • Giving access to certain objects, processes or resources.
  • Implemented using GRANTS on objects to ROLES or USERS.
  • Oracle implements authorization via users, roles and permissions




(3) Securing Data Access
  • The ability to access specific data values associated with an object (across records or columns)
  • STORED PROCEDURES are commonly used to secure data access.
  • Benefits of using stored procedures for all programmatic data access: (a) secure the data; (b) provide consistent application access to the data; (c) Hide the data structure.
  • Secure VIEWS can also be used to restrict access. (use WITH CHECK OPTION Constraint)
  • Controlling access at the record level:
    • Previous versions (before Oracle 10g): using secure views
    • More recently:
  • (a) Use Virtual Private Databases (VPDs)
  • (b) Use Fine-Grained Access Control (FGAC) - uses DBMS_RLS package (Row Level Security)
  • With FGAC (DBMS_RLS), you can transparently include a WHERE clause on any combinations of DML and SELECT when a user queries the database.

Securing DATA ACCESS
(a) Option: use Secure Views
userA owns EMPLOYEES table.
userB needs access to EMPLOYEES but cant see SALARY or MANAGER records.
userA may create a view and grant access to userB on that view.

-- User A:
 SQL> create or replace view emp_no_sal as 
      select employee_id, last_name, first_name, email, job_id, manager_id
      from iranmr.employees a
      where employee_id NOT IN (
           select nvl(manager_id, -1)
           from iranmr.employees b);

 SQL> grant select on emp_no_sal to userB;

(b) Option: Use Virtual Private Database (VPD)
  • With Virtual Private Databases (VPDs), Oracle allows column masking to hide columns.
  • When you select the row, Oracle will only display NULL for the secure columns.
  • If you're securing at the row level and column level, it's probably easier to just implement VPDs and not the secure views.

What's a Virtual Private Databases (VPDs)?
  • A VPD is just asking Oracle to put a where clause on DML against an object with a security policy on it.
  • A security policy is defined with DBMS_RLS package.
  • A security policy is normally defined in a CONTEXT (a piece of data that says how the where clause should be built).


(4) Audit
  • Older versions: audit implemented through triggers.
  • Trigger limitations: (a) triggers can easily be disabled or modified; (b) triggers do not fire on SELECT; (c) require extensive coding and testing.
  • Newer versions: Use Fine Grained Auditing (FGA). (DBMS_FGA package)


Oracle Advanced Security



Oracle Advanced Security (OAS)

  • Introduced in Oracle8i.
  • Combines (a) strong authentication with (b) encryption of data in storage and while being transferred to and from the database.
  • Includes Transparent Data Encryption (TDE), Wallet Management, Network Encryption, RADUIS, Kerberos, Secure Socket Layer (SSL) Authentication, etc.
  • Helps customers address regulatory compliance requirements, including
    • - Sarbanes-Oxley (?)
    • - Payment Card Industry Data Security Standard (PCI-DSS),
    • - Health Insurance Portability and Accountability Act (HIPAA), and numerous
    • - Breach notification laws.

OAS provides (transparent) data encryption and strong authentication services
- Protect sensitive data on the network, on storage media and within the database from unauthorized disclosure.
- Also protects against theft, loss, and improper decommissioning of storage media and database backups


OAS Components
Transparent data Encryption (TDE)
  • Encrypts data before it is written to storage and
  • automatically decrypts data when reading it from storage without any changes to existing applications (no need for triggers, views, etc..)
  • Access controls that are enforced by the Oracle database still remain in effect. These include object grants, roles, virtual private database and Oracle Database Vault.
  • Two supported modes: TABLESPACE ENCRYPTION (11g only) and COLUMN ENCRYPTION (Introduced on 10g r2)
    • Tablespace Encryption: good for encrypting entire app tables
    • Column Encryption: good for individual data elements (credit cards, SSNs, etc).
    • Frequently accessed data blocks are cached in memory in the same manner as traditional nonencrypted data blocks

KEY Management
  • Two-tier key management architecture: MASTER encryption key + one or more DATA encryption keys.
  • TDE MASTER Encryption key (MEK): used to encrypt and protect the DATA encryption keys.
  • TDE MEK: can be stored in the Oracle Wallet.

Network encryption
  • Provides standards-based network encryption
  • Connections can be rejected from clients that have encryption turned off
  • No changes to existing applications are required

Strong Authentication
  • Kerberos, PKI or RADIUS
  • SSL-based authentication can make use of Smart Cards.

Encrypted database backups
  • RMAN backups encrypted data.
  • RMAN can call TDE during the backup process to encrypt the entire database (including SYSTEM and SYSAUX).
  • RMAN can COMPRESS and use TDE to ENCRYPT => compact and secure backups.

Identity and Access Control: Authentication and Users


  • Access to a SQL Server database takes place through three levels of security.
  • In each one you need to have access and permissions configured independently.
  • You may need to:
    • (1) Login in to a Windows domain (level 1) (except for non-windows clients)
    • (2) The Windows login is then mapped to SQL Server Login (level 2)
    • (3) The SQL Server Login is mapped to a database user (level 3).

Authentication Mode
  • Controls how application users connect to SQL Server
  • (a) Windows Authentication mode
    • Default and recommended
    • Only authenticated Windows users can access the SQL Server instance
    • Allowed connections:
      • by users who are authenticated through the Windows Active Directory service or
      • by the local user account databse on the SQL Server machine.
    • A Windows Login(within SQL Server) has to be created for each Windows user or group that needs acess to the SQL instance.

  • (b) Mixed Mode Authentication
    • Windows users can be mapped to SQL Server Windows logins or
    • SQL Logins can be created directly in SQL Server.
    • This mode is required when non-Windows clients need to access the server.
    • SQL Logins are not considered as secure as Windows Logins.
    • While Windows uses Kerberos authentication, SQL Logins store username/passwd in the master database.
    • If the use of SQL Logins is a requirement, you should also use some form of lower-layer network encryption (i.e. IPSec or SSL).

Principals
  • Principals: Entities (individuals, groups, processes) that can request SQL Server resources.
  • Securables: the server, databases, and objects within databases.
  • Principals can be arranged in a hierarchy
  • Scope of influence of a principal depends on:
    • (a) its definition scope: Windows -> Server -> database
    • (b) whether principal is indivisible (i.e. windows login, SQL Logins) or collection (i.e windows group)
  • Every principal has a security indentifier (SID)



Windows-level principals
  • Windows group
  • Windows domain login
  • Windows local login

SQL Server-level principals
  • SQL Server Login
    • The sa login is created when instance is installed.
    • sa default database: master.
    • Server-level principals for internal use: names enclosed with '##' (certificate-based)
  • Fixed Server Role:
    • sysadmin, serveradmin, securityadmin, processadmin,
    • setupadmin, bulkadmin, diskadmin, dbcreator, public

Database-level principals
  • Database User
    • Every database includes information_schema and sys.
    • These entities appear as users, BUT information_schema and sys ARE NOT principals. Cannot be dropped.
    • The guest user is created by default in every user database.
    • The guest user cannot be dropped, but can be disabled (revoke CONNECT), except in master and tempdb.
  • Fixed | Flexible Database Role
  • (i.e. public. every database user belongs to the public db role.)
  • Application Role

Types of SQL Server Logins:
(neither of which are mapped to an OS user)
  • Windows Logins
  • SQL Server Logins

Creating a SQL Server Login
(a) Create a SQL Server login that uses Windows Authentication
-- domain/user have to be a valid/existing Windows user
Create Login [domain/user] from windows;


-- domain/group has to be a valid Windows group
create login [domain_name/group_name] from windows;

(b) create a SQL Server Login that uses SQL Server authentication
CREATE LOGIN [test_SQLAuth_1] WITH PASSWORD=***, 
     DEFAULT_DATABASE=[Books], 
     DEFAULT_LANGUAGE=[us_english], 
     CHECK_EXPIRATION=ON, 
     CHECK_POLICY=ON
GO

ALTER LOGIN [test_SQLAuth_1] DISABLE
GO

Drop login test_SQLAuth_1;

(a) use sys.server_principals

select name, type_desc, default_database_name
from sys.server_principals;
name type_desc default_database_name
----------------------------------------------
sa SQL_LOGIN master
public SERVER_ROLE NULL
...
NT AUTHORITY\SYSTEM WINDOWS_LOGIN master
NT SERVICE\MSSQL$SQLSERVER08 WINDOWS_GROUP master
iranmr-PC\iranmr WINDOWS_LOGIN master
NT SERVICE\SQLAgent$SQLSERVER08 WINDOWS_GROUP master
..
test_SQLAuth_1 SQL_LOGIN Books

(b) use sys.sql_logins
select name, type_desc, default_database_name
from sys.sql_logins;
name type_desc default_database_name
-----
sa SQL_LOGIN master
...
test_SQLAuth_1 SQL_LOGIN Books

Server-Level Roles (Fixed Server Roles)
  • SQL Server-level Logins can be managed using fixed server roles.
  • There are 9 Server-level roles.
    • sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator, public
  • Roles are similar to Windows OS groups
  • Server-level roles: also called fixed server roles (because cannot be created by users)
  • Have server-wide scope.
  • You can assign SQL Server logins, Windows accounts, and Windows groups to server-level roles.
Information about Server-Level Roles:
exec sp_helpsrvrole;
ServerRole Description
-----
sysadmin System Administrators
securityadmin Security Administrators
serveradmin Server Administrators
setupadmin Setup Administrators
processadmin Process Administrators
diskadmin Disk Administrators
dbcreator Database Creators
bulkadmin Bulk Insert Administrators

exec sp_helpsrvrolemember;
ServerRole MemberName MemberSID
-----
sysadmin sa
sysadmin NT AUTHORITY\SYSTEM 
sysadmin NT SERVICE\MSSQL$SQLSERVER08
sysadmin iranmr-PC\iranmr
sysadmin NT SERVICE\SQLAgent$SQLSERVER08


Adding/Dropping a Server Login to a Server-level Role
exec sp_addsrvrolemember test_SQLAuth_1, sysadmin;

exec sp_helpsrvrolemember;
ServerRole MemberName MemberSID
-----
sysadmin sa
sysadmin NT AUTHORITY\SYSTEM 
sysadmin NT SERVICE\MSSQL$SQLSERVER08
sysadmin iranmr-PC\iranmr
sysadmin NT SERVICE\SQLAgent$SQLSERVER08
sysadmin        test_SQLAuth_1

exec sp_dropsrvrolemember test_SQLAuth_1, sysadmin;


Database users
Database users are principals at the database level.
A database user needs to be created for each login that needs to access the database.
When a login that does not have a user mapped to it tries to access a database, it is logged as the guest database user.
However, this only happens if the guest user has been granted CONNECT to the database.
(a) Create database used mapped to an existing SQL Server Login
USE [Books]
GO

CREATE USER [test_auth_dbbooks1] FOR LOGIN [test_SQLAuth_1] 
       WITH DEFAULT_SCHEMA=[dbo]
GO

(b) Check the created database user among the database principals
use Books; go select name, type_desc, default_schema_name from sys.database_principals; name type_desc default_schema_name ------- public DATABASE_ROLE NULL dbo WINDOWS_USER dbo guest SQL_USER guest INFORMATION_SCHEMA SQL_USER NULL sys SQL_USER NULL test_auth_dbbooks1 SQL_USER dbo ...

Fixed | Flexible Database Roles
  • Roles are security principals that group other principals.
  • Can be used to group database users with similar access privileges.
  • You can create new (flexible) database roles to define groups based on business requisites.
  • You can nest roles (add role into other role)
  • Create new role: CREATE ROLE role_name
  • Add user to role: exec sp_addrolemember role_name, user_name.
  • Fixed roles exist in all databases:
    • db_onwer, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader


Application Role
  • Is a database principal that enables an application to run with its own, user-like permissions.
  • Can be used to enable access to specific data only to users connecting through a specific application.
  • Application roles contain no members.
  • How it works:
    • (1) Client application connects to SQL Server as a database user.
    • (2) Client application executes sp_setapprole.
    • (3) If validated, a context switch occurs and the application assumes the permission of the application role.
CREATE APPLICATION ROLE weekly_receipts 
    WITH PASSWORD = '987G^bv876sPY)Y5m23', 
    DEFAULT_SCHEMA = Sales;
GO


Managing Schemas
  • Schemas are collections of database objects that form a single namespace.
  • Starting in SQL Server 2005, schemas and database users are separate entities.
  • User name is no longer part of the object name.
  • Each schema is now a distinct namespace that exists independently of the database user who created it.
  • Each schema is owned either by a user or a role.
  • If you need to drop a user, you need to transfer the schema ownership to someone else first.
  • You can define a default schema for a user.
  • Implications of the separation of ownership:
    • Ownership of schemas is now transferable
    • Objects can be moved between schemas.
    • Multiple database users can share a single default schema.
    • Improved object-permission management.
    • Schema can be owned by any database principal: user, database role, application role.

Checking schemas
use Books;
go
select * from sys.schemas;

name                schema_id   principal_id
------------------  ----------  ------------
dbo                 1          1
guest 2 2
INFORMATION_SCHEMA 3 3
sys 4 4
db_owner 16384 16384
db_accessadmin 16385 16385
db_securityadmin 16386 16386
db_ddladmin 16387 16387
db_backupoperator 16389 16389
db_datareader 16390 16390
db_datawriter 16391 16391
db_denydatareader 16392 16392
db_denydatawriter 16393 16393


Using SQL Server and Database Principals, Schemas, application roles and context switching
(a) Create a SQL Server login, database user and schema.
(b) Create application role associated with the new schema
(c) Activate the new application role, switching user context
(d) Return to previous context.
-- Using database books
USE books;
GO
(1) Create a SQL Server login user1
CREATE login user1 WITH password = ***;
(2) Create a [books] database user user1. -- user1 is created in the [books] database, -- is associated with user1 SQL Server principal (login) -- has schema1 as default schema.
CREATE USER  user1 FOR login user1 WITH default_schema = schema1;
(3) Query sys.sql_logins to check that the SQL Server principal was created
SELECT name, type_desc, default_database_name
    FROM sys.sql_logins;
name type_desc default_database_name -------------- --------- --------------- sa SQL_LOGIN master ... test_SQLAuth_1 SQL_LOGIN Books user1 SQL_LOGIN master (4) Query books.sys.database_principals to check that databse user user1 was created
SELECT name, principal_id, type_desc, default_schema_name
    FROM sys.database_principals;
name principal_id type_desc default_schema_name ------------------- ------------- ------------- --------------- public 0 DATABASE_ROLE NULL dbo 1 SQL_USER dbo guest 2 SQL_USER guest INFORMATION_SCHEMA 3 SQL_USER NULL sys 4 SQL_USER NULL user1 6 SQL_USER schema1 db_owner 16834 DATABASE_ROLE NULL ... (5) Query books.sys.schemas to check that the schema1 DOES NOT yet exist.
SELECT name, schema_id, principal_id
    FROM sys.schemas;
name schema_id principal_id ----------------- --------- -------- dbo 1 1 guest 2 2 INFORMATION_SCHEMA 3 3 sys 4 4 db_owner 16384 16384 ...

(6) Create schema books.schema1, owned by user1
USE Books;
GO
CREATE SCHEMA schema1 AUTHORIZATION user1;
(7) Query sys.schemas to check schema1 was created.
SELECT name, schema_id, principal_id
    FROM sys.schemas;
name schema_id principal_id ----------------- --------- -------- dbo 1 1 guest 2 2 INFORMATION_SCHEMA 3 3 sys 4 4 schema1 5 6 -- <= owner: user1 db_owner 16384 16384 ... (8) Create table emp_sch1 in schema1, and insert one row.
CREATE TABLE schema1.emp_sch1 (id INT, name VARCHAR(20));
INSERT INTO books.schema1.emp_sch1 
        VALUES (1, 'John');
(9) Query books.schema1.emp_sch1 to check the inserted row.
SELECT * 
    FROM books.schema1.emp_sch1;
id name -- ---- 1 John

(10) Create application role approle_sch1, with default schema schema1. Grant select on schema1 to approle_sch1.
USE Books;
GO
CREATE application role approle_sch1
            WITH password = 'p4sswd',
            default_schema = schema1;
            GRANT 
SELECT ON schema::schema1 TO approle_sch1;
(11) Query the current user (should return 'dbo')
SELECT USER_NAME() AS USER_NAME;
user_name --------- dbo (12) Switch context to approle_sch1 using sp_setapprole. Use cookies to save orginal context information.
DECLARE @cookie VARBINARY(8000);
EXEC sp_setapprole 'approle_sch1', 'p4sswd',
    @fCreateCookie = true,
    @cookie = @cookie OUTPUT;
SELECT USER_NAME() AS USER_NAME;
    -- should return approle_sch1
SELECT * 
    FROM emp_sch1;
    -- should return one row
EXEC sp_unsetapprole @cookie;
GO
user_name ---------- approle_sch1 id name -- ---- 1 John (13) Revert to the original context using sp_unsetapprole.
SELECT USER_NAME() AS USER_NAME;
user_name ---------- dbo



SQL Server: the data dictionary


Distribution of catalog information:
  • What is stored in the master database? What is kept in the user databases?
  • Base metadata tables are stored in the resource database. Metadata views are available in the master database (system-wide information) and in each user database (db specific and some system-wide information).
  • Metadata information can be obtained through catalog views, information schema views, system stored procedures and functions, as well as OLE DB schema rowsets and ODBC catalog funtions.

Obtaining metadata information can be done by using either:
  • (a) System Stored Procedures (T-SQL)
    • Catalog stored procedures
  • (b) System views:
    • Object Catalog views
    • Dynamic Management Views and Functions
    • Information Schema Views

Catalog Stored Procedures and Object Catalog views

sp_columns, sys.columns, and information_schema.columns
sp_columns
USE AdventureWorks2008r2;
GO
EXEC sp_columns department;
Returns 19 columns.. TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME ... TYPE_NAME PRECISION LENGTH -------------------- -------------- --------- ------------ ----------------- ---------- ------ AdventureWorks2008R2 HumanResources Department DepartmentID smallint identity 5 2 AdventureWorks2008R2 HumanResources Department Name Name 50 100 AdventureWorks2008R2 HumanResources Department GroupName Name 50 100 AdventureWorks2008R2 HumanResources Department ModifiedDate datetime 23 16 (or)
USE AdventureWorks2008r2;
GO
EXEC sp_columns department, @column_name= 'name';
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME ... TYPE_NAME PRECISION LENGTH -------------------- -------------- --------- ------------ ----------------- ---------- ------ AdventureWorks2008R2 HumanResources Department Name Name 50 100 (for column information, you may also use:)
EXEC sp_help 'HumanResources.department';

sys.columns
Returns a row for each column of an object that has columns (system, internal and user tables, views, table-valued sql function, inline table-valued sql function, table-valued assembly functions).
SELECT name, system_type_id, is_nullable
    FROM sys.columns
    WHERE OBJECT_NAME(OBJECT_ID)= 'Department';
name system_type_id is_nullable ------------ -------------- ------------ DepartmentID 52 0 Name 231 0 GroupName 231 0 ModifiedDate 61 0
SELECT o.name table_name, c.name column_name, t.name data_type,
            t.length length, t.prec precision
    FROM syscolumns c
            INNER JOIN sysobjects o ON o.id = c.id
            LEFT JOIN  systypes t   ON t.xtype = c.TYPE
    WHERE o.TYPE = 'U'
        AND o.name = 'Department'
    ORDER BY o.name, c.name;
table_name column_name data_type length precision ---------- ----------- --------- ------ -------- Department DepartmentID smallint 2 5 Department GroupName NULL NULL NULL Department ModifiedDate datetime 8 23 Department Name NULL NULL NULL

information_schema.columns
Information schema views provide a system table-independent view of the SQL Server metadata.
The idea is that it provides an interface that remains unchanged even after significant changes have been made to the underlying system tables.
USE AdventureWorks2008R2;
GO
SELECT TABLE_NAME, COLUMN_NAME,
            COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Department';
TABLE_NAME COLUMN_NAME COLUMN_ID ---------- ----------- -------- Department DepartmentID 1 Department Name 2 Department GroupName 3 Department ModifiedDate 4

sp_column_privileges and information_schema.column_privileges
sp_column_privileges
USE AdventureWorks2008R2;
GO
EXEC sp_column_privileges 'Employee',
    @table_owner='HumanResources',
    @column_name = 'SalariedFlag';
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE -------------------- -------------- ---------- ------------ ------- ------- --------- --------- AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo INSERT YES AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo REFERENCES YES AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo SELECT YES AdventureWorks2008R2 HumanResources Employee SalariedFlag dbo dbo UPDATE YES
information_schema.column_privileges

SELECT table_name, column_name,column_name, privilege_type, is_grantable
    FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
    WHERE TABLE_NAME = 'Department';

sp_special_columns
sp_special_columns
Returns the optimal set of columns that uniquely identify a row in the table.
Returns columns automatically updated when any value in the row is updated.
USE AdventureWorks2008R2;
GO
EXEC sp_special_columns 'Department',
    @table_owner='HumanResources';
SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE PSEUDO_COLUMN ----- -------------- --------- ----------------- --------- ------ ----- ------------ 1 DepartmentID 5 smallint identity 5 2 0 1

sp_stored_procedures, sp_sproc_columns, sys.procedures and information_schema.routines
sp_stored_procedures
USE AdventureWorks2008R2;
GO
EXEC sp_stored_procedures;
PROCEDURE_QUALIFIER PROCEDURE_OWNER PROCEDURE_NAME ... ------------------- -------------- ----------------- AdventureWorks2008R2 dbo ufnGetAccountingEndDate;0 ... AdventureWorks2008R2 HumanResources uspUpdateEmployeeHireInfo;1 .. AdventureWorks2008R2 sys sp_spaceused;1 (also)
USE AdventureWorks2008R2;
GO
EXECUTE sp_stored_procedures @sp_owner = N'dbo';

sp_sproc_columns
Returns column information for a stored procedure or user-defined function.
USE AdventureWorks2008R2;
GO
EXEC sp_sproc_columns @procedure_name = 'uspLogError';
PROCEDURE_QUALIFIER PROCEDURE_OWNER PROCEDURE_NAME COLUMN_NAME ... TYPE_NAME ... IS_NULLABLE ------------------- --------------- -------------- ----------- --------- ----------- AdventureWorks2008R2 dbo uspLogError;1 @RETURN_VALUE int NO AdventureWorks2008R2 dbo uspLogError;1 @ErrorLogID int YES

sys.procedures
Returning all stored procedures in a given database;
USE AdventureWorks2008R2;
GO
SELECT name procedure_name,
            SCHEMA_NAME(schema_id) AS schema_name,
            type_desc,
            create_date,
            modify_date
    FROM sys.procedures;

information_schema.routines
Returns the stored procedures and functions that can be accessed by the current user in the current database.
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_BODY, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.routines;

sp_databases, sys.databases
sp_databases
Lists databases in an instance.
USE MASTER;
GO
EXEC sp_databases;
DATABASE_NAME DATABASE_SIZE REMARKS -------------------- ------------ -------- AdventureWorks2008R2 186240 NULL master 5120 NULL model 1792 NULL msdb 20288 NULL MyDB 10240 NULL tempdb 8704 NULL
sys.databases
USE MASTER;
GO
SELECT name, create_date, compatibility_level, user_access_desc,
            state_desc, recovery_model_desc
    FROM sys.databases;
name create_date compat_level user_access_desc state_desc rec_model_desc ------------------- ----------------------- ------------ ---------------- ---------- -------------- master 2003-04-08 09:13:36.390 100 MULTI_USER ONLINE SIMPLE ... model 2003-04-08 09:13:36.390 100 MULTI_USER ONLINE FULL TESTSQL 2009-06-08 10:43:13.347 100 MULTI_USER ONLINE BULK_LOGGED AdventureWorks2008R2 2009-06-05 17:40:24.250 100 MULTI_USER ONLINE SIMPLE
Other database information views: sys.database_files, sys.database_mirroring, sys.database_recovery_status, sys.master_files


sp_statistics

sp_fkeys


sp_pkeys

sp_table_privileges

sp_server_info

sp_tables