DB2角色管理.docxVIP

目的：测试DB2数据库角色，本脚本摘录自DB2安装目录admin_scripts/databaseroles.db2。版本：Windows DB2 Express-C V9.7用户要求：操作系统用户:mh with password mh1234 in SYSADM groupjohn with password john1234joe? with password joe1234bob? with password bob1234pat? 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