|
1
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  {
16 if(config.memory) {
17 return;
18 }
19
20 deleteDb("rights");
21 Connection conn = getConnection("rights");
22 stat = conn.createStatement();
23 testTableType(conn, "MEMORY");
25 testTableType(conn, "CACHED");
26
27 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 type) throws Exception {
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 sql) throws Exception {
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 sql) throws Exception {
159 if(stat.execute(sql)) {
160 ResultSet rs = stat.getResultSet();
161
162 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 |
Java API By Example, From Geeks To Geeks.