| >>sh rm -f LOG135-SECONDARY; |
| >>obey TEST135(set_up); |
| >>-- ============================================================================ |
| >> |
| >>-- create schema |
| >>create shared schema t135sch; |
| |
| --- SQL operation complete. |
| >> |
| >>-- Prepare library file |
| >>sh rm -f ./udrtest135.dll; |
| >>sh sh $$scriptsdir$$/tools/dll-compile.ksh udrtest135.cpp |
| +> 2>&1 | tee LOG135-SECONDARY; |
| >>set pattern $$DLL$$ udrtest135.dll; |
| >> |
| >>get tables in schema "_PRIVMGR_MD_"; |
| |
| Tables in Schema TRAFODION._PRIVMGR_MD_ |
| ======================================= |
| |
| COLUMN_PRIVILEGES |
| COMPONENTS |
| COMPONENT_OPERATIONS |
| COMPONENT_PRIVILEGES |
| OBJECT_PRIVILEGES |
| ROLE_USAGE |
| SCHEMA_PRIVILEGES |
| |
| --- SQL operation complete. |
| >> |
| >>-- Prepare metadata queries |
| >>prepare check_privs from |
| +>select object_name, grantee_name, grantor_name |
| +>from "_PRIVMGR_MD_".object_privileges |
| +>where |
| +> object_name in ('TRAFODION.T135SCH.T135_T1', 'TRAFODION.T135SCH.T135_T2', 'TRAFODION.T135SCH.T135_V1', 'TRAFODION.T135SCH.T135_V2', 'TRAFODION.T135SCH.T135_L1', 'TRAFODION.T135SCH.T135_L2', 'TRAFODION.T135SCH.T135_SESSIONIZE', 'TRAFODION.T135SCH.T135_ADD2') |
| +>order by 1,3,2 for read uncommitted access; |
| |
| --- SQL command prepared. |
| >> |
| >>obey TEST135(tbl_tests); |
| >>-- ============================================================================ |
| >>set schema t135sch; |
| |
| --- SQL operation complete. |
| >> |
| >>-- Verify that a create table adds privilege manager metadata |
| >>create table t135_t1 (c1 int not null primary key, c2 int); |
| |
| --- SQL operation complete. |
| >>-- returns 1 row |
| >>execute check_privs; |
| |
| OBJECT_NAME GRANTEE_NAME GRANTOR_NAME |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| TRAFODION.T135SCH.T135_T1 DB__ROOT _SYSTEM |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- Verify that a drop table removes privilege manager metadata |
| >>drop table t135_t1; |
| |
| --- SQL operation complete. |
| >>-- returns 0 rows |
| >>execute check_privs; |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- Verify metadata for tables and indexes |
| >>create table t135_t1 (c1 int not null primary key, c2 int); |
| |
| --- SQL operation complete. |
| >>create index ndx1 on t135_t1(c2); |
| |
| --- SQL operation complete. |
| >>-- returns 1 row |
| >>execute check_privs; |
| |
| OBJECT_NAME GRANTEE_NAME GRANTOR_NAME |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| TRAFODION.T135SCH.T135_T1 DB__ROOT _SYSTEM |
| |
| --- 1 row(s) selected. |
| >> |
| >>drop table t135_t1; |
| |
| --- SQL operation complete. |
| >>-- returns 0 rows |
| >>execute check_privs; |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- Verify metadata for tables, indexes, and views |
| >>create table t135_t1 (c1 int not null primary key, c2 int); |
| |
| --- SQL operation complete. |
| >>create index ndx1 on t135_t1(c2); |
| |
| --- SQL operation complete. |
| >>create view t135_v1 as select * from t135_t1; |
| |
| --- SQL operation complete. |
| >>create view t135_v2 as select * from t135_t1; |
| |
| --- SQL operation complete. |
| >>-- returns 3 rows |
| >>execute check_privs; |
| |
| OBJECT_NAME GRANTEE_NAME GRANTOR_NAME |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| TRAFODION.T135SCH.T135_T1 DB__ROOT _SYSTEM |
| TRAFODION.T135SCH.T135_V1 DB__ROOT _SYSTEM |
| TRAFODION.T135SCH.T135_V2 DB__ROOT _SYSTEM |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- fails |
| >>drop table t135_t1; |
| |
| *** ERROR[1047] Request failed. Dependent view TRAFODION.T135SCH.T135_V1 exists. |
| |
| --- SQL operation failed with errors. |
| >>-- returns 3 rows |
| >>execute check_privs; |
| |
| OBJECT_NAME GRANTEE_NAME GRANTOR_NAME |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| TRAFODION.T135SCH.T135_T1 DB__ROOT _SYSTEM |
| TRAFODION.T135SCH.T135_V1 DB__ROOT _SYSTEM |
| TRAFODION.T135SCH.T135_V2 DB__ROOT _SYSTEM |
| |
| --- 3 row(s) selected. |
| >> |
| >>drop table t135_t1 cascade; |
| |
| --- SQL operation complete. |
| >>-- returns 0 rows |
| >>execute check_privs; |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- verify views referencing multiple tables and create table like |
| >>create table t135_t1 (c1 int not null primary key, c2 int); |
| |
| --- SQL operation complete. |
| >>create table t135_t2 like t135_t1; |
| |
| --- SQL operation complete. |
| >>create view t135_v1 as select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2 |
| +> where t135_t1.c1 = t135_t2.c1; |
| |
| --- SQL operation complete. |
| >>-- return 3 rows |
| >>execute check_privs; |
| |
| OBJECT_NAME GRANTEE_NAME GRANTOR_NAME |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| TRAFODION.T135SCH.T135_T1 DB__ROOT _SYSTEM |
| TRAFODION.T135SCH.T135_T2 DB__ROOT _SYSTEM |
| TRAFODION.T135SCH.T135_V1 DB__ROOT _SYSTEM |
| |
| --- 3 row(s) selected. |
| >> |
| >>drop view t135_v1; |
| |
| --- SQL operation complete. |
| >>-- return 2 rows |
| >>execute check_privs; |
| |
| OBJECT_NAME GRANTEE_NAME GRANTOR_NAME |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| TRAFODION.T135SCH.T135_T1 DB__ROOT _SYSTEM |
| TRAFODION.T135SCH.T135_T2 DB__ROOT _SYSTEM |
| |
| --- 2 row(s) selected. |
| >>drop table t135_t1; |
| |
| --- SQL operation complete. |
| >>-- return 1 rows |
| >>execute check_privs; |
| |
| OBJECT_NAME GRANTEE_NAME GRANTOR_NAME |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| TRAFODION.T135SCH.T135_T2 DB__ROOT _SYSTEM |
| |
| --- 1 row(s) selected. |
| >>drop table t135_t2; |
| |
| --- SQL operation complete. |
| >>-- return 0 rows |
| >>execute check_privs; |
| |
| --- 0 row(s) selected. |
| >> |
| >>obey TEST135(view_tests); |
| >>-- ============================================================================ |
| >>set schema t135sch; |
| |
| --- SQL operation complete. |
| >>create table t135_t1 (c1 int not null primary key, c2 int); |
| |
| --- SQL operation complete. |
| >>create table t135_t2 (c1 int not null primary key, c2 int); |
| |
| --- SQL operation complete. |
| >>create table t135_t3 (a int, b int); |
| |
| --- SQL operation complete. |
| >>create sequence t135seq; |
| |
| --- SQL operation complete. |
| >> |
| >>-- create a view referencing a single table where view creator has all privs |
| >>create view t135_v1_t1 as select * from t135_t1; |
| |
| --- SQL operation complete. |
| >>-- view should be granted all DML privileges |
| >>showddl t135_v1_t1; |
| |
| CREATE VIEW TRAFODION.T135SCH.T135_V1_T1 AS |
| SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T1.C2 FROM |
| TRAFODION.T135SCH.T135_T1 ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_V1_T1 TO DB__ROOT WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >>drop view t135_v1_t1; |
| |
| --- SQL operation complete. |
| >> |
| >>-- create a non updatable, non insertable view |
| >>create view t135_v2_t1 |
| +>as select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2; |
| |
| --- SQL operation complete. |
| >>-- view should be granted only SELECT and REFERENCES privileges |
| >>showddl t135_v2_t1; |
| |
| CREATE VIEW TRAFODION.T135SCH.T135_V2_T1 AS |
| SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T2.C2 FROM |
| TRAFODION.T135SCH.T135_T1, TRAFODION.T135SCH.T135_T2 ; |
| |
| -- GRANT SELECT, REFERENCES ON TRAFODION.T135SCH.T135_V2_T1 TO DB__ROOT WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >>drop view t135_v2_t1; |
| |
| --- SQL operation complete. |
| >> |
| >>-- verify that users granted select privilege can create views |
| >>-- user cannot create view |
| >>sh sqlci -i "TEST135(user1_views)" -u sql_user1; |
| >>create view user1_v1 as select * from t135_t1; |
| |
| *** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T135SCH.T135_T1. |
| |
| --- SQL operation failed with errors. |
| >>showddl user1_v1; |
| |
| *** ERROR[4082] Object TRAFODION.T135SCH.USER1_V1 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>drop view user1_v1; |
| |
| *** ERROR[1389] Object TRAFODION.T135SCH.USER1_V1 does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- user can create view but only have select priv |
| >>grant select on t135_t1 to sql_user1; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST135(user1_views)" -u sql_user1; |
| >>create view user1_v1 as select * from t135_t1; |
| |
| --- SQL operation complete. |
| >>showddl user1_v1; |
| |
| CREATE VIEW TRAFODION.T135SCH.USER1_V1 AS |
| SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T1.C2 FROM |
| TRAFODION.T135SCH.T135_T1 ; |
| |
| -- GRANT SELECT ON TRAFODION.T135SCH.USER1_V1 TO SQL_USER1; |
| |
| --- SQL operation complete. |
| >>drop view user1_v1; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- user can create view and have all_dml privs |
| >>grant all_dml on t135_t1 to sql_user1; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST135(user1_views)" -u sql_user1; |
| >>create view user1_v1 as select * from t135_t1; |
| |
| --- SQL operation complete. |
| >>showddl user1_v1; |
| |
| CREATE VIEW TRAFODION.T135SCH.USER1_V1 AS |
| SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T1.C2 FROM |
| TRAFODION.T135SCH.T135_T1 ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.USER1_V1 TO SQL_USER1; |
| |
| --- SQL operation complete. |
| >>drop view user1_v1; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- make sure creator privileges are set when multiple referenced |
| >>-- objects are involved. |
| >>grant all_dml on t135_t1 to sql_user2; |
| |
| --- SQL operation complete. |
| >>-- should fail user2 does not have select privilege on t135_t2 |
| >>sh sqlci -i "TEST135(user2_views)" -u sql_user2; |
| >>create view user2_v1 as |
| +>select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2; |
| |
| *** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T135SCH.T135_T2. |
| |
| --- SQL operation failed with errors. |
| >>showddl user2_v1; |
| |
| *** ERROR[4082] Object TRAFODION.T135SCH.USER2_V1 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>drop view user2_v1; |
| |
| *** ERROR[1389] Object TRAFODION.T135SCH.USER2_V1 does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>grant select on t135_t2 to sql_user2; |
| |
| --- SQL operation complete. |
| >>-- user can create view and have select priv |
| >>sh sqlci -i "TEST135(user2_views)" -u sql_user2; |
| >>create view user2_v1 as |
| +>select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2; |
| |
| --- SQL operation complete. |
| >>showddl user2_v1; |
| |
| CREATE VIEW TRAFODION.T135SCH.USER2_V1 AS |
| SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T2.C2 FROM |
| TRAFODION.T135SCH.T135_T1, TRAFODION.T135SCH.T135_T2 ; |
| |
| -- GRANT SELECT ON TRAFODION.T135SCH.USER2_V1 TO SQL_USER2; |
| |
| --- SQL operation complete. |
| >>drop view user2_v1; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>grant all_DML on t135_t2 to sql_user2; |
| |
| --- SQL operation complete. |
| >>showddl t135_t1; |
| |
| CREATE TABLE TRAFODION.T135SCH.T135_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T1 |
| TO SQL_USER1; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T135SCH.T135_T1 TO SQL_USER2; |
| |
| --- SQL operation complete. |
| >>showddl t135_t2; |
| |
| CREATE TABLE TRAFODION.T135SCH.T135_T2 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T2 |
| TO SQL_USER2; |
| |
| --- SQL operation complete. |
| >>-- user can create view but just have select and references, it is |
| >>-- a not updatable and insertable view |
| >>sh sqlci -i "TEST135(user2_views)" -u sql_user2; |
| >>create view user2_v1 as |
| +>select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2; |
| |
| --- SQL operation complete. |
| >>showddl user2_v1; |
| |
| CREATE VIEW TRAFODION.T135SCH.USER2_V1 AS |
| SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T2.C2 FROM |
| TRAFODION.T135SCH.T135_T1, TRAFODION.T135SCH.T135_T2 ; |
| |
| -- GRANT SELECT, REFERENCES ON TRAFODION.T135SCH.USER2_V1 TO SQL_USER2; |
| |
| --- SQL operation complete. |
| >>drop view user2_v1; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- test creating a view from several views and tables |
| >>create view t135_v1_t1 as select c1 from t135_t1; |
| |
| --- SQL operation complete. |
| >>create view t135_v2_t1 as select c2 from t135_t1; |
| |
| --- SQL operation complete. |
| >>create view t135_v1_t2 as select * from t135_t2; |
| |
| --- SQL operation complete. |
| >> |
| >>-- have user3 create some objects |
| >>sh sqlci -i "TEST135(user3_objects)" -u sql_user3; |
| >>create table t135_t3 (c1 int not null primary key, c2 int); |
| |
| --- SQL operation complete. |
| >>create table t135_t4 (c1 int not null, c2 largeint not null primary key, c3 int); |
| |
| --- SQL operation complete. |
| >>showddl t135_t3; |
| |
| CREATE TABLE TRAFODION.T135SCH_USER3.T135_T3 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T3 TO SQL_USER3 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >>showddl t135_t4; |
| |
| CREATE TABLE TRAFODION.T135SCH_USER3.T135_T4 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C3 INT DEFAULT NULL |
| , PRIMARY KEY (C2 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T4 TO SQL_USER3 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- user3 create some views |
| >>-- fails because user3 has no privs |
| >>sh sqlci -i "TEST135(user3_views)" -u sql_user3; |
| >>create view t135_v1_user3 as |
| +> select t135sch.t135_t1.c2, t135sch.t135_v1_t1.c1, t135_t4.c3 |
| +> from t135sch.t135_t1, t135sch.t135_v1_t1, t135_t4; |
| |
| *** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T135SCH.T135_T1. |
| |
| *** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T135SCH.T135_V1_T1. |
| |
| --- SQL operation failed with errors. |
| >>showddl t135_v1_user3; |
| |
| *** ERROR[4082] Object TRAFODION.T135SCH_USER3.T135_V1_USER3 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>grant select on t135_v1_t1 to sql_user3; |
| |
| --- SQL operation complete. |
| >>grant select on t135_t1 to sql_user3; |
| |
| --- SQL operation complete. |
| >>-- operations should succeed |
| >>sh sqlci -i "TEST135(user3_views)" -u sql_user3; |
| >>create view t135_v1_user3 as |
| +> select t135sch.t135_t1.c2, t135sch.t135_v1_t1.c1, t135_t4.c3 |
| +> from t135sch.t135_t1, t135sch.t135_v1_t1, t135_t4; |
| |
| --- SQL operation complete. |
| >>showddl t135_v1_user3; |
| |
| CREATE VIEW TRAFODION.T135SCH_USER3.T135_V1_USER3 AS |
| SELECT TRAFODION.T135SCH.T135_T1.C2, TRAFODION.T135SCH.T135_V1_T1.C1, |
| TRAFODION.T135SCH_USER3.T135_T4.C3 FROM TRAFODION.T135SCH.T135_T1, |
| TRAFODION.T135SCH.T135_V1_T1, TRAFODION.T135SCH_USER3.T135_T4 ; |
| |
| -- GRANT SELECT ON TRAFODION.T135SCH_USER3.T135_V1_USER3 TO SQL_USER3; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- Since user3 now has a view, try to revoke privileges |
| >>-- fails because of user3's views |
| >>revoke select on t135_v1_t1 from sql_user3; |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION.T135SCH_USER3.T135_V1_USER3 exists. |
| |
| --- SQL operation failed with errors. |
| >>revoke all on t135_t1 from sql_user3; |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION.T135SCH_USER3.T135_V1_USER3 exists. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>sh sqlci -i "TEST135(user3_drops)" -u sql_user3; |
| >>drop table t135_t3 cascade; |
| |
| --- SQL operation complete. |
| >>drop table t135_t4 cascade; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- create some roles |
| >>create role t135_role1; |
| |
| --- SQL operation complete. |
| >>create role t135_role2; |
| |
| --- SQL operation complete. |
| >>grant role t135_role1, t135_role2 to sql_user4; |
| |
| --- SQL operation complete. |
| >>grant select on t135_t1 to t135_role1; |
| |
| --- SQL operation complete. |
| >>grant select on t135_v1_t1 to t135_role2; |
| |
| --- SQL operation complete. |
| >> |
| >>-- have sql_user4 create a view based on role privs |
| >>create schema if not exists t135sch_user4 authorization sql_user4; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST135(user4_views)" -u sql_user4; |
| >>create view t135_v1_user4 as |
| +> select t135sch.t135_t1.c2, t135sch.t135_v1_t1.c1 |
| +> from t135sch.t135_t1, t135sch.t135_v1_t1; |
| |
| --- SQL operation complete. |
| >>showddl t135_v1_user4; |
| |
| CREATE VIEW TRAFODION.T135SCH_USER4.T135_V1_USER4 AS |
| SELECT TRAFODION.T135SCH.T135_T1.C2, TRAFODION.T135SCH.T135_V1_T1.C1 FROM |
| TRAFODION.T135SCH.T135_T1, TRAFODION.T135SCH.T135_V1_T1 ; |
| |
| -- GRANT SELECT ON TRAFODION.T135SCH_USER4.T135_V1_USER4 TO SQL_USER4; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- should not be able to revoke user4 from role |
| >>revoke role t135_role1 from sql_user4; |
| |
| *** ERROR[1364] Cannot revoke role T135_ROLE1. Object TRAFODION.T135SCH_USER4.T135_V1_USER4 depends on privileges on object TRAFODION.T135SCH.T135_T1. |
| |
| --- SQL operation failed with errors. |
| >>revoke role t135_role2 from sql_user4; |
| |
| *** ERROR[1364] Cannot revoke role T135_ROLE2. Object TRAFODION.T135SCH_USER4.T135_V1_USER4 depends on privileges on object TRAFODION.T135SCH.T135_V1_T1. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- after droppping the view, revokes succeed |
| >>drop view t135sch_user4.t135_v1_user4; |
| |
| --- SQL operation complete. |
| >>revoke role t135_role1 from sql_user4; |
| |
| --- SQL operation complete. |
| >>revoke role t135_role2 from sql_user4; |
| |
| --- SQL operation complete. |
| >> |
| >>-- test views that reference sequence generators |
| >>GRANT COMPONENT privilege create_view on sql_operations to sql_user5; |
| |
| --- SQL operation complete. |
| >>insert into t135_t3 values (1,1); |
| |
| --- 1 row(s) inserted. |
| >>select * from t135_t3; |
| |
| A B |
| ----------- ----------- |
| |
| 1 1 |
| |
| --- 1 row(s) selected. |
| >>grant select on t135_t3 to sql_user5; |
| |
| --- SQL operation complete. |
| >>showddl t135_t3; |
| |
| CREATE TABLE TRAFODION.T135SCH.T135_T3 |
| ( |
| A INT DEFAULT NULL |
| , B INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T3 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT ON TRAFODION.T135SCH.T135_T3 TO SQL_USER5; |
| |
| --- SQL operation complete. |
| >>create schema if not exists t135sch_user5 authorization sql_user5; |
| |
| --- SQL operation complete. |
| >> |
| >>-- unable to create view, lacking USAGE privilege |
| >>sh sqlci -i "TEST135(user5_views)" -u sql_user5; |
| >>create view t135_v1_user5 as select seqnum(t135sch.t135seq) as a from t135sch.t135_t3; |
| |
| *** ERROR[4491] The user does not have USAGE privilege on sequence TRAFODION.T135SCH.T135SEQ. |
| |
| --- SQL operation failed with errors. |
| >>showddl t135_v1_user5; |
| |
| *** ERROR[4082] Object TRAFODION.T135SCH_USER5.T135_V1_USER5 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select * from t135_v1_user5; |
| |
| *** ERROR[4082] Object TRAFODION.T135SCH_USER5.T135_V1_USER5 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>set schema t135sch; |
| |
| --- SQL operation complete. |
| >>create view t135_v1_user5 as select seqnum(t135seq) as a from t135_t3; |
| |
| *** ERROR[4491] The user does not have USAGE privilege on sequence TRAFODION.T135SCH.T135SEQ. |
| |
| --- SQL operation failed with errors. |
| >>showddl t135_v1_user5; |
| |
| *** ERROR[4082] Object TRAFODION.T135SCH.T135_V1_USER5 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select * from t135_v1_user5; |
| |
| *** ERROR[4082] Object TRAFODION.T135SCH.T135_V1_USER5 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- grant privilege and try again |
| >>grant usage on sequence t135seq to sql_user5; |
| |
| --- SQL operation complete. |
| >>showddl sequence t135seq; |
| |
| CREATE SEQUENCE TRAFODION.T135SCH.T135SEQ |
| START WITH 1 /* NEXT AVAILABLE VALUE 1 */ |
| INCREMENT BY 1 |
| MAXVALUE 9223372036854775806 |
| MINVALUE 1 |
| CACHE 25 |
| NO CYCLE |
| LARGEINT |
| ; |
| |
| -- GRANT USAGE ON SEQUENCE TRAFODION.T135SCH.T135SEQ TO DB__ROOT WITH GRANT OPTION; |
| GRANT USAGE ON SEQUENCE TRAFODION.T135SCH.T135SEQ TO SQL_USER5; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST135(user5_views)" -u sql_user5; |
| >>create view t135_v1_user5 as select seqnum(t135sch.t135seq) as a from t135sch.t135_t3; |
| |
| --- SQL operation complete. |
| >>showddl t135_v1_user5; |
| |
| CREATE VIEW TRAFODION.T135SCH_USER5.T135_V1_USER5 AS |
| SELECT SEQNUM (TRAFODION.T135SCH.T135SEQ) AS A FROM |
| TRAFODION.T135SCH.T135_T3 ; |
| |
| -- GRANT SELECT, REFERENCES ON TRAFODION.T135SCH_USER5.T135_V1_USER5 TO SQL_USER5 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >>select * from t135_v1_user5; |
| |
| A |
| -------------------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >>set schema t135sch; |
| |
| --- SQL operation complete. |
| >>create view t135_v1_user5 as select seqnum(t135seq) as a from t135_t3; |
| |
| --- SQL operation complete. |
| >>showddl t135_v1_user5; |
| |
| CREATE VIEW TRAFODION.T135SCH.T135_V1_USER5 AS |
| SELECT SEQNUM (TRAFODION.T135SCH.T135SEQ) AS A FROM |
| TRAFODION.T135SCH.T135_T3 ; |
| |
| -- GRANT SELECT, REFERENCES ON TRAFODION.T135SCH.T135_V1_USER5 TO SQL_USER5 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >>select * from t135_v1_user5; |
| |
| A |
| -------------------- |
| |
| 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- try revoking privilege |
| >>-- fails because of sql_user5's view |
| >>revoke usage on sequence t135seq from sql_user5; |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION.T135SCH_USER5.T135_V1_USER5 exists. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- drop views and try again |
| >>drop view t135sch_user5.t135_v1_user5; |
| |
| --- SQL operation complete. |
| >>revoke usage on sequence t135seq from sql_user5; |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION.T135SCH.T135_V1_USER5 exists. |
| |
| --- SQL operation failed with errors. |
| >>drop view t135sch.t135_v1_user5; |
| |
| --- SQL operation complete. |
| >>revoke usage on sequence t135seq from sql_user5; |
| |
| --- SQL operation complete. |
| >>showddl sequence t135seq; |
| |
| CREATE SEQUENCE TRAFODION.T135SCH.T135SEQ |
| START WITH 1 /* NEXT AVAILABLE VALUE 26 */ |
| INCREMENT BY 1 |
| MAXVALUE 9223372036854775806 |
| MINVALUE 1 |
| CACHE 25 |
| NO CYCLE |
| LARGEINT |
| ; |
| |
| -- GRANT USAGE ON SEQUENCE TRAFODION.T135SCH.T135SEQ TO DB__ROOT WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>-- redo but this time grant SELECT, should fail |
| >>grant select on sequence t135seq to sql_user5; |
| |
| *** ERROR[1267] SELECT privilege is incompatible with this object type. |
| |
| --- SQL operation failed with errors. |
| >>showddl sequence t135seq; |
| |
| CREATE SEQUENCE TRAFODION.T135SCH.T135SEQ |
| START WITH 1 /* NEXT AVAILABLE VALUE 26 */ |
| INCREMENT BY 1 |
| MAXVALUE 9223372036854775806 |
| MINVALUE 1 |
| CACHE 25 |
| NO CYCLE |
| LARGEINT |
| ; |
| |
| -- GRANT USAGE ON SEQUENCE TRAFODION.T135SCH.T135SEQ TO DB__ROOT WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >>REVOKE COMPONENT privilege create_view on sql_operations from sql_user5; |
| |
| --- SQL operation complete. |
| >> |
| >>drop table t135_t1 cascade; |
| |
| --- SQL operation complete. |
| >>drop table t135_t2 cascade; |
| |
| --- SQL operation complete. |
| >>drop role t135_role1; |
| |
| --- SQL operation complete. |
| >>drop role t135_role2; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST135(udr_tests); |
| >>-- ============================================================================ |
| >>-- To create a function/table_mapping function, or SPJ, you need |
| >>-- to: |
| >>-- be DB__ROOT |
| >>-- be library owner |
| >>-- have the CREATE_ROUTINE component privilege |
| >>-- have USAGE privilege on the library |
| >>set schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >> |
| >>create role t135_role1; |
| |
| --- SQL operation complete. |
| >>get roles; |
| |
| Roles |
| ===== |
| |
| DB__HBASEROLE |
| DB__HIVEROLE |
| DB__LIBMGRROLE |
| DB__ROOTROLE |
| PUBLIC |
| T135_ROLE1 |
| |
| --- SQL operation complete. |
| >> |
| >>grant component privilege MANAGE_LIBRARY on sql_operations to t135_role1; |
| |
| --- SQL operation complete. |
| >>grant component privilege CREATE_ROUTINE on sql_operations to "PUBLIC"; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for t135_role1; |
| |
| Privilege information on Component SQL_OPERATIONS for T135_ROLE1 |
| ================================================================ |
| |
| MANAGE_LIBRARY |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for "PUBLIC"; |
| |
| Privilege information on Component SQL_OPERATIONS for PUBLIC |
| ============================================================ |
| |
| CREATE_ROUTINE |
| CREATE_SCHEMA |
| SHOW |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>-- create library and udrs as sql_user1 |
| >>-- sql_user1 needs MANAGE_LIBRARY privilege to create libraries |
| >>-- fails - unsufficient privs |
| >>sh sqlci -i "TEST135(create_library)" -u sql_user1; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>create private schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >>set schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >>create library t135_l1 file 'udrtest135.dll'; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>grant USAGE on library t135_l1 to sql_user3; |
| |
| *** ERROR[1389] Object T135_L1 does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- now it succeeds |
| >>grant role t135_role1 to sql_user1; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST135(create_library)" -u sql_user1; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>create private schema t135sch_udrs; |
| |
| *** ERROR[1022] Schema TRAFODION.T135SCH_UDRS already exists. |
| |
| --- SQL operation failed with errors. |
| >>set schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >>create library t135_l1 file 'udrtest135.dll'; |
| |
| --- SQL operation complete. |
| >>grant USAGE on library t135_l1 to sql_user3; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>sh sqlci -i "TEST135(create_drop_udrs)" -u sql_user1; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>obey TEST135(create_udrs); |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; |
| |
| --- SQL operation complete. |
| >>set schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >>log LOG135; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>create table_mapping function t135_sessionize(colname char(10), timeintval int) |
| +>returns (userid char(32), ts largeint, session_id largeint) |
| +>external name 'SESSIONIZE' |
| +>library t135_l1; |
| |
| --- SQL operation complete. |
| >> |
| >>create function t135_ADD2(int,int) returns (ADD2 int) |
| +>language c parameter style sql external name 'add2' |
| +>library t135_l1 |
| +>deterministic no sql final call allow any parallelism state area size 1024 ; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST135(drop_udrs); |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; |
| |
| --- SQL operation complete. |
| >>set schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >>log LOG135; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>drop function t135_add2; |
| |
| --- SQL operation complete. |
| >>drop table_mapping function t135_sessionize; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- verify that DB__ROOT can create/drop udrs |
| >>-- library l1 is owned by sql_user1 |
| >>obey TEST135(create_drop_udrs); |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; |
| |
| --- SQL operation complete. |
| >>set schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >>log LOG135; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| DB__ROOT |
| |
| --- 1 row(s) selected. |
| >> |
| >>obey TEST135(create_udrs); |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; |
| |
| --- SQL operation complete. |
| >>set schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >>log LOG135; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| DB__ROOT |
| |
| --- 1 row(s) selected. |
| >> |
| >>create table_mapping function t135_sessionize(colname char(10), timeintval int) |
| +>returns (userid char(32), ts largeint, session_id largeint) |
| +>external name 'SESSIONIZE' |
| +>library t135_l1; |
| |
| --- SQL operation complete. |
| >> |
| >>create function t135_ADD2(int,int) returns (ADD2 int) |
| +>language c parameter style sql external name 'add2' |
| +>library t135_l1 |
| +>deterministic no sql final call allow any parallelism state area size 1024 ; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST135(drop_udrs); |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; |
| |
| --- SQL operation complete. |
| >>set schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >>log LOG135; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| DB__ROOT |
| |
| --- 1 row(s) selected. |
| >> |
| >>drop function t135_add2; |
| |
| --- SQL operation complete. |
| >>drop table_mapping function t135_sessionize; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >> |
| >>-- verify that user with USAGE privilege on library can create/drop udrs |
| >>-- first show lack of USAGE privilege |
| >>sh sqlci -i "TEST135(create_udrs)" -u sql_user2; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>create table_mapping function t135_sessionize(colname char(10), timeintval int) |
| +>returns (userid char(32), ts largeint, session_id largeint) |
| +>external name 'SESSIONIZE' |
| +>library t135_l1; |
| |
| *** ERROR[4481] The user does not have USAGE privilege on table or view TRAFODION.T135SCH_UDRS.T135_L1. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>create function t135_ADD2(int,int) returns (ADD2 int) |
| +>language c parameter style sql external name 'add2' |
| +>library t135_l1 |
| +>deterministic no sql final call allow any parallelism state area size 1024 ; |
| |
| *** ERROR[4481] The user does not have USAGE privilege on table or view TRAFODION.T135SCH_UDRS.T135_L1. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- grant and verify user granted USAGE privilege can create udrs |
| >>sh sqlci -i "TEST135(create_user3_udrs)" -u sql_user3; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER3 |
| |
| --- 1 row(s) selected. |
| >> |
| >>create table_mapping function t135_sessionize(colname char(10), timeintval int) |
| +>returns (userid char(32), ts largeint, session_id largeint) |
| +>external name 'SESSIONIZE' |
| +>library t135sch_udrs.t135_l1; |
| |
| --- SQL operation complete. |
| >> |
| >>create function t135_ADD2(int,int) returns (ADD2 int) |
| +>language c parameter style sql external name 'add2' |
| +>library t135sch_udrs.t135_l1 |
| +>deterministic no sql final call allow any parallelism state area size 1024 ; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- try to revoke the USAGE privilege from sql_user3 |
| >>sh sqlci -i "TEST135(revoke_usage)" -u sql_user1; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>revoke usage on library t135_l1 from sql_user3; |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION."T135SCH_USER3"."T135_SESSIONIZE" exists. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- now it should work |
| >>drop function TRAFODION."T135SCH_USER3"."T135_ADD2"; |
| |
| --- SQL operation complete. |
| >>drop table_mapping function t135sch_user3.t135_sessionize; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST135(revoke_usage)" -u sql_user1; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>revoke usage on library t135_l1 from sql_user3; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- reset |
| >>obey TEST135(drop_library); |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; |
| |
| --- SQL operation complete. |
| >>set schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >>log LOG135; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| DB__ROOT |
| |
| --- 1 row(s) selected. |
| >> |
| >>drop library t135_l1; |
| |
| --- SQL operation complete. |
| >>drop schema t135sch_udrs; |
| |
| --- SQL operation complete. |
| >> |
| >>revoke component privilege "MANAGE_LIBRARY" on sql_operations from t135_role1; |
| |
| --- SQL operation complete. |
| >>revoke component privilege CREATE_ROUTINE on sql_operations from "PUBLIC"; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for t135_role1; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for "PUBLIC"; |
| |
| Privilege information on Component SQL_OPERATIONS for PUBLIC |
| ============================================================ |
| |
| CREATE_SCHEMA |
| SHOW |
| |
| --- SQL operation complete. |
| >> |
| >>revoke role t135_role1 from sql_user1; |
| |
| --- SQL operation complete. |
| >>drop role t135_role1; |
| |
| --- SQL operation complete. |
| >>get roles; |
| |
| Roles |
| ===== |
| |
| DB__HBASEROLE |
| DB__HIVEROLE |
| DB__LIBMGRROLE |
| DB__ROOTROLE |
| PUBLIC |
| |
| --- SQL operation complete. |
| >> |
| >>drop schema t135sch_user3 cascade; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST135(negative_tests); |
| >>-- ============================================================================ |
| >>set schema t135sch; |
| |
| --- SQL operation complete. |
| >> |
| >>log; |
