Using Roles in Oracle

This article is written about how to create roles, grant/revoke privileges to roles, enable/disable roles, set roles as the default, and drop roles in Oracle with syntax and examples.

Description

A position is a set or crew of privileges that can be granted to users or every other role. This is a outstanding way for database directors to keep time and effort.

Create Role

You may additionally wish to create a position so that you can logically crew the users’ permissions. Please be aware that to create a role, you need to have CREATE ROLE device privileges.

Syntax

The syntax for creating a role in Oracle is:

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

role_name The name of the new role that you are creating. This is how you will refer to the grouping of privileges. NOT IDENTIFIED It capacity that the function is at once enabled. No password is required to allow the role. IDENTIFIED It potential that a person have to be authorized via a unique method before the position is enabled. BY password It ability that a person have to grant a password to allow the role. USING package It capacity that you are growing an software role – a function that is enabled only by using purposes the use of an licensed package. EXTERNALLY It means that a consumer should be licensed by way of an exterior service to allow the role. An external carrier can be an operating machine or third-party service. GLOBALLY It capability that a person must be licensed via the business enterprise listing provider to enable the role.

Note

If both NOT IDENTIFIED and IDENTIFIED are overlooked in the CREATE ROLE statement, the position will be created as a NOT IDENTIFIED role.

Example

Let’s seem at an instance of how to create a position in Oracle.

For example:

CREATE ROLE test_role;

This first example creates a function called test_role.

CREATE ROLE test_role
IDENTIFIED BY test123;

This 2nd example creates the same function referred to as test_role, but now it is password blanketed with the password of test123.

Grant TABLE Privileges to Role

Once you have created the position in Oracle, your next step is to furnish privileges to that role.

Just as you granted privileges to users, you can grant privileges to a role. Let’s start with granting desk privileges to a role. Table privileges can be any mixture of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax

The syntax for granting desk privileges to a function in Oracle is:

GRANT privileges ON object TO role_name

privileges The privileges to assign to the role. It can be any of the following values: Privilege Description SELECT Ability to operate SELECT statements on the table. INSERT Ability to perform INSERT statements on the table. UPDATE Ability to function UPDATE statements on the table. DELETE Ability to perform DELETE statements on the table. REFERENCES Ability to create a constraint that refers to the table. ALTER Ability to function ALTER TABLE statements to alternate the desk definition. INDEX Ability to create an index on the desk with the create index statement. ALL All privileges on table. object The identify of the database object that you are granting privileges for. In the case of granting privileges on a table, this would be the table name. role_name The name of the role that will be granted these privileges.

Example

Let’s appear at some examples of how to supply desk privileges to a function in Oracle.

For example, if you desired to supply SELECT, INSERT, UPDATE, and DELETE privileges on a desk known as suppliers to a position named test_role, you would run the following GRANT statement:

GRANT select, insert, update, delete ON suppliers TO test_role;

You can additionally use the ALL key-word to indicate that you wish all permissions to be granted. For example:

GRANT all ON suppliers TO test_role;

Revoke Table Privileges from Role

Once you have granted table privileges to a role, you may additionally want to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any aggregate of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax

The syntax for revoking desk privileges from a function in Oracle is:

REVOKE privileges ON object FROM role_name;

privileges The privileges to revoke from the role. It can be any of the following values: Privilege Description SELECT Ability to perform SELECT statements on the table. INSERT Ability to operate INSERT statements on the table. UPDATE Ability to perform UPDATE statements on the table. DELETE Ability to perform DELETE statements on the table. REFERENCES Ability to create a constraint that refers to the table. ALTER Ability to function ALTER TABLE statements to change the table definition. INDEX Ability to create an index on the desk with the create index statement. ALL All privileges on table. object The identify of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name. role_name The identify of the function that will have these privileges revoked.

Example

Let’s seem to be at some examples of how to revoke desk privileges from a role in Oracle.

For example, if you wanted to revoke DELETE privileges on a table called suppliers from a role named test_role, you would run the following REVOKE statement:

REVOKE delete ON suppliers FROM test_role;

If you wanted to revoke ALL privileges on the table referred to as suppliers from a role named test_role, you could use the ALL keyword. For example:

REVOKE all ON suppliers FROM test_role;

Grant Function/Procedure Privileges to Role

When dealing with functions and procedures, you can grant a role the capacity to EXECUTE these functions and procedures.

Syntax

The syntax for granting EXECUTE privileges on a function/procedure to a position in Oracle is:

GRANT EXECUTE ON object TO role_name;

EXECUTE The capacity to collect the function/procedure and the capability to execute the function/procedure directly. object The title of the database object that you are granting privileges for. In the case of granting EXECUTE privileges on a characteristic or procedure, this would be the characteristic title or the technique name. role_name The name of the function that will be granted the EXECUTE privileges.

Example

Let’s appear at an example of how to provide EXECUTE privileges on a function or process to a role in Oracle.

For example, if you had a feature called Find_Value and you desired to supply EXECUTE get right of entry to to the position named test_role, you would run the following GRANT statement:

GRANT execute ON Find_Value TO test_role;

Revoke Function/Procedure Privileges from Role

Once you have granted EXECUTE privileges on a characteristic or manner to a role, you can also want to revoke these privileges from that role. To do this, you can execute a REVOKE command.

Syntax

The syntax for the revoking privileges on a feature or system from a role in Oracle is:

REVOKE execute ON object FROM role_name;

EXECUTE Revoking the capability to assemble the function/procedure and the capability to execute the function/procedure directly. object The identify of the database object that you are revoking privileges for. In the case of revoking EXECUTE privileges on a characteristic or procedure, this would be the function identify or the process name. role_name The identify of the function that will have the EXECUTE privileges revoked.

Example

Let’s seem at an example of how to provide EXECUTE privileges on a feature or procedure to a function in Oracle.

If you wanted to revoke EXECUTE privileges on a feature called Find_Value from a function named test_role, you would run the following REVOKE statement:

REVOKE execute ON Find_Value FROM test_role;

Grant Role to User

Now, that you’ve created the role and assigned the privileges to the role, you’ll want to furnish the function to specific users.

Syntax

The syntax to supply a position to a user in Oracle is:

GRANT role_name TO user_name;

role_name The title of the position that you want to grant. user_name The title of the user that will be granted the role.

Example

Let’s appear at an instance of how to supply a role to a person in Oracle:

GRANT test_role TO smithj;

This instance would provide the position called test_role to the consumer named smithj.

Enable/Disable Role (Set Role Statement)

To enable or disable a position for a modern-day session, you can use the SET ROLE statement.

When a consumer logs into Oracle, all default roles are enabled, however non-default roles must be enabled with the SET ROLE statement.

Syntax

The syntax for the SET ROLE assertion in Oracle is:

SET ROLE
( role_name [ IDENTIFIED BY password ] | ALL [EXCEPT role1, role2, ... ] | NONE );

role_name The title of the function that you want to enable. IDENTIFIED BY password The password for the function to enable it. If the role does not have a password, this phrase can be omitted. ALL It skill that all roles need to be enabled for this modern session, barring those listed in EXCEPT. NONE Disables all roles for the modern session (including all default roles).

Example

Let’s appear at an example of how to allow a position in Oracle.

For example:

SET ROLE test_role IDENTIFIED BY test123;

This instance would enable the position called test_role with a password of test123.

Set role as DEFAULT Role

A default position capacity that the position is continually enabled for the current session at logon. It is not essential to trouble the SET ROLE statement. To set a position as a DEFAULT ROLE, you want to issue the ALTER USER statement.

Syntax

The syntax for placing a role as a DEFAULT ROLE in Oracle is:

ALTER USER user_name
DEFAULT ROLE
( role_name | ALL [EXCEPT role1, role2, ... ] | NONE );

user_name The identify of the person whose position you are setting as DEFAULT. role_name The title of the function that you want to set as DEFAULT. ALL It potential that all roles have to be enabled as DEFAULT, without these listed in EXCEPT. NONE Disables all roles as DEFAULT.

Example

Let’s appear at an instance of how to set a role as a DEFAULT ROLE in Oracle.

For example:

ALTER USER smithj
DEFAULT ROLE
test_role;

This instance would set the role known as test_role as a DEFAULT role for the consumer named smithj.

ALTER USER smithj
DEFAULT ROLE
ALL;

This example would set all roles assigned to smithj as DEFAULT.

ALTER USER smithj
DEFAULT ROLE
ALL EXCEPT test_role;

This instance would set all roles assigned to smithj as DEFAULT, barring for the function known as test_role.

Drop Role

Once a role has been created in Oracle, you may at some factor need to drop the role.

Syntax

The syntax to drop a role in Oracle is:

DROP ROLE role_name;

role_name The title of the position that is to be dropped.

Example

Let’s appear at an instance of how to drop a position in Oracle.

For example:

DROP ROLE test_role;

This DROP statement would drop the function known as test_role that we described earlier.