Create A Read Only Database User for Reporting
The below script will create a database user named "biaccess" that has read only access to iDempiere's database. Be sure to change the password before executing the script!
Here is a script as well: https://github.com/chuboe/idempiere-installation-script/blob/master/utils/chuboe_bi_init_permissions.sql
-- This script is run inside the idempiere database -- Create a group/role CREATE ROLE readaccess; -- Grant access to existing tables GRANT USAGE ON SCHEMA adempiere TO readaccess; -- Grant access to existing tables -- Be careful about giving this user full read (not recommended) -- It can expose data you might not want exposed -- It is much better to give individual table access -- Some BI tools will cripple your iDempiere instance when trying to digest the data. It will also impact your ability to deploy changes to tables. GRANT SELECT ON ALL TABLES IN SCHEMA adempiere TO readaccess; -- Grant access to future tables -- Be careful about giving this user full read (not recommended) -- It can expose data you might not want exposed -- It is much better to give individual table access -- Some BI tools will cripple your iDempiere instance when trying to digest the data. It will also impact your ability to deploy changes to tables. ALTER DEFAULT PRIVILEGES IN SCHEMA adempiere GRANT SELECT ON TABLES TO readaccess; -- Create a final user with password CREATE USER biaccess WITH PASSWORD 'SomePassword7861'; GRANT readaccess TO biaccess;
If you want to revoke and drop this read-only user:
revoke select on all tables in schema adempiere from readaccess; revoke usage on schema adempiere from readaccess; drop user biaccess; drop role readaccess;
Resources: