A good way to check a user's rights and the privileges granted to users or to a public role and other security weaknesses can be to initiate it from Oracle Enterprise Manager Database Control by using the secure configuration evaluation feature.
If you do not have OEM installed and configured, you may use the dbca or emca command line utility to perform interactive installation and configuration (for example, emca –config dbcontrol db):
- Log in to Oracle Enterprise Manager (OEM) as an administrative user. Navigate to the Server tab, go to the Related Links panel, and click on the Policy Groups link shown as follows:
- The Policy Group Library page will open. In line with the Policy Group Secure configuration for Oracle in the Scheduled Evaluation column, click on the opened notebook icon as shown in the following screenshot:
- Next, Scheduled Evaluations: Secure Configuration for Oracle Database will open; here click on the Schedule Evaluation button, as follows:
- In the Schedule page choose your database to be evaluated (in our case, HACKDB). Give a name to your evaluation in the Schedule panel, check One time (Immediately) in Type, and click on the OK button shown as follows:
- At this step the evaluation begins; wait for a minute or less and then press the Return button, as follows:
- You will be returned back to the Policy Group Library page; here click on the Evaluation Results tab. Here you may see a general Compliance result, which in our case is 52 percent, a very low score indeed. Next, click on the Secure Configuration for Oracle Database link as follows:
- On the following page, we have a listbox with different categories related to installations, permissions, parameters, passwords, profiles, and access settings on the left-hand side, as seen in the following screenshot. Next, in Database Access Settings choose Tables and click on Restricted Access to SYS.USER$. Click on the Violations tab in the right-hand panel; here you will get a list of users who have access to this table, as follows:
The evaluations are developed following Oracle security best practices. After every run, you will get the security issues and general recommendations and a hint about how to resolve them. The final evaluation is marked with a compliance score. You should use a minimalistic approach for databases; as for operating security, remove or do not install features that you will not use.
Some recommendations for increasing the initial database security are as follows:
- Detect users with default passwords. Default passwords can be queried by using
DBA_USER_WITH_DEFPWDdictionary view (for exampleSELECTusername,FROM DBA_USERS_WITH_DEFPWD). - Use strong passwords.
- Remove unnecessary users, lock or secure the database with impossible passwords. An impossible password contains in its hashed value ASCII characters which in practice can not be generated by using hash functions, hence its value will be impossible to be matched using pattern matching or dictionary attacks (for example
ALTER USER TESTidentified by valuesALOP34RTYN}_). - Use password crackers to check if your passwords are really strong.
- Force passwords to be case sensitive by setting
SEC_CASE_SENSITIVE_LOGONtoTRUE(this is the default value) and connection throttling by usingSEC_MAX_FAILED_LOGIN_ATTEMPTSparameter.Note
SEC_MAX_FAILED_LOGIN_ATTEMPTSparameter will limit and drop the connection after the specified number of failed connection attempts(default is10) using usernames that do not exist in the database and will slow down gradually (throttle) further attempts to connect to overcome resource saturation. It does not apply to valid users. - Use custom profiles to force users to use strong passwords, and enforce password policies such as aging and locking.
- Remove, or do not install, features that you will not use.
For a more advanced security assessment, see the Appendix, Installing and Configuring Guardium, ODF, and OAV (the link for the appendix is available in the Preface of this book). For account security assessments related to privileges, known as vulnerabilities and password strength, you may use dedicated commercial tools such as NGS SQUIRREL, AppSecInc's AppDetectivePro, McAfee Database Vulnerability Manager for Databases, and Repscan.
Also, you can use some very useful scripts for privilege assessment reporting developed by Pete Finnigan (for updates and more details check http://www.petefinnigan.com/tools.htm). These scripts are interactive and are excellent for creating different entitlement reports.
For example, use the following script to list the users with specific system privileges:
SQL>@d:petefinniganfind_all_privs.sql who_has_priv: Release 1.0.3.0.0 - Production on Tue May 15 15:59:05 2012 Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. PRIVILEGE TO CHECK [SELECT ANY TABLE]: SELECT ANY DICTIONARY OUTPUT METHOD Screen/File [S]: FILE NAME FOR OUTPUT [priv.lst]: OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]: EXCLUDE CERTAIN USERS [N]: USER TO SKIP [TEST%]: Privilege => SELECT ANY DICTIONARY has been granted to => ==================================================================== User => OLAPSYS (ADM = NO) Role => DBA (ADM = YES) which is granted to => User => SYS (ADM = YES) User => SYSTEM (ADM = YES) User => WMSYS (ADM = YES) User => SYSMAN (ADM = NO) User => ORACLE_OCM (ADM = NO) Role => OEM_MONITOR (ADM = NO) which is granted to => User => DBSNMP (ADM = NO) User => SYS (ADM = YES) User => DBSNMP (ADM = NO) User => VASCAN (ADM = NO) User => IX (ADM = NO) PL/SQL procedure successfully completed. For updates please visit http://www.petefinnigan.com/tools.htm
To find all the privileges granted to a specific user, use the following script:
SQL>@d:petefinniganfind_all_privs.sql Find all privileges granted to a specific user : find_all_privs: Release 1.0.7.0.0 - Production on Tue May 15 16:02:58 2012 Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. NAME OF USER TO CHECK [ORCL]: HR OUTPUT METHOD Screen/File [S]: FILE NAME FOR OUTPUT [priv.lst]: OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]: User => HR has been granted the following privileges ==================================================================== ROLE => RESOURCE which contains => SYS PRIV => CREATE CLUSTER grantable => NO SYS PRIV => CREATE INDEXTYPE grantable => NO SYS PRIV => CREATE OPERATOR grantable => NO SYS PRIV => CREATE PROCEDURE grantable => NO SYS PRIV => CREATE SEQUENCE grantable => NO SYS PRIV => CREATE TABLE grantable => NO SYS PRIV => CREATE TRIGGER grantable => NO SYS PRIV => CREATE TYPE grantable => NO SYS PRIV => ALTER SESSION grantable => NO SYS PRIV => CREATE DATABASE LINK grantable => NO SYS PRIV => CREATE SEQUENCE grantable => NO SYS PRIV => CREATE SESSION grantable => NO SYS PRIV => CREATE SYNONYM grantable => NO SYS PRIV => CREATE VIEW grantable => NO SYS PRIV => EXEMPT ACCESS POLICY grantable => NO SYS PRIV => UNLIMITED TABLESPACE grantable => NO TABLE PRIV => EXECUTE object => SYS.DBMS_STATS grantable => NO PL/SQL procedure successfully completed. For updates please visit http://www.petefinnigan.com/tools.htm SQL>
The object-level privileges granted to the specific objects can be checked as follows:
SQL> @d:petefinniganwho_can_access.sql who_can_access: Release 1.0.3.0.0 - Production on Tue May 15 16:05:28 2012 Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. NAME OF OBJECT TO CHECK [USER_OBJECTS]: EMPLOYEES OWNER OF THE OBJECT TO CHECK [USER]: HR OUTPUT METHOD Screen/File [S]: FILE NAME FOR OUTPUT [priv.lst]: OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]: EXCLUDE CERTAIN USERS [N]: USER TO SKIP [TEST%]: Checking object => HR.EMPLOYEES ==================================================================== Object type is => TABLE (TAB) Privilege => REFERENCES is granted to => User => OE (ADM = NO) Privilege => SELECT is granted to => User => OE (ADM = NO) PL/SQL procedure successfully completed. For updates please visit http://www.petefinnigan.com/tools.htm