sábado, 16 de septiembre de 2017

PL/SQL White Lists

PL/SQL White Lists Using the ACCESSIBLE BY Clause in Oracle Database 12c Release 1 (12.1)

The ACCESSIBLE BY clause can be added to packages, procedures, functions and types to specify which objects are able to reference the PL/SQL object directly. This ability to create so called white lists is a new feature introduced in Oracle Database 12c Release 1 (12.1) to allow you to add an extra layer of security to your PL/SQL objects.

Setup

The following examples will use the two database users defined below.
CONN sys/password@pdb1 AS SYSDBA

CREATE USER test1 IDENTIFIED BY test1;
GRANT CREATE SESSION, CREATE PROCEDURE, CREATE TABLE TO test1;

CREATE USER test2 IDENTIFIED BY test2;
GRANT CREATE SESSION, CREATE PROCEDURE TO test2;

Basic Usage

The procedure below includes an ACCESSIBLE BY clause, indicating it can only be called by an object called calling_proc.
CONN test1/test1@pdb1

CREATE OR REPLACE PROCEDURE protected_proc
  ACCESSIBLE BY (calling_proc)
AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : protected_proc');
END;
/

Procedure created.

SQL>
Notice we have not created the calling_proc procedure yet, but no error is produced. This is because the objects referenced by the ACCESSIBLE BY clause are not checked at compile time. Only the syntax of the clause is checked.
We can create the calling_proc procedure and use it to call the protected_proc procedure.
CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : calling_proc');
  protected_proc;
END;
/

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC calling_proc;
TEST1 : calling_proc
TEST1 : protected_proc

PL/SQL procedure successfully completed.

SQL>
If we attempt to call the protected_proc procedure directly, we get an error.
SQL> EXEC protected_proc;
BEGIN protected_proc; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object PROTECTED_PROC
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>
If we try to create a new object that references the protected_proc procedure and the new object is not in the white list, we get a compilation error.
CREATE OR REPLACE PROCEDURE another_calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : another_calling_proc');
  protected_proc;
END;
/
Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE ANOTHER_CALLING_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3  PL/SQL: Statement ignored
4/3  PLS-00904: insufficient privilege to access object PROTECTED_PROC
SQL>

Cross-Schema Usage

If no schema is explicitly mentioned in the white list, it is assumed the object listed is in the same schema as the object with the ACCESSIBLE BY clause. For example, if we switch to another user and create a procedure called calling_proc that accesses test1.protected_proc, it will not work.
CONN test1/test1@pdb1
GRANT EXECUTE ON protected_proc TO test2;

Grant succeeded.

SQL> 


CONN test2/test2@pdb1

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST2 : calling_proc');
  test1.protected_proc;
END;
/

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE CALLING_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3  PL/SQL: Statement ignored
4/3  PLS-00904: insufficient privilege to access object PROTECTED_PROC
SQL>
For this to be successful, we must add the reference into the white list using the fully qualified object name. The example below includes an object reference without a schema prefix, signifying current schema (TEST1) and one with an explicit schema reference.
CONN test1/test1@pdb1

CREATE OR REPLACE PROCEDURE protected_proc
  ACCESSIBLE BY (calling_proc, test2.calling_proc)
AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : protected_proc');
END;
/

Procedure created.

SQL>
Now, we can reference and execute the protected_proc procedure from the other user.
CONN test2/test2@pdb1

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST2 : calling_proc');
  test1.protected_proc;
END;
/

Procedure created.

SQL>


SQL> SET SERVEROUTPUT ON
SQL> EXEC calling_proc;
TEST2 : calling_proc
TEST1 : protected_proc

PL/SQL procedure successfully completed.

SQL>

Unit Type

The examples so far have not specified the unit type (object type) when defining the white list references, which means any compatible object type with the correct name in the correct schema will pass the white list test. If we want to make the test more stringent, we can specify not just the name, but the unit type also. Some examples are shown below.
ACCESSIBLE BY (PACKAGE calling_pkg)
ACCESSIBLE BY (PROCEDURE calling_proc)
ACCESSIBLE BY (FUNCTION calling_func)
ACCESSIBLE BY (TYPE calling_type)
ACCESSIBLE BY (TRIGGER calling_trg)
There seem to be some discrepancies about which unit types can access PL/SQL objects that use the ACCESSIBLE BY clause. The New Features Guide suggests tables, indexes and views can also be referenced in the white list, but the unit types TABLE, INDEX and VIEW are not allowed. The following example shows that function-based indexes and views do not work against a function using the ACCESSIBLE BY clause.
CONN test1/test1@pdb1

CREATE OR REPLACE FUNCTION protected_func (id IN NUMBER)
  RETURN NUMBER
  ACCESSIBLE BY (t1_fbi, t1_vw)
AS
BEGIN
  RETURN id;
END;
/

CREATE TABLE t1 (
  id NUMBER
);


SQL> CREATE INDEX t1_fbi ON t1(protected_func(id));
CREATE INDEX t1_fbi ON t1(protected_func(id))
                          *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC

SQL>


-- Views are created normally, but fail when run.
CREATE OR REPLACE VIEW t1_vw AS
SELECT protected_func(id) AS id_vw
FROM   t1;

View created.

SQL> SELECT * FROM t1_vw;
SELECT * FROM t1_vw
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC

SQL>

Package White Lists

In Oracle 12.1 the ACCESSIBLE BY clause is only valid at the top-level of the package specification. It can not be applied to individual packaged procedures, functions or types within the package. The following example shows a white list applied to a package specification.
CREATE OR REPLACE PACKAGE protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc;
END;
/

Package created.

SQL>
Trying to apply the white list to the packaged procedure, rather than the top-level package, results in an error.
CREATE OR REPLACE PACKAGE protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc);
END;
/

Warning: Package created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE PROTECTED_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0  PLS-00157: Only schema-level programs allow ACCESSIBLE BY
SQL>
The ACCESSIBLE CLAUSE is not valid in the package body. It can only be defined in the package specification.
CREATE OR REPLACE PACKAGE protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc;
END;
/

Package created.

SQL>


CREATE OR REPLACE PACKAGE BODY protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc AS
  BEGIN
    NULL;
  END;
END;
/

Warning: Package Body created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE BODY PROTECTED_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3  PLS-00103: Encountered the symbol "ACCESSIBLE" when expecting one
  of the following:
  is as compress compiled wrapped

SQL>

Package White Lists (12.2 Update)

In Oracle database 12.1 it was only possible to use a white list for a whole package. In Oracle 12.2 this limitation is no longer present, making the concept of white lists for packages much more granular. It is now possible to white list individual subprograms or a package.
CREATE OR REPLACE PACKAGE protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc);

  FUNCTION protected_func RETURN NUMBER
    ACCESSIBLE BY (PROCEDURE calling_func);
END;
/

CREATE OR REPLACE PACKAGE BODY protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc)
  AS
  BEGIN
    NULL;
  END;

  FUNCTION protected_func RETURN NUMBER
    ACCESSIBLE BY (PROCEDURE calling_func)
  AS
  BEGIN
    RETURN NULL;
  END;
END;
/

sys_refcursor

You can do this:
PROCEDURE abc( p_cursor IN SYS_REFCURSOR) IS
   v_sol_id blt.sol_id%TYPE;
   v_bill_id blt.bill_id%TYPE;
   v_bank_id blt.bank_id%TYPE;
BEGIN
   LOOP
      FETCH p_cursor INTO v_sol_id, v_bill_id, v_bank_id;
      EXIT WHEN p_cursor%NOTFOUND;
      ...
   END LOOP;
END;
Then use it:
DECLARE
   v_cursor SYS_REFCURSOR;
BEGIN
   OPEN v_cursor FOR
      SELECT  BLT.sol_id,
              BLT.bill_id,
              BLT.bank_id
      FROM BLT;
   abc (v_cursor);
   CLOSE v_cursor;
END;
CREATE OR REPLACE FUNCTION func_refcur(
    ip_dept_id NUMBER)
  RETURN sys_refcursor
IS
  l_rc_var1 sys_refcursor;
BEGIN
  OPEN l_rc_var1 FOR SELECT * FROM employees WHERE department_id=ip_dept_id;
  RETURN l_rc_var1;
END;
Un cursor ref Strong siempre devuelve un tipo conocido, normalmente de un objeto TYPE declarado. El compilador puede encontrar problemas en un bloque PL / SQL comparando los tipos devueltos a cómo se utilizan.
Un cursor de referencia debil tiene un tipo de retorno que depende de la instrucción SQL que ejecuta, es decir, sólo una vez que se abre el cursor es el tipo conocido (en tiempo de ejecución). El compilador no puede determinar los tipos hasta que se ejecuta, por lo que se debe tener cuidado para asegurarse de que el conjunto de resultados del cursor se gestiona correctamente para evitar errores de tiempo de ejecución.

viernes, 15 de septiembre de 2017

ESTADISTICAS SCRIPT DBMS_APPLICATION_INFO

CREATE OR REPLACE PROCEDURE SP_ESTADISTICAS_TABLAS(esquema varchar2) AS
sentencia VARCHAR2(500);
modulo VARCHAR2(64);
accion VARCHAR2(64);
--Cursor con todas las tablas del esquema
CURSOR get_Tablas IS
  SELECT owner,table_name
  FROM dba_tables
  WHERE owner=esquema;
BEGIN
modulo := 'Administracion DBA';
accion := 'Estadisticas Tablas';
dbms_application_info.set_module(modulo, accion);
  --se recorren las tablas del esquema
  FOR i IN get_tablas loop
    --se prepara la sentencia SQL en la cual se hace el conteo de los registros y se inserta en la tabla de auditoria(aud_registros)
    sentencia:=' BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || esquema || ''', TABNAME => ''' || i.table_name || ''' ); END;';
    EXECUTE IMMEDIATE(sentencia);
    dbms_application_info.read_module(modulo, accion);
  END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE SP_ESTADISTICAS_INDICES(esquema varchar2) AS
sentencia VARCHAR2(500);
modulo VARCHAR2(64);
accion VARCHAR2(64);
--Cursor con todas las tablas del esquema
CURSOR get_indices IS
  SELECT owner,index_name
  FROM dba_indexes
  WHERE owner=esquema;
BEGIN
modulo := 'Administracion DBA';
accion := 'Estadisticas Indices';
dbms_application_info.set_module(modulo, accion);
  --se recorren los indices del esquema
  FOR i IN get_indices loop
    sentencia:=' BEGIN DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => ''' || esquema || ''', INDNAME => ''' || i.index_name || ''' ); END;';
    EXECUTE IMMEDIATE(sentencia);
    dbms_application_info.read_module(modulo, accion);
  END LOOP;
END;
/

jueves, 14 de septiembre de 2017

Como Ver Set de Caracteres Oracle Database

How to check character set in Oracle

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

SELECT * FROM NLS_DATABASE_PARAMETERS


lunes, 11 de septiembre de 2017

DBMS_SERVER_ALERT

Tablespace Thresholds and Alerts (DBMS_SERVER_ALERT)

This article describes how to set tablespace thresholds using the DBMS_SERVER_ALERTpackage as an early warning mechanism for space issues.

Introduction

Oracle allows you to set tablespace thresholds using the DBMS_SERVER_ALERT package as an early warning mechanism for space issues. These can be set database-wide, or for individual tablespaces. When the threshold is crossed warnings are sent by the Enterprise Manager (DB Control, Grid Control or Cloud Control).
Setting the OBJECT_NAME parameter to NULL sets the default threshold for all tablespace in the database. Setting the OBJECT_NAMEparameter to a tablespace name sets the threshold for the specified tablespace and overrides any default setting.
There are two types of tablespace thresholds that can be set.
  • TABLESPACE_PCT_FULL : Percent full. When the warning or critical threshold based on percent full is crossed a notification occurs.
  • TABLESPACE_BYT_FREE : Free Space Remaining (KB). The constant name implies the value is in bytes, but it is specified in KB. When the warning or critical threshold based on remaining free space is crossed a notification occurs. When you view these thresholds in different tools the units may vary, for example Cloud Control displays and sets these values in MB.
The thresholds are set using a value and an operator.
  • OPERATOR_LE : Less than or equal.
  • OPERATOR_GE : Greater than or equal.

Setting Thresholds

 Make a note of your existing thresholds before changing them, so you know what to set them back to.
The following examples show how to set the different types of alerts.
BEGIN
  -- Database-wide KB free threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);
    
  -- Database-wide percent full threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '85',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '97',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);

  -- Tablespace-specific KB free threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
    
  -- Tablespace-specific percent full threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '90',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '98',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
END;
/
Setting the warning and critical levels to '0' disables the notification.

Displaying Thresholds

The threshold settings can be displayed using the DBA_THRESHOLDS view.
SET LINESIZE 200

COLUMN tablespace_name FORMAT A30
COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT object_name AS tablespace_name,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_thresholds
WHERE  object_type = 'TABLESPACE'
ORDER BY object_name;

TABLESPACE_NAME                METRICS_NAME                   WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
------------------------------ ------------------------------ ------------ ------------------------------ ------------ ---------------
TEMP                           Tablespace Space Usage         DO NOT CHECK    DO_NOT_CHECK 0
UNDOTBS1                       Tablespace Space Usage         DO NOT CHECK    DO_NOT_CHECK 0
UNDOTBS2                       Tablespace Space Usage         DO NOT CHECK    DO_NOT_CHECK 0
USERS                          Tablespace Bytes Space Usage   LE           1024000                        LE           102400
USERS                          Tablespace Space Usage         GE           90                             GE           98
                               Tablespace Space Usage         GE           85                             GE           97
                               Tablespace Bytes Space Usage   LE           1024000                        LE           102400

7 rows selected.

SQL>
Oracle 11g Release 2 introduced the DBA_TABLESPACE_THRESHOLDS view, which displays the settings for all tablespaces, showing the default where no tablespace-specific threshold is set.

SET LINESIZE 200

COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT tablespace_name,
       contents,
       extent_management,
       threshold_type,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_tablespace_thresholds
ORDER BY tablespace_name;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN THRESHOL METRICS_NAME                   WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
------------------------------ --------- ---------- -------- ------------------------------ ------------ ------------------------------ ------------ ---------------
EXAMPLE                        PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
EXAMPLE                        PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSAUX                         PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
SYSAUX                         PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSTEM                         PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSTEM                         PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
TEMP                           TEMPORARY LOCAL      EXPLICIT Tablespace Space Usage         DO NOT CHECK    DO NOT CHECK 0
UNDOTBS1                       UNDO      LOCAL      EXPLICIT Tablespace Space Usage         DO NOT CHECK    DO NOT CHECK 0
USERS                          PERMANENT LOCAL      EXPLICIT Tablespace Bytes Space Usage   LE           1024000                        LE           102400
USERS                          PERMANENT LOCAL      EXPLICIT Tablespace Space Usage         GE           90                             GE           98

10 rows selected.

SQL>

sábado, 9 de septiembre de 2017

TRIGGER DDL_LOG

CREATE TABLE ddl_log (
operation   VARCHAR2(30),
obj_owner   VARCHAR2(30),
object_name VARCHAR2(30),
sql_text    VARCHAR2(64),
attempt_by  VARCHAR2(30),
attempt_dt  DATE);



CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER OR DROP
ON DATABASE | SCHEMA

DECLARE
 oper hr.ddl_log.operation%TYPE;
 sql_text ora_name_list_t;
 i        PLS_INTEGER;
BEGIN
  SELECT 
ora_sysevent
  INTO 
oper
  FROM DUAL;

  i := sql_txt(sql_text);

  IF 
oper IN ('CREATE', 'DROP') THEN
    INSERT INTO hr.ddl_log
    SELECT 
ora_syseventora_dict_obj_owner,
    
ora_dict_obj_name, sql_text(1), USER, SYSDATE
    FROM DUAL;
  ELSIF 
oper = 'ALTERTHEN
    INSERT INTO hr.ddl_log
    SELECT 
ora_syseventora_dict_obj_owner,
    
ora_dict_obj_name, sql_text(1), USER, SYSDATE
    FROM sys.gv_$sqltext
    WHERE UPPER(sql_text) LIKE 'ALTER%'
    AND UPPER(sql_text) LIKE '%NEW_TABLE%';
  END IF;
END ddl_trigger;
/

TRIGGER TRUNCATE

create table event_log 
(log_date varchar2(36), 
event varchar2(36), 
username varchar2(36), 
owner varchar2(36), 
object_name varchar2(36), 
object_type varchar2(36), 
dbname varchar2(36) 
); 

create or replace trigger truncate_trg 
before truncate on database 
begin
insert into event_log 
values(sysdate, 
ora_sysevent, 
ora_login_user, 
ora_dict_obj_owner, 
ora_dict_obj_name, 
ora_dict_obj_type, 
ora_database_name) 
end; 

TRIIGER AFTER SERVERERROR


CREATE TABLE servererror_log (
    error_datetime  TIMESTAMP,
    error_user      VARCHAR2(30),
    db_name         VARCHAR2(9),
    error_stack     VARCHAR2(2000),
    captured_sql    VARCHAR2(1000));
/
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
 captured_sql VARCHAR2(1000);
BEGIN
  SELECT q.sql_text
  INTO captured_sql
  FROM gv$sql q, gv$sql_cursor c, gv$session s
  WHERE s.audsid = audsid
  AND s.prev_sql_addr = q.address
  AND q.address = c.parent_handle;

  INSERT INTO servererror_log
  (error_datetime, error_user, db_name,
   error_stack, captured_sql)
  VALUES
  (systimestamp, sys.login_user, sys.database_name,
  dbms_utility.format_error_stack, captured_sql);
END log_server_errors;