Limit table scope

Learn how to limit what tables in your database have models generated for them on application layers.

Limiting the scope of tables

Often times we don't want to create a new class for every single table in our database. Sometimes a table is no longer used, has pieces of information we don't need exposed to our application layers, or other reasons altogether. So, what should we do to prevent our applications from creating those tables or schemas as classes?

The answer comes in the way of creating a new database role that limits how much access it is given.

We require a new database role and a new base to be created when limiting access. If we were to alternatively allow an exception list be sent to our API then it could easily be avoided by consuming users. We want to encourage strict usage when it comes to data.

When not all tables are needed you can create a new database Role that limits what tables are actually accessible.

Database roles

Creating a new database role will require a level of priviege on your database that allows the action to occur, so ask your administrator. By doing this you are simply creating another username/password pair that can access your database but explicitly telling that login what exact tables it *can* access.

Postgres

If you are using a Postgres database then refer to the following steps on how to create a new user, limited_user, that only has access to the users table in your public schema.

-- Create the role
CREATE ROLE limited_user WITH LOGIN PASSWORD 'password';

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO limited_user;

-- Grant select and insert privileges on the table
GRANT SELECT, INSERT ON TABLE public.users TO limited_user;

MySQL

Similarly to Postgres, reference the SQL statements below to create a new user, limited_user, that only is granted access to the users table in your public schema.

-- Create the user
CREATE USER 'limited_user'@'localhost' IDENTIFIED BY 'password';

-- Grant select and insert privileges on the table
GRANT SELECT, INSERT ON public.users TO 'limited_user'@'localhost';

-- Apply the changes
FLUSH PRIVILEGES;

Microsoft SQL Server

With MSSQL, reference the following statements to reate a new user, limited_user, that is only allowed access to the users table in the public schema.

-- Create the user
CREATE LOGIN limited_user WITH PASSWORD = 'password';
USE your_database;

-- Create the user in the database
CREATE USER limited_user FOR LOGIN limited_user;

-- Grant select and insert privileges on the table
GRANT SELECT, INSERT ON OBJECT::public.users TO limited_user;

SQLite

Unlike the other relational databases such as Postgres, MySQL, and Microsoft SQL Server; SQLite does not offer the ability to create new user roles for tables. If this is a requirement you must have you may want to look into migrating your database to a provider that can handle it.

Create a new Base

You will need an Outerbase account to complete this step. After you have created an account you can reference the section Connect a data source to see how to create a new Base in your Outerbase workspaces.

NOTE: Make sure you use the newly created credentials above when asked for your database credentials while creating a new base.

Generate a new API key

Finally, with your new database roles committed to your database and a new Base created you can continue to follow the steps to fetch your API token.