CREATE ROLE

Create a user role.

Syntax:

   CREATE ROLE role_name [NOT IDENTIFIED]
   CREATE ROLE role_name [IDENTIFIED BY password]
   CREATE ROLE role_name [IDENTIFIED EXTERNALLY]
   CREATE ROLE role_name [IDENTIFIED GLOBALLY]

Example
--Create the role
CREATE ROLE MY_ORACLE_ROLE

--Assign all object rights from the current user schema (user_objects)

spool GrantRights.sql

select
decode(
object_type,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON'||&OWNER||'.',
'VIEW','GRANT SELECT ON '||&OWNER||'.',
'SEQUENCE','GRANT SELECT ON '||&OWNER||'.',
'PROCEDURE','GRANT EXECUTE ON '||&OWNER||'.',
'PACKAGE','GRANT EXECUTE ON '||&OWNER||'.',
'FUNCTION','GRANT EXECUTE ON'||&OWNER||'.' )||object_name||' TO MY_ORACLE_ROLE ;' 
from user_objects 
WHERE
OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE','FUNCTION'
)
ORDER BY OBJECT_TYPE

spool off
@GrantRights.sql

"A man's gotta know his limitations." - Clint Eastwood, as Dirty Harry

Related Commands:

PROFILE - ALTER PROFILE
ROLE - ALTER ROLE
ROLE - SET ROLE
ROLE - DROP ROLE

Related Views:

                                           USER_RESOURCE_LIMITS
 DBA_RGROUP
 DBA_ROLES
 DBA_ROLE_PRIVS                            USER_ROLE_PRIVS      ROLE_ROLE_PRIVS
 DBA_SYS_PRIVS                             USER_SYS_PRIVS       ROLE_SYS_PRIVS