12c New Feature: Unused Privileges

Did you know 12c now has a mechanism to examine which privileges are actually being used by an user, module or for the database as a whole, as opposed to merely the privileges granted?
This is a great security improvement in order give users the privileges that they need precisely without granting too much.

Here’s how is works?

1. First you have to set up a capture process using the new package DBMS_PRIVILEGE_CAPTURE.

# Example:

BEGIN 
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE( 
        name         => 'all_priv_analysis_pol', 
        description  => 'database-wide policy to analyze all privileges', 
        type         => DBMS_PRIVILEGE_CAPTURE.G_DATABASE); 
END;
/

This starts the capture process database wide. Let it run for a bit.

2. Now you can either generate a report:

# Example

BEGIN 
DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT( 
        name         => 'all_priv_analysis_pol'); 
END;
/

Or examine the new views:

DBA_USED_PRIVS 
DBA_UNUSED_PRIVS 
DBA_USED_OBJPRIVS 
DBA_UNUSED_OBJPRIVS

3. To turn off you proceed with the following:

BEGIN 
DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE( 
        name         => 'all_priv_analysis_pol'); 
END;
/

So now you ensure that you use the best practice of least privilege for users.

Cheers!

Database Resident Connection Pool (DRCP)

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Database Resident Connection Pool

The database resident connection pool (DRCP) reduces the resource requirements of applications that currently don’t support connection pooling, either because it is not supported by the application infrastructure, or it has not been implemented. DRCP is only supported for database connections using the OCI driver.

The pool is managed using the DBMS_CONNECTION_POOL package. he DRCP is started and stopped using the START_POOL and STOP_POOL procedures respectively.

SQL> EXEC DBMS_CONNECTION_POOL.start_pool;

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_CONNECTION_POOL.stop_pool;

PL/SQL procedure successfully completed.

The ALTER_PARAM procedure allows you to configure an individual pool parameter, while the CONFIGURE_POOL allows you to configure all pool parameters in one call. The default settings are restored using the RESTORE_DEFAULTS procedure. The pool parameters that are currently supported are listed below:

PROCEDURE CONFIGURE_POOL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 POOL_NAME                      VARCHAR2                IN     DEFAULT
 MINSIZE                        BINARY_INTEGER          IN     DEFAULT
 MAXSIZE                        BINARY_INTEGER          IN     DEFAULT
 INCRSIZE                       BINARY_INTEGER          IN     DEFAULT
 SESSION_CACHED_CURSORS         BINARY_INTEGER          IN     DEFAULT
 INACTIVITY_TIMEOUT             BINARY_INTEGER          IN     DEFAULT
 MAX_THINK_TIME                 BINARY_INTEGER          IN     DEFAULT
 MAX_USE_SESSION                BINARY_INTEGER          IN     DEFAULT
 MAX_LIFETIME_SESSION           BINARY_INTEGER          IN     DEFAULT

Continue reading

11g Feature: Fine Grained Dependency Tracking (FGDT)

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

11g Feature: Fine Grained Dependency Tracking (FGDT)

In previous versions, object dependencies were managed at the object level. This way, altering an object automatically invalidated all dependent objects.
Oracle 11g has more granular dependency management, so only changes that directly affect an object will cause an invalidation.

This is not a Feature under control of Dev or DBA, but I judged important to mention here, as per has important impact to development and deployment processes.

More details can be found here: http://www.orafaq.com/node/2683

11g Feature: Fine-Grained Access Control (FGAC) on Network Services

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Fine-Grained Access Control (FGAC) on Network Services

Oracle supplies PL/SQL utility packages such as UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR to access to network services. In 11g Oracle have enhanced security available.
Rather than public being granted execute privileges on these packages, now it’s needed to create an ACCESS CONTROL LIST (ACL) in order to use these packages. Some ACL Related Data Dictionary VIEWS are DBA_NETWORK_ACLS and [DBA/USER]_NETWORK_ACL_PRIVILEGES.

> To create ACL:

SQL> begin
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'example.xml',
description=>'EXEMPLE ACL',
principal=>'EXAMPLE',                        
is_grant=>TRUE,
privilege=>'connect');
End;
/

> Once the ACL is created, additional user or privileges can be added using the DBMS_NETWORK_ACL_ADMIN.add_privileges procedure:

SQL> BEGIN
  DBMS_NETWORK_ACL_ADMIN.add_privilege ( 
    acl         =>  'example.xml', 
    principal   => 'SCOTT',
    is_grant    => FALSE, 
    privilege   => 'connect', 
    position    => NULL, 
    start_date  => NULL,
    end_date    => NULL);
  COMMIT;
END;
/

* DBMS_NETWORK_ACL_ADMIN.delete_privileges can be usedto drop privileges and DBMS_NETWORK_ACL_ADMIN.drop_acl to drop ACL.

> To assign ACL to a Network Host:

SQL> begin
DBMS_NETWORK_ACL_ADMIN.assign_acl(
acl => 'example.xml',
host=>'grepora');
End;
/

See you next week!

11g Feature: PLS-00436 Restriction in FORALL Statements Removed

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

11g Feature: PLS-00436 Restriction in FORALL Statements Removed

In 11g, the PLS-00436 restriction has been removed, meaning individual elements of a collection can be referenced with SET and WHERE clauses in a FORALL construction.

Please check the following example, setting text of my_table to ‘Line x’ where x is the line number (also ID column):

DECLARE
  TYPE t_test IS TABLE OF my_table%ROWTYPE;
  l_test t_test;
BEGIN
  SELECT * BULK COLLECT INTO l_test FROM my_table;
  
  FOR i IN l_test.first .. l_test.last LOOP
    l_test(i).text := 'Line ' || i;
  END LOOP;

  FORALL i IN l_test.first .. l_test.last
    UPDATE my_table SET text = l_test(i).text WHERE id = l_test(i).id;
  COMMIT;
END;
/

11g Feature: Skip Locked Syntax in SELECT FOR UPDATE

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Skip Locked Syntax in SELECT FOR UPDATE

This is an 11g feature, and it’s a bit controversial. Why?
The SELECT FOR UPDATE statement is well known and responsible by several problematic operations, mainly in transactional databases. It’s not rare to face issues like errors below when trying to perform large updates on database:

ORA-30006: resource busy; acquire with WAIT timeout expired
ORA-00054 resource busy and NOWAIT specified

Worse than this, if a select for update task aborts, a zombie process may hold the row locks long term, requiring DBA intervention.

In 11g, the clause SKIP LOCKED has been released, allowing to skip-over any rows that are already locked. Check below for a simple example:

select COLUMN1, COLUMN2 from MYTABLE 
where COLUMN1='DESIRED_VALUE' for update skipped locked;

This is very useful in transaction environments, specially when facing errors mentioned above, however can cause logical corruption.
The reason is obvious, if some rows be in lock, those are not being updated. In this case, if the table has 100 entries where COLUMN1=’DESIRED_VALUE’ but 10 of them are in lock, only 90 will be actually selected, making statement invalid in some circumstances.

This is very useful in transaction environments, specially when facing errors mentioned above, however can cause logical corruption.
The reason is obvious, if some rows be in lock, those are not being updated.

Additional Note: In some cases, increasing the table’s initrans allows more buckets for locking:

alter table MYTABLE move initrans xxxx;

Here is a very interesting post by Jonathan Lewis about it.

See you next week!

Oracle Database Licensing: First Step!

Oracle licensing is always a complex question, right?
I made some search about it today and decided to share, on quick mode. As I usually do. I focused on Database, by the way. 🙂

The first step is to understand Features vs Options vs Packs relation. Oracle documentation is always good for that. I recommend you to spend some time on Database Licensing Information User Manual.

Ok, now the best way to undestand how evaluate my environment is searching on Oracle Support, right?
And it do not disappoint: Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2 and 12c (Doc ID 1317265.1)
In this note you can get a complete and actual script used to evaluate features/options/packs utilization (options_packs_usage_statistics.sql). This is a good way if you are preparing for an auditing…

SOFTWARE-LICENSE-REVIEW-300x199

I made some simple queries to validate/understand results from Oracle Script. So, if you don’t have access to Oracle Support, it might help you:

Get Features usage:

SELECT u1.name,
u1.detected_usages,
u1.currently_used,
u1.version,
u1.description
FROM   dba_feature_usage_statistics u1
WHERE  u1.version = (SELECT MAX(u2.version)
FROM   dba_feature_usage_statistics u2
WHERE  u2.name = u1.name)
AND    u1.dbid = (SELECT dbid FROM v$database)
--WHERE DETECTED_USAGES>0 -- To get used features only
ORDER BY name
/

Get Options usage:

col parameter for a50
select parameter,value from v$option
--where value='TRUE' -- To get used options only
/

Information about Session license limits:

SELECT sessions_max s_max,sessions_warning s_warning,
sessions_current s_current,sessions_highwater s_high,users_max
FROM v$license;

Information about CPU license limits:

select cpu_count_current, cpu_core_count_current,CPU_SOCKET_COUNT_CURRENT,
CPU_COUNT_HIGHWATER,CPU_CORE_COUNT_HIGHWATER,CPU_SOCKET_COUNT_HIGHWATER
FROM v$license;

An interesting point is that you can disable and enable options through the command chopt. But, you must to get database down first. Example to disable partitioning option:

chopt disable partitioning

The complete explanation and examples (including right values to activate/deactivate options) can be found on Oracle Database Postinstallation Tasks – Enabling and Disabling Database Options.

Some time ago I wrote a post about evaluating Database license in all database park through OEM. It remains valid, I recommend you take a look in this post too.

 

KB and other interesting stuffs:
Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2 and 12c (Doc ID 1317265.1)
12c Release 1 Database Licensing Information User Manual
11g Release 1 Database Licensing Information User Manual
Enabling and Disabling Database Options
Excellent and actual presentation by Martin Berger
Article about top license pitfalls. Good to reflection. Written by OMTCO Consulting

Matheus.