How to grant privilege to a row in oracle?

@dedrick 

To grant privilege to a row in Oracle, you can use row-level security policies. Row-level security allows you to restrict access to certain rows in a table based on specified criteria.

Here is a general outline of how you can grant privilege to a row in Oracle using row-level security policies:

1. Create a function that evaluates the criteria for granting privilege to a row. This function should return a boolean value that indicates whether a user has access to the row or not.
2. Create a policy function that uses the previously created function to define the access control policy for the table.
3. Associate the policy function with the table using the dbms_rls package.
4. Grant the necessary privileges to users or roles so they can access the table and its rows based on the defined policy.

Here is an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- Creating a function to evaluate the criteria for access
CREATE OR REPLACE FUNCTION row_access_policy (p_owner VARCHAR2, p_table_name VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
  IF user = 'ADMIN' THEN
    RETURN '1=1';
  ELSE
    RETURN 'OWNER = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
  END IF;
END;
/

-- Creating a policy function for the table
BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema    => 'HR',
    object_name      => 'EMPLOYEES',
    policy_name      => 'row_policy',
    function_schema  => 'HR',
    policy_function  => 'row_access_policy',
    statement_types  => 'SELECT',
    update_check     => TRUE,
    enable           => TRUE
  );
END;
/

-- Granting necessary privileges to users or roles
GRANT SELECT ON HR.EMPLOYEES TO PUBLIC;

In this example, the row_access_policy function evaluates whether the user is the table owner or an admin. If the user is an admin, all rows are accessible. If the user is the table owner, they only have access to rows where the OWNER column matches their session user.

By setting up row-level security policies in this manner, you can grant privilege to specific rows in Oracle based on defined criteria.