Oracle 12c – User privilege analysis

April 11, 2017 Larry Catt Leave a comment

Oracle 12c gives the ability to perform a user privilege analysis to bounce against granted privileges. This allows you to reduce granted privilege down to what the individual user actually needs and no more. This article gives a procedure to perform User privilege analysis.

Logon oracle server as the oracle software owner than logon to sqlplus with sysdba privilegs. If this is done by none privileged user account, the user must have the CAPTURE_ADMIN role granted.

[root@linux2 ~]# su – oracle

Last login: Thu Dec 15 07:50:00 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 13:05:59 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

Define the privilege analysis policy with the CREATE_CAPTURE procedure of DBMS_PRIVILEGE_CAPTURE package. This example below will capture all privileges used.

BEGIN

DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(

name => ‘user_priv_capture’,

description => ‘Capture the privileges by user test’,

type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);

END;

Enable the privilege analysis policy.

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (‘user_priv_capture’);

PL/SQL procedure successfully completed.

SQL>

Logon as the user test and perform DML processing.

SQL> connect test/test

Connected.

SQL> insert into test.test_times(pers_id, first_name) values(99,’test’);

1 row created.

SQL> commit;

Commit complete.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$

Logon as sysdba and disable the privilege analysis policy’s recording of privilege use.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 13:25:29 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (‘user_priv_capture’);

PL/SQL procedure successfully completed.

SQL>

Generate privilege analysis results.

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (‘user_priv_capture’);

PL/SQL procedure successfully completed.

SQL>

Query the table dba_used_privs to see privileges used during the capture period.

SQL> column username format a12

column sys_priv format a15

column object_owner format a10

column object_name format a25

set pagesize 500

SELECT username, sys_priv, object_owner, object_name FROM dba_used_privs WHERE capture = ‘user_priv_capture’;

SQL> SQL> SQL> SQL> SQL>

USERNAME SYS_PRIV OBJECT_OWN OBJECT_NAME

———— ————— ———- ————————-

TEST CREATE SESSION

TEST SYS USER_TABLES

APEX_040200 SYS DBMS_OUTPUT

TEST CREATE SESSION

TEST SYS DUAL

APEX_040200 SYS DBMS_OUTPUT

TEST CREATE SESSION

TEST SYS DUAL

APEX_040200 SYS DEFAULT_JOB_CLASS

TEST CREATE SESSION

TEST SYSTEM PRODUCT_PRIVS

TEST SYS DBMS_APPLICATION_INFO

17 rows selected.

SQL>

Optionally, drop the privilege analysis policy.

EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (‘user_priv_capture’);

Larry Catt

OCP

12c, storage

Oracle 12c – Secure file LOBs

April 11, 2017 Larry Catt Leave a comment

In Oracle 12c SecureFiles is now the default storage of LOB when the COMPATIBLE parameter is set to 12.1 or higher. SecureFiles gives better performance than BasicFiles for unstructured objects. The following enhancements have been made to SecureFiles:

PDML Operations – parallel DML operations for non-partitioned tables containing SecureFile LOB columns, table cannot contain BasicFile LOB columns. Operations which can use parallelism include:
1. insert
2. insert as select
3. create table as select
4. delete
5. update
6. merger (conditional update and insert)
7. multi-table insert
8. SQL*Loader
9. Import/Export
LogMiner – LogMiner now fully supports SecureFiles LOBs (deduplication of SecureFile LOB SecureFile Database File System (DBFS). Only SQL_REDO columns are filled in SecureFile LOB, SQL_UNDO is not used.

Larry Catt

OCP

12c, flashback

Oracle 12c – Flashback Transaction Query

April 11, 2017 Larry Catt Leave a comment

Flashback Transaction Query is used to retrieve metadata and historical data for single and all transactions in a time interval from a static data dictionary view FLASHBACK_TRANSACTION_QUERY.

The FLASHBACK_TRANSACTION_QUERY has a column UNDO_SQL which is the opposite of DML which caused the change. You can use the DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure to roll back transaction and dependent transactions. This procedure does not commit the rollback but temporarily holds the rollback until a manual commit is made. To perform the flashback transaction query the database must be in archivelog mode and supplemental logging must be enabled with command: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

For a user to use the flashback query the admin must grant following.

Grant flashback to user.
Grant select privileges on objects.
To allow queries on all tables grant FLASHBACK ANY TABLE privilege.
Grant SELECT ANY TRANSACTION privilege.
Must have SELECT, UPDATE, DELETE, INSERT privilege on that table.
Execute privilege on DBMS_FLASHBACK package.

This article details setting up flashback transactions for your database, granting user scott the appropriate privileges, and performing flashback.

Logon to your SQLPLUS as sysdba.

[oracle@linux2 flash_recovery_area]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 6 08:25:35 2017

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

Verify that the database is in archivelog mode, if not switch to archive log mode.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 2708

Next log sequence to archive 2710

Current log sequence 2710

SQL>

Issue the command ‘ ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;’

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL>

To allow queries on all tables grant FLASHBACK ANY TABLE privilege.

SQL> grant flashback any table to scott;

Grant succeeded.

SQL>

Grant SELECT ANY TRANSACTION privilege.

SQL> grant select any transaction to scott;

Grant succeeded.

SQL>

Execute privilege on DBMS_FLASHBACK package.

SQL> grant execute on dbms_flashback to scott;

Grant succeeded.

SQL>

Logon as the user scott.

SQL> connect scott/password

Connected.

SQL>

Create the test table fb_test.

create table fb_test(fname varchar2(30), amt number);

SQL> create table fb_test(fname varchar2(30), amt number);

Table created.

SQL>

Insert single record into fb_test.

insert into scott.fb_test(fname,amt) values(‘Larry’,111);

commit;

SQL> insert into scott.fb_test(fname,amt) values(‘Larry’,111);

commit;

1 row created.

SQL>

Commit complete.

SQL>

Update single record and commit;

update scott.fb_test set amt=222 where fname=’Larry’;

commit;

SQL> update scott.fb_test set amt=222 where fname=’Larry’;

commit;

1 row updated.

SQL>

Commit complete.

SQL>

Select value from scott.fb_test.

SQL> select * from scott.fb_test;

FNAME AMT

—————————— ———-

Larry 222

SQL>

Query flashback_transaction_query for transactions against scott.fb_test.

SQL> select operation, undo_sql from flashback_transaction_query

where table_name=’FB_TEST’; 2

OPERATION

——————————–

UNDO_SQL

——————————————————————————–

INSERT

delete from “SCOTT”.”FB_TEST” where ROWID = ‘AAAXaKAAGAAALUdAAA’;

UPDATE

update “SCOTT”.”FB_TEST” set “AMT” = ‘111’ where ROWID = ‘AAAXaKAAGAAALUdAAA’;

SQL>

You can now execute the UNDO_SQL column to rewind the update statement.

SQL> update “SCOTT”.”FB_TEST” set “AMT” = ‘111’ where ROWID = ‘AAAXaKAAGAAALUdAAA’;

1 row updated.

SQL> commit;

Commit complete.

SQL>

For a more complex set of updates impacting muptiple objects, you would use the dbms_flashback.transaction_backout procedure.

This completes flashback transaction query.

Larry Catt

OCP

Catt Solutions

Daily Archives: April 11, 2017

Oracle 12c – User privilege analysis

Oracle 12c – Secure file LOBs

Oracle 12c – Flashback Transaction Query

Oracle tips and tricks.