Is there any way to add user from PL/SQL? I would like to manage add/remove users from code, not in admin menu"Security/Repositories".

Is it possible?

Thank you very much

asked 23 Jun '15, 06:15

simrob's gravatar image

accept rate: 0%

Hi Simrob,

Yes, this is possible indeed. There are internal API's to achieve this. The internal API's are not as convenient to use as the external API's but you can certainly achieve what you've described.

To create a user, the following procedure in the API package bdf_ac_user_api:

procedure createUser(in_repoName_tx varchar2,in_name_tx varchar2,in_password_tx varchar2,in_admin_yn varchar2,in_encrypt_yn varchar2:=null);

set in_encrypt_yn value to Y.

Next, to give a role to your new user, call the following procedure in the API package bdf_ac_userrole_api:

procedure createUserRole(in_repoName_tx varchar2,in_userName_tx varchar2,in_roleName_tx varchar2);

Finally, Formspider uses a denormalized table to quickly read keys for each user. You need to populate that yourself. To achieve this, you should first find out the ID of the user you created and the ID of the role (or roles) you gave to it. You can do this by calling the following SQL:

SELECT u.bdf_ac_user_oid
  FROM t_bdf_ac_user u, t_bdf_ac_repo r
  where u.name_tx=:userName
  and r.name_tx=:repoName
  and r.bdf_ac_repo_oid=u.bdf_ac_repo_oid

:userName is the user you created and the :repoName is the name of security repository you created it in.

Similarly, find the ID of the Role (or roles) you gave to the user using the following SQL for each role.

SELECT ro.bdf_ac_role_oid
  FROM t_bdf_ac_role ro, t_bdf_ac_repo r
  where ro.name_tx=:roleName
  and r.name_tx=:repoName
  and r.bdf_ac_repo_oid=ro.bdf_ac_repo_oid

Now you can populate the denormalized table.

Call the following procedure for each role you gave the user.

bdf_ac_userkey_api.createUserRole(in_user_id number, in_role_id number);

Commit your changes and you should be set to go.

Hope this helps.

Kind Regards,


answered 23 Jun '15, 14:27

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
accept rate: 15%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 23 Jun '15, 06:15

Seen: 875 times

Last updated: 23 Jun '15, 14:27

© Copyright Gerger 2017