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!

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;

usersQ: Can you create users in ASM instance?

A: Yes, absolutely.

Q: Why?

A: For better security.

Q: I was told there is no database associated with ASM, it is just an instance, so where is this user really created?

A: You must have a password file to create users. The users are added to password file.

Q: Ha! are there default users?

A: Yes, SYS and ASMSNMP are default users.

Q: What privileges can I grant to a user?

A: You can grant SYSDBA, SYSASM or SYSOPER privileges.

Q: How do you create a new user?

A: Use SQL*Plus, connect to the instance as SYSASM for all user management. You may also use orapwusr command in asmcmd.

Q: Can you show an example of creating a new user using SQL*Plus?

A: Sure.

Q: I notice that you used 12.1 release database. Is this feature available in lower release as well?

A: Absolutely. the syntax, privileges and functionality is exactly same in 11gR2. This feature is not available in 10g ASM.

Q: How do you add user using ASMCMD?

A: Use the orapwusr command to add, modify or delete a user. Use the lspwusr command to list the user and privileges.

Q: Can you show how to?

A: Here, I create an user, modify its privilege, list the user, and finally drop the user. Notice that only one privilege can be granted using the –privilege option, and all other privileges gets revoked. Using SQL*Plus, I was able to grant more than one privilege.

Q: How do I connect as the new user when logging in?

A: In SQL*Plus, you use the id and password. For “asmcmd”, there is no option – connect as sysasm or sysdba privilege based on the OS group membership.

Q: Does ASM write anywhere which user logged in?

A: I would guess so, but in reality, it does not write which user. Just says [/], instead of user name. Looking the audit file generated in  AUDIT_FILE_DEST location.

Q: You are connected as SYSDBA, how about if you connect as SYSASM?

A: Good question, here the audit file shows the actual user.

 

 

 

%d bloggers like this: