SQL/MX 3.2 Installation and Upgrade Guide (H06.25+, J06.14+)

?CATALOG_NAME
from definition_schema_version_3100.col_privileges as col_privileges
where col_privileges.grantor != -2
and not exists
( select
col_privileges2.table_uid,col_privileges2.grantor,col_privileges2.grantee,
col_privileges2.privilege_type,col_privileges.column_number
from definition_schema_version_3100.col_privileges as col_privileges2
where
(col_privileges.grantor = col_privileges2.grantee
or
col_privileges2.grantee = -1
)
and
col_privileges2.table_uid = col_privileges.table_uid
and
col_privileges.privilege_type = col_privileges2.privilege_type
and
col_privileges.column_number = col_privileges2.column_number
and
col_privileges2.is_grantable = 'Y'
);
insert into $$COMMANDS_TABLE$$
select distinct
'REVOKE ' ||
case privilege_type
when 'D' then 'DELETE'
when 'E' then 'EXECUTE'
when 'I' then 'INSERT'
when 'R' then 'REFERENCE'
when 'S' then 'SELECT'
else 'UPDATE'
end ||
case grants.column_number
when -1 then ''
else '(' || trim( cols.column_name ) || ')'
end ||
' ON ' ||
trim( catsys.cat_name ) ||
'.' ||
trim( schemata.schema_name ) ||
'.' ||
trim( objects.object_name ) ||
' FROM "' ||
user( grantee ) ||
'";'
from $$GRANTS_TABLE$$ as grants,
nonstop_sqlmx_$$SYSTEM$$.system_schema.schemata as schemata,
nonstop_sqlmx_$$SYSTEM$$.system_schema.catsys as catsys,
definition_schema_version_3100.cols as cols,
definition_schema_version_3100.objects as objects
where grants.catalog_name = ucase( ?CATALOG_NAME )
and
schemata.cat_uid = catsys.cat_uid
and
objects.schema_uid = schemata.schema_uid
and
objects.object_uid = grants.table_uid
and
cols.object_uid = grants.table_uid
and
(grants.column_number = -1 OR cols.column_number =
grants.column_number);
-- End Repeat for every catalog on the system...
-- Execute the following three statements only after repeating the above loop
-- for every catalog in the system...
log $$COMMANDS_FILE$$ clear;
select * from $$COMMANDS_TABLE$$;
56 Removing security administrator grants