Represents information of classes for which the current user has access authorization to a database.
Attribute Name |
Data Type |
Description |
---|---|---|
class_name |
VARCHAR(255) |
Class name |
owner_name |
VARCHAR(255) |
Name of class owner |
class_type |
VARCHAR(6) |
'CLASS' for a class, and 'VCLASS' for a virtual class |
is_system_class |
VARCHAR(3) |
'YES' for a system class, and ‘NO’ otherwise. |
partitioned |
VARCHAR(3) |
'YES' for a partitioned group class, and 'NO' otherwise. |
is_reuse_oid_class |
VARCHAR(3) |
'YES' for a REUSE_OID class, and 'NO' otherwise. |
CREATE VCLASS db_class (class_name, owner_name, class_type, is_system_class, partitioned, is_reuse_oid_class)
AS
SELECT c.class_name, CAST(c.owner.name AS VARCHAR(255)),
CASE c.class_type WHEN 0 THEN 'CLASS' WHEN 1 THEN 'VCLASS' ELSE 'UNKNOW' END,
CASE WHEN MOD(c.is_system_class, 2) = 1 THEN 'YES' ELSE 'NO' END,
CASE WHEN c.sub_classes IS NULL THEN 'NO' ELSE NVL((SELECT 'YES' FROM _db_partition p WHERE p.class_of = c and p.pname IS NULL), 'NO') END,
CASE WHEN MOD(c.is_system_class / 8, 2) = 1 THEN 'YES' ELSE 'NO' END
FROM _db_class c
WHERE CURRENT_USER = 'DBA' OR
{c.owner.name} SUBSETEQ (
SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{t.g.name}), SET{})
FROM db_user u, TABLE(groups) AS t(g)
WHERE u.name = CURRENT_USER) OR
{c} SUBSETEQ (
SELECT SUM(SET{au.class_of})
FROM _db_auth au
WHERE {au.grantee.name} SUBSETEQ(
SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{t.g.name}), SET{})
FROM db_user u, TABLE(groups) AS t(g)
WHERE u.name = CURRENT_USER) AND au.auth_type = 'SELECT');
The following example shows how to retrieve classes owned by the current user.
SELECT class_name
FROM db_class
WHERE owner_name = CURRENT_USER;
class_name
======================
'stadium'
'code'
'nation'
'event'
'athlete'
'participant'
'olympic'
'game'
'record'
'history'
'female_event'
Note All examples of system catalog classes have been written in the csql utility. In this example, the user option is omitted (if omitted, the default user is PUBLIC). If not otherwise specified, --no-auto-commit (No auto-commit mode) and -u (Specify the user dba) options are used.
% csql --no-auto-commit -u dba demo
The following example shows how to retrieve virtual classes that can be accessed by the current user.
SELECT class_name
FROM db_class
WHERE class_type = 'VCLASS';
class_name
======================
'db_stored_procedure_args'
'db_stored_procedure'
'db_partition'
'db_trig'
'db_auth'
'db_index_key'
'db_index'
'db_meth_file'
'db_meth_arg_setdomain_elm'
'db_meth_arg'
'db_method'
'db_attr_setdomain_elm'
'db_attribute'
'db_vclass'
'db_direct_super_class'
'db_class'
The following example shows how to retrieve system classes that can be accessed by the current user user (PUBLIC user).
SELECT class_name
FROM db_class
WHERE is_system_class = 'YES' AND class_type = 'CLASS'
ORDER BY 1;
class_name
======================
'db_authorization'
'db_authorizations'
'db_root'
'db_serial'
'db_user'