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

1

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:

     select acc.grantee_reference
    from   sdw_users usr
    ,      sdw_access_rights acc
    where  usr.username = b_role_name
    and    acc.object_reference = usr.irid
    and    acc.grantee_reference = acc.grantor_reference

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.

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

Share.

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