Object-Type Level Privileges Management

You can assign privileges at different levels of granularity on system and database objects to control access for users in the  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.
1
Create the view_analyst group. You can see the created group using the sys.groups system catalog table.
SQL
CREATE GROUP "view_analyst";
CREATE GROUP "view_analyst";
﻿
2
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";
GRANT VIEW VIEW ON DATABASE "test" TO GROUP "view_analyst";
﻿
3
Grant the SELECT privilege on the database to the group.
SQL
GRANT SELECT ON DATABASE "test" TO GROUP "view_analyst";
GRANT SELECT ON DATABASE "test" TO GROUP "view_analyst";
﻿
4
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';
SELECT privilege, privilege_target, object_type, 
    grantee, grantor 
FROM sys.privileges 
WHERE grantee = 'view_analyst' and grantor != 'view_analyst';
﻿
Output
Text
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
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.
1
Create the test_user user.
SQL
 CREATE USER test_user PASSWORD='**';
 CREATE USER test_user PASSWORD='**';
﻿
2
Add the user to the view_analyst group.
SQL
ALTER GROUP "view_analyst" ADD USER test_user;
ALTER GROUP "view_analyst" ADD USER test_user;
﻿
3
Create the view view that selects five generated rows.
SQL
CREATE VIEW "test"."view" AS (SELECT * FROM sys.dummy5);
CREATE VIEW "test"."view" AS (SELECT * FROM sys.dummy5);
﻿
4
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=**;
connect to jdbc:ocient://10.10.1.1:4050/test;user=test_user;password=**;
﻿
5
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';
SELECT privilege, privilege_target, object_type, grantee, grantor 
FROM sys.privileges 
WHERE grantee = 'test_user' AND grantor != 'test_user';
﻿
Output
Text
privilege                                    privilege_target                             object_type                                  grantee                                      grantor                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Fetched 0 rows
Warning: No data found
privilege                                    privilege_target                             object_type                                  grantee                                      grantor                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Fetched 0 rows
Warning: No data found
﻿
6
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;
SHOW VIEWS;
﻿
Output
Text
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
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
﻿
7
Verify that the user can select the data in the view view.
SQL
SELECT * FROM "test"."view";
SELECT * FROM "test"."view";
﻿
Output
Text
c1         
-----------
1 
2 
3 
4 
5 

Fetched 5 rows
c1         
-----------
1 
2 
3 
4 
5 

Fetched 5 rows
﻿

Related Links
﻿Data Control Language (DCL) Statement Reference﻿ 
﻿System Catalog 
﻿Generate Tables Using sys.dummy﻿ 
﻿
Result Set Caching
Authentication Methods