Advanced Queuing

Oracle Database 12c:
Advanced Queuing

Tested on:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

References:
http://www.oracle.com/technetwork/database/oracleadvancedqueuingwp-2524215.pdf
https://docs.oracle.com/database/121/ADQUE/jm_exmpl.htm#ADQUE1600

1.) Configure AQ Administrator account
The AQ Administrator user (‘aq_admin’) creates and owns the queuing infrastructure. The role 
AQ_ADMINISTATOR_ROLE that allows for the creation and administration of the queuing infrastructure needs 
to be granted to the ‘aq_admin’ user:

CREATE USER aq_admin IDENTIFIED BY aq_admin
DEFAULT TABLESPACE users 
TEMPORARY TABLESPACE temp; 

ALTER USER aq_admin QUOTA UNLIMITED ON users;

grant connect, resource to aq_admin;
GRANT aq_administrator_role TO aq_admin; 
GRANT connect TO aq_admin

GRANT create type TO aq_admin;

2.) Set up a simple message 
The following steps must be executed as the aq_admin user.

conn aq_admin/aq_admin
 
CREATE TYPE tst_message_type AS OBJECT 
(
id number,
name varchar2(40),
value number,
obs varchar2(4000)
);

3.) Create Queue Table and Queue 
Queues are implemented using a queue table that can hold multiple queues with the same payload type. 
The following creates a queue table ‘TST_QUEUE_TB’ and a queue ‘TST_QUEUE’. 

EXEC DBMS_AQADM.CREATE_QUEUE_TABLE 
(queue_table => 'aq_admin.tst_queue_tb',
queue_payload_type =>'aq_admin.tst_message_type'); 

exec DBMS_AQADM.CREATE_QUEUE 
(queue_name => 'TST_QUEUE', 
queue_table => 'aq_admin.tst_queue_tb',
queue_type => DBMS_AQADM.NORMAL_QUEUE,
max_retries => 0, 
retry_delay => 0,
retention_time => 1209600, 
dependency_tracking => FALSE, 
comment => 'Test AQ',
auto_commit => FALSE);

4.) Start Queue

exec DBMS_AQADM.START_QUEUE('TST_QUEUE');

5.) Configure AQ user account 

conn / as sysdba

CREATE USER aq_user IDENTIFIED BY aq_user
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT aq_user_role TO aq_user; 
grant connect, resource to aq_user;
grant select on aq_admin.tst_queue_tb to aq_user;
GRANT EXECUTE ON aq_admin.tst_message_type TO aq_user;
exec DBMS_AQADM.GRANT_QUEUE_PRIVILEGE( privilege =>'ALL', queue_name => 'aq_admin.tst_queue',grantee => 'aq_user', grant_option => FALSE);

6.) ENQUEUE MESSAGE
 
CONNECT aq_user/aq_user

DECLARE
  l_enqueue_options     DBMS_AQ.enqueue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_event_msg           AQ_ADMIN.tst_message_type;
BEGIN
  l_event_msg := AQ_ADMIN.tst_message_type(1,'TEST 1',1000,'OBS 1');

  DBMS_AQ.enqueue(queue_name          => 'aq_admin.tst_queue',        
                  enqueue_options     => l_enqueue_options,     
                  message_properties  => l_message_properties,   
                  payload             => l_event_msg,             
                  msgid               => l_message_handle);

  COMMIT;
END;
/


7.) DEQUEUE MESSAGE

CONNECT aq_user/aq_user

SET SERVEROUTPUT ON

DECLARE
  l_dequeue_options     DBMS_AQ.dequeue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_event_msg           AQ_ADMIN.tst_message_type;
BEGIN
  DBMS_AQ.dequeue(queue_name          => 'aq_admin.tst_queue',
                  dequeue_options     => l_dequeue_options,
                  message_properties  => l_message_properties,
                  payload             => l_event_msg,
                  msgid               => l_message_handle);

  DBMS_OUTPUT.put_line ('id          : ' || l_event_msg.id);
  DBMS_OUTPUT.put_line ('Name: ' || l_event_msg.name);
  DBMS_OUTPUT.put_line ('Value   : ' || l_event_msg.value);
  DBMS_OUTPUT.put_line ('Obs   : ' || l_event_msg.obs);
  
  COMMIT;
END;
/

id          : 1
Name: TEST 1
Value   : 1000
Obs   : OBS 1

PL/SQL procedure successfully completed.

8.) Test Procedure:

DECLARE
  l_enqueue_options     DBMS_AQ.enqueue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_event_msg           AQ_ADMIN.tst_message_type;
  l_dequeue_options     DBMS_AQ.dequeue_options_t;
  
  i number;
  qtd_msgs number;
  
BEGIN
   --- enqueue
  
  for i in 1..10 loop
  
      l_event_msg := AQ_ADMIN.tst_message_type(i,'TEST'||to_char(i),i*100,'OBS'||to_char(i));

      DBMS_AQ.enqueue(queue_name          => 'aq_admin.tst_queue',        
                      enqueue_options     => l_enqueue_options,     
                      message_properties  => l_message_properties,   
                      payload             => l_event_msg,             
                      msgid               => l_message_handle);

    
  end loop;
  commit;
  --- dequeue
   select count(*) into qtd_msgs from aq_admin.tst_queue_tb where state=0;
  
  for i in 1..qtd_msgs loop
  
       DBMS_AQ.dequeue(queue_name          => 'aq_admin.tst_queue',
                      dequeue_options     => l_dequeue_options,
                      message_properties  => l_message_properties,
                      payload             => l_event_msg,
                      msgid               => l_message_handle);

      DBMS_OUTPUT.put_line ('id          : ' || l_event_msg.id);
      DBMS_OUTPUT.put_line ('Name: ' || l_event_msg.name);
      DBMS_OUTPUT.put_line ('Value   : ' || l_event_msg.value);
      DBMS_OUTPUT.put_line ('Obs   : ' || l_event_msg.obs);
  
    end loop;  
  commit;
END;
/
Advertisements

DBMS_PRIVILEGE_CAPTURE

The DBMS_PRIVILEGE_CAPTURE package provides an interface 
to database privilege analysis.

References:
http://docs.oracle.com/database/121/DVADM/priv_analysis.htm#DVADM591
http://docs.oracle.com/database/121/ARPLS/d_priv_prof.htm#ARPLS74328
https://oracle-base.com/articles/12c/capture-privilege-usage-12cr1

Tested on:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

1.) Create user to be analyzed:

create user checkprivs identified by test;
grant dba to checkprivs;
grant resource to checkprivs;
grant connect to checkprivs;

2.) Creating the capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'CHECK_PRIVS',
    type        => DBMS_PRIVILEGE_CAPTURE.g_role_and_context,
    roles       => role_name_list('DBA', 'RESOURCE'),
    condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''CHECKPRIVS'''
  );

  DBMS_PRIVILEGE_CAPTURE.enable_capture(
    name        => 'CHECK_PRIVS'
  );
END;
/

3.) Some commands:

conn checkprivs/test;

create table check1 
(num number);

insert into check1 values (1);
commit;

select * from v$database;

truncate table check1;

insert into check1 values (2);
commit;

alter table check1 add (dt date);

update check1 set dt=sysdate;

commit;

4.) Stopping the capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.disable_capture(
    name        => 'CHECK_PRIVS'
  );

  DBMS_PRIVILEGE_CAPTURE.generate_result(
    name        => 'CHECK_PRIVS'
  );
END;
/


5.) Checking the Results:
-- Thanks to ORACLE-BASE for the queries:

COLUMN username FORMAT A20
COLUMN sys_priv FORMAT A20

SELECT username, sys_priv
FROM   dba_used_sysprivs
WHERE  capture = 'CHECK_PRIVS'
ORDER BY username, sys_priv;

USERNAME             SYS_PRIV
-------------------- --------------
CHECKPRIVS           CREATE SESSION
CHECKPRIVS           CREATE TABLE

COLUMN username FORMAT A20
COLUMN used_role FORMAT A30
COLUMN sys_priv FORMAT A20
COLUMN path FORMAT A50
SET LINESIZE 200

SELECT username, sys_priv, used_role, path
FROM   dba_used_sysprivs_path
WHERE  capture = 'CHECK_PRIVS'
ORDER BY username, sys_priv;

USERNAME             SYS_PRIV             USED_ROLE                      PATH
-------------------- -------------------- ------------------------------ --------------------------------------------------
CHECKPRIVS           CREATE SESSION       EM_EXPRESS_BASIC               GRANT_PATH('CHECKPRIVS', 'DBA', 'EM_EXPRESS_ALL',
                                                                         'EM_EXPRESS_BASIC')

CHECKPRIVS           CREATE TABLE         DATAPUMP_EXP_FULL_DATABASE     GRANT_PATH('CHECKPRIVS', 'DBA', 'DATAPUMP_EXP_FULL
                                                                         _DATABASE')

CHECKPRIVS           CREATE TABLE         DATAPUMP_EXP_FULL_DATABASE     GRANT_PATH('CHECKPRIVS', 'DBA', 'DATAPUMP_EXP_FULL
                                                                         _DATABASE', 'EXP_FULL_DATABASE')
                                                                         
                                                                         COLUMN username FORMAT A20
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A20
COLUMN object_type FORMAT A11

SELECT username, obj_priv, object_owner, object_name, object_type 
FROM   dba_used_objprivs
WHERE  capture = 'CHECK_PRIVS';

USERNAME             OBJ_PRIV OBJECT_OWNER    OBJECT_NAME          OBJECT_TYPE
-------------------- -------- --------------- -------------------- -----------
CHECKPRIVS           SELECT   SYS             V_$DATABASE          VIEW


COLUMN username FORMAT A20
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A20
COLUMN used_role FORMAT A20
COLUMN path FORMAT A30
SET LINESIZE 200

SELECT username, obj_priv, object_owner, object_name, used_role, path 
FROM   dba_used_objprivs_path
WHERE  capture = 'CHECK_PRIVS';

USERNAME             OBJ_PRIV OBJECT_OWNER    OBJECT_NAME          USED_ROLE            PATH
-------------------- -------- --------------- -------------------- -------------------- ------------------------------
CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'IMP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'DATAPUMP_IMP_FULL_DATABASE'
                                                                                        , 'EXP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'EM_EXPRESS_ALL', 'EM_EXPRES
                                                                                        S_BASIC', 'SELECT_CATALOG_ROLE
                                                                                        ')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'DATAPUMP_EXP_FULL_DATABASE'
                                                                                        , 'EXP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'SELECT_CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'EXP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'DATAPUMP_IMP_FULL_DATABASE'
                                                                                        , 'IMP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')                                                                       

6.) Drop Capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.drop_capture(
    name        => 'CHECK_PRIVS'
  );
END;
/
 

7.) Change:

According with the results we can create a ROLE with the GRANTS below:

CREATE SESSION
CREATE TABLE  
SELECT ON SYS.V_$DATABASE

We can REVOKE DBA from user CHECKPRIVS;