Creating a read-only database user for Oracle

Products

• Security Information Manager

Issue/Introduction

 

Resolution

Creating a read-only database user for Oracle

Collectors that use a database sensor require that you create a read-only database user account so that the collector can query for events.

See Installing collectors that use a database sensor.

You can set up Oracle database users in the following ways:

• By using Oracle 10g or 11g Enterprise Manager

  See “To create a read-only database user account by using Oracle Enterprise Manager”.

• By using SQLPlus

  See “To create a read-only Oracle database user by using SQLPlus”.

To create a read-only database user account by using Oracle Enterprise Manager

1. Log in to the Oracle database as a user that has permissions to create roles and users with Enterprise Manager.

2. On the Database Instance page, click the Administration tab.

3. Under the Users & Privileges heading, click Roles.

4. On the Roles page, click Create.

5. On the Create Role page, in the Name field, type a new role name.

   For example, ReadOnlyRole.

6. Click System Privileges, and then click Edit List.

7. In the Available System Privileges list, select the privilege named SELECT.

8. Do one of the following steps:

   • If Oracle Auditing is configured to use the Database Audit Trail, select the DBA_AUDIT_TRAIL view.

   • If Oracle Auditing is configured to use the XML Audit Trail, select the V_$XML_AUDIT_TRAIL view.

9. Click Move so that the privileges appear in the Selected System Privileges list.

10. Click OK, and then click OK again to create the role.

11. Click the link Database Instance:database_name.

12. On the Administration tab, under Users & Privileges, click Users.

13. Click Create.

14. On the Create User page, perform the following tasks in the order given:

    • In the Name field, type a user name.

      For example, READONLYUSER.

    • In the Profile field, leave the value as DEFAULT.

    • In the Authentication field, leave the value as PASSWORD.

    • In the Enter Password and Confirm Password fields, type a password and confirm it.

    • In the Default Tablespace field, type a default table space for the user.

      For example, USERS.

    • In the Temporary Tablespace field, type a temporary table space for the user.

      For example, TEMP.

15. Click Roles.

16. On the Roles page, click Edit List.

17. In the Available Role list, select the role that you created in step 4.

18. Click Move so that the role appears in the Selected Roles list.

19. Click OK, and then click OK again, to create the user with read-only access to the database tables.

To create a read-only Oracle database user by using SQLPlus

1. To start SQLPlus without logging in to a database, at a command prompt, type the following:

   sqlplus /nolog;

2. To connect as a system database administrator, at the SQLPlus prompt, type the following command:

   connect sys/password@SID as sysdba;

3. To create a read-only role, at the SQLPlus prompt, type the following command:

   create role read_only_role;

4. Do one of the following steps:

   • If Oracle Auditing is configured to use the Database Audit Trail, type the following command:

     grant select on DBA_AUDIT_TRAIL to read_only_role;

   • If Oracle Auditing is configured to use the XML Audit Trail, type the following command:

     grant select on V_$XML_AUDIT_TRAIL to read_only_user;

5. To create a read-only user, at the SQLPlus prompt, type the following commands:

   create user read_only_user identified by password default tablespace users temporary tablespace temp quota unlimited on users;

6. To assign the role you that created in step 3 to the user you that created in step 5, at the SQLPlus prompt, type the following command:

   grant read_only_role to read_only_user;