Connect & Resource Privilege

[richardana]

Hi All,
I have a group of 3000 users that I have created.
in a table called my emp_tbl. I also created 3 roles as follows.
1)select_role ......for the 2800 users( for read only purpose.

2)select_update_delete_role (150 users)
3)insert_update_delete_role. (50 users)

These users needed to be granted CONNECT, RESOURCE privilege in order or to be connected to the database.

Questions
1)How do I go by granting all the 3000 users CONNECT, RESOURCE with one script? rather doing granting them one by one.

Now that they can be connected...
How do I go by granting appropriate role to the members of the 1st, 2nd, and 3rd role?
Can you please send me a step by step guide?

Respectfully
Richard

[marist89]

1. grant connect, resource to roles you have defined.

grant connect, resource to select_role;
grant connect, resource to select_update_delete_role;
grant connect, resource to insert_update_delete_role;

2. I would add a field that has your usernames called role_name. I would then populate this field based on which role I would want the user in. Once that's done, you can create a query that generates some SQL that assigns your users.

[gd_1976]

HI,
Try this proc, It may help you. You can run this provided you have grant any privilege. Let me know whether it is useful.
CREATE OR REPLACE PROCEDURE grant_user
cursor_handle integer;
feedback integer;
err_msg varchar2(1000);
obj_name varchar2(100);
obj_type varchar2(100);
obj_preff varchar2(20);
alter_statement varchar2(1000);

cursor inv_objects_cur2 is
select usre_namefrom emp_tbl
--ur user_id as well as table name
order by user_name
begin
dbms_output.enable('10000');
open inv_objects_cur2;
loop
fetch inv_objects_cur2 into obj_name,sec_lvl;
exit when inv_objects_cur2%notfound;
--
cursor_handle:=dbms_sql.open_cursor;

alter_statement:='grant connect,resource to ' || obj_name;
dbms_output.put_line(alter_statement);
dbms_sql.parse(cursor_handle,alter_statement,dbms_sql.native);
feedback:=dbms_sql.execute(cursor_handle);
dbms_sql.close_cursor(cursor_handle);
dbms_output.put_line ( ' ' );
dbms_output.put_line(obj_name || ' granted');
end loop;
close inv_objects_cur2;
dbms_output.put_line('success');

exception
when others then
err_msg:=sqlerrm;
dbms_output.put_line('Error while granting user ' || obj_name ||' '|| err_msg);

end grant_user;
Thanks.
Gd_1976

[jmodic]

Users do not need CONNECT and/or RESOURCE roles (btw, this are *roles*, not *privileges*) to connect to the database. The only privilege they need to have to be able to connect is CREATE SESSION privilege.

CONNECT and RESOURCE roles have some privileges that vast majority of application users never need, some of which are quite powerfull. I personally never grant CONNECT, RESOURCE to eny of the users on my production databases.

As Jeff allready suggested, the easiest way would be to add the privilege directly to your application roles.

grant CREATE SESSION to select_role;
grant CREATE SESSION to select_update_delete_role;
grant CREATE SESSION to insert_update_delete_role;

[marist89]

There I go again, assuming people have a reason for doing something they probably shouldn't. Thanks for the input jmodic.

[richardana]

Thank you all for the input.
Richard

[gd_1976]

Hi Jeff and marist89,
If there is something wrong in my proc., let me know. I had the same prob. and i was able to do it in the way. The way in which i did, you could enhance the proc. in such a way that user can be created programatically. Instead of creating 3000 users. I felt it would be easy in this way.
Thanks.
Gd_1976

[marist89]

You could do it either way. If I am only going to do something once, I prefer to not write a procedure because it is more work. If I will do it more than once, I will write a script. If it becomes routine, I will write a procedure.