DB2_資料庫角色

目的：
測試DB2資料庫角色，本指令碼摘錄自DB2安裝目錄admin_scripts/databaseroles.db2。
版本：Windows DB2 Express-C V9.7

使用者要求：
作業系統使用者:
mh with password "mh1234" in SYSADM group
john with password "john1234"
joe  with password "joe1234"
bob  with password "bob1234"
pat  with password "pat1234"

操作步驟：
使用"db2cmd db2 -t"進入互動模式,執行後續操作。

0,設定環境
CONNECT TO sample;

-- Create tables TEMP_EMPLOYEE and TEMP_DEPARTMENT under 'mh' schema.
CREATE TABLE mh.TEMP_EMPLOYEE LIKE EMPLOYEE;
CREATE TABLE mh.TEMP_DEPARTMENT LIKE DEPARTMENT;

-- Populate the above created tables with the data from EMPLOYEE & DEPARTMENT tables.
-- export the table data to file 'load_employee.ixf'.
EXPORT TO load_employee.ixf OF IXF SELECT * FROM EMPLOYEE;

-- loading data from data file inserting data into the table TEMP_EMPLOYEE.
LOAD FROM load_employee.ixf of IXF INSERT INTO mh.TEMP_EMPLOYEE;

-- export the table data to file 'load_department.ixf'.
EXPORT TO load_department.ixf OF IXF SELECT * FROM DEPARTMENT;

-- loading data from data file inserting data into the table TEMP_DEPARTMENT.
LOAD FROM load_department.ixf of IXF INSERT INTO mh.TEMP_DEPARTMENT;

1，使用ROLE授予select許可權
-- Connect to sample database using user mh.
CONNECT TO sample user mh using mh1234;

-- Grant SECADM authority to a user JOHN.
GRANT SECADM ON DATABASE TO USER john;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOHN(SECADM).
CONNECT TO sample USER john USING john1234;

-- Create role DEVELOPMENT_ROLE. Only a user with SECADM authority can create
-- the role. In this sample user JOHN is assigned SECADM authority and has
-- the privilege to create the roles.
CREATE ROLE development_role;

-- Create tables DEV_TABLE1 and DEV_TABLE2. Any user can create these tables.
-- In the sample these tables are created by user JOHN.
CREATE TABLE dev_table1 (project VARCHAR(25), dept_no INT);
INSERT INTO dev_table1 VALUES ('DB0', 1);

CREATE TABLE dev_table2 (defect_no INT, scheme_repository VARCHAR(25));
INSERT INTO dev_table2 VALUES (100, 'wsdbu');

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user mh(SYSADM).
-- User mh is system administrator(SYSADM).
CONNECT TO sample user mh using mh1234;

-- Grant SELECT privilege on tables DEV_TABLE1 and DEV_TABLE2 to role
-- DEVELOPMENT_ROLE.
GRANT SELECT ON TABLE john.dev_table1
  TO ROLE development_role;
GRANT SELECT ON TABLE john.dev_table2
  TO ROLE development_role;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOHN(SECADM).
CONNECT TO sample user john using john1234;

-- Grant role DEVELOPMENT_ROLE to users JOE and BOB.
GRANT ROLE development_role TO USER joe, USER bob;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOE.
CONNECT TO sample USER joe USING joe1234;

-- User JOE is granted DEVELOPMENT_ROLE role and hence gains select privilege
-- on the tables DEV_TABLE1 and DEV_TABLE2 through membership of this role.
-- SELECT from tables DEV_TABLE1 and DEV_TABLE2 to verify user JOHN has
-- select privileges.
SELECT * FROM john.dev_table1;
SELECT * FROM john.dev_table2;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user BOB
CONNECT TO sample USER bob USING bob1234;

-- User BOB is granted DEVELOPMENT_ROLE role and hence gains select privilege
-- on the tables DEV_TABLE1 and DEV_TABLE2 through membership of this role.
-- SELECT from tables DEV_TABLE1 and DEV_TABLE2 to verify user BOB has select
-- privileges.

SELECT * FROM john.dev_table1;
SELECT * FROM john.dev_table2;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOHN(SECADM).
CONNECT TO sample user john using john1234;

-- REVOKE the role DEVELOPMENT_ROLE from users JOE and BOB.
REVOKE ROLE development_role FROM USER joe, USER bob;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOE.
CONNECT TO sample USER joe USING joe1234;

-- The following two SELECT statements will fail. Users JOE cannot perform
-- SELECT on the tables DEV_TABLE1 and DEV_TABLE2 now. JOE has lost SELECT
-- privilege on these tables as role DEVELOPMENT_ROLE was revoked from him.

-- Error displayed for the following SELECT statement will be :
-- SQL0551N  "JOE" does not have the privilege to perform. operation "SELECT"
-- on object "JOHN.DEV_TABLE1".  SQLSTATE=42501
SELECT * FROM john.dev_table1;
!echo "Above error is expected !";

-- Error displayed for the following SELECT statement will be :
-- SQL0551N  "JOE" does not have the privilege to perform. operation "SELECT"
-- on object "JOHN.DEV_TABLE2".  SQLSTATE=42501
SELECT * FROM john.dev_table2;
!echo "Above error is expected !";

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user BOB
CONNECT TO sample USER bob USING bob1234;

-- The following two SELECT statements will fail as user BOB has lost SELECT
-- privilege on these tables as the role DEVELOPMENT_ROLE was revoked
-- from him.

-- Error displayed for the following SELECT statement will be :
-- SQL0551N  "BOB" does not have the privilege to perform. operation "SELECT"
-- on object "JOHN.DEV_TABLE1".  SQLSTATE=42501
SELECT * FROM john.dev_table1;
!echo "Above error is expected !";

-- Error displayed for the following SELECT statement will be :
-- SQL0551N  "BOB" does not have the privilege to perform. operation "SELECT"
-- on object "JOHN.DEV_TABLE2".  SQLSTATE=42501
SELECT * FROM john.dev_table2;
!echo "Above error is expected !";

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOHN.
CONNECT TO sample USER john USING john1234;

-- Grant role DEVELOPMENT_ROLE to new employee PAT.
-- Once this is done, PAT can SELECT from tables DEV_TABLE1
-- and DEV_TABLE2.
GRANT ROLE development_role TO USER pat;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user PAT.
CONNECT TO sample USER pat USING pat1234;

-- The following two SELECT statements will be successful.
SELECT * FROM john.dev_table1;
SELECT * FROM john.dev_table2;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOHN(SECADM).
CONNECT TO sample USER john USING john1234;

-- Drop the tables.
DROP TABLE dev_table1;
DROP TABLE dev_table2;

-- Drop the role. Only a user having SECADM authority can drop the role.
DROP ROLE development_role;

-- Disconnect from sample database.
CONNECT RESET;

2，用ROLE代替作業系統組
-- Connect to sample database using user JOHN.
CONNECT TO sample USER john USING john1234;

-- Create roles DEVELOPER, TESTER and SALES.
CREATE ROLE developer;
CREATE ROLE tester;
CREATE ROLE sales;

-- Grant role DEVELOPER to user BOB.
GRANT ROLE developer TO USER bob;

-- Grant role TESTER to users JOE and PAT.
GRANT ROLE tester TO USER joe, USER pat;

-- Grant role SALES to users JOE and BOB.
GRANT ROLE sales TO USER joe, USER bob;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user BOB.
CONNECT TO SAMPLE USER bob USING bob1234;

-- Create table TEMP1.
CREATE TABLE temp1 (a int);

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user mh(SYSADM)so that he can grant
-- ALTER privilege to role DEVELOPER. (DBADM can also grant ALTER privilege).
CONNECT TO SAMPLE USER mh USING mh1234;

-- Grant ALTER privilege on table TEMP1 to role DEVELOPER. mh(SYSADM)
-- (also DBADM) can grant the ALTER privilege on tables.
-- Once ALTER privilege is granted to role DEVELOPER, any user who
-- is part of role DEVELOPER can ALTER this table. So in this sample,
-- user BOB can perform. an alter operation on table TEMP1. Users belonging to
-- other roles cannot perform. alter operation on TEMP1 unless they have
-- privilege granted.
GRANT ALTER ON bob.temp1 TO ROLE developer;
CONNECT RESET;

-- The following statements show that a trigger TRG1 will be created
-- when user BOB holds the privilege through role DEVELOPER. But this is
-- not possible if user BOB is part of group DEVELOPER_G.

-- Connect to sample database using user BOB.
CONNECT TO SAMPLE USER bob using bob1234;

-- Create trigger TRG1. The following statements show that trigger TRG1 can
-- only be created by user BOB, as he holds the privilege through role
-- DEVELOPER. But this is not possible if user BOB holds the necessary
-- privileges through a group.
CREATE TRIGGER trg1 AFTER DELETE ON bob.temp1
  FOR EACH STATEMENT MODE DB2SQL INSERT INTO bob.temp1 VALUES (1);

-- Drop the table TEMP1.
DROP TABLE bob.temp1;

-- Drop the trigger TRG1.
DROP trigger trg1;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOHN(SECADM).
CONNECT TO SAMPLE USER john using john1234;

-- Drop the roles. Only a user with SECADM authority can drop the roles.
-- In this sample, user JOHN(SECADM) can only drop the roles.
DROP ROLE developer;
DROP ROLE tester;
DROP ROLE sales;

-- Disconnect from sample database.
CONNECT RESET;

3，替換資料庫物件屬主
-- Connect to sample database using user JOHN(SECADM).
CONNECT TO SAMPLE USER john USING john1234;

-- Create role EMP_DEPT_ROLE.
CREATE ROLE emp_dept_role;

-- Disconnect from sample database.
CONNECT RESET;

-- User mh(SYSADM) grants SELECT privilege on tables TEMP_EMPLOYEE and TEMP_DEPARTMENT
-- to role EMP_DEPT_ROLE.

-- Connect to sample database using user mh.
CONNECT TO sample user mh using mh1234;

GRANT SELECT ON TABLE mh.temp_employee
           TO ROLE emp_dept_role;
GRANT SELECT ON TABLE mh.temp_department
           TO ROLE emp_dept_role;

-- Disconnect from sample database.
CONNECT RESET;

-- To transfer ownership of view EMP_DEPT(created below), BOB must hold SELECT
-- privilege on table TEMP_EMPLOYEE and table TEMP_DEPARTMENT.
-- Since role EMP_DEPT_ROLE has these privileges, role EMP_DEPT_ROLE
-- is granted to user BOB using the statement, below.
-- For the TRANSFER to work, new user JOE must also hold SELECT privilege on
-- the above two tables. Hence user JOHN(SECADM) grants SELECT privilege
-- to user JOE also.
-- Connect to sample database using user JOHN(SECADM).
CONNECT TO SAMPLE USER john USING john1234;

GRANT ROLE emp_dept_role TO USER bob, USER joe;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user BOB.
CONNECT TO SAMPLE USER bob USING bob1234;

-- User BOB creates a view EMP_DEPT which depends upon tables
-- TEMP_EMPLOYEE and TEMP_DEPARTMENT.
-- Create view EMP_DEPT using tables TEMP_EMPLOYEE and TEMP_DEPARTMENT.
CREATE VIEW emp_dept AS SELECT * FROM mh.temp_employee, mh.temp_department;

-- Transfer view EMP_DEPT to user JOE from user BOB.
TRANSFER OWNERSHIP OF VIEW bob.emp_dept TO USER joe PRESERVE PRIVILEGES;

-- Connect to sample database using user JOE.
CONNECT TO SAMPLE USER joe using joe1234;

-- After the TANSFER is done,user BOB cannot drop the view. Only new user JOE,
-- who is current owner of the view, can drop it.
DROP VIEW bob.emp_dept;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOHN(SECADM).
CONNECT TO SAMPLE USER john using john1234;

-- User JOHN(SECADM) drops the role.
DROP ROLE emp_dept_role;

-- Disconnect from sample database.
CONNECT RESET;

4，從ROLE中取消許可權
-- Connect to sample database using user JOE.
CONNECT TO SAMPLE USER joe USING joe1234;

-- Create table TEMP_TABLE.
CREATE TABLE temp_table (x int);

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOHN(SECADM).
CONNECT TO SAMPLE USER john USING john1234;

-- Create role DEVELOPER.
CREATE ROLE developer;

-- Grant role DEVELOPER to user BOB
GRANT ROLE developer TO USER bob;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user mh(SYSADM).
CONNECT TO SAMPLE USER mh using mh1234;

-- Grant SELECT and INSERT privileges on table TEMP_TABLE to PUBLIC and
-- to role DEVELOPER. Only a user with SYSADM or DBADM authority can grant
-- the privileges on the table.
-- In this sample, the user mh(SYSADM)can grant the SELECT and the INSERT
-- privileges on the table TEMP_TABLE to PUBLIC and to role DEVELOPER.
GRANT SELECT ON TABLE joe.temp_table TO PUBLIC;
GRANT INSERT ON TABLE joe.temp_table TO PUBLIC;
GRANT SELECT ON TABLE joe.temp_table
  TO ROLE developer;
GRANT INSERT ON TABLE joe. temp_table
  TO developer;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database uing user BOB.
CONNECT TO SAMPLE USER bob USING bob1234;

-- Create a view VIEW_TEMP on the table TEMP_TABLE.
CREATE VIEW view_temp
  AS SELECT * FROM joe.temp_table;

-- Disconnect form. sample database.
CONNECT RESET;

-- Connect to sample database using user mh(SYSADM).
CONNECT TO SAMPLE USER mh using mh1234;

-- If SELECT privilege on table JOE.TEMP_TABLE is revoked from PUBLIC,
-- the view, BOB.VIEW_TEMP will still be accessible by users who are
-- part of the role DEVELOPER.
REVOKE SELECT ON joe.temp_table FROM PUBLIC;

-- Disconnect from sample database.
CONNECT RESET;

CONNECT TO sample USER bob USING bob1234;
select * from view_temp;

-- Connect to sample database using user mh(SYSADM).
CONNECT TO sample USER mh USING mh1234;

-- If SELECT privilege on table JOE.TEMP_TABLE is revoked from the role
-- DEVELOPER, user BOB will lose SELECT privilege on the table JOE.TEMP_TABLE
-- because required privileges are not held through either role DEVELOPER
-- or any other means.
REVOKE SELECT ON TABLE joe.temp_table FROM ROLE developer;

-- Disconnect from sample database.
CONNECT RESET;

CONNECT TO sample USER bob USING bob1234;
select * from view_temp;
--&gtSQL0727N  隱式系統操作型別 "3" 期間出錯。對該錯誤返回的資訊包括 SQLCODE
"-551"、SQLSTATE "42501" 和訊息標記 "BOB|SELECT|JOE.TEMP_TABLE"。
SQLSTATE=56098

-- Connect to sample database using user BOB.
CONNECT TO sample USER bob USING bob1234;

-- Drop a view VIEW_TEMP.
DROP VIEW bob.view_temp;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOE.
CONNECT TO SAMPLE USER joe USING joe1234;

-- Drop a table TEMP_TABLE.
DROP TABLE joe.temp_table;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOHN(SECADM).
CONNECT TO SAMPLE USER john USING john1234;

-- Drop a role DEVELOPER.
DROP ROLE developer;

-- Disconnect from sample database.
CONNECT RESET;

5，建立層級ROLE
-- Connect to sample database using user JOHN(SECADM).
CONNECT TO SAMPLE USER john USING john1234;

-- Create roles MANAGER, TECH_LEAD and DEVELOPER.
CREATE ROLE manager;
CREATE ROLE tech_lead;
CREATE ROLE developer;

-- These two statements create a role hierarchy.
GRANT ROLE developer TO ROLE tech_lead;
GRANT ROLE tech_lead TO ROLE manager;

-- Drop roles MANAGER, TECH_LEAD and DEVELOPER.
DROP ROLE manager;
DROP ROLE tech_lead;
DROP ROLE developer;

-- Disconnect from sample database.
CONNECT RESET;

6，使用WITH ADMIN OPTIONS從授權ID授予/取消許可權
-- Connect to sample database using user JOHN(SECADM).
CONNECT TO SAMPLE USER john USING john1234;

-- Create role DEVELOPER.
CREATE ROLE developer;

-- Grant role DEVELOPER to user JOE and give the WITH ADMIN privilege.
GRANT ROLE developer TO USER joe WITH ADMIN OPTION;

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOE.
CONNECT TO SAMPLE USER joe USING joe1234;

-- The following statements will be successful because user JOE has the
-- WITH ADMIN privileges on role DEVELOPER.
-- User JOE can GRANT and REVOKE ROLE to/from the other users.
GRANT ROLE developer TO USER bob;

REVOKE ROLE developer FROM USER bob;

-- The following statement will fail since user JOE doesn't have the privilege
-- to drop the role DEVELOPER. Only user JOHN(SECADM) is allowed to drop the
-- role.
-- Error displayed will be:
-- SQL0552N "JOE" does not have the privilege to perform. operation
-- "DROP ROLE".  SQLSTATE=42502
DROP ROLE developer;
!echo "Above error is expected!";

-- The following statement will fail because user JOE cannot GRANT/REVOKE
-- the role DEVELOPER to another user by using the WITH ADMIN OPTION clause.
-- Only a SECADM can grant/revoke the WITH ADMIN OPTION.
-- Error displayed will be:
-- SQL0551N .JOE" does not have the privilege to perform. operation
-- "GRANT/REVOKE" on object "DEVELOPER".  SQLSTATE=42501
GRANT ROLE DEVELOPER TO USER bob WITH ADMIN OPTION;
!echo "Above error is expected!";

REVOKE ADMIN OPTION FOR ROLE developer FROM USER bob;
!echo "Above error is expected!";

-- Disconnect from sample database.
CONNECT RESET;

-- Connect to sample database using user JOHN(SECADM).
CONNECT TO SAMPLE USER john USING john1234;

-- The following statement will be successful because user JOHN(SECADM)
-- is executing it.
-- With the command below, only ADMIN OPTION is revoked, role DEVELOPER is
-- still granted to user JOE.
REVOKE ADMIN OPTION FOR ROLE developer FROM USER joe;

CONNECT TO SAMPLE USER joe USING joe1234;

-- The following statements will be successful because user JOE has the
-- WITH ADMIN privileges on role DEVELOPER.
-- User JOE can GRANT and REVOKE ROLE to/from the other users.
GRANT ROLE developer TO USER bob;
--&gtSQL0551N  "JOE" 不具有對物件 "DEVELOPER" 執行操作 "GRANT" 的必需許可權或特權。
SQLSTATE=42501

REVOKE ROLE developer FROM USER bob;
--&gtSQL0551N  "JOE" 不具有對物件 "DEVELOPER" 執行操作 "GRANT" 的必需許可權或特權。
SQLSTATE=42501

CONNECT TO SAMPLE USER john USING john1234;

-- Revoke role DEVELOPER from user JOE.
REVOKE ROLE developer FROM USER joe;

-- Drop a role.
DROP ROLE developer;

-- Disconnect from sample database.
CONNECT RESET;

7，清理測試環境
CONNECT TO SAMPLE;

DROP TABLE mh.TEMP_EMPLOYEE;
DROP TABLE mh.TEMP_DEPARTMENT;

TERMINATE;