CREATE ROLE

Create a user role.

Syntax:

Create role without a password:
   CREATE ROLE role NOT 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

"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


© Copyright SS64.com 1999-2014
Some rights reserved