Home » SQL & PL/SQL » SQL & PL/SQL » How to chech whether roles got changed to an user
How to chech whether roles got changed to an user [message #432699] Wed, 25 November 2009 17:02 Go to next message
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 #432700 is a reply to message #432699] Wed, 25 November 2009 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>if assigned roles to an user are changed,
I am not clear exactly what is meant by statement above.
It could mean that the roles assigned to a USER are changed or
it could mean that the privs assigned to a ROLE got changed.

>then we need to revoke all the roles from the user and assign new roles to the user.
By default Oracle does not know or care which ROLES are assigned to which USERs.
Who/what maintains this relationship?
If the relationship between USERs & ROLEs does not change, then what is gained by REVOKE & GRANT?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
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 Go to previous messageGo to next message
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 #432703 is a reply to message #432702] Wed, 25 November 2009 18:20 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>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.

so if I do the following:

GRANT AP_CLERK TO SCOTT;

Your procedure will do the following:

REVOKE AP_CLERK FROM SCOTT;
GRANT AP_CLERK TO SCOTT;

why?
never confuse movement with progress.
Going around in circles is movement,
but most folks would not consider it to be progress.
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 Go to previous message
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]

Previous Topic: Problems in Updating Table
Next Topic: Copy/Ammend/Insert Row based on ID
Goto Forum:
  


Current Time: Wed Sep 28 21:12:13 CDT 2016

Total time taken to generate the page: 0.14991 seconds