Assigning users and groups administrative permission for database-based security

Database-based security takes the security settings of the host database and uses these settings to protect the shared repository.

Before you begin

Make sure you are operating on the RSBI objects.

About this task

To determine if a user has administrator authorization, QMF calls for the contents of RSBI.ADMIN_VIEW and runs the following SQL statement:

SELECT COUNT(*) FROM RSBI.ADMIN_VIEW WHERE AUTHID=:OWNER

Where OWNER is the user login. If this statement returns the value that is greater than zero, the user is the repository administrator.

The database-based security for shared repositories requires SYSADM authority on Db2® and DBA authority on Oracle. Without this authority users cannot administer the repository.

For Db2 databases you can populate the RSBI.AUTHID_TABLE with PRIMARY_IDs and SECONDARY_IDs pairs of users and groups, where the group does have SYSADM authority.

Results

The ADMIN_VIEW will have the list from SYSIBM.SYSUSERAUTH with the addition of the PRIMARY_IDs from the AUTHID_TABLE, whose SECONDARY_IDs are also in the list from SYSIBM.SYSUSERAUTH.

Example

The following examples illustrate the QMF authorization processes on different server types:

Db2 for z/OS®

CREATE VIEW "RSBI"."ADMIN_VIEW" (
"AUTHID")
AS
SELECT A."GRANTEE" AS "AUTHID"
FROM "SYSIBM"."SYSUSERAUTH" A
WHERE A."SYSADMAUTH" IN ('G', 'Y')
UNION
SELECT A."PRIMARY_ID" AS "AUTHID"
FROM "RSBI"."AUTHID_TABLE" A
INNER JOIN
"SYSIBM"."SYSUSERAUTH" B ON
B."GRANTEE" = A."SECONDARY_ID"

DB2 for iSeries

CREATE VIEW "RSBI"."ADMIN_VIEW" (
"AUTHID")
AS
SELECT "AUTHID"
FROM "RSBI"."ADMIN_TABLE"

Db2 LUW

CREATE VIEW "RSBI"."ADMIN_VIEW" (
"AUTHID")
AS
SELECT A."GRANTEE" AS "AUTHID"
FROM "SYSIBM"."SYSDBAUTH" A
WHERE A."DBADMAUTH" = 'Y'
UNION
SELECT A."PRIMARY_ID" AS "AUTHID"
FROM "RSBI"."AUTHID_TABLE" A
INNER JOIN
"SYSIBM"."SYSDBAUTH" B ON
B."GRANTEE" = A."SECONDARY_ID"

DB2 for z/VM®

CREATE VIEW "RSBI"."ADMIN_VIEW" (
"AUTHID")
AS
SELECT A."NAME" AS "AUTHID"
FROM "SQLDBA"."SYSUSERLIST" A
WHERE A."AUTHOR" = ''
AND A."DBAAUTH" = 'Y'
UNION
SELECT A."PRIMARY_ID" AS "AUTHID"
FROM "RSBI"."AUTHID_TABLE" A
INNER JOIN
"SQLDBA"."SYSUSERLIST" B ON
B."NAME" = A."SECONDARY_ID"

Oracle

CREATE VIEW "RSBI"."ADMIN_VIEW" (
"AUTHID")
AS
SELECT A."GRANTEE" AS "AUTHID"
FROM "SYS"."DBA_ROLE_PRIVS" A
WHERE A."GRANTED_ROLE" = 'DBA'