Roles and Priveleges in Oracle Database!!!

Creating a Role
To create a role, you must have CREATE ROLE system privileges.

The syntax for creating a role is:
CREATE ROLE role_name[ NOT IDENTIFIED IDENTIFIED {BY password USING [schema.] package EXTERNALLY GLOBALLY } ;

For example:
CREATE ROLE test_role;

This first example creates a role called test_role.

CREATE ROLE test_roleIDENTIFIED BY test123;

This second example creates the same role called test_role, but now it is password protected with the password of test123.

Grant Privileges (on Tables) to Roles

Privilege Description
Select Ability to query the table with a select statement.
Insert Ability to add new rows to the table with the insert statement.
Update Ability to update rows in the table with the update statement.
Delete Ability to delete rows from the table with the delete statement.
References Ability to create a constraint that refers to the table.
Alter Ability to change the table definition with the alter table statement.
Index Ability to create an index on the table with the create index statement.

The syntax for granting privileges on a table is:
grant privileges on object to role_name

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a role named test_role, you would execute the following statement:

grant select, insert, update, delete on suppliers to test_role;

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to test_role;

Revoke Privileges (on Tables) to Roles
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:

revoke privileges on object from role_name;

Dropping a Role
It is also possible to drop a role. The syntax for dropping a role is:

DROP ROLE role_name;

For example:
DROP ROLE test_role;

2 system privileges :SYSDBA & SYSOPER

SYSDBA and SYSOPER
Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.
The following are the operations that are authorized by the SYSDBA and SYSOPER system privileges:
System Privilege Operations Authorized SYSDBA Perform

1. STARTUP and SHUTDOWN operations
2. ALTER DATABASE: open, mount, back up, or change character set
3. CREATE DATABASE
4. CREATE SPFILE
5. ARCHIVELOG and RECOVERY
6. Includes the RESTRICTED SESSION privilege

Effectively, this system privilege allows a user to connect as user SYS.

SYSOPER Perform

1. STARTUP and SHUTDOWN operations
2. CREATE SPFILE
3. ALTER DATABASE OPEN/MOUNT/BACKUP
4. ARCHIVELOG and RECOVERY
5. Includes the RESTRICTED SESSION privilege

This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.
The manor in which you are authorized to use these privileges depends upon the method of authentication that you choose to use.
When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.

SYS and SYSTEM DBA role

SYS
When any database is created, the user SYS is automatically created and granted the DBA role.

All of the base tables and views for the database's data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by Oracle. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.)

Ensure that most database users are never able to connect using the SYS account.

SYSTEM
When a database is created, the user SYSTEM is also automatically created and granted the DBA role.

The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools. Never create in the SYSTEM schema tables of interest to individual users.

The DBA Role
A predefined role, named DBA, is automatically created with every Oracle database. This role contains most database system privileges. Therefore, it is very powerful and should be granted only to fully functional database administrators.

The DBA role does not include the SYSDBA or SYSOPER system privileges. These are special administrative privileges that allow an administrator to perform basic database administration tasks, such as creating the database and instance startup and shutdown