Skip to navigation
Database AdministrationManage Users, Groups, and Roles

Object-Type Level Privileges Management

View as Markdown

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.

1

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

CREATE GROUP "view_analyst";
2

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

GRANT VIEW VIEW ON DATABASE "test" TO GROUP "view_analyst";
3

Grant the SELECT privilege on the database to the group.

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.

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

Output

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.

 CREATE USER test_user PASSWORD='**';
2

Add the user to the view_analyst group.

ALTER GROUP "view_analyst" ADD USER test_user;
3

Create the view view that selects five generated rows.

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.

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.

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

Output

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.

SHOW VIEWS;

Output

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.

SELECT * FROM "test"."view";

Output

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

Related Links

Data Control Language (DCL) Statement Reference

System Catalog

Generate Tables Using sys.dummy