KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > h2 > test > db > TestRights


1 /*
2  * Copyright 2004-2006 H2 Group. Licensed under the H2 License, Version 1.0 (http://h2database.com/html/license.html).
3  * Initial Developer: H2 Group
4  */

5 package org.h2.test.db;
6
7 import java.sql.*;
8
9 import org.h2.test.TestBase;
10
11 public class TestRights extends TestBase {
12     
13     Statement stat;
14
15     public void test() throws Exception JavaDoc {
16         if(config.memory) {
17             return;
18         }
19         
20         deleteDb("rights");
21         Connection conn = getConnection("rights");
22         stat = conn.createStatement();
23         // default table type
24
testTableType(conn, "MEMORY");
25         testTableType(conn, "CACHED");
26
27         // rights on tables and views
28
executeSuccess("CREATE USER PASSREADER PASSWORD 'abc'");
29         executeSuccess("CREATE TABLE PASS(ID INT PRIMARY KEY, NAME VARCHAR, PASSWORD VARCHAR)");
30         executeSuccess("CREATE VIEW PASS_NAME AS SELECT ID, NAME FROM PASS");
31         executeSuccess("GRANT SELECT ON PASS_NAME TO PASSREADER");
32         conn.close();
33         
34         conn = getConnection("rights", "PASSREADER", "abc");
35         stat = conn.createStatement();
36         executeSuccess("SELECT * FROM PASS_NAME");
37         executeError("SELECT * FROM PASS");
38         conn.close();
39         
40         conn = getConnection("rights");
41         stat = conn.createStatement();
42         
43         executeSuccess("CREATE USER TEST PASSWORD 'abc'");
44         executeSuccess("ALTER USER TEST ADMIN TRUE");
45         executeSuccess("CREATE TABLE TEST(ID INT)");
46         executeSuccess("CREATE SCHEMA SCHEMA_A AUTHORIZATION SA");
47         executeSuccess("CREATE TABLE SCHEMA_A.TABLE_B(ID INT)");
48         executeSuccess("GRANT ALL ON SCHEMA_A.TABLE_B TO TEST");
49         executeSuccess("CREATE TABLE HIDDEN(ID INT)");
50         executeSuccess("CREATE TABLE PUBTABLE(ID INT)");
51         executeSuccess("CREATE TABLE ROLETABLE(ID INT)");
52         executeSuccess("CREATE ROLE TESTROLE");
53         executeSuccess("GRANT SELECT ON ROLETABLE TO TESTROLE");
54         executeSuccess("GRANT UPDATE ON ROLETABLE TO TESTROLE");
55         executeSuccess("REVOKE UPDATE ON ROLETABLE FROM TESTROLE");
56         executeError("REVOKE SELECT, SUB1 ON ROLETABLE FROM TESTROLE");
57         executeSuccess("GRANT TESTROLE TO TEST");
58         executeSuccess("GRANT SELECT ON PUBTABLE TO PUBLIC");
59         executeSuccess("GRANT SELECT ON TEST TO TEST");
60         executeSuccess("CREATE ROLE SUB1");
61         executeSuccess("CREATE ROLE SUB2");
62         executeSuccess("CREATE TABLE SUBTABLE(ID INT)");
63         executeSuccess("GRANT ALL ON SUBTABLE TO SUB2");
64         executeSuccess("REVOKE UPDATE, DELETE ON SUBTABLE FROM SUB2");
65         executeSuccess("GRANT SUB2 TO SUB1");
66         executeSuccess("GRANT SUB1 TO TEST");
67         
68         executeSuccess("ALTER USER TEST SET PASSWORD 'def'");
69         executeSuccess("CREATE USER TEST2 PASSWORD 'def' ADMIN");
70         executeSuccess("ALTER USER TEST ADMIN FALSE");
71         executeSuccess("SCRIPT TO '"+BASE_DIR+"/rights.sql' CIPHER XTEA PASSWORD 'test'");
72         conn.close();
73         
74         try {
75             conn = getConnection("rights", "Test", "abc");
76             error("unexpected success (mixed case user name)");
77         } catch(SQLException e) {
78             checkNotGeneralException(e);
79         }
80         try {
81             conn = getConnection("rights", "TEST", "abc");
82             error("unexpected success (wrong password)");
83         } catch(SQLException e) {
84             checkNotGeneralException(e);
85         }
86         try {
87             conn = getConnection("rights", "TEST", null);
88             error("unexpected success (wrong password)");
89         } catch(SQLException e) {
90             checkNotGeneralException(e);
91         }
92         conn = getConnection("rights", "TEST", "def");
93         stat = conn.createStatement();
94         
95         executeError("SET DEFAULT_TABLE_TYPE MEMORY");
96
97         executeSuccess("SELECT * FROM TEST");
98         executeSuccess("SELECT * FROM SYSTEM_RANGE(1,2)");
99         executeSuccess("SELECT * FROM SCHEMA_A.TABLE_B");
100         executeSuccess("SELECT * FROM PUBTABLE");
101         executeSuccess("SELECT * FROM ROLETABLE");
102         executeError("UPDATE ROLETABLE SET ID=0");
103         executeError("DELETE FROM ROLETABLE");
104         executeError("SELECT * FROM HIDDEN");
105         executeError("UPDATE TEST SET ID=0");
106         executeSuccess("SELECT * FROM SUBTABLE");
107         executeSuccess("INSERT INTO SUBTABLE VALUES(1)");
108         executeError("DELETE FROM SUBTABLE");
109         executeError("UPDATE FROM SUBTABLE");
110         
111         executeError("CREATE USER TEST3 PASSWORD 'def'");
112         executeError("ALTER USER TEST2 ADMIN FALSE");
113         executeError("ALTER USER TEST2 SET PASSWORD 'ghi'");
114         executeError("ALTER USER TEST2 RENAME TO TEST_X");
115         executeError("ALTER USER TEST RENAME TO TEST_X");
116         executeSuccess("ALTER USER TEST SET PASSWORD 'ghi'");
117         executeError("DROP USER TEST2");
118         
119         conn.close();
120         conn = getConnection("rights");
121         stat = conn.createStatement();
122         executeSuccess("DROP ROLE SUB1");
123         executeSuccess("DROP TABLE ROLETABLE");
124         executeSuccess("DROP USER TEST");
125         
126         conn.close();
127         conn = getConnection("rights");
128         stat = conn.createStatement();
129
130         executeSuccess("DROP TABLE IF EXISTS TEST");
131         executeSuccess("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
132         executeSuccess("CREATE USER GUEST PASSWORD 'abc'");
133         executeSuccess("GRANT SELECT ON TEST TO GUEST");
134         executeSuccess("ALTER USER GUEST RENAME TO GAST");
135         conn.close();
136         conn = getConnection("rights");
137         conn.close();
138     }
139     
140     private void testTableType(Connection conn, String JavaDoc type) throws Exception JavaDoc {
141         executeSuccess("SET DEFAULT_TABLE_TYPE " + type);
142         executeSuccess("CREATE TABLE TEST(ID INT)");
143         ResultSet rs = conn.createStatement().executeQuery("SELECT STORAGE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='TEST'");
144         rs.next();
145         check(rs.getString(1), type);
146         executeSuccess("DROP TABLE TEST");
147     }
148
149     public void executeError(String JavaDoc sql) throws Exception JavaDoc {
150         try {
151             stat.execute(sql);
152             error("unexpected success (not admin)");
153         } catch(SQLException e) {
154             checkNotGeneralException(e);
155         }
156     }
157
158     public void executeSuccess(String JavaDoc sql) throws Exception JavaDoc {
159         if(stat.execute(sql)) {
160             ResultSet rs = stat.getResultSet();
161             
162             // this will check if the resultset is updatable
163
rs.getConcurrency();
164             
165             ResultSetMetaData meta = rs.getMetaData();
166             int columnCount = meta.getColumnCount();
167             for(int i=0; i<columnCount; i++) {
168                 meta.getCatalogName(i+1);
169                 meta.getColumnClassName(i+1);
170                 meta.getColumnDisplaySize(i+1);
171                 meta.getColumnLabel(i+1);
172                 meta.getColumnName(i+1);
173                 meta.getColumnType(i+1);
174                 meta.getColumnTypeName(i+1);
175                 meta.getPrecision(i+1);
176                 meta.getScale(i+1);
177                 meta.getSchemaName(i+1);
178                 meta.getTableName(i+1);
179             }
180             while(rs.next()) {
181                 for(int i=0; i<columnCount; i++) {
182                     rs.getObject(i+1);
183                 }
184             }
185         }
186     }
187     
188 }
189
Popular Tags