>>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;