For the purposes of this document, the user is referred to as the Sharding/SODA user, and the user name is app_schema in the examples.

To create the Sharding/SODA user:

1. Connect to the shard catalog database (for example, as SYSDBA).
2. Enable SHARD DDL.
3. Run CREATE USER command, granting the permissions shown in the example below.

   Note that the Sharding/SODA user is created on the PDB, not the CDB.

The following is an example Sharding/SODA user creation script.

-- Set the container and create the sharded user
alter session set container=SDBPDB;
alter session enable shard ddl;
create user app_schema identified by password;
 
-- Grant basic privileges
grant connect, resource, alter session to app_schema;
grant execute on dbms_crypto to app_schema;

-- All privileges below are required. User can also be granted all privileges
grant create table, create procedure, create tablespace, create
materialized view to app_schema;
grant unlimited tablespace to app_schema;
grant select_catalog_role to app_schema;

-- Grant soda_app for this user
grant soda_app to app_schema;

-- Specific grants on shard plsql
grant execute on exec_shard_plsql to app_schema;
grant gsmadmin_role to app_schema;
grant gsm_pooladmin_role to app_schema;

Note the standard database schema privileges and the standard SODA privileges granted to the user. The exec_shard_plsql grant, which gives the user the ability to run PL/SQL procedures on a distributed database, is a sharding-specific privilege required for the Sharding/SODA user.

For more information about Oracle Globally Distributed Database schema design, including sharding user creation and running PL/SQL, see Oracle Globally Distributed Database Schema Design.