Securing Non-SQL-defined Schemas

Securing Database Resources › Securing SQL Access to Databases › Securing Non-SQL-defined Schemas

Securing Non-SQL-defined Schemas

About Non-SQL-Defined Schemas

When you secure resource type DB, you control who can create an SQL schema for a non-SQL-defined schema. Until you secure resource type DB, any user can create an SQL schema for a non-SQL-defined schema.

How to Secure Non-SQL-defined Schemas

You secure non-SQL-defined schemas by securing the DB resource.

Note: For more information, see Securing Databases.

If you secure non-SQL-defined schemas externally, you must also include an entry in the SRTT with external security information for resource type NSCH.

How to Grant USE Privilege on a Non-SQL-defined Schema

To allow a user to specify a non-SQL-defined schema when creating an SQL schema, you issue a GRANT statement on the non-SQL-defined schema specifying the USE privilege.

As a holder of SYSADMIN or DBADMIN privilege, you can specify WITH GRANT OPTION when you grant this privilege to allow the recipient to grant the same privilege to another user.

More Information:

For more information, see GRANT Non-SQL Definition Privilege.
For more information, see REVOKE Non-SQL Definition Privilege.
For more information about creating SQL schemas, see the CA IDMS SQL Reference Guide.

Granting SQL Access to Non-SQL-defined Databases

You allow SQL access to non-SQL-defined databases by creating an SQL schema for a non-SQL schema. This requires the CREATE privilege on the SQL schema and the USE privilege on the non-SQL-defined schema, as in this example:

grant create on
  schema qschtest
  to dba;

grant use on
  nonsql schema v0001.nschtest
  to dba;

The owner of the SQL schema for non-SQL schema has the authority to access the non-SQL-defined database using SQL. Other users require table access privileges.