Oozie Non Default Database Setup

[#1]

From Ambari 1.7 doc http://docs.hortonworks.com/HDPDocuments/Ambari-1.7.0.0/Ambari_Doc_Suite/ADS_v170.html#ref-f6bcf79a-..., why do Oozie user need to have all privileges (GRANT ALL)? Database admins might not want to set it up this way.

[#2]

It should be GRANT ALL to just it's Oozie Database. Because the 'oozie' user needs to be able to create the schema in the target database.

[#3 · Accepted solution]

These permissions should be enough for the oozie user on Oracle:

GRANT SELECT_CATALOG_ROLE TO <OOZIE USER>;
GRANT CONNECT, RESOURCE TO <OOZIE USER>;

For the other databases MySQL and PostgresQL I agree with @David Streever that the permission needs to be set to just Oozie database. Additionally we want to limit the access from just the oozie host. On MySQL this can be:

GRANT ALL PRIVILEGES ON <OOZIE DB>.* TO '<OOZIE USER>'@'<OOZIE HOST>'

[#4]

Yes! GRANT ALL is needed!

mysql> create database oozie;
Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on oozie.* to 'oozie'@'localhost' identified by 'oozie';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on oozie.* to 'oozie'@'%' identified by 'oozie';
Query OK, 0 rows affected (0.00 sec)

mysql> exit