CREATE ROLE

Create a user role.

Syntax:

Create role without a password:
CREATE ROLE roleNOT IDENTIFIED
Create role with a password:
CREATE ROLE role IDENTIFIED BY password
Create an application role:
CREATE ROLE role IDENTIFIED USING [schema.]package
Create role authorised by the OS:
ALTER ROLE role IDENTIFIED EXTERNALLY
Create role authorised by Directory Service:
ALTER ROLE role 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