Exploring securing vector similarity searches with Real Application Security

In this post, I want to explore how you can use Real Application Security to provide access controls for vectors in a vector store in Oracle Database 23ai.

I’m going to use the vector store we created in the last post as an example. If you want to follow along, you should follow that one first to create and populate your vector store, then come back here.

You should have a vector store table called MOBY_DICK_500_30 that you created in that previous post. You can connect to Oracle using SQLcl or SQL*Plus or whatever tool you prefer and check the structure of that table:

SQL> describe moby_dick_500_30

Name         Null?       Type
____________ ___________ ____________________________
ID           NOT NULL    RAW(16 BYTE)
TEXT                     CLOB
METADATA                 JSON
EMBEDDING                VECTOR(768,FLOAT32,DENSE)

Let’s observe that that metadata column contains the document structure information from the loaders that we used. If we filter for Chapter 12, we can see there are 13 vectors associated with that chapter:

SQL> select metadata from moby_dick_500_30 where metadata like '%CHAPTER 12.%';

METADATA
__________________________________________________________________________________
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}
{"Header 1":"MOBY-DICK; or, THE WHALE.","Header 2":"CHAPTER 12. Biographical."}

13 rows selected.

We are going to use this metadata to filter access to the vectors.

Set up permissions

Let’s start by setting up the necessary permissions. You will need to run this as the SYS user:

alter session set container=freepdb1;
grant create session, xs_session_admin to vector;
exec sys.xs_admin_util.grant_system_privilege('provision', 'vector', sys.xs_admin_util.ptype_db);
grant create role to vector;
exec sys.xs_admin_util.grant_system_privilege('admin_sec_policy', 'vector', sys.xs_admin_util.ptype_db);
exec sys.xs_admin_util.grant_system_privilege('ADMIN_ANY_SEC_POLICY', 'vector', sys.xs_admin_util.ptype_db);

Great! Now let’s set up Real Application Security. We will run the rest of these commands as the VECTOR user.

Let’s start by creating a RAS role named role1:

exec sys.xs_principal.create_role(name => 'role1', enabled => true);

Now, we will create a user named user1 and add grant them role1 and connect privileges:

exec  sys.xs_principal.create_user(name => 'user1', schema => 'vector');
exec  sys.xs_principal.set_password('user1', 'pwd1');
exec  sys.xs_principal.grant_roles('user1', 'XSCONNECT');
exec  sys.xs_principal.grant_roles('user1', 'role1');

Let’s also create a regular database role and give it access to the vector store table:

create role db_emp;
grant select, insert, update, delete on vector.moby_dick_500_30 to db_emp;

Grant DB_EMP to the application roles, so they have the required object privileges to access the table:

grant db_emp to role1;

Next, we want to create a security class, and include the predefined DML security class:

begin
  sys.xs_security_class.create_security_class(
    name        => 'moby_privileges',
    parent_list => xs$name_list('sys.dml'),
    priv_list   => xs$privilege_list(xs$privilege('view_moby_dick')));
end;

Now we can create an ACL (access control list) which will grant the privileges for the policy that we will define in a moment:

declare 
  aces xs$ace_list := xs$ace_list(); 
begin
  aces.extend(1);
  aces(1) := xs$ace_type(
     privilege_list => xs$name_list('select'),
     principal_name => 'USER1');
  
  sys.xs_acl.create_acl(
    name  => 'moby_acl',
    ace_list  => aces,
    sec_class => 'moby_privileges');
end;

Ok, nearly there! Finally, let’s define the security policy and apply it to the table:

declare
  realms xs$realm_constraint_list := xs$realm_constraint_list();      
begin  
  realms.extend(1);
 
  -- Filter based on column value
  realms(1) := xs$realm_constraint_type(
    realm    => 'metadata LIKE ''%CHAPTER 12.%''',
    acl_list => xs$name_list('moby_acl'));

  sys.xs_data_security.create_policy(
    name                   => 'moby_policy',
    realm_constraint_list  => realms);
    
  sys.xs_data_security.apply_object_policy(
    policy => 'moby_policy',
    schema => 'vector',
    object =>'moby_dick_500_30');
end;

Ok, that’s it!

Now, you may have noticed we did not give ourselves any permissions, so if we try to query that vector store table now, you’ll see it appears empty!

SQL> select count(*) from moby_dick_500_30;

   COUNT(*)
___________
          0

But, if we reconnect with the application user (user1) that we defined, and do the same query, we will see those 13 records for Chapter 12:

SQL> connect user1/pwd1
Connected.
SQL> select count(*) from moby_dick_500_30;

   COUNT(*)
___________
         13

So there you have it! We can define policies to easily control access to vectors. In this example we used the metadata to create the filtering rules, of course you could create whatever kind of rules you need.

This allows you to have a vector store which can be easily filtered for different users (or roles), essentially creating a virtual private vector store. You might want to allow ‘customer-support’ role access a certain subset of vectors for example, but your ‘supervisor’ role to access a larger set (or all) of the vectors.

What’s great about this, is that the security is enforced in the database itself. When an AI Assistant, chatbot, MCP client, etc., performs a vector search, they will only ever be able to get back results from the vectors that the user is allowed to see. The database will never send vectors to users which they are not allowed to see. So you don’t have to worry about trusting the LLM not to make a mistake and give out the wrong data, because it will literally never see the data in the first place.