Mastering Oracle’s Native RBAC: A Comprehensive Guide – Part 2
4. Advanced Oracle’s Native RBAC Concepts
Oracle’s RBAC implementation offers advanced features that provide additional flexibility and granularity in managing access control. Let’s explore some of these advanced concepts.
4.1 Role Hierarchies
Role hierarchies allow you to establish parent-child relationships between roles. A child role has the same privileges as its parent role. The child role also receives any additional privileges given specifically to it. This enables the creation of a hierarchical structure of roles, simplifying the management of complex access control policies.
To create a role hierarchy in Oracle, you use the GRANT statement to grant a role to another role. For example:
-- Create a parent role named "manager"
CREATE ROLE manager;
-- Grant privileges to the "manager" role
GRANT SELECT, INSERT, UPDATE ON employees TO manager;
-- Create a child role named "sales_manager"
CREATE ROLE sales_manager;
-- Grant the "manager" role to the "sales_manager" role
GRANT manager TO sales_manager;
-- Grant additional privileges specific to the "sales_manager" role
GRANT SELECT ON sales TO sales_manager;
In this example, we create a parent role named “manager” and grant it privileges on the “employees” table. We then create a child role named “sales_manager” and grant the “manager” role to it. The sales manager has all the same privileges as a manager, plus extra privileges related to sales.
Role hierarchies simplify privilege management by setting basic privileges at higher levels and customizing them at lower levels. This reduces redundancy and makes it easier to maintain and update access control policies.
4.2 Secure Application Roles
Typically, these conditions depend on the successful execution of a PL/SQL package or function.
To make a secure application role in Oracle, use the CREATE ROLE statement with the IDENTIFIED USING clause. This package or function specifies which role should be enabled. For example:
-- Create a PL/SQL package to check conditions
CREATE OR REPLACE PACKAGE security_pkg IS
FUNCTION check_access RETURN BOOLEAN;
END security_pkg;
/
-- Create a secure application role
CREATE ROLE secure_role IDENTIFIED USING security_pkg.check_access;
-- Grant privileges to the secure application role
GRANT SELECT ON sensitive_data TO secure_role;
In this example, we create a PL/SQL package named “security_pkg” that contains a function “check_access”. This function determines whether the secure application role should be activated. It considers factors such as the user’s IP address, the current time, and specific application rules.
Next, we make a safe role called “secure_role” using the IDENTIFIED USING clause and “security_pkg.check_access” specification. function. The role is only enabled when the “check_access” function returns TRUE.
We give special access to the secure app role, so users with the role and meeting requirements can see sensitive data.
Secure app roles enhance overall security measures by activating roles only when certain conditions are met, adding extra security. This stops unauthorized access and adds an extra layer of control beyond the usual role-based access methods.
4.3 Fine-Grained Access Control
Fine-grained access control in Oracle’s native RBAC lets you decide who can access data based on specific conditions or attributes in detail. This means you can control access to data at an extremely granular level.
You can specify exactly who has permission to view or modify certain data. FGAC gives you the ability to restrict access based on specific criteria or attributes. Oracle offers tools like VPD and OLS for precise access control in databases.
VPD lets you add security rules to SQL queries for a table or view. These policies can enforce row-level security based on user attributes or application context. For example:
-- Create a VPD policy function
CREATE OR REPLACE FUNCTION policy_func (
schema_var IN VARCHAR2,
table_var IN VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
RETURN 'department_id = SYS_CONTEXT(''USERENV'', ''DEPARTMENT_ID'')';
END;
/
-- Apply the VPD policy to a table
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_POLICY',
function_schema => 'HR',
policy_function => 'POLICY_FUNC',
statement_types => 'SELECT,UPDATE,DELETE',
update_check => TRUE
);
END;
/
In this example, we create a VPD policy function named “policy_func.” This function generates a rule based on the user’s department ID. The function returns a condition that ensures users can only access records that belong to their own department.
Next, we use the VPD policy on the “EMPLOYEES” table with the DBMS_RLS.ADD_POLICY procedure. We specify the policy function and the types of statements it applies to.
The VPD policy allows users to access and change records that match their department ID. This gives precise control over row-level access.
Oracle Label Security (OLS) is another feature that enables fine-grained access control based on data classification labels. OLS lets you label data rows and control access based on user’s labels. This is particularly useful in environments with sensitive data that requires strict confidentiality and data segregation.
Fine-grained access control enhances RBAC by adding extra security levels. It lets you enforce access rules based on specific data attributes or conditions.
Attribute-Based Access Control (ABAC)
ABAC is a way to control access by considering user attributes, resource attributes, and the environment to determine access permissions. ABAC provides a more dynamic and flexible approach compared to traditional RBAC.
In ABAC, access control policies are defined based on attributes rather than roles. Attributes are characteristics that can include user details, resource details, and environmental details.
User details may include job title or department. Resource details may include data classification or sensitivity level. Environmental details may include time of day or location.
Oracle supports ABAC through various features and technologies, such as Oracle Entitlements Server (OES) and Oracle Access Manager (OAM). These solutions allow you to define attribute-based policies and enforce them across different applications and resources.
Here’s an example of how ABAC can be implemented using Oracle Entitlements Server:
-- Define user attributes
CREATE ATTRIBUTE USER_ATTRIBUTES.JOB_TITLE VARCHAR(255);
CREATE ATTRIBUTE USER_ATTRIBUTES.DEPARTMENT VARCHAR(255);
CREATE ATTRIBUTE USER_ATTRIBUTES.SECURITY_CLEARANCE VARCHAR(255);
-- Define resource attributes
CREATE ATTRIBUTE RESOURCE_ATTRIBUTES.CLASSIFICATION VARCHAR(255);
CREATE ATTRIBUTE RESOURCE_ATTRIBUTES.SENSITIVITY_LEVEL VARCHAR(255);
-- Define an ABAC policy
CREATE POLICY ACCESS_POLICY
GRANT VIEW ON RESOURCE
WHERE RESOURCE_ATTRIBUTES.CLASSIFICATION = 'CONFIDENTIAL'
AND USER_ATTRIBUTES.SECURITY_CLEARANCE >= 'SECRET'
AND USER_ATTRIBUTES.DEPARTMENT = 'FINANCE';
In this example, we talk about users’ job titles and departments, as well as resources’ classifications and sensitivity levels.
We have a policy called “ACCESS_POLICY.” This policy only allows users with ‘SECRET’ security clearance and in the ‘FINANCE’ department to view ‘CONFIDENTIAL’ resources.
ABAC policies can be more complex and include multiple attributes and conditions. The policy evaluation engine examines user, resource, and environment details to determine whether access should be granted.
ABAC complements RBAC by providing a more fine-grained and dynamic approach to access control. It allows for more flexible and context-aware access control decisions based on a combination of attributes.
6. Access Control Lists (ACLs)Access Control Lists (ACLs) are another mechanism for controlling access to resources. ACLs are used to specify permissions for individual users or groups on specific objects or resources.
In Oracle, ACLs are often used to manage access to outside network resources like web services or remote databases. Oracle provides a built-in ACL mechanism through the DBMS_NETWORK_ACL_ADMIN package.
Here’s an example of how to create an ACL and grant permissions using the DBMS_NETWORK_ACL_ADMIN package:
-- Create an ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'my_acl.xml',
description => 'ACL for accessing external web service',
principal => 'HR_USER',
is_grant => TRUE,
privilege => 'connect'
);
END;
/ -- Assign the ACL to a network host BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'my_acl.xml',
host => 'www.example.com',
lower_port => 80,
upper_port => 80
);
END;
/
In this example, we create an ACL named “my_acl.xml” using the DBMS_NETWORK_ACL_ADMIN.CREATE_ACL procedure. We specify the principal (user or role) that the ACL applies to, in this case, ‘HR_USER’. We also set the permission to ‘connect’, which allows the principal to establish a connection to the external resource.
Next, we assign the ACL to a specific network host using the DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL procedure. We specify the ACL name, the host name or IP address, and the port range to which the ACL applies.
With this ACL in place, the ‘HR_USER’ will be able to connect to the specified network host on the designated port range.
ACLs help control access to external resources in detail, working alongside the database’s own access control mechanisms. They are helpful for managing network resources and making sure only approved users or apps can use them.
This concludes Part 2. In the last Part 3 we’re going to cover the Roles in more detail.
Looking for professional database security solutions? Join us online for a comprehensive demo session on User and Roles management in DataSunrise, offering advanced solutions for Oracle databases. Discover how to streamline access control and enhance security with our intuitive platform.
