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>