SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Installation and Management Guide—523723-004
8-61
Displaying All Privileges for a Column
Displaying All Privileges for a Column
This example displays all privileges for the column EMPNUM in the table
SAMDBCAT.PERSNL.EMPLOYEE:
>>set schema samdbcat.definition_schema_version_1200;
--- SQL operation complete.
>> select grantor as grantor_id,
case (grantor_type)
when 'S' then 'System'
when 'U' then 'User'
else 'Unknown'
end as grantor_t,
grantee as grantee_id,
case (grantee_type)
when 'P' then 'Public'
when 'U' then 'User'
else 'Unknown'
end as grantee_t,
case (privilege_type)
when 'S' then 'Select'
when 'I' then 'Insert'
when 'D' then 'Delete'
when 'U' then 'Update'
when 'R' then 'Refer'
else 'Unknown'
end as prvtype,
is_grantable as grantable
from col_privileges cp, cols c, objects o
where cp.table_uid = o.object_uid
and c.object_uid = o.object_uid
and c.column_number = cp.column_number
and c.column_name = 'EMPNUM'
and o.object_name = 'EMPLOYEE'
and o.object_type = 'BT'
and o.schema_uid =
(select schema_uid
from nonstop_sqlmx_figaro.system_schema.schemata
where
schema_name = 'PERSNL' and
cat_uid =
(select cat_uid
from nonstop_sqlmx_figaro.system_schema.catsys
where cat_name = 'SAMDBCAT'
)
)
for read uncommitted access;
GRANTOR_ID GRANTOR_T GRANTEE_ID GRANTEE_T PRVTYPE GRANTABLE
---------- --------- ----------- --------- ------- ---------
-2 System 65535 User Refer Y
-2 System 65535 User Update Y