0 
0 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;
Related posts:
Anydata and Collections 
Book Review: Mastering Oracle PL/SQL: Practical Solutions Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table 
Extracting Master-Detail data from an XMLType with a single SQL statement My first steps with Oracle BI Publisher Enterprise Edition 10.1.3.2: create a report based on the OraBlogs rss-feed
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