Thursday, 23 November 2017

DBMS_FGA DBMS_FILE_TRANSFER DBMS_FLASHBACK

DBMS_FGA

The DBMS_FGA package provides fine-grained security functions.
You must have the AUDIT_ADMIN role or the EXECUTE privilege on the DBMS_FGA package to create audit policies.
To analyze and audit data, you must have the AUDIT_VIEWER role.

ADD_POLICY Procedure

This procedure creates an audit policy using the supplied predicate as the audit condition.
Syntax
DBMS_FGA.ADD_POLICY(
   object_schema      IN  VARCHAR2 DEFAULT NULL, 
   object_name        IN  VARCHAR2, 
   policy_name        IN  VARCHAR2, 
   audit_condition    IN  VARCHAR2 DEFAULT NULL, 
   audit_column       IN  VARCHAR2 DEFAULT NULL, 
   handler_schema     IN  VARCHAR2 DEFAULT NULL, 
   handler_module     IN  VARCHAR2 DEFAULT NULL, 
   enable             IN  BOOLEAN DEFAULT TRUE, 
   statement_types    IN  VARCHAR2 DEFAULT SELECT,
   audit_trail        IN  BINARY_INTEGER DEFAULT NULL,
   audit_column_opts  IN  BINARY_INTEGER DEFAULT ANY_COLUMNS,
   policy_owner       IN  VARCHAR2 DEFAULT NULL);

DBMS_FGA.ADD_POLICY (
   object_schema      =>  'scott',
   object_name        =>  'emp',
   policy_name        =>  'mypolicy1',
   audit_condition    =>  'sal < 100',
   audit_column       =>  'comm,sal',
   handler_schema     =>   NULL,
   handler_module     =>   NULL,
   enable             =>   TRUE,
   statement_types    =>  'INSERT, UPDATE',
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS,
   policy_owner       =>  'sec_admin);

DROP_POLICY Procedure

This procedure drops an audit policy.
Syntax
DBMS_FGA.DROP_POLICY(
   object_schema  IN  VARCHAR2, 
   object_name    IN  VARCHAR2, 
   policy_name    IN  VARCHAR2);


ENABLE_POLICY Procedure

This procedure enables an audit policy.
Syntax
DBMS_FGA.ENABLE_POLICY(
   object_schema  IN  VARCHAR2,
   object_name    IN  VARCHAR2,
   policy_name    IN  VARCHAR2,
   enable         IN  BOOLEAN);

DBMS_FGA.ENABLE_POLICY (
object_schema    =>  'scott',
object_name      =>  'emp',
policy_name      =>  'mypolicy1',
enable           =>   TRUE);

DISABLE_POLICY Procedure

This procedure disables an audit policy.
Syntax
DBMS_FGA.DISABLE_POLICY(
   object_schema  IN  VARCHAR2, 
   object_name    IN  VARCHAR2, 
   policy_name    IN  VARCHAR2);

DBMS_FILE_TRANSFER

The DBMS_FILE_TRANSFER package provides procedures to copy a binary file within a database or to transfer a binary file between databases
DBMS_FILE_TRANSFER supports online backup. You should therefore be careful in copying or transferring a file that is being modified by the database because this can result in an inconsistent file, and require recovery. To guarantee consistency, bring files offline when the database is in use.

COPY_FILE Procedure

This procedure reads a file from a source directory and creates a copy of it in a destination directory. The source and destination directories can both be in a local file system, or both be in an Automatic Storage Management (ASM) disk group, or between local file system and ASM with copying in either direction
DBMS_FILE_TRANSFER.COPY_FILE(
   source_directory_object       IN  VARCHAR2,
   source_file_name              IN  VARCHAR2,
   destination_directory_object  IN  VARCHAR2,
   destination_file_name         IN  VARCHAR2);

Examples
SQL> create directory DGROUP as '+diskgroup1/dbs/backup';


SQL>  BEGIN
   2    DBMS_FILE_TRANSFER.COPY_FILE('SOURCEDIR','t_xdbtmp.f', 'DGROUP',
                                   't_xdbtmp.f');
   3  END;
   4  /

GET_FILE Procedure

This procedure contacts a remote database to read a remote file and then creates a copy of the file in the local file system or ASM. The file that is copied is the source file, and the new file that results from the copy is the destination file. The destination file is not closed until the procedure completes successfully.
Syntax
DBMS_FILE_TRANSFER.GET_FILE
   source_directory_object      IN  VARCHAR2,    
   source_file_name             IN  VARCHAR2,  
   source_database              IN  VARCHAR2,  
   destination_directory_object IN  VARCHAR2,
   destination_file_name        IN  VARCHAR2);

Examples
CREATE OR REPLACE DIRECTORY df AS '+datafile' ;
GRANT WRITE ON DIRECTORY df TO "user";
CREATE DIRECTORY DSK_FILES AS ''^t_work^'';
GRANT WRITE ON DIRECTORY dsk_files TO "user";

-- asumes that dbs2 link has been created and we are connected to the instance.
-- dbs2 could be a loopback or point to another instance.

BEGIN
-- asm file to an os file
-- get an asm file from dbs1.asm/a1 to dbs2.^t_work^/oa5.dat
  DBMS_FILE_TRANSFER.GET_FILE ( 'df' , 'a1' , 'dbs1', 'dsk_files' , 'oa5.dat' );

-- os file to an os file
-- get an os file from dbs1.^t_work^/a2.dat to dbs2.^t_work^/a2back.dat
  DBMS_FILE_TRANSFER.GET_FILE ( 'dsk_files' , 'a2.dat' , 'dbs1', 'dsk_files' , 'a2back.dat' );

END ;

PUT_FILE Procedure

This procedure reads a local file or ASM and contacts a remote database to create a copy of the file in the remote file system.
The file that is copied is the source file, and the new file that results from the copy is the destination file. The destination file is not closed until the procedure completes successfully.
Syntax
DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object       IN  VARCHAR2,   
   source_file_name              IN  VARCHAR2,
   destination_directory_object  IN  VARCHAR2,
   destination_file_name         IN  VARCHAR2,  
   destination_database          IN  VARCHAR2);

Examples
CREATE OR REPLACE DIRECTORY df AS '+datafile' ;
GRANT WRITE ON DIRECTORY df TO "user";
CREATE OR REPLACE DIRECTORY ft1 AS '+datafile/ft1' ;
GRANT READ,WRITE ON DIRECTORY ft1 TO "user";
CREATE OR REPLACE DIRECTORY ft1_1 AS '+datafile/ft1/ft1_1' ;

CONNECT user;
Enter password: password

-- - put a1.dat to a4.dat (using dbs2 dblink)
-- - level 2 sub dir to parent dir
-- - user has read privs on ft1_1 at dbs1 and write on df in dbs2
BEGIN
 DBMS_FILE_TRANSFER.PUT_FILE ( 'ft1_1' , 'a2.dat' , 'df' , 'a4.dat' ,
                               'dbs2' ) ;
END ;

DBMS_FLASHBACK

Using DBMS_FLASHBACK, you can flash back to a version of the database at a specified time or a specified system change number (SCN).
You may want to use DBMS_FLASHBACK for the following reasons:
·       Self-service repair: If you accidentally delete rows from a table, you can recover the deleted rows.
·       Packaged applications such as email and voicemail: You can use Flashback to restore deleted email by re-inserting the deleted message into the current message box.
·       Decision support system (DSS) and online analytical processing (OLAP) applications: You can perform data analysis or data modeling to track seasonal demand.

Examples

The following example illustrates how Flashback can be used when the deletion of a senior employee triggers the deletion of all the personnel reporting to him. Using the Flashback feature, you can recover and re-insert the missing employees.
DROP TABLE employee;
DROP TABLE keep_scn;
 
REM -- Keep_scn is a temporary table to store scns that we are interested in
 
CREATE TABLE keep_scn (scn number); 
SET ECHO ON 
CREATE TABLE employee ( 
   employee_no   number(5) PRIMARY KEY, 
   employee_name varchar2(20), 
   employee_mgr  number(5) 
      CONSTRAINT mgr_fkey REFERENCES EMPLOYEE ON DELETE CASCADE, 
   salary        number, 
   hiredate      date 
); 
 
REM -- Populate the company with employees
INSERT INTO employee VALUES (1, 'John Doe', null, 1000000, '5-jul-81'); 
INSERT INTO employee VALUES (10, 'Joe Johnson', 1, 500000, '12-aug-84'); 
INSERT INTO employee VALUES (20, 'Susie Tiger', 10, 250000, '13-dec-90'); 
INSERT INTO employee VALUES (100, 'Scott Tiger', 20, 200000, '3-feb-86'); 
INSERT INTO employee VALUES (200, 'Charles Smith', 100, 150000, '22-mar-88'); 
INSERT INTO employee VALUES (210, 'Jane Johnson', 100, 100000, '11-apr-87'); 
INSERT INTO employee VALUES (220, 'Nancy Doe', 100, 100000, '18-sep-93'); 
INSERT INTO employee VALUES (300, 'Gary Smith', 210, 75000, '4-nov-96'); 
INSERT INTO employee VALUES (310, 'Bob Smith', 210, 65000, '3-may-95'); 
COMMIT; 
 
REM -- Show the entire org
SELECT lpad(' ', 2*(level-1)) || employee_name Name 
FROM employee 
CONNECT BY PRIOR employee_no = employee_mgr 
START WITH employee_no = 1 
ORDER BY LEVEL; 
 
REM -- Sleep for a short time (approximately 10 to 20  seconds) to avoid 
REM -- querying close to table creation
 
EXECUTE DBMS_LOCK.SLEEP(10);
 
REM -- Store this snapshot for later access through Flashback
DECLARE 
I NUMBER; 
BEGIN 
I := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; 
INSERT INTO keep_scn VALUES (I); 
COMMIT; 
END;
/
 
REM -- Scott decides to retire but the transaction is done incorrectly
DELETE FROM EMPLOYEE WHERE employee_name = 'Scott Tiger'; 
COMMIT; 
 
REM -- notice that all of scott's employees are gone 
SELECT lpad(' ', 2*(level-1)) || employee_name Name 
FROM EMPLOYEE 
CONNECT BY PRIOR employee_no = employee_mgr 
START WITH employee_no = 1 
ORDER BY LEVEL; 
 
REM -- Flashback to see Scott's organization
DECLARE 
   restore_scn number; 
BEGIN 
   SELECT  scn INTO restore_scn FROM keep_scn; 
   DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (restore_scn); 
END; 
/ 
 
REM -- Show Scott's org.
SELECT lpad(' ', 2*(level-1)) || employee_name Name 
FROM employee 
CONNECT BY PRIOR employee_no = employee_mgr 
START WITH employee_no = 
   (SELECT employee_no FROM employee WHERE employee_name = 'Scott Tiger') 
ORDER BY LEVEL; 
 
REM -- Restore scott's organization.
DECLARE 
   scotts_emp NUMBER; 
   scotts_mgr NUMBER; 
   CURSOR c1 IS 
      SELECT employee_no, employee_name, employee_mgr, salary, hiredate 
      FROM employee 
      CONNECT BY PRIOR employee_no = employee_mgr 
      START WITH employee_no = 
         (SELECT employee_no FROM employee WHERE employee_name = 'Scott Tiger'); 
   c1_rec c1 % ROWTYPE; 
BEGIN 
   SELECT employee_no, employee_mgr INTO scotts_emp, scotts_mgr FROM employee 
   WHERE employee_name = 'Scott Tiger'; 
   /* Open c1 in flashback mode */
   OPEN c1; 
   /* Disable Flashback */
   DBMS_FLASHBACK.DISABLE; 
 LOOP 
   FETCH c1 INTO c1_rec; 
   EXIT WHEN c1%NOTFOUND; 
   /*
     Note that all the DML operations inside the loop are performed
     with Flashback disabled
   */
   IF (c1_rec.employee_mgr = scotts_emp) then 
      INSERT INTO employee VALUES (c1_rec.employee_no, 
         c1_rec.employee_name, 
         scotts_mgr, 
         c1_rec.salary, 
         c1_rec.hiredate); 
   ELSE 
   IF (c1_rec.employee_no != scotts_emp) THEN 
   INSERT INTO employee VALUES (c1_rec.employee_no, 
         c1_rec.employee_name, 
         c1_rec.employee_mgr, 
         c1_rec.salary, 
         c1_rec.hiredate); 
      END IF; 
    END IF; 
 END LOOP; 
END; 
/ 
 
REM -- Show the restored organization.
select lpad(' ', 2*(level-1)) || employee_name Name 
FROM employee 
CONNECT BY PRIOR employee_no = employee_mgr 
START WITH employee_no = 1
ORDER BY LEVEL; 
at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)