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.

Create the view_analyst group. You can see the created group using the sys.groups system catalog table.

SQL

CREATE GROUP "view_analyst";

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 the SELECT privilege on the database to the group.

SQL

GRANT SELECT ON DATABASE "test" TO GROUP "view_analyst";

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

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

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.

Create the test_user user.

SQL

 CREATE USER test_user PASSWORD='**';

Add the user to the view_analyst group.

SQL

ALTER GROUP "view_analyst" ADD USER test_user;

Create the view view that selects five generated rows.

SQL

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

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=**;

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

Text

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

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

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

Verify that the user can select the data in the view view.

SQL

SELECT * FROM "test"."view";

Output

Text

c1
-----------
1
2
3
4
5

Fetched 5 rows

Data Control Language (DCL) Statement Reference System Catalog Generate Tables Using sys.dummy

​Grant Privileges

​Verify Privileges

​Related Links

Grant Privileges

Verify Privileges

Related Links