Re-assigning Roles to an Owner in Oracle Designer Role Management


This article came about as a result of the questions asked by Victor Bax with regard to an earlier post: Oracle Designer – Script for verifying the privileges assigned to roles against the Module Table Usages. His concerns were with regard to the roles management, an extension to Oracle Designer in the Oracle Designer Web Assistant and the Repository Object Browser. In Victor’s Repository, they had accidentally removed the owner and original creator or the roles as a Repository User. As a result, they could no longer administrate those roles. After re-creating this user – same name, same database user – in the Repository, they still could not manage the roles in the Security Manager. In this article, I explain why this is the case and how it can be corrected. ....

Roles are not an orginal part of the Repository Architecture (except for PUBLIC). Roles were added as a ‘hack’ if you like in the Oracle Designer Web Assistant. Roles are records in the SDW_USERS table with USER_TYPE =0. I have checked the source for the Security Manager – package odwapred – and concluded that in order to grant the role to other users, reconcile the role or edit the role privileges, you need to have Admin Privileges on the Role or be the Owner of the Role. The Owner of a Role is the user that has Access privileges on the Role and who has been granted access privileges by himself:

&nbsp;    select acc.grantee_reference<br />    from   sdw_users usr<br />    ,      sdw_access_rights acc<br />    where  usr.username = b_role_name<br />    and    acc.object_reference = usr.irid<br />    and    acc.grantee_reference = acc.grantor_reference<br /><br />

In Victor’s case I think that probably the orinal creator of the role – the Repository Owner or some other hotshot user- has lost all his privileges on the roles when he was deleted. What you need to do is restore all those privileges, by creating new entries in the SWD_USERS table.

You can use the following script; make sure for update the l_repowner_username with the name of your Repository Owner. Note: the script contains a commit near the end. You can of course remove it,run the script, verify its results and then commit the transaction manually.

<p>declare<br /> l_new_acc_privs     sdd_acc_priv_list:=sdd_acc_priv_list( 'N','N','N','Y','N','N','N','N'); <br /> l_repowner_username varchar2(30):= 'SCM';<br /> l_grant_irid        number(38);<br />begin<br />   -- set the admin privileges that belong to the role owner<br />   l_new_acc_privs(2):= 'Y'; -- ADM privilege<br />   l_new_acc_privs(1):= 'Y'; -- DEL privilege     <br />   l_new_acc_privs(7):= 'Y';  -- CMP privilege<br />   -- find all roles without an owner<br />   for lostroles in ( select role.irid<br />                      ,      role.full_user_name<br />                      from   sdw_users role<br />                      where  role.user_type = 0<br />                      and    not exists ( select acc.grantee_reference<br />                                          from   sdw_access_rights acc<br />                                          where  acc.object_reference = role.irid<br />                                          and    acc.grantee_reference = acc.grantor_reference<br />                                        ) <br />                    ) loop<br />     l_grant_irid := jr_util.get_new_irid;  -- get primary key for this new record<br />     insert into SDW_ACCESS_RIGHTS <br />     ( IRID <br />     , IVID <br />     , GRANTEE_REFERENCE <br />     , GRANTOR_REFERENCE <br />     , OBJECT_REFERENCE <br />     , OBJECT_TYPE <br />     , ACCESS_PRIVS<br />     ) <br />     VALUES <br />     ( l_grant_irid<br />     , 1 <br />     , l_repowner_username <br />     , USER <br />     , lostroles.irid<br />     , 'WA' -- for now let us pretend a ROLE is a WA (Workarea); <br />            -- that allows us to grant Compile (= Reconcile) privs; <br />            -- the Check Constraint SDW_ACCESS_RIGHTS_CHECK1 forces us<br />            -- currently to use one of CEL, WA, CFG<br />     , l_new_acc_privs<br />     ); <br />   end loop; -- lost_roles<br />   commit;<br />end; <br /></p>

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment

  1. Lucas, my saviour!

    I indeed commented out the COMMIT temporarily, until after I checked that the script indeed achieved my goal. And it did. The only role that was not meant to get the full set of desired privileges was the PUBLIC so I made a tiny adjustment in the beginning of the loop.

    This is valuable information and should be published more often.

    Thanks ever so much.

    Victor Bax