SQL/MX 3.1 Database and Application Migration Guide (H06.23+, J06.12+)

HP NonStop SQL/MX Release 3.1 Database and Application Migration Guide—663853-001
A-1
A Removing Security
Administrator Grants
In the following steps, queries create a command file containing SQL REVOKE
statements targeting all grants that were performed by security administrators. You can
then use the command file as input to mxci to remove the grants. To effectively use
these queries, carefully read and follow the directions embedded within the comments
as some variables must be modified prior to execution. These queries must be run
against version 3100 metadata.
-- Execute all of the following from within mxci.
-- Set the pattern $$GRANTS_TABLE$$ and $$COMMANDS_TABLE$$ to two different
-- fully qualified names of non-existent tables in schemas in which the user
-- executing the queries may create tables. The catalog(s) and schema(s)
-- containing the tables must already exist.
-- Set pattern $$SYSTEM$$ to the name of the system on which the queries are
-- executing.
-- set pattern $$COMMANDS_FILE$$ to the name of an OSS file to which to write
-- the revoke commands.
set pattern $$GRANTS_TABLE$$ CAT.SCH.SA_GRANTS;
set pattern $$COMMANDS_TABLE$$ CAT.SCH.SA_COMMANDS;
set pattern $$SYSTEM$$ YOURSYS;
set pattern $$COMMANDS_FILE$$ obey.txt;
create table $$GRANTS_TABLE$$( table_uid largeint not null, grantor int,
grantee int, privilege_type char(2), column_number int, catalog_name char(
128) );
create table $$COMMANDS_TABLE$$( command varchar( 512 ) heading ' ' );
-- All of the following statements between the "--Begin Repeat" and
-- "--End Repeat" comments must be executed for every catalog on the system,
-- changing the following "set catalog"and "set param" statement arguments
-- to match the next catalog in sequence for each iteration.
-- Begin Repeat for every catalog on the system...
-- Set catalog to <catalog name>
-- Set param ?CATALOG_NAME to '<catalog name>'
set catalog YOURCAT;
set param ?CATALOG_NAME 'YOURCAT';
insert into $$GRANTS_TABLE$$
select
tbl_privileges.table_uid,tbl_privileges.grantor,tbl_privileges.grantee,
tbl_privileges.privilege_type, -1, ?CATALOG_NAME
from definition_schema_version_3100.tbl_privileges as tbl_privileges
where tbl_privileges.grantor != -2
and not exists
( select
tbl_privileges2.table_uid,tbl_privileges2.grantor,
tbl_privileges2.grantee,tbl_privileges2.privilege_type
from
definition_schema_version_3100.tbl_privileges as tbl_privileges2
where
(tbl_privileges.grantor = tbl_privileges2.grantee
or
tbl_privileges2.grantee = -1
)
and
tbl_privileges2.table_uid = tbl_privileges.table_uid
and
tbl_privileges.privilege_type = tbl_privileges2.privilege_type