Oracle/PLSQL: Roles
This Oracle tutorial explains 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 role is a set or group of privileges that can be granted to users or another role. This is a great way for database administrators to save time and effort.
Create Role
You may wish to create a role so that you can logically group the users' permissions. Please note that to create a role, you must have CREATE ROLE system 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 means that the role is immediately enabled. No password is required to enable the role.
IDENTIFIED
It means that a user must be authorized by a specified method before the role is enabled.
BY password
It means that a user must supply a password to enable the role.
USING package
It means that you are creating an application role - a role that is enabled only by applications using an authorized package.
EXTERNALLY
It means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.
GLOBALLY
It means that a user must be authorized by the enterprise directory service to enable the role.
Note:
· If both NOT IDENTIFIED and IDENTIFIED are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.
Example
Let's look at an example of how to create a role in Oracle.
For example:
CREATE ROLE test_role;
This first example creates a role called test_role.
CREATE ROLE test_role
IDENTIFIED BY test123;
This second example creates the same role called test_role, but now it is password protected with the password of test123.
Grant TABLE Privileges to Role
Once you have created the role in Oracle, your next step is to grant privileges to that role.
Just as you granted privileges to users, you can grant privileges to a role. Let's start with granting table privileges to a role. Table privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.
Syntax
The syntax for granting table privileges to a role 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 perform SELECT statements on the table. |
|
INSERT |
Ability to perform 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 perform ALTER TABLE statements to change the table definition. |
|
INDEX |
Ability to create an index on the table with the create index statement. |
|
ALL |
All privileges on table. |
object
The name 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 look at some examples of how to grant table privileges to a role in Oracle.
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 run the following GRANT 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 Table Privileges from Role
Once you have granted table privileges to a role, 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, INDEX, or ALL.
Syntax
The syntax for revoking table privileges from a role 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 perform 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 perform ALTER TABLE statements to change the table definition. |
|
INDEX |
Ability to create an index on the table with the create index statement. |
|
ALL |
All privileges on table. |
object
The name 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 name of the role that will have these privileges revoked.
Example
Let's look at some examples of how to revoke table 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 called 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 ability to EXECUTE these functions and procedures.
Syntax
The syntax for granting EXECUTE privileges on a function/procedure to a role in Oracle is:
GRANT EXECUTE ON object TO role_name;
EXECUTE
The ability to compile the function/procedure and the ability to execute the function/procedure directly.
object
The name of the database object that you are granting privileges for. In the case of granting EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.
role_name
The name of the role that will be granted the EXECUTE privileges.
Example
Let's look at an example of how to grant EXECUTE privileges on a function or procedure to a role in Oracle.
For example, if you had a function called Find_Value and you wanted to grant EXECUTE access to the role 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 function or procedure to a role, you may need 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 function or procedure from a role in Oracle is:
REVOKE execute ON object FROM role_name;
EXECUTE
Revoking the ability to compile the function/procedure and the ability to execute the function/procedure directly.
object
The name of the database object that you are revoking privileges for. In the case of revoking EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.
role_name
The name of the role that will have the EXECUTE privileges revoked.
Example
Let's look at an example of how to grant EXECUTE privileges on a function or procedure to a role in Oracle.
If you wanted to revoke EXECUTE privileges on a function called Find_Value from a role 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 need to grant the role to specific users.
Syntax
The syntax to grant a role to a user in Oracle is:
GRANT role_name TO user_name;
role_name
The name of the role that you wish to grant.
user_name
The name of the user that will be granted the role.
Example
Let's look at an example of how to grant a role to a user in Oracle:
GRANT test_role TO smithj;
This example would grant the role called test_role to the user named smithj.
Enable/Disable Role (Set Role Statement)
To enable or disable a role for a current session, you can use the SET ROLE statement.
When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.
Syntax
The syntax for the SET ROLE statement in Oracle is:
SET ROLE
( role_name [ IDENTIFIED BY password ] | ALL [EXCEPT role1, role2, ... ] | NONE );
role_name
The name of the role that you wish to enable.
IDENTIFIED BY password
The password for the role to enable it. If the role does not have a password, this phrase can be omitted.
ALL
It means that all roles should be enabled for this current session, except those listed in EXCEPT.
NONE
Disables all roles for the current session (including all default roles).
Example
Let's look at an example of how to enable a role in Oracle.
For example:
SET ROLE test_role IDENTIFIED BY test123;
This example would enable the role called test_role with a password of test123.
Set role as DEFAULT Role
A default role means that the role is always enabled for the current session at logon. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT ROLE, you need to issue the ALTER USER statement.
Syntax
The syntax for setting 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 name of the user whose role you are setting as DEFAULT.
role_name
The name of the role that you wish to set as DEFAULT.
ALL
It means that all roles should be enabled as DEFAULT, except those listed in EXCEPT.
NONE
Disables all roles as DEFAULT.
Example
Let's look at an example of how to set a role as a DEFAULT ROLE in Oracle.
For example:
ALTER USER smithj
DEFAULT ROLE
test_role;
This example would set the role called test_role as a DEFAULT role for the user 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 example would set all roles assigned to smithj as DEFAULT, except for the role called test_role.
Drop Role
Once a role has been created in Oracle, you might at some point need to drop the role.
Syntax
The syntax to drop a role in Oracle is:
DROP ROLE role_name;
role_name
The name of the role that is to be dropped.
Example
Let's look at an example of how to drop a role in Oracle.
For example:
DROP ROLE test_role;
This DROP statement would drop the role called test_role that we defined earlier.
--
--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
출처: 인터넷어딘가;
'DB > sql' 카테고리의 다른 글
| datafile size 줄이는 방법 (0) | 2015.05.24 |
|---|---|
| nolog 하는 이유 (0) | 2015.04.08 |
| 현재 접속한 계정 소유의 테이블명을 모두 조회하고 싶을 때 (0) | 2015.03.01 |
| 1장 select (0) | 2015.02.14 |
| 오라클 sqlplus 접속 관련 명령어 및 에러해결법 (0) | 2015.02.14 |
