Oracle Database Roles are a feature that bundles multiple system privileges and object privileges into a single unit for management. This eliminates the effort of granting detailed privileges to individual users, realizing operational efficiency and improved security. This article explains the mechanism of roles, followed by practical steps for creating, granting, and verifying them.
Conclusion / Shortest Path (To-Do List)
The basic flow of role operation is as follows:
- Create a role (
CREATE ROLE) - Grant privileges to the role (
GRANT ... TO role) - Grant the role to a user (
GRANT role TO user) - Verify enablement (
SELECT ... FROM SESSION_ROLES)
Note: These tasks are usually performed by privileged users such as SYS or SYSTEM, or a user with the CREATE ROLE privilege.
What is an Oracle Role? Mechanism of Privilege Management
In database operations, granting “table creation privileges,” “select privileges,” and “connection privileges” individually every time a user is added is inefficient and becomes a breeding ground for configuration errors.
A Role is like a “package (bag) of privileges” that groups these together.
Image of Granting Privileges
When NOT using Roles (Not Recommended):
Because privileges are passed directly to each user, management becomes complex.
[System Privilege: CREATE SESSION] ──────┐
├─> [User: TANAKA]
[Object Privilege: SELECT on EMP] ───────┘
When using Roles (Recommended):
Put privileges into a role, and pass that role to users.
[Privilege: CREATE SESSION] ──┐
├─> [Role: APP_DEV_ROLE] ──> [User: TANAKA]
[Privilege: SELECT on EMP] ───┘ │
└──────────────> [User: SUZUKI]
Predefined Roles (Default Roles)
Oracle has representative roles provided from the beginning.
| Role Name | Characteristics and Usage |
| CONNECT | Contains only the CREATE SESSION privilege required to connect to the DB (10g R2 and later). |
| RESOURCE | A basic set for developers to create and own tables or procedures. |
| DBA | The strongest management role holding all system privileges. Do not grant this to general users lightly. |
Procedures and Implementation: From Role Creation to User Granting
Here, we will introduce the steps to create a role for application developers and assign it to a user.
Prerequisites and Environment
- Target: Oracle Database 19c (Enterprise / Standard Edition 2)
- Environment: Connection to a PDB (Pluggable Database) is recommended.
※ If created in the CDB (root container), it becomes a “Common Role” and the name must start with
c##. However, “Local Roles” specific to each PDB are standard in actual development.
1. Creation of Role
First, create an empty role. It is also possible to set a password (omitted here).
-- Syntax: CREATE ROLE <role_name>;
CREATE ROLE app_dev_role;
app_dev_role: An arbitrary role name.
2. Granting Privileges to the Role
Put the necessary privileges into the created role. Here, we permit “connection,” “table creation,” and “viewing a specific table.”
-- Grant system privileges to the role
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_dev_role;
-- Grant object privileges to the role
-- (Assuming operations are done by the owner of the SCOTT.EMP table or DBA)
GRANT SELECT, INSERT, UPDATE ON scott.emp TO app_dev_role;
- Point: It is also possible to grant another role to a role (inheritance).
3. Granting Role to User
Grant the created role to the target user (e.g., mike).
-- Grant the role to a user
GRANT app_dev_role TO mike;
4. Setting Default Role (If Necessary)
Usually, roles granted to a user are automatically enabled upon login, but this can be controlled explicitly. The following is a setting to “enable all granted roles upon login.”
ALTER USER mike DEFAULT ROLE ALL;
Method to Confirm Settings
Use data dictionary views to verify if the settings are reflected correctly.
User Side: Check Currently Effective Roles
Log in as the target user (mike) and check the valid roles in the current session.
SELECT * FROM SESSION_ROLES;
SQL> SELECT * FROM SESSION_ROLES;
ROLE
--------------------------------------------------------------------------------
RESOURCE
SODA_APP
2 rows selected.
Admin Side: Check Which Roles are Granted to Whom
Execute with a user holding DBA privileges.
-- Check roles granted to specific user
SELECT grantee, granted_role, admin_option
FROM dba_role_privs
WHERE grantee = 'MIKE';
Admin Side: Check Privileges Contained in a Role
-- Check system privileges within a role
SELECT privilege, admin_option
FROM role_sys_privs
WHERE role = 'APP_DEV_ROLE';
-- Check object privileges within a role
SELECT owner, table_name, privilege
FROM role_tab_privs
WHERE role = 'APP_DEV_ROLE';
Troubleshooting
Common errors encountered during role operation and their solutions.
| Error Code | Error Message | Cause and Solution |
| ORA-01924 | role ‘string’ not granted or does not exist | The role specified in SET ROLE etc. is not granted to that user, or does not exist. Check DBA_ROLE_PRIVS. |
| ORA-01031 | insufficient privileges | Even if you hold privileges via a role, errors may occur during Stored Procedure (PL/SQL) compilation. In PL/SQL, role privileges are disabled by default. Please grant the necessary privileges directly to the user (Direct Grant). |
| ORA-01919 | role ‘string’ does not exist | The role name you tried to delete or grant does not exist. Check for spelling errors. |
Operation, Monitoring, and Security Notes
1. Caution with WITH ADMIN OPTION
When granting a role, if you add WITH ADMIN OPTION, the user receiving the role can further grant that role to others. As this poses a risk of privilege management becoming uncontrollable, do not grant this in principle.
-- Avoid this unless absolutely necessary
GRANT app_dev_role TO mike WITH ADMIN OPTION;
2. The Trap of Stored Procedures and Roles
As mentioned in the troubleshooting section above, privileges obtained via roles are ignored when creating PL/SQL (Definer’s Rights).
- Solution: Directly
GRANTthe privileges necessary for procedure creation (e.g.,CREATE TABLEorSELECT ON table) to the user.
3. Deletion of Roles and Impact
When you delete a role (DROP ROLE), privileges are immediately revoked from all users who held that role.
-- Warning: This affects all users who have this role
DROP ROLE app_dev_role;
FAQ (Frequently Asked Questions)
Q1: What is the difference between System Privileges and Object Privileges?
A. System Privileges are “rights to perform operations on the entire DB or specific actions (e.g., table creation, session connection).” Object Privileges are “rights to manipulate specific data (e.g., viewing SCOTT’s EMP table).” Roles can contain both of these.
Q2: Are there any roles automatically granted when creating a user?
A. Basically, no. A user immediately after creation cannot even connect. You must explicitly grant GRANT CREATE SESSION or the CONNECT role containing it.
Q3: What is a password-protected role?
A. It is a feature to enhance application-specific security. Privileges do not become effective unless SET ROLE role_name IDENTIFIED BY password; is executed. It is used to prevent accidental operations by users who simply logged in via SQL*Plus, etc.
Summary
Utilizing roles in Oracle Database is the first step toward secure and efficient DB management.
- Efficiency: Package privileges and manage them collectively.
- Standardization: Understand predefined roles like
CONNECTandRESOURCE. - Points of Caution: Do not forget the specification where roles become invalid during PL/SQL creation (as of 19c).
- Principle: Create and operate with the minimum necessary set of privileges (Principle of Least Privilege).
Design appropriate roles to build a robust database environment.
This article explains concepts based on Oracle Database 19c (screens and default values may differ in other versions).
[reference]
7.2 About User Privileges and Roles
コメント