# oceanbase-security

Part of **OCEANBASE**

<!-- intent-backlink:auto -->

> 💡 **Path Selection**: This skill is one implementation path for [Secure database access and protect data](../../intent/oceanbase-secure-access/SKILL.md). If you're unsure which path to take, check the routing skill first.

# OceanBase Security and Data Protection

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| Query Schema Privileges | Synchronous | Retrieve privilege information for database schemas. |
| Query Table Privileges | Synchronous | Access privilege details for specific tables. |
| Query User Privileges | Synchronous | Examine the privileges assigned to database users. |
| Configure Password Policy | Synchronous | Set and validate password complexity and security requirements. |
| Encrypt and Decrypt Data | Synchronous | Encrypt and decrypt data using DBMS_CRYPTO functions. |
| Encrypt Data | Synchronous | Apply encryption functions to secure sensitive data. |
| Check Table Encryption Status | Synchronous | Verify whether specific tables have encryption enabled. |
| Check Tablespace Encryption Status | Synchronous | Verify encryption status for database tablespaces. |

## API Calling Patterns

### Authentication
OceanBase Security APIs do not require external API keys or tokens. Authentication is handled through standard database user credentials when connecting to the OceanBase instance.

- Use your database username and password when establishing a connection via JDBC, ODP, or MySQL protocol clients.
- No special headers or environment variables are needed for these system view and PL/SQL package calls.
- Ensure your database user has appropriate privileges (e.g., SELECT on `information_schema` or `v$` views, EXECUTE on `DBMS_CRYPTO`).

### Service Endpoint
These APIs are accessed directly through SQL queries executed on your OceanBase database instance.

- No HTTP endpoints are involved — all operations are performed as SQL statements.
- Connect to your OceanBase tenant using standard database connection methods (MySQL protocol or Oracle mode).
- Queries run against system schemas like `information_schema`, dynamic performance views (`v$`), or built-in packages (`DBMS_CRYPTO`).

### Synchronous Query Pattern
All security and data protection functions in OceanBase follow a synchronous calling pattern:

1. Establish a database connection using your preferred client (MySQL CLI, JDBC, etc.)
2. Execute a SELECT statement against system views (`information_schema.SCHEMA_PRIVILEGES`, `v$encrypted_tables`, etc.) or call PL/SQL functions (`DBMS_CRYPTO.ENCRYPT`)
3. Process the immediate result set returned by the database
4. Handle any SQL errors according to standard database error handling practices

No polling, streaming, or asynchronous mechanisms are required — results are returned immediately with the query response.

## Parameter Reference

### Configure Password Policy

| Parameter | Type | Required | Default | Constraints | Description |
|-----------|------|----------|---------|-------------|-------------|
| Default value | string | false | LOW | - | The default setting for the password policy is LOW. |
| Value range | string | false | - | LOW, MEDIUM | Valid values are LOW and MEDIUM. LOW performs basic checks; MEDIUM includes additional checks such as length, character composition, and resemblance to username. |
| Effective scope | string | false | - | GLOBAL | The policy applies globally across the system. |
| Involved in serialization | boolean | false | - | Yes, No | Indicates whether the parameter is included in data serialization. |

### Encrypt and Decrypt Data

| Parameter | Type | Required | Default | Constraints | Description |
|-----------|------|----------|---------|-------------|-------------|
| data / src | RAW | true | - | - | The data to be encrypted or decrypted. |
| key | RAW | true | - | - | The encryption/decryption key used with the algorithm. |
| algorithm / typ | PLS_INTEGER | true | - | - | Specifies the encryption algorithm to use (e.g., ENCRYPT_AES128). |
| iv | RAW | false | NULL | - | Optional initialization vector for block cipher modes like CBC. |

## Code Examples

### Query Schema Privileges - SQL - all

```sql
SELECT GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE, IS_GRANTABLE 
FROM information_schema.SCHEMA_PRIVILEGES;
```

### Query Table Privileges - SQL - all

```sql
SELECT GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, IS_GRANTABLE 
FROM information_schema.TABLE_PRIVILEGES;
```

### Encrypt Data Using DBMS_CRYPTO - SQL - all

```sql
DBMS_CRYPTO.ENCRYPT(src IN RAW, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL) RETURN RAW;
```

### Decrypt Data Using DBMS_CRYPTO - SQL - all

```sql
SELECT DBMS_CRYPTO.DECRYPT(encrypted_data, cipher_type, decryption_key, iv) FROM table_name;
```

### Check Table Encryption Status - SQL - all

```sql
SELECT TABLE_NAME, ENCRYPTIONALG, ENCRYPTED, STATUS 
FROM v$encrypted_tables;
```

### Check Tablespace Encryption Status - SQL - all

```sql
SELECT TABLESPACE_ID, ENCRYPTIONALG, ENCRYPTED, STATUS 
FROM v$encrypted_tablespaces;
```

### Query User Privileges - SQL - all

```sql
SELECT GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE 
FROM information_schema.USER_PRIVILEGES;
```

## Response Format

```json
{
  "GRANTEE": "user@host",
  "TABLE_SCHEMA": "database_name",
  "PRIVILEGE_TYPE": "SELECT",
  "IS_GRANTABLE": "YES"
}
```

**Key Fields**:
- `GRANTEE` — The user account that has been granted the privilege
- `TABLE_SCHEMA` / `TABLE_NAME` — The database object the privilege applies to
- `PRIVILEGE_TYPE` — The type of privilege (SELECT, INSERT, UPDATE, etc.)
- `IS_GRANTABLE` — Whether the grantee can grant this privilege to others
- `ENCRYPTIONALG` — The encryption algorithm used (e.g., AES128)
- `ENCRYPTED` — Whether the object is currently encrypted (YES/NO)
- `STATUS` — Current encryption status (e.g., ENCRYPTED, DECRYPTED)

## Error Handling

This domain does not define specific error codes beyond standard SQL error handling. Common issues include:

- **Insufficient privileges**: User lacks SELECT permission on system views or EXECUTE on DBMS_CRYPTO
- **Invalid parameters**: Incorrect algorithm type or key length for encryption functions
- **Data type mismatches**: Passing non-RAW data to encryption functions

Handle errors using standard database exception handling in your application code.

## Environment Requirements

- Connect to an OceanBase database instance (version 4.0 or later recommended)
- Database user must have appropriate privileges:
  - SELECT on `information_schema` tables for privilege queries
  - SELECT on `v$` views for encryption status checks
  - EXECUTE on `DBMS_CRYPTO` package for encryption/decryption
- For password policy configuration, user needs ALTER SETTINGS or equivalent administrative privileges

## FAQ

Q: How do I check if a specific table is encrypted?
A: Query the `v$encrypted_tables` view and filter by `TABLE_NAME`. If the table appears in the result with `ENCRYPTED = 'YES'`, it is encrypted.

Q: What encryption algorithms does OceanBase support?
A: OceanBase supports industry-standard algorithms through DBMS_CRYPTO, including AES128, AES192, and AES256. The exact algorithm is specified using constants like `ENCRYPT_AES128`.

Q: Can I query privileges for a specific user?
A: Yes, add a WHERE clause to your query on `information_schema` privilege tables, e.g., `WHERE GRANTEE = 'user@host'`.

Q: Do I need special permissions to use DBMS_CRYPTO?
A: Yes, your database user needs EXECUTE privilege on the DBMS_CRYPTO package to use encryption and decryption functions.

Q: Are there any costs associated with using these security features?
A: No, all these security features are included with your OceanBase database license and do not incur additional usage charges.

## Pricing & Billing

### Billing Model
Free — All security and data protection features described in this skill are included with your OceanBase database license at no additional cost.

### Free Tier
Included with OceanBase license; no additional cost for querying system tables or using built-in cryptographic functions.

### Usage Limits
No specific quotas mentioned; subject to general database usage limits based on your tenant and resource pool configuration.

### Billing Notes
Querying system tables like `information_schema.SCHEMA_PRIVILEGES` and using built-in packages like `DBMS_CRYPTO` are part of core database functionality and not billed separately.