Oracle Database User Setup

This guide explains how to create a dedicated Oracle database user with the appropriate permissions for executing SQL queries and retrieving schema information in your Text-to-SQL application.

Prerequisites

• Access to an Oracle database server with administrative privileges
• SQL*Plus, Oracle SQL Developer, or another Oracle client tool
• Basic knowledge of Oracle security concepts

Creating a Database User

Using SQL*Plus

1. Connect to your Oracle database as an administrator (e.g., SYSTEM or SYS):

sqlplus system/your_admin_password@//hostname:port/service_name

2. Create a new user with a secure password:

CREATE USER texttosql_user IDENTIFIED BY "your_secure_password";

3. Grant the necessary permissions for connecting and retrieving schema information:

-- Grant basic connection privilege
GRANT CREATE SESSION TO texttosql_user;
 
-- Grant read-only access to all tables in a specific schema
-- Replace 'your_schema' with your actual schema name
GRANT SELECT ANY TABLE TO texttosql_user;
 
-- Grant access to data dictionary views for schema information
GRANT SELECT_CATALOG_ROLE TO texttosql_user;
GRANT SELECT ANY DICTIONARY TO texttosql_user;
 
-- Set the default tablespace and quota (optional)
ALTER USER texttosql_user DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
 
-- If you need the user to execute other types of queries (INSERT, UPDATE, DELETE)
-- add the appropriate permissions:
-- GRANT INSERT, UPDATE, DELETE ANY TABLE TO texttosql_user;

4. Exit SQL*Plus:

EXIT;

Using Oracle SQL Developer

1. Connect to your Oracle database using SQL Developer with administrative privileges
2. Open a new SQL worksheet
3. Execute the same SQL commands as shown in the SQL*Plus section above
4. Alternatively, you can use the graphical interface:
   • In the Connections panel, right-click on "Other Users" and select "New User"
   • Fill in the user details, including username and password
   • In the "Granted Roles" tab, select:
     • CONNECT
     • SELECT_CATALOG_ROLE
   • In the "System Privileges" tab, add:
     • SELECT ANY TABLE
     • SELECT ANY DICTIONARY
   • Click "Apply" to create the user

Verifying the Setup

To verify that the user has the correct permissions:

1. Connect to Oracle using the new user:

sqlplus texttosql_user/your_secure_password@//hostname:port/service_name

2. Try to retrieve schema information:

-- List all tables in a schema
SELECT table_name FROM all_tables WHERE owner = 'YOUR_SCHEMA';
 
-- Get column information for a specific table
SELECT column_name, data_type, data_length 
FROM all_tab_columns 
WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE';

3. Try executing a simple query:

SELECT * FROM your_schema.your_table WHERE ROWNUM <= 5;

Connection Configuration

When configuring your Text-to-SQL application, use these credentials:

• Host: Your Oracle server hostname or IP
• Port: 1521 (default Oracle port)
• Service Name: Your Oracle service name
• Username: texttosql_user
• Password: your_secure_password

Security Considerations

• Use a strong, unique password for the database user
• Consider using Oracle's Virtual Private Database (VPD) for row-level security
• Implement network encryption using Oracle Advanced Security
• Limit the user's permissions to only what is necessary
• Regularly audit the user's permissions to ensure they remain appropriate
• Store connection credentials securely and never expose them in client-side code
• Consider using a connection pool to manage database connections efficiently

Oracle-Specific Notes

• Oracle's data dictionary views (ALL_TABLES, ALL_TAB_COLUMNS, etc.) are crucial for schema retrieval
• The SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY privileges provide access to these views
• For production environments, consider creating more fine-grained permissions instead of broad grants like SELECT ANY TABLE
• Oracle's case sensitivity for object names can affect schema retrieval; the Text-to-SQL application handles this automatically