JOPR - User creation using stored procedures

[senthur_kumaran]

Hi All,

I am planning to automate the user creation process in Jopr. All the users will be assigned to default role (Role already created manually through administration option). The user information will be directly feed into JON database using stored procedures.

There is an employee management system in our organization and all other applications are using common data from this system.

My idea is to create a stored procedure which extracts data from Employee management system and create users with default pwd and default role.

Example:
First Name : xyz
Last Name : abc
Email : [e-mail]
pwd : password123
username : xabc
Role : Support Role (RHQ_Role.id=500252,RHQ_Role.Name=Support Role)

I appreciate your further ideas.

Thanks & Regards
Senthur

[mazz]

three things off the top of my head:

1) you need to make sure RHQ_SUBJECT gets a new row for your user
2) you need to make sure RHQ_PRINCIPAL gets a new row for your user (that contains the MD5 hash of the user's password).
3) you'll need to link the user with the role via a row in RHQ_SUBJECT_ROLE

[senthur_kumaran]

Thanks for your quick reply!
Here is my SQL Queries

insert into RHQ_SUBJECT (NAME, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS, SMS_ADDRESS, PHONE_NUMBER, DEPARTMENT, FACTIVE, FSYSTEM, CONFIGURATION_ID, ID) values('axyz','abc','xyz','[e-mail]',' ','1',' ',1,0,null, 500605)

insert into RHQ_PRINCIPAL (PRINCIPAL, PASSWORD, ID) values ('axyz','odjl47Qq4fqrg2Sax62zdg==',100125)

insert into RHQ_SUBJECT_ROLE_MAP (SUBJECT_ID, ROLE_ID) values (500605, 500252)

I took the highest subject id+1 and principal id+1 for the above queries.

Can you please tell me how to generate rhq subject id and rhq principal id? What are the sequences involved in this?

Thanks
Senthur

[ips]

The SQL for getting the next id for a sequence is different, depending on whether you're using Postgres or Oracle. For Postgres, it's:

(SELECT nextval('RHQ_SUBJECT_ID_SEQ'::text))

and for Oracle, it's:

RHQ_SUBJECT_ID_SEQ.nextval

Note, our table id sequences are always named tableName_ID_SEQ.

[senthur_kumaran]

MY database environment is Oracle 10g.

I tried "select RHQ_SUBJECT_ID_SEQ.nextval from dual" , which returns 10023.

When i tried creating new user using UI , I got 500609 as new subject id.

Am I missing something?

[ips]

Hmm, I'm not sure. The generated sequence id's are not usually consecutive, but the gap between those two id's does seem unusually large.

[senthur_kumaran]

I tried creating one more user through UI. It generated subject id 500610.

But the current value in RHQ_SUBJECT_ID_SEQ is still 10023.

I think the Jopr system takes some other sequence id or it has some numerical calculations in generating the id.

I am stuck in this "create user automation" task...

Please get back to me if you find anything.

Thanks in advance
Senthur

[ccrouch]

Wouldn't it be easier to look into exposing for remote access the EJB session calls which the Jopr web UI uses to create users. That way this feature will be available to anyone regardless of what backend database they are using.

Cheers

[ips]

Well, if you look at the org.rhq.core.domain.auth.Subject entity bean, it's annotated with:

@SequenceGenerator(name = "RHQ_SUBJECT_ID_SEQ", sequenceName = "RHQ_SUBJECT_ID_SEQ")

If we were persisting new Subjects using the EJB3 entityManager, then the id's would get created using the RHQ_SUBJECT_ID_SEQ sequence, which is what you're doing with raw SQL. So I'm guessing that we're creating the Subjects in some other way (e.g. straight JDBC) that is not using RHQ_SUBJECT_ID_SEQ to set the id.

[ccrouch]

"ips" wrote:
Well, if you look at the org.rhq.core.domain.auth.Subject entity bean, it's annotated with:

@SequenceGenerator(name = "RHQ_SUBJECT_ID_SEQ", sequenceName = "RHQ_SUBJECT_ID_SEQ")

If we were persisting new Subjects using the EJB3 entityManager, then the id's would get created using the RHQ_SUBJECT_ID_SEQ sequence, which is what you're doing with raw SQL. So I'm guessing that we're creating the Subjects in some other way (e.g. straight JDBC) that is not using RHQ_SUBJECT_ID_SEQ to set the id.

Senthur, all of these issues become irrelevant to you if can use the EJB3 session layer to do this work :-)
People with more experience of making this api remotely may be able to provide more insight into how feasible this would be. I'm thinking that since the object graph for users and roles isnt very complicated (e.g. easier to serialize) it should be possible.

[jayshaughnessy]

In (RHQ) trunk there is a WS remote api already defined, and partially implemented, exposing the necessary SLSB's. SubjectManagerRemote and RoleManagerRemote. There is also a TestRemoteInterface class that uses the API to create Subjects and Roles and making assignments.

This work is not fully cooked but hopefully will get some more attention in the near future. The Remote API will definitely be less fragile than coding directly against the database or lower level apis.