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

Lucas Jellema 1
0 0
Read Time:2 Minute, 54 Second

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;

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

One thought on “Re-assigning Roles to an Owner in Oracle Designer Role Management

  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

Comments are closed.

Next Post

Ajax-based Post Loading of resources in HTML pages - for reuse of resources and fast user feedback

This article discusses a generic method for Post Loading Resources in HTML pages. What do I mean by that? Just as your browser will fire additional requests, to potentially multiple web-servers when needed, to fetch images specified in your HTML page by the IMG tag or JavaScript and CSS resources […]
%d bloggers like this: