----------------------------------------------------------------------------- -- (c) Copyright IBM Corp. 2007 All rights reserved. -- -- The following sample of source code ("Sample") is owned by International -- Business Machines Corporation or one of its subsidiaries ("IBM") and is -- copyrighted and licensed, not sold. You may use, copy, modify, and -- distribute the Sample in any form without payment to IBM, for the purpose of -- assisting you in the development of your applications. -- -- The Sample code is provided to you on an "AS IS" basis, without warranty of -- any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR -- IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF -- MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do -- not allow for the exclusion or limitation of implied warranties, so the above -- limitations or exclusions may not apply to you. IBM shall not be liable for -- any damages you suffer as a result of using, copying, modifying or -- distributing the Sample, even if IBM has been advised of the possibility of -- such damages. ----------------------------------------------------------------------------- -- -- SAMPLE FILE NAME: databaseroles.db2 -- -- PURPOSE : To demonstrate how database roles can be used in DB2 LUW. -- -- USAGE SCENARIO : In an enterprise, each employee has certain privileges -- based on his/her role in each department. When an -- employee joins, leaves or moves to or from a different -- department, permissions need to be -- granted, revoked, or transferred on department specific -- database objects individually. This sample demonstrates -- how all these can easily be done in single statement using -- roles. -- -- The sample uses a scenario of an enterprise with three -- departments DEVELOPMENT, TESTING, and SALES and three new -- employees JOE, BOB, and PAT, joining the three departments -- respectively. Employees for each department should have -- access to only department specific information (tables) -- and in case an employee gets transferred to another -- department or leaves the enterprise, the permissions need -- to be modified accordingly. The sample demonstrates how -- the enterprise DBA's task of modifying permission on -- different database objects for different users can be -- simplified by the creation of database roles. -- -- PREREQUISITE : The following users should exist in the operating system. -- -- newton with password "Way2discoveR" in SYSADM group. -- john with password "john123456" -- joe with password "joe123456" -- bob with password "bob123456" -- pat with password "pat123456" -- -- -- EXECUTION : db2 -tvf databaseroles.db2 -- -- INPUTS : NONE -- -- OUTPUTS : Roles will be created and dropped in the database. -- -- -- -- DEPENDENCIES : NONE -- -- SQL STATEMENTS USED: -- CREATE ROLE -- CREATE TABLE -- CRETE VIEW -- CONNECT -- DROP -- GRANT -- INSERT -- REVOKE -- SELECT -- TRANSFER -- **************************************************************************** -- For more information about the command line processor (CLP) scripts, -- see the README file. -- For information on using SQL statements, see the SQL Reference. -- -- For the latest information on programming, building, and running DB2 -- applications, visit the DB2 Information Center: -- http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp -- **************************************************************************** -- SAMPLE DESCRIPTION -- **************************************************************************** -- 1: How to GRANT select privilege to users through ROLES. -- 2. How to replace GROUPS with ROLES. -- 3: How to transfer ownership of database objects. -- 4: How to REVOKE privileges from roles. -- 5: How to build a role hierarchy. -- 6: GRANT-ing and REVOKE-ing WITH ADMIN OPTIONS to and from an -- authorization ID. -- **************************************************************************** -- **************************************************************************** -- SETUP -- **************************************************************************** CONNECT TO sample; -- Create tables TEMP_EMPLOYEE and TEMP_DEPARTMENT under 'newton' schema. CREATE TABLE newton.TEMP_EMPLOYEE LIKE EMPLOYEE; CREATE TABLE newton.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 newton.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 newton.TEMP_DEPARTMENT; -- ---------------------------------------------------------------------------- -- 1: How to GRANT select privilege to users through ROLES. -- ---------------------------------------------------------------------------- -- Usage scenario : -- The code below shows how to GRANT/REVOKE select privilege to users JOE and -- BOB through role DEVELOPMENT_ROLE. User JOHN, who is a -- security administrator(SECADM), creates a role DEVELOPMENT_ROLE and grants -- employees JOE and BOB, working in department DEVELOPMENT, SELECT privilege -- on tables DEV_TABLE1 and DEV_TABLE2 via DEVELOPMENT_ROLE. -- The sample also shows how REVOKING of the DEVELOPMENT_ROLE from employees -- JOE and BOB causes employees JOE and BOB to lose SELECT privilege on -- tables DEV_TABLE1 and DEV_TABLE2. At this point, if employees JOE and -- BOB try to perform a select operation on tables DEV_TABLE1 and DEV_TABLE2, -- the select statement will fail. The sample also shows when a new -- employee PAT joins department DEVELOPMENT, she is granted SELECT privilege -- on the two development tables via DEVELOPMENT_ROLE. -- ---------------------------------------------------------------------------- -- Connect to sample database using user NEWTON. CONNECT TO sample user newton using Way2discoveR; -- 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 john123456; -- 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 NEWTON(SYSADM). -- User NEWTON is system administrator(SYSADM). CONNECT TO sample user newton using Way2discoveR; -- 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 john123456; -- 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 joe123456; -- 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 bob123456; -- 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 john123456; -- 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 joe123456; -- 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 bob123456; -- 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 john123456; -- 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 pat123456; -- 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 john123456; -- 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. How to replace GROUPS with ROLES. -- ---------------------------------------------------------------------------- -- Usage scenario: -- Assume there are three groups DEVELOPER_G, TESTER_G, and SALES_G and three -- users (BOB, JOE and PAT) defined in the operating system. -- BOB belongs to group DEVELOPER_G and SALES_G, JOE -- belongs to group TESTER_G and SALES_G and PAT belongs to group TESTER_G. -- Roles DEVELOPER, TESTER and SALES will be created and used instead of groups -- DEVELOPER_G, TESTER_G, and SALES_G respectively . In this scenario, all the -- privileges held by GROUPS will be granted to appropriate ROLES and revoked -- from the GROUPS. This leaves the users with the same privileges but now -- held through ROLES instead of GROUPS. -- ---------------------------------------------------------------------------- -- Connect to sample database using user JOHN. CONNECT TO sample USER john USING john123456; -- 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 bob123456; -- Create table TEMP1. CREATE TABLE temp1 (a int); -- Disconnect from sample database. CONNECT RESET; -- Connect to sample database using user Newton(SYSADM)so that he can grant -- ALTER privilege to role DEVELOPER. (DBADM can also grant ALTER privilege). CONNECT TO SAMPLE USER newton USING Way2discoveR; -- Grant ALTER privilege on table TEMP1 to role DEVELOPER. NEWTON(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 bob123456; -- 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 john123456; -- 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: How to transfer ownership of database objects. -- ---------------------------------------------------------------------------- -- Usage scenario: -- Consider the tables TEMP_EMPLOYEE and TEMP_DEPARTMENT in the sample database. User BOB -- creates a view EMP_DEPT on tables TEMP_EMPLOYEE and TEMP_DEPARTMENT. -- The user JOHN(SECADM) creates a role EMP_DEPT_ROLE which is granted SELECT -- privilege on table TEMP_EMPLOYEE and TEMP_DEPARTMENT. The sample shows how to transfer -- ownership of view BOB.EMP_DEPT, which depends on tables TEMP_EMPLOYEE and -- TEMP_DEPARTMENT, to new user JOE. For the TRANSFER to work, new user JOE must -- hold SELECT privilege on the above two table. -- ---------------------------------------------------------------------------- -- Connect to sample database using user JOHN(SECADM). CONNECT TO SAMPLE USER john USING john123456; -- Create role EMP_DEPT_ROLE. CREATE ROLE emp_dept_role; -- Disconnect from sample database. CONNECT RESET; -- User NEWTON(SYSADM) grants SELECT privilege on tables TEMP_EMPLOYEE and TEMP_DEPARTMENT -- to role EMP_DEPT_ROLE. -- Connect to sample database using user NEWTON. CONNECT TO sample user newton using Way2discoveR; GRANT SELECT ON TABLE newton.temp_employee TO ROLE emp_dept_role; GRANT SELECT ON TABLE newton.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 john123456; 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 bob123456; -- 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 newton.temp_employee, newton.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 joe123456; -- 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 john123456; -- User JOHN(SECADM) drops the role. DROP ROLE emp_dept_role; -- Disconnect from sample database. CONNECT RESET; -- ---------------------------------------------------------------------------- -- 4: How to REVOKE privileges from roles. -- ---------------------------------------------------------------------------- -- Usage scenario: -- To show the effect on a user~Rs access privilege to a database object, if -- some privileges are revoked from an authorization ID and privileges are -- held only through a role. User JOE creates a table TEMP_TABLE. -- User JOHN(SECADM) creates a role DEVELOPER and is grants it to user BOB. -- User NEWTON(SYSADM) grants SELECT and INSERT privileges on table JOE.TEMP_TABLE to -- PUBLIC and to role DEVELOPER. User BOB creates a view VIEW_TEMP which is -- dependent on table JOE.TEMP_TABLE. The sample shows what happens to -- user BOB's access privilege when SELECT privilege is revoked from PUBLIC -- and from role developer. -- ---------------------------------------------------------------------------- -- Connect to sample database using user JOE. CONNECT TO SAMPLE USER joe USING joe123456; -- 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 john123456; -- 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 NEWTON(SYSADM). CONNECT TO SAMPLE USER newton using Way2discoveR; -- 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 NEWTON(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 bob123456; -- 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 NEWTON(SYSADM). CONNECT TO SAMPLE USER newton using Way2discoveR; -- 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 database using user NEWTON(SYSADM). CONNECT TO sample USER newton USING Way2discoveR; -- 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 database using user BOB. CONNECT TO sample USER bob USING bob123456; -- 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 joe123456; -- 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 john123456; -- Drop a role DEVELOPER. DROP ROLE developer; -- Disconnect from sample database. CONNECT RESET; -- ---------------------------------------------------------------------------- -- 5: How to build a role hierarchy. -- ---------------------------------------------------------------------------- -- Usage scenario: -- The following sample demonstrates how to represent hierarchy levels -- in an enterprise by building a role hierarchy. -- Consider an enterprise having the following roles: MANAGER, TECH_LEAD and -- DEVELOPER. A role hierarchy is built by granting a role to another role, but -- without creating cycles. Role DEVELOPER will be granted to role TECH_LEAD, -- and role TECH_LEAD will be granted to role MANAGER. Granting role MANAGER to -- role DEVELOPER will create a cycle and it is not allowed. -- By building a hierarchy, role MANAGER will have all the privileges of roles -- DEVELOPER and TECH_LEAD along with the privileges granted to it directly. -- Role TECH_LEAD will have privileges of role DEVELOPER and the privileges -- granted to it directly. Role DEVELOPER will just have the privileges granted -- to it. Later, depending upon the enterprise needs, each role can be granted -- to specific employees to form the hierarchy. -- ---------------------------------------------------------------------------- -- Connect to sample database using user JOHN(SECADM). CONNECT TO SAMPLE USER john USING john123456; -- 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: GRANT-ing and REVOKE-ing WITH ADMIN OPTIONS to and from an -- authorization ID. -- ---------------------------------------------------------------------------- -- Usage scenario: -- A security administrator will create role DEVELOPER and grant it to user JOE -- using WITH ADMIN OPTION. Once the ADMIN OPTION is granted to user JOE, he can -- GRANT or REVOKE role DEVELOPER to or from another user BOB who is a member of -- this role. But JOE will not get the authority to drop role DEVELOPER or to -- grant ADMIN OPTION to another user. User JOE is not allowed to REVOKE the -- ADMIN OPTION from role DEVELOPER because he does not have SECADM authority. -- A security administrator can revoke the ADMIN OPTION from role DEVELOPER, -- and user JOE will still have role DEVELOPER granted. -- If a SECADM revokes the role DEVELOPER from the user JOE, the user JOE will -- lose all the privileges he has received by being a member of role DEVELOPER -- and the ADMIN OPTION on role DEVELOPER if this was held. -- ---------------------------------------------------------------------------- -- Connect to sample database using user JOHN(SECADM). CONNECT TO SAMPLE USER john USING john123456; -- 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 joe123456; -- 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 john123456; -- 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; -- Revoke role DEVELOPER from user JOE. REVOKE ROLE developer FROM USER joe; -- Drop a role. DROP ROLE developer; -- Disconnect from sample database. CONNECT RESET; -- **************************************************************************** -- CLEAN UP -- **************************************************************************** -- Drop the temporary tables created. CONNECT TO SAMPLE; DROP TABLE newton.TEMP_EMPLOYEE; DROP TABLE newton.TEMP_DEPARTMENT; TERMINATE;