-
1. Re: JOPR - User creation using stored procedures
mazz Apr 14, 2009 12:28 PM (in response to senthur_kumaran)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 -
2. Re: JOPR - User creation using stored procedures
senthur_kumaran Apr 14, 2009 12:47 PM (in response to 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','xyz@mydomain.com',' ','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 -
3. Re: JOPR - User creation using stored procedures
ips Apr 14, 2009 12:54 PM (in response to senthur_kumaran)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. -
4. Re: JOPR - User creation using stored procedures
senthur_kumaran Apr 14, 2009 1:05 PM (in response to 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? -
5. Re: JOPR - User creation using stored procedures
ips Apr 14, 2009 1:22 PM (in response to senthur_kumaran)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.
-
6. Re: JOPR - User creation using stored procedures
senthur_kumaran Apr 14, 2009 1:33 PM (in response to 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 -
7. Re: JOPR - User creation using stored procedures
ccrouch Apr 14, 2009 3:24 PM (in response to senthur_kumaran)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 -
8. Re: JOPR - User creation using stored procedures
ips Apr 14, 2009 3:24 PM (in response to senthur_kumaran)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. -
9. Re: JOPR - User creation using stored procedures
ccrouch Apr 14, 2009 3:46 PM (in response to senthur_kumaran)"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. -
10. Re: JOPR - User creation using stored procedures
jayshaughnessy Apr 24, 2009 5:24 AM (in response to senthur_kumaran)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.