We are seeing a strange issue while setting up portal database. Initially we created a db user with system privileges ( similar to DBA privilege) and set this user under PortalDS datasource. Once we start the Jboss Portal Server. All the JBP/JBPM tables, Sequences( hibernate_sequence, portlet_sequence,portal_seq, instance_seq,sec_seq,user_seq) are created. We set up the portal and its security, themes etc through the admin console. Everything works fine using this db user.
Since this user has system privilege, there is a concern from DBAs as well as infrastructure to have a similar user with system privilege credential in production and staging environment.
As a next step, we created a new db user and granted "all" access to the JBP/JBPM tables and synonyms created for jboss portal sequences(hibernate_sequence, portlet_sequence,portal_seq, instance_seq,sec_seq,user_seq). In the hibernate.cfg file the hbm2ddl.auto is set to "update".
When we started the jboss portal with the user credential of newly created user in PortalDS, the application server is starting fine with 3 of the sequences ( portal_seq, instance_seq, sec_seq) giving error as (e.g. Table Not Found: portal_seq), then when i try to login to portal using portal user or admin. It is not going through.
I'm wondering why not able to get into JBP when JBP is started with a DB user not having system privilege while it is working with a DB user having system privilege. Since the tables, sequences are already created and the newly created user is being given "all" privileges on the tables and sequence synonyms.
Has anyone faced this issue or this is a limitation in JBP ?
Appreciate your response ?
When you say full access, do you give DBA privileges for that user. In our case, we dont give DBA access to the user. Rather we give only ( select,insert,update,delete, references, alter, index) privileges on tables. For the sequences, we create synonyms and provide ( select,alter) privileges.
I'll base my answers on a MySQL database. I give all privileges (there are quire a number of them) to the user on the created database. The user also has GRANT privileges, which enables that user to also grant the same privileges to others. I haven't tried creating a user without GRANT privilege, nor have I attempted to determine the minimum number of privileges required for the software to work.
By the way, Portal uses Hibernate for its data access, so any posts or blogs that deal with security issues with Hibernate on Oracle should apply to Portal.