---
title: Object-Type Level Privileges Management
slug: R9wk-object
docTags: 
createdAt: Tue Feb 25 2025 15:13:11 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:27 GMT+0000 (Coordinated Universal Time)
---

You can assign privileges at different levels of granularity on system and database objects to control access for users in the Ocient® System. The levels are:

- All objects of the specified object type in the system
- All objects of the specified object type in the database
- Specific object

This series of workflows shows how you can manage access to the `VIEW` privilege for all views (where a view is the object type) in a specified database.

# Grant Privileges

This workflow creates a group and grants the `VIEW` privilege to all views and the ability to read the views in the database to the group.

::::WorkflowBlock
:::WorkflowBlockItem
Create the `view_analyst` group. You can see the created group using the `sys.groups` system catalog table.

```sql
CREATE GROUP "view_analyst";
```
:::

:::WorkflowBlockItem
Grant the `VIEW` privilege for views on the `test` database to the `view_analyst` group.

```sql
GRANT VIEW VIEW ON DATABASE "test" TO GROUP "view_analyst";
```
:::

:::WorkflowBlockItem
Grant the `SELECT` privilege on the database to the group.

```sql
GRANT SELECT ON DATABASE "test" TO GROUP "view_analyst";
```
:::

:::WorkflowBlockItem
View the granted privileges using the `sys.privileges` system catalog table. The  Ocient System grants the `USE` privilege on the database implicitly.

```sql
SELECT privilege, privilege_target, object_type, 
    grantee, grantor 
FROM sys.privileges 
WHERE grantee = 'view_analyst' and grantor != 'view_analyst';
```

*Output*

```none
privilege                                    privilege_target                             object_type                                  grantee                                      grantor                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
VIEW                                         VIEW                                         DATABASE                                     view_analyst                                 admin@system 
USE                                          NULL                                         DATABASE                                     view_analyst                                 admin@system 
SELECT                                       NULL                                         DATABASE                                     view_analyst                                 admin@system 

Fetched 3 rows
```
:::
::::

# Verify Privileges

This workflow creates a user, assigns this user to the created group, and creates a view with generated data. The workflow shows the information that the user has access to in the database based on the granted privileges.

::::WorkflowBlock
:::WorkflowBlockItem
Create the `test_user` user.

```sql
 CREATE USER test_user PASSWORD='**';
```
:::

:::WorkflowBlockItem
Add the user to the `view_analyst` group.

```sql
ALTER GROUP "view_analyst" ADD USER test_user;
```
:::

:::WorkflowBlockItem
Create the `view` view that selects five generated rows.

```sql
CREATE VIEW "test"."view" AS (SELECT * FROM sys.dummy5);
```
:::

:::WorkflowBlockItem
Connect to the database at the `10.10.1.1` IP address with port number `4050` as the `test_user` user.

```shell
connect to jdbc:ocient://10.10.1.1:4050/test;user=test_user;password=**;
```
:::

:::WorkflowBlockItem
View the privileges of the user using the `sys.privileges` system catalog table. This user has no privileges of their own.

```sql
SELECT privilege, privilege_target, object_type, grantee, grantor 
FROM sys.privileges 
WHERE grantee = 'test_user' AND grantor != 'test_user';
```

*Output*

```none
privilege                                    privilege_target                             object_type                                  grantee                                      grantor                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Fetched 0 rows
Warning: No data found
```
:::

:::WorkflowBlockItem
See the metadata for views that the user has access to within this database. The user has access to the `test` view only.

```sql
SHOW VIEWS;
```

*Output*

```none
table_catalog                                table_schema                                 table_name                                   view_definition                              created_at                   updated_at                   creator_id                           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test                                         test                                         view                                         select * from sys.dummy5                     2025-02-19 14:53:53.513978712 2025-02-19 14:53:53.513978712 NULL 

Fetched 1 row
```
:::

:::WorkflowBlockItem
Verify that the user can select the data in the `view` view.

```sql
SELECT * FROM "test"."view";
```

*Output*

```none
c1         
-----------
1 
2 
3 
4 
5 

Fetched 5 rows
```
:::
::::



# Related Links

[Data Control Language (DCL) Statement Reference](<./././SQL Reference/Data Control Language _DCL_ Statement Reference.md>)&#x20;

[System Catalog](https://docs.ocient.com/system-catalog)&#x20;

[Generate Tables Using sys.dummy](<./././Query Ocient/General SQL Syntax/Generate Tables Using sys_dummy.md>)&#x20;



