Roles creation issue in oracle database

[Ainsley]

Sponsored Links

I am new to database , I am having a small assignment in my course .The assignment is like that..Assume you are the DBA of a large international banking concern (sites in three countries). You have just taken over the position and found that there is currently no roles used in the database. What roles do you need to create? Consider job function, location, and any other criteria you can identify.
They are asking the role alone not the code in oracle , so please help me to solve the Roles creation issue in oracle database.

[Reegan]

The role is a powerful and secure feature of oracle database.It is a group of privileges that can be granted to one or more than one users and role obviously.

If you are working in an organization as a DBA and you are responsible to create and manage the database user. In a particular organization, there are so many employees are working on different posts and their needs of data usage are also different.It is not so easy to write the grant statement for every user according to their accessibility. As a DBA you need to categorize the user in different sections or departments and after that you can create and use the role and easily grant the permission to every users of database.

[opaper]

Creating Role in database:
If you are not working as a DBA,then you need to have the privileges to create the role .

The syntax for creating role:

CREATE ROLE_NAME 
[ NOT IDENTIFIED |
IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;

ROLE_NAME - identifies the name of the Role.

NOT IDENTIFIED means the role is enabled and no need for password to enable it.

IDENTIFIED means the role creator(user) must be authorized by particular method before enabling the role and the respective options are given like by PASSWORD, EXTERNALLY and GLOBALLY.

[MindSpace]

After the creation,you need to grant some privileges to the role.Suppose, I have different posts in organization like manager and executive. I will create a role named " MANAGER_ROLE".
Now, its time to assign some privileges to that created role. I want to grant only SELECT, INSERT AND UPDATE PRIVILEGE to the manager of every managers.That would be as follows :

CREATE ROLE MANAGER_ROLE
IDENTIFIED BY MAN123;

Granting the privilege to the role:

GRANT SELECT,INSERT,UPDATE ON <Object_name> TO MANAGER_ROLE;

[Modifier]

Now, you can easily grant the created role to one or more users of the database.The syntax would be as follows:

GRANT name_of_role TO name_of_user;

The implementation of the role would be as follows for SCOTT user:

GRANT MANAGER_ROLE to scott;