| >>obey TEST137(set_up); |
| >>prepare get_roles from |
| +>select substring (auth_db_name,1,20) as role_name |
| +>from "_MD_".AUTHS |
| +>where auth_db_name like 'LIBRARY%' |
| +>order by 1 |
| +>for read uncommitted access; |
| |
| --- SQL command prepared. |
| >> |
| >>prepare get_components from |
| +>select substring (component_name,1,20) as component, |
| +> substring (component_description,1,30) as description |
| +>from trafodion."_PRIVMGR_MD_".components |
| +>where component_name like 'LIBRARY%' |
| +>order by 1 |
| +>for read uncommitted access; |
| |
| --- SQL command prepared. |
| >> |
| >>prepare get_component_operations from |
| +>select substring (c.component_name,1,20) as component, |
| +> substring (o.operation_name,1,20) as operation_name, |
| +> substring (o.operation_code,1,2) as operation_code |
| +>from trafodion."_PRIVMGR_MD_".component_operations o, |
| +> trafodion."_PRIVMGR_MD_".components c |
| +>where (component_name like 'LIBRARY%' and |
| +> c.component_uid = o.component_uid) |
| +>order by 1,2, 3 |
| +>for read uncommitted access; |
| |
| --- SQL command prepared. |
| >> |
| >>prepare get_priv_desc from |
| +>select substring (component_name,1,20) as component, |
| +> substring (operation_name,1,20) as operation_name, |
| +> substring (grantee_name,1,20) as grantee_name, |
| +> grant_depth as grant_depth |
| +>from trafodion."_PRIVMGR_MD_".components c, |
| +> trafodion."_PRIVMGR_MD_".component_operations p, |
| +> trafodion."_PRIVMGR_MD_".component_privileges d |
| +>where c.component_uid = p.component_uid |
| +> and p.operation_code = d.operation_code |
| +> and p.component_uid = d.component_uid |
| +> and c.component_name like 'LIBRARY%' |
| +>order by 1, 2, d.grantee_name, grant_depth |
| +>for read uncommitted access; |
| |
| --- SQL command prepared. |
| >> |
| >>obey TEST137(create_db); |
| >>create role md_access; |
| |
| --- SQL operation complete. |
| >>grant select on "_MD_".auths to md_access; |
| |
| --- SQL operation complete. |
| >>grant select on "_PRIVMGR_MD_".components to md_access; |
| |
| --- SQL operation complete. |
| >>grant select on "_PRIVMGR_MD_".component_operations to md_access; |
| |
| --- SQL operation complete. |
| >>grant select on "_PRIVMGR_MD_".component_privileges to md_access; |
| |
| --- SQL operation complete. |
| >>grant role md_access to sql_user1, sql_user4, sql_user5; |
| |
| --- SQL operation complete. |
| >> |
| >>create shared schema t137; |
| |
| --- SQL operation complete. |
| >>set schema t137; |
| |
| --- SQL operation complete. |
| >> |
| >>create role library_admin; |
| |
| --- SQL operation complete. |
| >>grant role library_admin to sql_user1; |
| |
| --- SQL operation complete. |
| >>grant role library_admin to sql_user2; |
| |
| --- SQL operation complete. |
| >>create role library_ckout_clerks; |
| |
| --- SQL operation complete. |
| >>grant role library_ckout_clerks to sql_user1; |
| |
| --- SQL operation complete. |
| >>grant role library_ckout_clerks to sql_user2; |
| |
| --- SQL operation complete. |
| >>grant role library_ckout_clerks to sql_user3, sql_user4, sql_user5; |
| |
| --- SQL operation complete. |
| >> |
| >>create table books |
| +> (book_name varchar (50) not null, |
| +> book_id largeint primary key not null, |
| +> book_author varchar (50) not null, |
| +> book_type int not null); |
| |
| --- SQL operation complete. |
| >> |
| >>--grant all on books to library_admin; |
| >>--grant select, update on bools to library_ckout_clerks; |
| >>grant select on books to public; |
| |
| --- SQL operation complete. |
| >>grant all on books to sql_user1; |
| |
| --- SQL operation complete. |
| >>grant select on books to sql_user1; |
| |
| --- SQL operation complete. |
| >>grant update on books to sql_user1; |
| |
| --- SQL operation complete. |
| >>grant select on books to sql_user2; |
| |
| --- SQL operation complete. |
| >>grant update on books to sql_user2; |
| |
| --- SQL operation complete. |
| >>grant select on books to sql_user6; |
| |
| --- SQL operation complete. |
| >>grant select on books to sql_user7; |
| |
| --- SQL operation complete. |
| >>grant select on books to sql_user8; |
| |
| --- SQL operation complete. |
| >> |
| >>create table library_users |
| +> (user_name varchar (50) not null, |
| +> user_id int primary key not null, |
| +> user_details varchar (50) not null); |
| |
| --- SQL operation complete. |
| >> |
| >>--grant all on library_users to library_admin; |
| >>grant all on library_users to sql_user1; |
| |
| --- SQL operation complete. |
| >>grant select on library_users to sql_user6; |
| |
| --- SQL operation complete. |
| >>grant select on library_users to sql_user7; |
| |
| --- SQL operation complete. |
| >>grant select on library_users to sql_user8; |
| |
| --- SQL operation complete. |
| >>--grant select on library_users to library_ckout_clerk; |
| >> |
| >>showddl role library_admin; |
| |
| CREATE ROLE "LIBRARY_ADMIN"; |
| -- GRANT ROLE "LIBRARY_ADMIN" TO "DB__ROOT" WITH ADMIN OPTION; |
| GRANT ROLE |
| "LIBRARY_ADMIN" TO "SQL_USER1"; |
| GRANT ROLE "LIBRARY_ADMIN" TO "SQL_USER2"; |
| |
| --- SQL operation complete. |
| >>showddl role library_ckout_clerks; |
| |
| CREATE ROLE "LIBRARY_CKOUT_CLERKS"; |
| -- GRANT ROLE "LIBRARY_CKOUT_CLERKS" TO "DB__ROOT" WITH ADMIN OPTION; |
| GRANT |
| ROLE "LIBRARY_CKOUT_CLERKS" TO "SQL_USER1"; |
| GRANT ROLE "LIBRARY_CKOUT_CLERKS" |
| TO "SQL_USER2"; |
| GRANT ROLE "LIBRARY_CKOUT_CLERKS" TO "SQL_USER3"; |
| GRANT ROLE |
| "LIBRARY_CKOUT_CLERKS" TO "SQL_USER4"; |
| GRANT ROLE "LIBRARY_CKOUT_CLERKS" TO |
| "SQL_USER5"; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST137(register_components); |
| >>register component Library_books; |
| |
| --- SQL operation complete. |
| >>register component library_accounts detail 'Test component 2'; |
| |
| --- SQL operation complete. |
| >>register component library_users detail 'Test component 3'; |
| |
| --- SQL operation complete. |
| >>-- should be 3 rows |
| >>execute get_components; |
| |
| COMPONENT DESCRIPTION |
| -------------------- ------------------------------ |
| |
| LIBRARY_ACCOUNTS TEST COMPONENT 2 |
| LIBRARY_BOOKS |
| LIBRARY_USERS TEST COMPONENT 3 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- fails with a syntax error |
| >>register component user; |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| register component user; |
| ^ (23 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>register component abi**def; |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| register component abi**def; |
| ^ (24 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- fails with component already registered |
| >>register component library_books; |
| |
| *** ERROR[1055] Object LIBRARY_BOOKS already exists. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- unregister a component that has not been defined |
| >>unregister component library_clerks; |
| |
| *** ERROR[1004] Object LIBRARY_CLERKS does not exist or object type is invalid for the current operation. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- a successful unregister |
| >>register component library_clerks; |
| |
| --- SQL operation complete. |
| >>-- should be 4 rows |
| >>execute get_components; |
| |
| COMPONENT DESCRIPTION |
| -------------------- ------------------------------ |
| |
| LIBRARY_ACCOUNTS TEST COMPONENT 2 |
| LIBRARY_BOOKS |
| LIBRARY_CLERKS |
| LIBRARY_USERS TEST COMPONENT 3 |
| |
| --- 4 row(s) selected. |
| >>unregister component library_clerks; |
| |
| --- SQL operation complete. |
| >>-- should return 3 rows |
| >>execute get_components; |
| |
| COMPONENT DESCRIPTION |
| -------------------- ------------------------------ |
| |
| LIBRARY_ACCOUNTS TEST COMPONENT 2 |
| LIBRARY_BOOKS |
| LIBRARY_USERS TEST COMPONENT 3 |
| |
| --- 3 row(s) selected. |
| >> |
| >>obey TEST137(create_privs); |
| >>-- create component privileges for library_books: |
| >>create component privilege lib_manage_checkouts as 'MC' on library_books; |
| |
| --- SQL operation complete. |
| >>create component privilege lib_view_checkouts as 'VC' on library_books |
| +> detail 'Can see checkout information'; |
| |
| --- SQL operation complete. |
| >>create component privilege lib_view_repository as 'VR' on library_books; |
| |
| --- SQL operation complete. |
| >> |
| >>-- create component privileges for library_accounts: |
| >>create component privilege lib_manage_overdue as 'MO' on library_accounts |
| +> detail 'Can handle tasks to manage overdue books'; |
| |
| --- SQL operation complete. |
| >>create component privilege lib_view_checkouts as 'VC' on library_accounts; |
| |
| --- SQL operation complete. |
| >> |
| >>-- create component privileges for library_users: |
| >>create component privilege lib_manage_users as 'MU' on library_users; |
| |
| --- SQL operation complete. |
| >>create component privilege lib_view_users as 'VU' on library_users; |
| |
| --- SQL operation complete. |
| >> |
| >>-- should return 7 rows |
| >>execute get_component_operations; |
| |
| COMPONENT OPERATION_NAME OPERATION_CODE |
| -------------------- -------------------- -------------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE MO |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS VC |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS MC |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS VC |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY VR |
| LIBRARY_USERS LIB_MANAGE_USERS MU |
| LIBRARY_USERS LIB_VIEW_USERS VU |
| |
| --- 7 row(s) selected. |
| >> |
| >>-- error: component does not exist |
| >>create component privilege lib_misc as 'MI' on library_xxxx; |
| |
| *** ERROR[1004] Object LIBRARY_XXXX does not exist or object type is invalid for the current operation. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- error: privilege already defined |
| >>create component privilege lib_manage_users as 'MA' on library_users; |
| |
| *** ERROR[1357] Cannot create the component privilege specified. Component privilege name LIB_MANAGE_USERS for the component already exists. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- error: privilege type already defined |
| >>create component privilege lib_misc as 'MU' on library_users; |
| |
| *** ERROR[1356] Cannot create the component privilege specified. Component privilege code MU for the component already exists. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>obey TEST137(grant_revoke_priv_desc); |
| >>grant component privilege lib_manage_checkouts, |
| +> lib_view_checkouts, |
| +> lib_view_repository |
| +> on library_books to library_admin; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_manage_checkouts, |
| +> lib_view_checkouts, |
| +> lib_view_repository |
| +> on library_books to sql_user1 with grant option; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_view_checkouts, |
| +> lib_view_repository |
| +> on library_books to library_ckout_clerks; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_view_repository |
| +> on library_books to public; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user5; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user6; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user7; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user8; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_manage_overdue, lib_view_checkouts |
| +> on library_accounts to library_admin; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_manage_overdue, lib_view_checkouts |
| +> on library_accounts to sql_user2 with grant option; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_view_checkouts |
| +> on library_accounts to library_ckout_clerks; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_manage_users, lib_view_users |
| +> on library_users to library_admin; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_manage_users, lib_view_users |
| +> on library_users to sql_user3 with grant option; |
| |
| --- SQL operation complete. |
| >> |
| >>-- should return 7 rows |
| >>execute get_component_operations; |
| |
| COMPONENT OPERATION_NAME OPERATION_CODE |
| -------------------- -------------------- -------------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE MO |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS VC |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS MC |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS VC |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY VR |
| LIBRARY_USERS LIB_MANAGE_USERS MU |
| LIBRARY_USERS LIB_VIEW_USERS VU |
| |
| --- 7 row(s) selected. |
| >> |
| >>-- should return 28 rows |
| >>execute get_priv_desc; |
| |
| COMPONENT OPERATION_NAME GRANTEE_NAME GRANT_DEPTH |
| -------------------- -------------------- -------------------------------------------------------------------------------- ----------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE DB__ROOT -1 |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE LIBRARY_ADMIN 0 |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE SQL_USER2 -1 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS DB__ROOT -1 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS SQL_USER2 -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS DB__ROOT -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS DB__ROOT -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY DB__ROOT -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY PUBLIC 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER5 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER6 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER7 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER8 0 |
| LIBRARY_USERS LIB_MANAGE_USERS DB__ROOT -1 |
| LIBRARY_USERS LIB_MANAGE_USERS LIBRARY_ADMIN 0 |
| LIBRARY_USERS LIB_MANAGE_USERS SQL_USER3 -1 |
| LIBRARY_USERS LIB_VIEW_USERS DB__ROOT -1 |
| LIBRARY_USERS LIB_VIEW_USERS LIBRARY_ADMIN 0 |
| LIBRARY_USERS LIB_VIEW_USERS SQL_USER3 -1 |
| |
| --- 29 row(s) selected. |
| >> |
| >>-- grant privileges already granted |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user6; |
| |
| --- SQL operation complete. |
| >> |
| >>-- should return 7 rows |
| >>execute get_component_operations; |
| |
| COMPONENT OPERATION_NAME OPERATION_CODE |
| -------------------- -------------------- -------------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE MO |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS VC |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS MC |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS VC |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY VR |
| LIBRARY_USERS LIB_MANAGE_USERS MU |
| LIBRARY_USERS LIB_VIEW_USERS VU |
| |
| --- 7 row(s) selected. |
| >> |
| >>-- add WGO |
| >>-- should return 28 rows, sql_user6 should be WGO |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user6 with grant option; |
| |
| --- SQL operation complete. |
| >>execute get_priv_desc; |
| |
| COMPONENT OPERATION_NAME GRANTEE_NAME GRANT_DEPTH |
| -------------------- -------------------- -------------------------------------------------------------------------------- ----------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE DB__ROOT -1 |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE LIBRARY_ADMIN 0 |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE SQL_USER2 -1 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS DB__ROOT -1 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS SQL_USER2 -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS DB__ROOT -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS DB__ROOT -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY DB__ROOT -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY PUBLIC 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER5 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER6 -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER7 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER8 0 |
| LIBRARY_USERS LIB_MANAGE_USERS DB__ROOT -1 |
| LIBRARY_USERS LIB_MANAGE_USERS LIBRARY_ADMIN 0 |
| LIBRARY_USERS LIB_MANAGE_USERS SQL_USER3 -1 |
| LIBRARY_USERS LIB_VIEW_USERS DB__ROOT -1 |
| LIBRARY_USERS LIB_VIEW_USERS LIBRARY_ADMIN 0 |
| LIBRARY_USERS LIB_VIEW_USERS SQL_USER3 -1 |
| |
| --- 29 row(s) selected. |
| >> |
| >>-- make sure it is not taken away |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user6; |
| |
| --- SQL operation complete. |
| >>-- returns 28 rows, sql_user6 still has WGO as Y |
| >>execute get_priv_desc; |
| |
| COMPONENT OPERATION_NAME GRANTEE_NAME GRANT_DEPTH |
| -------------------- -------------------- -------------------------------------------------------------------------------- ----------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE DB__ROOT -1 |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE LIBRARY_ADMIN 0 |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE SQL_USER2 -1 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS DB__ROOT -1 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS SQL_USER2 -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS DB__ROOT -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS DB__ROOT -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY DB__ROOT -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY PUBLIC 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER5 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER6 -1 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER7 0 |
| LIBRARY_BOOKS LIB_VIEW_REPOSITORY SQL_USER8 0 |
| LIBRARY_USERS LIB_MANAGE_USERS DB__ROOT -1 |
| LIBRARY_USERS LIB_MANAGE_USERS LIBRARY_ADMIN 0 |
| LIBRARY_USERS LIB_MANAGE_USERS SQL_USER3 -1 |
| LIBRARY_USERS LIB_VIEW_USERS DB__ROOT -1 |
| LIBRARY_USERS LIB_VIEW_USERS LIBRARY_ADMIN 0 |
| LIBRARY_USERS LIB_VIEW_USERS SQL_USER3 -1 |
| |
| --- 29 row(s) selected. |
| >> |
| >>-- error unknown component |
| >>grant component privilege lib_view_repository on library_xxxx to sql_user9; |
| |
| *** ERROR[1004] Object LIBRARY_XXXX does not exist or object type is invalid for the current operation. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- error: unknown privilege |
| >>grant component privilege lib_view_xxx on library_books to sql_user9; |
| |
| *** ERROR[1194] Component operation LIB_VIEW_XXX does not exist for component LIBRARY_BOOKS |
| |
| --- SQL operation failed with errors. |
| >>grant component privilege lib_view_checkouts, |
| +> lib_view_repository, |
| +> lib_view_xxx |
| +> on library_books to sql_user9; |
| |
| *** ERROR[1194] Component operation LIB_VIEW_XXX does not exist for component LIBRARY_BOOKS |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- error: grant to unknown user or role |
| >>grant component privilege lib_view_repository on library_books to authid_xxxx; |
| |
| *** ERROR[1008] Authorization identifier AUTHID_XXXX does not exist. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- success: grant to a role WGO |
| >>grant component privilege lib_view_repository |
| +> on library_books to DB__ROOTROLE with grant option; |
| |
| --- SQL operation complete. |
| >>revoke grant option for component privilege lib_view_repository |
| +> on library_books from DB__ROOTROLE; |
| |
| --- SQL operation complete. |
| >>revoke component privilege lib_view_repository |
| +> on library_books from DB__ROOTROLE; |
| |
| --- SQL operation complete. |
| >> |
| >>-- error: specify the same privilege twice |
| >>grant component privilege lib_view_repository, |
| +> lib_view_repository |
| +> on library_books to DB__ROOTROLE; |
| |
| *** ERROR[3170] Duplicate component privileges were specified. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>obey TEST137(drop_privs); |
| >>drop component privilege lib_view_repository on library_books cascade; |
| |
| --- SQL operation complete. |
| >>-- returns 6 rows |
| >>execute get_component_operations; |
| |
| COMPONENT OPERATION_NAME OPERATION_CODE |
| -------------------- -------------------- -------------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE MO |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS VC |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS MC |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS VC |
| LIBRARY_USERS LIB_MANAGE_USERS MU |
| LIBRARY_USERS LIB_VIEW_USERS VU |
| |
| --- 6 row(s) selected. |
| >> |
| >>-- unregister component gets rid of priivleges |
| >>unregister component library_users cascade; |
| |
| --- SQL operation complete. |
| >>-- returns 4 rows |
| >>execute get_component_operations; |
| |
| COMPONENT OPERATION_NAME OPERATION_CODE |
| -------------------- -------------------- -------------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE MO |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS VC |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS MC |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS VC |
| |
| --- 4 row(s) selected. |
| >>-- returns 2 rows |
| >>execute get_components; |
| |
| COMPONENT DESCRIPTION |
| -------------------- ------------------------------ |
| |
| LIBRARY_ACCOUNTS TEST COMPONENT 2 |
| LIBRARY_BOOKS |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- error: unknown component |
| >>drop component privilege lib_view_repository on library_xxxx; |
| |
| *** ERROR[1004] Object LIBRARY_XXXX does not exist or object type is invalid for the current operation. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- error unknown privilege |
| >>drop component privilege lib_view_xxxx on library_books; |
| |
| *** ERROR[1004] Object LIB_VIEW_XXXX does not exist or object type is invalid for the current operation. |
| |
| --- SQL operation failed with errors. |
| >> |
| >> |
| >>log; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >>-- These tests should succeed |
| >>execute get_component_operations; |
| |
| COMPONENT OPERATION_NAME OPERATION_CODE |
| -------------------- -------------------- -------------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE MO |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS VC |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS MC |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS VC |
| |
| --- 4 row(s) selected. |
| >>grant component privilege lib_view_checkouts on library_books to sql_user4 |
| +> with grant option; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_manage_checkouts on library_books to sql_user5; |
| |
| --- SQL operation complete. |
| >>-- returns 16 rows |
| >>execute get_priv_desc; |
| |
| COMPONENT OPERATION_NAME GRANTEE_NAME GRANT_DEPTH |
| -------------------- -------------------- -------------------------------------------------------------------------------- ----------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE DB__ROOT -1 |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE LIBRARY_ADMIN 0 |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE SQL_USER2 -1 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS DB__ROOT -1 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS SQL_USER2 -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS DB__ROOT -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS SQL_USER5 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS DB__ROOT -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS SQL_USER4 -1 |
| |
| --- 16 row(s) selected. |
| >> |
| >>-- These tests should fail |
| >>register component library_other; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>create component privilege lib_other as 'LO' on library_books; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>--revoke component privilege lib_manage_checkouts |
| >>-- on library_books from library_admin; |
| >>--revoke component privilege lib_view_repository |
| >>-- on library_books from sql_user6; |
| >>log; |
| >>values(user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER4 |
| |
| --- 1 row(s) selected. |
| >>-- these tests should succeed |
| >>grant component privilege lib_view_checkouts on library_books to sql_user6; |
| |
| --- SQL operation complete. |
| >>execute get_priv_desc; |
| |
| COMPONENT OPERATION_NAME GRANTEE_NAME GRANT_DEPTH |
| -------------------- -------------------- -------------------------------------------------------------------------------- ----------- |
| |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE DB__ROOT -1 |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE LIBRARY_ADMIN 0 |
| LIBRARY_ACCOUNTS LIB_MANAGE_OVERDUE SQL_USER2 -1 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS DB__ROOT -1 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_ACCOUNTS LIB_VIEW_CHECKOUTS SQL_USER2 -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS DB__ROOT -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_MANAGE_CHECKOUTS SQL_USER5 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS DB__ROOT -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS LIBRARY_ADMIN 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS LIBRARY_CKOUT_CLERKS 0 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS SQL_USER1 -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS SQL_USER4 -1 |
| LIBRARY_BOOKS LIB_VIEW_CHECKOUTS SQL_USER6 0 |
| |
| --- 17 row(s) selected. |
| >>revoke component privilege lib_view_checkouts on library_books from sql_user6; |
| |
| --- SQL operation complete. |
| >> |
| >>-- these tests should fail |
| >>grant component privilege lib_manage_checkouts on library_books to sql_user6; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>log; |
| >>-- test getting component privileges through roles |
| >>obey TEST137(role_tests); |
| >>-- tests grants and revoke on roles owned by user work correctly |
| >>create role lib_role_test; |
| |
| --- SQL operation complete. |
| >> |
| >>-- user5_tests fail - user5 has no priv |
| >>grant component privilege lib_view_checkouts on library_books to lib_role_test with grant option; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST137(user5_tests)" -u sql_user5; |
| >>values(user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER5 |
| |
| --- 1 row(s) selected. |
| >>grant component privilege lib_view_repository on library_books to sql_user7; |
| |
| *** ERROR[1194] Component operation LIB_VIEW_REPOSITORY does not exist for component LIBRARY_BOOKS |
| |
| --- SQL operation failed with errors. |
| >>grant component privilege lib_view_checkouts on library_books to sql_user7; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>log; |
| >> |
| >>-- user5_tests for priv lib_view_checkouts succeeds, have privs through role |
| >>grant role lib_role_test to sql_user5; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST137(user5_tests)" -u sql_user5; |
| >>values(user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER5 |
| |
| --- 1 row(s) selected. |
| >>grant component privilege lib_view_repository on library_books to sql_user7; |
| |
| *** ERROR[1194] Component operation LIB_VIEW_REPOSITORY does not exist for component LIBRARY_BOOKS |
| |
| --- SQL operation failed with errors. |
| >>grant component privilege lib_view_checkouts on library_books to sql_user7; |
| |
| --- SQL operation complete. |
| >>log; |
| >>revoke component privilege lib_view_checkouts on library_books from lib_role_test; |
| |
| --- SQL operation complete. |
| >>revoke role lib_role_test from sql_user5; |
| |
| --- SQL operation complete. |
| >>drop role lib_role_test; |
| |
| --- SQL operation complete. |
| >> |
| >>-- user5_tests for priv lib_view_checkouts succeeds, have privs through DB__ROOTROLE |
| >>grant role db__rootrole to sql_user5; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST137(user5_tests)" -u sql_user5; |
| >>values(user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER5 |
| |
| --- 1 row(s) selected. |
| >>grant component privilege lib_view_repository on library_books to sql_user7; |
| |
| *** ERROR[1194] Component operation LIB_VIEW_REPOSITORY does not exist for component LIBRARY_BOOKS |
| |
| --- SQL operation failed with errors. |
| >>grant component privilege lib_view_checkouts on library_books to sql_user7; |
| |
| --- SQL operation complete. |
| >>log; |
| >>revoke role db__rootrole from sql_user5; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>-- test the SQL_OPERATIONS privilege |
| >>obey TEST137(operator_privs); |
| >>-- This section tests operation privileges: |
| >>-- Test environment |
| >>-- sql_user2 - has been granted library_admin role |
| >>-- sql_user3 - has been granted library_ckout_clerks role |
| >>-- sql_user6 - has been granted db__useradmin role |
| >>-- sql_user7 - will be granted/revoked SQL privileges |
| >>-- sql_user8 - no SQL privileges or roles with SQL privileges |
| >>-- db__root |
| >>-- db__useradmin - has implicit SQL privileges |
| >>-- library_admin - will be granted and revoked SQL privileges |
| >>-- library_ckout_clerks - no SQL privileges |
| >> |
| >>-- set up privileges |
| >>showddl role library_admin; |
| |
| CREATE ROLE "LIBRARY_ADMIN"; |
| -- GRANT ROLE "LIBRARY_ADMIN" TO "DB__ROOT" WITH ADMIN OPTION; |
| GRANT ROLE |
| "LIBRARY_ADMIN" TO "SQL_USER1"; |
| GRANT ROLE "LIBRARY_ADMIN" TO "SQL_USER2"; |
| |
| --- SQL operation complete. |
| >>showddl role library_ckout_clerks; |
| |
| CREATE ROLE "LIBRARY_CKOUT_CLERKS"; |
| -- GRANT ROLE "LIBRARY_CKOUT_CLERKS" TO "DB__ROOT" WITH ADMIN OPTION; |
| GRANT |
| ROLE "LIBRARY_CKOUT_CLERKS" TO "SQL_USER1"; |
| GRANT ROLE "LIBRARY_CKOUT_CLERKS" |
| TO "SQL_USER2"; |
| GRANT ROLE "LIBRARY_CKOUT_CLERKS" TO "SQL_USER3"; |
| GRANT ROLE |
| "LIBRARY_CKOUT_CLERKS" TO "SQL_USER4"; |
| GRANT ROLE "LIBRARY_CKOUT_CLERKS" TO |
| "SQL_USER5"; |
| |
| --- SQL operation complete. |
| >>showddl component sql_operations; |
| |
| REGISTER COMPONENT SQL_OPERATIONS SYSTEM DETAIL |
| 'System component SQL_OPERATIONS'; |
| |
| CREATE COMPONENT PRIVILEGE ALTER AS 'A0' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform ALTER operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_SCHEMA AS 'AH' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform ALTER_SCHEMA operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_LIBRARY AS 'AL' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform ALTER_LIBRARY operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_SEQUENCE AS 'AQ' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform ALTER_SEQUENCE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_SEQUENCE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_SEQUENCE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_ROUTINE AS 'AR' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform ALTER_ROUTINE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_ROUTINE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_ROUTINE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_TABLE AS 'AT' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform ALTER_TABLE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_VIEW AS 'AV' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform ALTER_VIEW operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE AS 'C0' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform CREATE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_SCHEMA AS 'CH' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform CREATE_SCHEMA operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_SCHEMA" ON "SQL_OPERATIONS" TO "PUBLIC"; |
| GRANT COMPONENT PRIVILEGE "CREATE_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_INDEX AS 'CI' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform CREATE_INDEX operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_INDEX" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_INDEX" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_LIBRARY AS 'CL' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform CREATE_LIBRARY operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_LIBRARY" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE COMMENT AS 'CO' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform COMMENT operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "COMMENT" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "COMMENT" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_PROCEDURE AS 'CP' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform CREATE_PROCEDURE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_PROCEDURE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_PROCEDURE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_SEQUENCE AS 'CQ' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform CREATE_SEQUENCE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_SEQUENCE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_SEQUENCE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_ROUTINE AS 'CR' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform CREATE_ROUTINE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_ROUTINE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_ROUTINE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_TABLE AS 'CT' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform CREATE_TABLE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_VIEW AS 'CV' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform CREATE_VIEW operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP AS 'D0' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_SCHEMA AS 'DH' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_SCHEMA operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_INDEX AS 'DI' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_INDEX operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_INDEX" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_INDEX" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_LIBRARY AS 'DL' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_LIBRARY operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_PROCEDURE AS 'DP' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform DROP_PROCEDURE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_PROCEDURE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_PROCEDURE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_SEQUENCE AS 'DQ' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform DROP_SEQUENCE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_SEQUENCE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_SEQUENCE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_ROUTINE AS 'DR' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_ROUTINE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_ROUTINE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_ROUTINE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_TABLE AS 'DT' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_TABLE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_VIEW AS 'DV' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_VIEW operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE AS 'M0' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform MANAGE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_COMPONENTS AS 'MC' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform MANAGE_COMPONENTS operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_COMPONENTS" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_COMPONENTS" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_LIBRARY AS 'ML' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform MANAGE_LIBRARY operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_LIBRARY" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_PRIVILEGES AS 'MP' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform MANAGE_PRIVILEGES operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_PRIVILEGES" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_PRIVILEGES" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_ROLES AS 'MR' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform MANAGE_ROLES operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_ROLES" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_ROLES" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_STATISTICS AS 'MS' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform MANAGE_STATISTICS operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_STATISTICS" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_STATISTICS" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_LOAD AS 'MT' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform MANAGE_LOAD operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_LOAD" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_LOAD" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_USERS AS 'MU' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform MANAGE_USERS operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_USERS" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_USERS" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DML_SELECT_METADATA AS 'PM' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform DML_SELECT_METADATA operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DML_SELECT_METADATA" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DML_SELECT_METADATA" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE QUERY_ACTIVATE AS 'QA' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform QUERY_ACTIVATE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "QUERY_ACTIVATE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "QUERY_ACTIVATE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE QUERY_CANCEL AS 'QC' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform QUERY_CANCEL operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "QUERY_CANCEL" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "QUERY_CANCEL" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE QUERY_SUSPEND AS 'QS' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform QUERY_SUSPEND operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "QUERY_SUSPEND" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "QUERY_SUSPEND" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE SHOW AS 'SW' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform SHOW operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "SHOW" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "SHOW" ON "SQL_OPERATIONS" TO "PUBLIC"; |
| GRANT COMPONENT PRIVILEGE "SHOW" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| |
| --- SQL operation complete. |
| >> |
| >>-- user2 |
| >>-- Create operations fail |
| >>sh sh runmxci.ksh -i "TEST137(user2_cc)" -u sql_user2; |
| >>-- user2 requests |
| >>--create catalog t137cat1; |
| >>--create schema t137cat1.t137sch; |
| >>--drop schema t137cat1.t137sch; |
| >>--drop catalog t137cat1; |
| >>--create schema t137sch; |
| >>--drop schema t137sch cascade; |
| >>log; |
| >>-- create catalog works |
| >>grant component privilege create_catalog on sql_operations to library_admin; |
| |
| --- SQL operation complete. |
| >>sh sh runmxci.ksh -i "TEST137(user2_cc)" -u sql_user2; |
| >>-- user2 requests |
| >>--create catalog t137cat1; |
| >>--create schema t137cat1.t137sch; |
| >>--drop schema t137cat1.t137sch; |
| >>--drop catalog t137cat1; |
| >>--create schema t137sch; |
| >>--drop schema t137sch cascade; |
| >>log; |
| >>-- create catalog and create schema works |
| >>grant component privilege create_schema on sql_operations to library_admin; |
| |
| --- SQL operation complete. |
| >>sh sh runmxci.ksh -i "TEST137(user2_cc)" -u sql_user2; |
| >>-- user2 requests |
| >>--create catalog t137cat1; |
| >>--create schema t137cat1.t137sch; |
| >>--drop schema t137cat1.t137sch; |
| >>--drop catalog t137cat1; |
| >>--create schema t137sch; |
| >>--drop schema t137sch cascade; |
| >>log; |
| >>-- create catalog fails |
| >>revoke component privilege create_catalog |
| +> on sql_operations from library_admin; |
| |
| --- SQL operation complete. |
| >>sh sh runmxci.ksh -i "TEST137(user2_cc)" -u sql_user2; |
| >>-- user2 requests |
| >>--create catalog t137cat1; |
| >>--create schema t137cat1.t137sch; |
| >>--drop schema t137cat1.t137sch; |
| >>--drop catalog t137cat1; |
| >>--create schema t137sch; |
| >>--drop schema t137sch cascade; |
| >>log; |
| >>revoke component privilege create_schema |
| +> on sql_operations from library_admin; |
| |
| --- SQL operation complete. |
| >> |
| >>-- user3 (operation fail) |
| >>sh sh runmxci.ksh -i "TEST137(user3_cc)" -u sql_user3; |
| >>-- user3 requests |
| >>--create catalog t137cat1; |
| >>--create schema t137sch; |
| >>log; |
| >> |
| >>-- user6 |
| >>-- operation fails |
| >>sh sh runmxci.ksh -i "TEST137(user6_cc)" -u sql_user6; |
| >>-- user6 requests |
| >>--create schema t137sch; |
| >>--drop schema t137sch cascade; |
| >>log; |
| >>-- operation works |
| >>grant role db__useradmin to sql_user6; |
| |
| *** ERROR[1338] Role DB__USERADMIN is not defined in the database. |
| |
| --- SQL operation failed with errors. |
| >>sh sh runmxci.ksh -i "TEST137(user6_cc)" -u sql_user6; |
| >>-- user6 requests |
| >>--create schema t137sch; |
| >>--drop schema t137sch cascade; |
| >>log; |
| >>-- operation fails |
| >>revoke role db__useradmin from sql_user6; |
| |
| *** ERROR[1338] Role DB__USERADMIN is not defined in the database. |
| |
| --- SQL operation failed with errors. |
| >>sh sh runmxci.ksh -i "TEST137(user6_cc)" -u sql_user6; |
| >>-- user6 requests |
| >>--create schema t137sch; |
| >>--drop schema t137sch cascade; |
| >>log; |
| >> |
| >>-- user7 |
| >>-- fails |
| >>sh sh runmxci.ksh -i "TEST137(user7_cc)" -u sql_user7; |
| >>-- user7 requests |
| >>--create catalog t137cat1; |
| >>--create schema t137cat1.t137sch; |
| >>--drop schema t137cat1.t137sch; |
| >>--drop catalog t137cat1; |
| >>log; |
| >>-- works |
| >>grant component privilege create_catalog, create_schema |
| +> on sql_operations to sql_user7; |
| |
| --- SQL operation complete. |
| >>sh sh runmxci.ksh -i "TEST137(user7_cc)" -u sql_user7; |
| >>-- user7 requests |
| >>--create catalog t137cat1; |
| >>--create schema t137cat1.t137sch; |
| >>--drop schema t137cat1.t137sch; |
| >>--drop catalog t137cat1; |
| >>log; |
| >>-- fails |
| >>revoke component privilege create_schema, create_catalog |
| +> on sql_operations from sql_user7; |
| |
| --- SQL operation complete. |
| >>sh sh runmxci.ksh -i "TEST137(user7_cc)" -u sql_user7; |
| >>-- user7 requests |
| >>--create catalog t137cat1; |
| >>--create schema t137cat1.t137sch; |
| >>--drop schema t137cat1.t137sch; |
| >>--drop catalog t137cat1; |
| >>log; |
| >> |
| >>-- user8 (fails) |
| >>sh sh runmxci.ksh -i "TEST137(user8_cc)" -u sql_user8; |
| >>-- user8 requests |
| >>--create catalog t137cat1; |
| >>--create schema t137sch; |
| >>log; |
| >> |
| >>showddl component sql_operations; |
| |
| REGISTER COMPONENT SQL_OPERATIONS SYSTEM DETAIL |
| 'System component SQL_OPERATIONS'; |
| |
| CREATE COMPONENT PRIVILEGE ALTER AS 'A0' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform ALTER operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_SCHEMA AS 'AH' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform ALTER_SCHEMA operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_LIBRARY AS 'AL' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform ALTER_LIBRARY operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_SEQUENCE AS 'AQ' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform ALTER_SEQUENCE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_SEQUENCE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_SEQUENCE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_ROUTINE AS 'AR' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform ALTER_ROUTINE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_ROUTINE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_ROUTINE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_TABLE AS 'AT' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform ALTER_TABLE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE ALTER_VIEW AS 'AV' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform ALTER_VIEW operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "ALTER_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "ALTER_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE AS 'C0' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform CREATE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_SCHEMA AS 'CH' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform CREATE_SCHEMA operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_SCHEMA" ON "SQL_OPERATIONS" TO "PUBLIC"; |
| GRANT COMPONENT PRIVILEGE "CREATE_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_INDEX AS 'CI' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform CREATE_INDEX operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_INDEX" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_INDEX" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_LIBRARY AS 'CL' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform CREATE_LIBRARY operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_LIBRARY" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE COMMENT AS 'CO' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform COMMENT operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "COMMENT" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "COMMENT" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_PROCEDURE AS 'CP' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform CREATE_PROCEDURE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_PROCEDURE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_PROCEDURE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_SEQUENCE AS 'CQ' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform CREATE_SEQUENCE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_SEQUENCE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_SEQUENCE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_ROUTINE AS 'CR' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform CREATE_ROUTINE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_ROUTINE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_ROUTINE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_TABLE AS 'CT' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform CREATE_TABLE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE CREATE_VIEW AS 'CV' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform CREATE_VIEW operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "CREATE_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "CREATE_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP AS 'D0' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_SCHEMA AS 'DH' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_SCHEMA operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_SCHEMA" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_INDEX AS 'DI' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_INDEX operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_INDEX" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_INDEX" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_LIBRARY AS 'DL' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_LIBRARY operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_PROCEDURE AS 'DP' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform DROP_PROCEDURE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_PROCEDURE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_PROCEDURE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_SEQUENCE AS 'DQ' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform DROP_SEQUENCE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_SEQUENCE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_SEQUENCE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_ROUTINE AS 'DR' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_ROUTINE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_ROUTINE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_ROUTINE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_TABLE AS 'DT' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_TABLE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_TABLE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DROP_VIEW AS 'DV' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform DROP_VIEW operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DROP_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DROP_VIEW" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE AS 'M0' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform MANAGE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_COMPONENTS AS 'MC' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform MANAGE_COMPONENTS operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_COMPONENTS" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_COMPONENTS" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_LIBRARY AS 'ML' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform MANAGE_LIBRARY operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_LIBRARY" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_LIBRARY" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_PRIVILEGES AS 'MP' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform MANAGE_PRIVILEGES operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_PRIVILEGES" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_PRIVILEGES" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_ROLES AS 'MR' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform MANAGE_ROLES operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_ROLES" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_ROLES" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_STATISTICS AS 'MS' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform MANAGE_STATISTICS operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_STATISTICS" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_STATISTICS" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_LOAD AS 'MT' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform MANAGE_LOAD operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_LOAD" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_LOAD" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE MANAGE_USERS AS 'MU' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform MANAGE_USERS operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "MANAGE_USERS" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "MANAGE_USERS" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE DML_SELECT_METADATA AS 'PM' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform DML_SELECT_METADATA operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "DML_SELECT_METADATA" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "DML_SELECT_METADATA" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE QUERY_ACTIVATE AS 'QA' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform QUERY_ACTIVATE operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "QUERY_ACTIVATE" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "QUERY_ACTIVATE" ON "SQL_OPERATIONS" TO |
| "DB__ROOTROLE" WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE QUERY_CANCEL AS 'QC' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform QUERY_CANCEL operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "QUERY_CANCEL" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "QUERY_CANCEL" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE QUERY_SUSPEND AS 'QS' ON SQL_OPERATIONS SYSTEM |
| DETAIL 'Allow grantee to perform QUERY_SUSPEND operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "QUERY_SUSPEND" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "QUERY_SUSPEND" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" |
| WITH GRANT OPTION; |
| |
| CREATE COMPONENT PRIVILEGE SHOW AS 'SW' ON SQL_OPERATIONS SYSTEM DETAIL |
| 'Allow grantee to perform SHOW operation'; |
| |
| -- GRANT COMPONENT PRIVILEGE "SHOW" ON "SQL_OPERATIONS" TO "DB__ROOT" WITH GRANT OPTION; |
| GRANT COMPONENT PRIVILEGE "SHOW" ON "SQL_OPERATIONS" TO "PUBLIC"; |
| GRANT COMPONENT PRIVILEGE "SHOW" ON "SQL_OPERATIONS" TO "DB__ROOTROLE" WITH |
| GRANT OPTION; |
| |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>obey TEST137(clean_up); |
| >>-- remove component information |
| >>unregister component library_books cascade; |
| |
| --- SQL operation complete. |
| >>unregister component library_accounts cascade; |
| |
| --- SQL operation complete. |
| >>unregister component library_users cascade; |
| |
| *** ERROR[1004] Object LIBRARY_USERS does not exist or object type is invalid for the current operation. |
| |
| --- SQL operation failed with errors. |
| >>unregister component library_other cascade; |
| |
| *** ERROR[1004] Object LIBRARY_OTHER does not exist or object type is invalid for the current operation. |
| |
| --- SQL operation failed with errors. |
| >>execute get_component_operations; |
| |
| --- 0 row(s) selected. |
| >>execute get_components; |
| |
| --- 0 row(s) selected. |
| >>-- drop database |
| >>drop schema t137 cascade; |
| |
| --- SQL operation complete. |
| >> |
| >>drop component privilege lib_view_benefactors cascade; |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| drop component privilege lib_view_benefactors cascade; |
| ^ (53 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>unregister component library_fund_raisers cascade; |
| |
| *** ERROR[1004] Object LIBRARY_FUND_RAISERS does not exist or object type is invalid for the current operation. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- remove roles |
| >>revoke role library_admin from sql_user1; |
| |
| --- SQL operation complete. |
| >>revoke role library_admin from sql_user2; |
| |
| --- SQL operation complete. |
| >>drop role library_admin; |
| |
| --- SQL operation complete. |
| >> |
| >>revoke role library_ckout_clerks from sql_user1; |
| |
| --- SQL operation complete. |
| >>revoke role library_ckout_clerks from sql_user2; |
| |
| --- SQL operation complete. |
| >>revoke role library_ckout_clerks from sql_user3, sql_user4, sql_user5; |
| |
| --- SQL operation complete. |
| >>drop role library_ckout_clerks; |
| |
| --- SQL operation complete. |
| >> |
| >>revoke component privilege lib_view_checkouts on library_books from lib_role_test; |
| |
| *** ERROR[1008] Authorization identifier LIB_ROLE_TEST does not exist. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>revoke role lib_role_test from sql_user5; |
| |
| *** ERROR[1338] Role LIB_ROLE_TEST is not defined in the database. |
| |
| --- SQL operation failed with errors. |
| >>revoke role db__rootrole from sql_user5; |
| |
| *** ERROR[1018] Grant of role or privilege DB__ROOTROLE from DB__ROOT to SQL_USER5 not found, revoke request ignored. |
| |
| --- SQL operation failed with errors. |
| >>drop role lib_role_test; |
| |
| *** ERROR[1338] Role LIB_ROLE_TEST is not defined in the database. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>revoke role md_access from sql_user1, sql_user4, sql_user5; |
| |
| --- SQL operation complete. |
| >>revoke select on "_MD_".auths from md_access; |
| |
| --- SQL operation complete. |
| >>revoke select on "_PRIVMGR_MD_".components from md_access; |
| |
| --- SQL operation complete. |
| >>revoke select on "_PRIVMGR_MD_".component_operations from md_access; |
| |
| --- SQL operation complete. |
| >>revoke select on "_PRIVMGR_MD_".component_privileges from md_access; |
| |
| --- SQL operation complete. |
| >>drop role md_access; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>-- run tests for revoke. |
| >>obey TEST137(revoke_comp_privs_setup); |
| >>cqd ALLOW_WGO_FOR_ROLES 'on'; |
| |
| --- SQL operation complete. |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; |
| |
| --- SQL operation complete. |
| >>log LOG137; |
| >>create role library_admin; |
| |
| --- SQL operation complete. |
| >>grant role library_admin to sql_user1, sql_user2; |
| |
| --- SQL operation complete. |
| >>create role library_ckout_clerks; |
| |
| --- SQL operation complete. |
| >>grant role library_ckout_clerks to sql_user1, sql_user2, sql_user3, sql_user4, sql_user5; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>register component Library_books; |
| |
| --- SQL operation complete. |
| >>create component privilege lib_manage_checkouts as 'MC' on library_books; |
| |
| --- SQL operation complete. |
| >>create component privilege lib_view_checkouts as 'VC' on library_books |
| +> detail 'Can see checkout information'; |
| |
| --- SQL operation complete. |
| >>create component privilege lib_view_repository as 'VR' on library_books; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_manage_checkouts, |
| +> lib_view_checkouts, |
| +> lib_view_repository |
| +> on library_books to library_admin; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_manage_checkouts, |
| +> lib_view_checkouts, |
| +> lib_view_repository |
| +> on library_books to sql_user1 with grant option; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_view_checkouts, |
| +> lib_view_repository |
| +> on library_books to library_ckout_clerks; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user5; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user6; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user7; |
| |
| --- SQL operation complete. |
| >>grant component privilege lib_view_repository |
| +> on library_books to sql_user8; |
| |
| --- SQL operation complete. |
| >> |
| >>log; |
| >>grant component privilege lib_view_repository |
| +>on library_books to sql_user2 with grant option; |
| |
| --- SQL operation complete. |
| >>log; |
| >>grant component privilege lib_view_repository |
| +>on library_books to sql_user3 with grant option; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_view_repository |
| +>on library_books to sql_user1; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_view_repository |
| +>on library_books to sql_user5; |
| |
| --- SQL operation complete. |
| >>log; |
| >>grant component privilege lib_view_repository |
| +>on library_books to DB__ROOT with grant option; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_view_repository |
| +>on library_books to sql_user5; |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege lib_view_repository |
| +>on library_books to sql_user4 with grant option; |
| |
| --- SQL operation complete. |
| >>log; |
| >>grant component privilege lib_view_repository |
| +>on library_books to sql_user5; |
| |
| --- SQL operation complete. |
| >>log; |
| >>revoke component privilege lib_view_repository |
| +>on library_books from sql_user3; |
| |
| *** ERROR[1025] Request failed. Dependent object exists. |
| |
| --- SQL operation failed with errors. |
| >>log; |
| >>unregister component Library_books cascade; |
| |
| --- SQL operation complete. |
| >>revoke role library_admin from sql_user1, sql_user2; |
| |
| --- SQL operation complete. |
| >>revoke role library_ckout_clerks from sql_user1, sql_user2, sql_user3, sql_user4, sql_user5; |
| |
| --- SQL operation complete. |
| >>drop role library_admin; |
| |
| --- SQL operation complete. |
| >>drop role library_ckout_clerks; |
| |
| --- SQL operation complete. |
| >>log; |
