目的:
测试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;
-->SQL0727N 隐式系统操作类型 "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;
-->SQL0551N "JOE" 不具有对对象 "DEVELOPER" 执行操作 "GRANT" 的必需权限或特权。
SQLSTATE=42501
REVOKE ROLE developer FROM USER bob;
-->SQL0551N "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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-702149/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-702149/