데이터베이스 내에서 현재 사용자가 접근 권한을 가진 가상 클래스들에 대해 그 SQL 정의문을 보여준다.
속성명 |
데이터 타입 |
설명 |
---|---|---|
vclass_name |
VARCHAR (255) |
가상 클래스명 |
vclass_def |
VARCHAR 4096) |
가상 클래스의 SQL 정의문 |
CREATE VCLASS db_vclass (vclass_name, vclass_def)
AS
SELECT q.class_of.class_name, q.spec
FROM _db_query_spec q
WHERE CURRENT_USER = 'DBA' OR
{q.class_of.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
{q.class_of} 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');
다음 예제에서는 가상 클래스 ‘db_class’의 SQL 정의문을 검색한다.
SELECT vclass_def
FROM db_vclass
WHERE vclass_name = 'db_class';
'SELECT c.class_name, CAST(c.owner.name AS VARCHAR(255)), CASE c.class_type WHEN 0 THEN 'CLASS' WHEN 1 THEN 'VCLASS' WHEN 2 THEN 'PROXY' 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 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')'