How to chech whether roles got changed to an user [message #432699] |
Wed, 25 November 2009 17:02  |
astik.mondal
Messages: 14 Registered: March 2008 Location: Kolkata
|
Junior Member |
|
|
Hi All,
We have a requirement, if assigned roles to an user are changed, then we need to revoke all the roles from the user and assign new roles to the user.
I have written the below procedure.
My question is how I can check whether roles got changed. What will be the condition to check this before revoking and assigning new roles to the user?
Any inputs on this would be appreciated.
CREATE TYPE roleArrayType AS VARRAY(100) OF VARCHAR2(100);
/
CREATE TYPE OBJ_USER AS OBJECT
(
USER_NAME VARCHAR2(100),
ROLE_NAME roleArrayType,
APPLICATION_NAME VARCHAR2(100));
/
create or replace procedure role_proc(p_user in obj_user, p_application in varchar2, p_message out varchar2)
AS
ddl_statement varchar2(1000);
v_role_str varchar2(1000);
begin
-------------------------To revoke the existing roles from the user-----------------------------
FOR getRoles IN (select role_app.role_name
from dba_role_privs role_privs, role_app_tab role_app
where role_privs.granted_role = role_app.role_name
and role_privs.grantee = p_user.user_name
and role_app.application_name = p_user.application_name)
LOOP
v_role_str := v_role_str ||','||getRoles.role_name;
END LOOP;
/*----------------------------------------------------------------------------------------------------------------------------------- -----------
<Here we need to check whether roles got changed or not>
------------------------------------------------------------------------------------------------------------------------------------- --------*/
begin
ddl_statement := 'REVOKE ' || substr(v_role_str,2) || ' FROM '|| p_user.user_name;
execute immediate ddl_statement;
exception
when others then
p_message := 'Error: ' || substr('Role ' || substr(v_role_str,2) || ': ' || sqlcode || ' / ' || SQLERRM(sqlcode),1,255);
raise_application_error (-20002,'An error has occurred revoking roles from the existing user');
end;
--------------------------To grant the new roles to the user-----------------------------
v_role_name:= p_user.role_name;
v_role_str := null;
FOR i in v_role_name.FIRST .. v_role_name.LAST
LOOP
v_role_str := v_role_str ||','||v_role_name(i);
END LOOP;
begin
ddl_statement := 'GRANT ' || substr(v_role_str,2) || ' TO ' || p_user.user_name;
execute immediate ddl_statement;
exception
when others then
p_message := 'Error: ' || substr('Grant ' || v_role_str|| ': ' || sqlcode || ' / ' || SQLERRM(sqlcode),1,255);
raise_application_error (-20002,'An error has occurred updating user granting role to the user');
end role_proc;
Thanks,
Astik
|
|
|
|
Re: How to chech whether roles got changed to an user [message #432702 is a reply to message #432700] |
Wed, 25 November 2009 17:47   |
astik.mondal
Messages: 14 Registered: March 2008 Location: Kolkata
|
Junior Member |
|
|
Hi,
Thank you for your inputs. I mean to say if the roles assigned to a USER are changed, then we need to revoke all the roles from the user and assign new roles to the user.
I checked with the below condition in the loop. But it didn't work.
------
if getRoles.role_name <> p_user.role_name then
-----
---revoke roles
---assign new roles..
-----
|
|
|
|
Re: How to chech whether roles got changed to an user [message #432762 is a reply to message #432703] |
Thu, 26 November 2009 02:57  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
@BlackSwan :Quote:I mean to say if the roles assigned to a USER are changed, then we need to revoke all the roles from the user and assign new roles to the user.
He means that they need to revoke all the roles currently assigned to the user.
@Astik: In answer to this bit:Quote:My question is how I can check whether roles got changed. What will be the condition to check this before revoking and assigning new roles to the user?
The easiest way I can think of to compare the two sets of roles would be to load each of them into a nested table and just compare the collections. [url=http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#BABCBFHA
Example[/url]
|
|
|