Category: Security

Posted on

ODC Appreciation Day: Two Cool 12c Security Features #ThanksODC #ThanksOTN

There are quite a lot of new features in the 12c database that I like and have discussed and talked about them at various events. On this #ThanksODC day, I would like to take the opportunity to thank Oracle Technology Network (or Oracle Developer Community) and Oracle ACE Program for all the support they provide to the DBA and Developer community to share and gain knowledge.

Two simple addition to the 12c database makes my life much easier on the security front. The requirement was to lock the user accounts that are not logged into the database for consecutive 60 days.

1.  Last Login Time 

In releases prior to Oracle Database 12c 12.1 to find out when a user last successfully logged into the database, we need to enable session auditing, which has some overhead and the DBA needs to set up routines to clean the audit table, etc. In 11g database, I had to enable session auditing (keep the session audit records for at least 61 days) and write a SQL querying DBA_USERS and DBA_AUDIT_SESSION to list out all the active users who did not use the database for 60 days.

In 12.1, Oracle automatically registers the successful login time in SYS.USER$ table and is visible in DBA_USERS. The LAST_LOGIN column of DBA_USERS shows the last login time of the user in the database. In 12.1 database, my requirement is met by writing a simple SQL against DBA_USERS using the LAST_LOGIN column! No auditing required. The result is used to build dynamic SQL to lock each account using ALTER USER ACCOUNT LOCK statement (similar to 11g).

BTW, have you noticed, when you login to the database using SQL*Plus, you will see the last login time displayed as well.

2.  Inactive Account Lock

My requirement got completely automated in 12.2 database. Oracle introduced the INACTIVE_ACCOUNT_TIME parameter for user profiles. The INACTIVE_ACCOUNT_TIME profile parameter locks a user account that has not logged in to the database instance in a specified number of days. The default value for INACTIVE_ACCOUNT_TIME is 35. The minimum setting is 15 and the maximum is 24855.

Thanks, Oracle!

#ThanksOTN

#ThanksODC

And thank you, @OracleBase, for doing the appreciation day again!

Posted on

Oracle 12c – READ Privilege

The READ privilege is new in Oracle Database 12c 12.1.0.2. Why do we need a READ privilege when there is SELECT privilege? Well, the SELECT privilege includes few more privilege than a pure read-only privilege! With the SELECT privilege, in addition to reading (or “selecting” from) the table, you are also able to do

  • LOCK TABLE<table_name> IN EXCLUSIVE MODE;
  • SELECT … FROM<table_name> FOR UPDATE;

So, for better security to enforce pure read-only privilege Oracle needed another privilege, which is READ privilege. You can use the READ object privilege or READ ANY TABLE system privilege. Both operate similar to their SELECT counterpart, except for the privileges listed above.

No, there is no READ statement to go with the READ privilege. You will still be using the SELECT statement to read from the table or view. You use the READ object privilege to enable users query database tables, views, materialized views, and synonyms. The READ ANY TABLE privilege enables to query any table or view in the database.

So, from now onwards, you may start granting the READ or READ ANY TABLE privilege instead of SELECT or SELECT ANY TABLE, to the users requiring only the query privilege on the table or view for better security.

Examples of grants and revoke:

SQL> GRANT READ ON XX.XXLE_CUSTOMERS_T TO smith;

SQL> GRANT READ ON XX.XXLE_SUPPLIERS_VW TO smith;

SQL> GRANT READ ANY TABLE TO peter;

SQL> REVOKE READ ON XX.XXLE_SUPPLIERS_VW FROM smith;

SQL> REVOKE READ ANY TABLE FROM peter;