10 Column Level Security

Column Level Security (CLS) controls which columns a user can read from Oracle AI Data Platform Workbench tables and views when using supported Spark-based access paths.

Column Level Security is used when different users, roles, or groups should see different subsets of columns in the same table or view. Column level security is only supported for SELECT access only.

Use column level security when you need to restrict read access to sensitive columns while still allowing access to permitted columns in the same dataset. If User1 has access to name and id in a table named employee with name, ID, and email, reads return only authorized columns, while direct access to email results in an error.

Key concepts

  • Column Level Security policy: A rule that controls SELECT access to specific columns in AI Data Platform Workbench table.
  • Principals: CLS can be assigned or revoked for AI Data Platform Workbench Role, IAM user, and IAM group.
  • Supported execution surfaces: Spark SQL, Python/Scala DataFrame, and Dataset APIs are in scope. RDD and DStream are not supported.

How It Fits in the Platform

Column Level Security is defined on AI Data Platform Workbench tables and views and enforced when users access those tables or views through supported Spark execution paths like Spark SQL, Python/Scala DataFrame/Dataset APIs from notebook cells, workflows, and column visibility in the Table page through Master catalog UI or API.

Common Use Cases

  • Restrict sensitive columns in a shared table while allowing users to query permitted columns.
  • Grant or revoke column access for an IAM user, IAM group, or an AI Data Platform Workbench role.
  • Enforce column visibility consistently across Spark SQL, notebook DataFrame/Dataset usage, workflows, and table-page column views.

Example: Column Level Security

As as an example, a given table named Table1 has columns C1, C2, C3. User1 has access to columns C1 and C2.

When the user runs the following code:

df = spark.read.table("cat1.schema1.table1")

The result of this code includes only C1 and C2.

If the user runs the following code:

df = spark.read.table("cat1.schema1.table1").select("C1")

The result of this code includes only C1.

If the user runs the following code:

df = spark.read.table("cat1.schema1.table1").select("C3")

The result of this code is an error: "One or more referenced columns are either not found in the accessible schema or the current user does not have access to them."

If a user has SELECT permission on the parent Catalog or Schema, then Column Level Security is not applicable on the table and the user will have access to all the columns.

Best Practices

  • Use CLS for SELECT access control only
  • Validate CLS through supported execution paths: Spark SQL and Python/Scala DataFrame/Dataset APIs.
  • Do not use RDD, DStream, forColumn Level Security scenarios.
  • Keep policies aligned to roles or groups where possible, then use IAM user assignment for narrower cases.

Troubleshooting

  • Querying an unauthorized column returns an error.
  • Query only columns assigned to the user RDD or DStream access does not enforce CLS as expected

Limitations

Column renames on the base table are not automatically handled for dependent views.

Example:

CREATE VIEW v1 AS SELECT c1, c2 FROM t1;

If the columns in the underlying table are renamed like this:

ALTER TABLE t1 RENAME COLUMN c1 TO c1_new;

The view definition still refers to the old column name c1 even though AI Data Platform Workbench updates column policies for the renamed columns on the base table. AI Data Platform Workbench does not update or reconcile the dependent view definitions and view-level column policies. As a result, queries against the view may fail because the view is still based on the old column names.

Altering a view to point to a new table with different column names does not update existing view column grants

Example:

CREATE VIEW v1 AS SELECT c1, c2 FROM t1;

Assume column-level grants or policies already exist on view columns c1 and c2. Later, the view altered:

ALTER VIEW v1 AS SELECT * FROM t2;

If t2 exposes different column names, for example x1, x2 instead of c1, c2, the existing grants or policies on the view are still tied to the old view column names c1 and c2. AI Data Platform does not automatically remap, drop, or recreate those view-column policies when the view definition changes.

Scala display(df) and dataframe to Json function do not work with tables where column level security is enabled.

Create Table Column Level Permissions

You can control which users and roles have access to tables you own.

  1. On the home page, click Master Catalog.
  2. Navigate to your table, then click the Permissions tab.
  3. Click New permission icon New Permission.
  4. Select Column level permission as the permission type.
  5. Select the principle type from the drop-down menu.
  6. Select whether to add the user by user name or OCID.
    • For User name, click Search and enter a user name. Select the user from the list.
    • For Enter OCID, enter the OCID of the user.
  7. Select the columns you want to grant permission to for the selected principle.
  8. Click Create.

    Note:

    If the secured tables are connected to an active compute cluster, restart the compute cluster to ensure column level security is enforced.