001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.db;
007:
008: import java.sql.Connection;
009: import java.sql.ResultSet;
010: import java.sql.ResultSetMetaData;
011: import java.sql.SQLException;
012: import java.sql.Statement;
013:
014: import org.h2.test.TestBase;
015:
016: /**
017: * Access rights tests.
018: */
019: public class TestRights extends TestBase {
020:
021: private Statement stat;
022:
023: public void test() throws Exception {
024: testDropTempTables();
025: // testLowerCaseUser();
026: testSchemaRenameUser();
027: testAccessRights();
028: }
029:
030: // public void testLowerCaseUser() throws Exception {
031: // Documentation: For compatibility,
032: // only unquoted or uppercase user names are allowed.
033: // deleteDb("rights");
034: // Connection conn = getConnection("rights");
035: // stat = conn.createStatement();
036: // stat.execute("CREATE USER \"TEST1\" PASSWORD 'abc'");
037: // stat.execute("CREATE USER \"Test2\" PASSWORD 'abc'");
038: // conn.close();
039: // conn = getConnection("rights", "TEST1", "abc");
040: // conn.close();
041: // conn = getConnection("rights", "Test2", "abc");
042: // conn.close();
043: // }
044:
045: private void testDropTempTables() throws Exception {
046: deleteDb("rights");
047: Connection conn = getConnection("rights");
048: stat = conn.createStatement();
049: stat
050: .execute("CREATE USER IF NOT EXISTS READER PASSWORD 'READER'");
051: stat.execute("CREATE TABLE TEST(ID INT)");
052: Connection conn2 = getConnection("rights", "READER", "READER");
053: Statement stat2 = conn2.createStatement();
054: try {
055: stat2.execute("SELECT * FROM TEST");
056: error();
057: } catch (SQLException e) {
058: checkNotGeneralException(e);
059: }
060: stat2
061: .execute("CREATE LOCAL TEMPORARY TABLE IF NOT EXISTS MY_TEST(ID INT)");
062: stat2.execute("INSERT INTO MY_TEST VALUES(1)");
063: stat2.execute("SELECT * FROM MY_TEST");
064: stat2.execute("DROP TABLE MY_TEST");
065: conn2.close();
066: conn.close();
067: }
068:
069: public void testSchemaRenameUser() throws Exception {
070: if (config.memory) {
071: return;
072: }
073: deleteDb("rights");
074: Connection conn = getConnection("rights");
075: stat = conn.createStatement();
076: stat.execute("create user test password '' admin");
077: stat.execute("create schema b authorization test");
078: stat.execute("create table b.test(id int)");
079: stat.execute("alter user test rename to test1");
080: conn.close();
081: conn = getConnection("rights");
082: stat = conn.createStatement();
083: stat.execute("select * from b.test");
084: try {
085: stat.execute("alter user test1 admin false");
086: error();
087: } catch (SQLException e) {
088: checkNotGeneralException(e);
089: }
090: try {
091: stat.execute("drop user test1");
092: error();
093: } catch (SQLException e) {
094: checkNotGeneralException(e);
095: }
096: stat.execute("drop schema b");
097: stat.execute("alter user test1 admin false");
098: stat.execute("drop user test1");
099: conn.close();
100: }
101:
102: public void testAccessRights() throws Exception {
103: if (config.memory) {
104: return;
105: }
106:
107: deleteDb("rights");
108: Connection conn = getConnection("rights");
109: stat = conn.createStatement();
110: // default table type
111: testTableType(conn, "MEMORY");
112: testTableType(conn, "CACHED");
113:
114: // rights on tables and views
115: executeSuccess("CREATE USER PASS_READER PASSWORD 'abc'");
116: executeSuccess("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
117: executeSuccess("CREATE TABLE PASS(ID INT PRIMARY KEY, NAME VARCHAR, PASSWORD VARCHAR)");
118: executeSuccess("CREATE VIEW PASS_NAME AS SELECT ID, NAME FROM PASS");
119: executeSuccess("GRANT SELECT ON PASS_NAME TO PASS_READER");
120: executeSuccess("GRANT SELECT, INSERT, UPDATE ON TEST TO PASS_READER");
121: conn.close();
122:
123: conn = getConnection("rights", "PASS_READER", "abc");
124: stat = conn.createStatement();
125: executeSuccess("SELECT * FROM PASS_NAME");
126: executeSuccess("SELECT * FROM (SELECT * FROM PASS_NAME)");
127: executeSuccess("SELECT (SELECT NAME FROM PASS_NAME) P FROM PASS_NAME");
128: executeError("SELECT (SELECT PASSWORD FROM PASS) P FROM PASS_NAME");
129: executeError("SELECT * FROM PASS");
130: executeError("INSERT INTO TEST SELECT 1, PASSWORD FROM PASS");
131: executeError("INSERT INTO TEST VALUES(SELECT PASSWORD FROM PASS)");
132: executeError("UPDATE TEST SET NAME=(SELECT PASSWORD FROM PASS)");
133: executeError("DELETE FROM TEST WHERE NAME=(SELECT PASSWORD FROM PASS)");
134: executeError("SELECT * FROM (SELECT * FROM PASS)");
135: executeError("CREATE VIEW X AS SELECT * FROM PASS_READER");
136: conn.close();
137:
138: conn = getConnection("rights");
139: stat = conn.createStatement();
140:
141: executeSuccess("DROP TABLE TEST");
142: executeSuccess("CREATE USER TEST PASSWORD 'abc'");
143: executeSuccess("ALTER USER TEST ADMIN TRUE");
144: executeSuccess("CREATE TABLE TEST(ID INT)");
145: executeSuccess("CREATE SCHEMA SCHEMA_A AUTHORIZATION SA");
146: executeSuccess("CREATE TABLE SCHEMA_A.TABLE_B(ID INT)");
147: executeSuccess("GRANT ALL ON SCHEMA_A.TABLE_B TO TEST");
148: executeSuccess("CREATE TABLE HIDDEN(ID INT)");
149: executeSuccess("CREATE TABLE PUB_TABLE(ID INT)");
150: executeSuccess("CREATE TABLE ROLE_TABLE(ID INT)");
151: executeSuccess("CREATE ROLE TEST_ROLE");
152: executeSuccess("GRANT SELECT ON ROLE_TABLE TO TEST_ROLE");
153: executeSuccess("GRANT UPDATE ON ROLE_TABLE TO TEST_ROLE");
154: executeSuccess("REVOKE UPDATE ON ROLE_TABLE FROM TEST_ROLE");
155: executeError("REVOKE SELECT, SUB1 ON ROLE_TABLE FROM TEST_ROLE");
156: executeSuccess("GRANT TEST_ROLE TO TEST");
157: executeSuccess("GRANT SELECT ON PUB_TABLE TO PUBLIC");
158: executeSuccess("GRANT SELECT ON TEST TO TEST");
159: executeSuccess("CREATE ROLE SUB1");
160: executeSuccess("CREATE ROLE SUB2");
161: executeSuccess("CREATE TABLE SUB_TABLE(ID INT)");
162: executeSuccess("GRANT ALL ON SUB_TABLE TO SUB2");
163: executeSuccess("REVOKE UPDATE, DELETE ON SUB_TABLE FROM SUB2");
164: executeSuccess("GRANT SUB2 TO SUB1");
165: executeSuccess("GRANT SUB1 TO TEST");
166:
167: executeSuccess("ALTER USER TEST SET PASSWORD 'def'");
168: executeSuccess("CREATE USER TEST2 PASSWORD 'def' ADMIN");
169: executeSuccess("ALTER USER TEST ADMIN FALSE");
170: executeSuccess("SCRIPT TO '" + baseDir
171: + "/rights.sql' CIPHER XTEA PASSWORD 'test'");
172: conn.close();
173:
174: try {
175: conn = getConnection("rights", "Test", "abc");
176: error("mixed case user name");
177: } catch (SQLException e) {
178: checkNotGeneralException(e);
179: }
180: try {
181: conn = getConnection("rights", "TEST", "abc");
182: error("wrong password");
183: } catch (SQLException e) {
184: checkNotGeneralException(e);
185: }
186: try {
187: conn = getConnection("rights", "TEST", null);
188: error("wrong password");
189: } catch (SQLException e) {
190: checkNotGeneralException(e);
191: }
192: conn = getConnection("rights", "TEST", "def");
193: stat = conn.createStatement();
194:
195: executeError("SET DEFAULT_TABLE_TYPE MEMORY");
196:
197: executeSuccess("SELECT * FROM TEST");
198: executeSuccess("SELECT * FROM SYSTEM_RANGE(1,2)");
199: executeSuccess("SELECT * FROM SCHEMA_A.TABLE_B");
200: executeSuccess("SELECT * FROM PUB_TABLE");
201: executeSuccess("SELECT * FROM ROLE_TABLE");
202: executeError("UPDATE ROLE_TABLE SET ID=0");
203: executeError("DELETE FROM ROLE_TABLE");
204: executeError("SELECT * FROM HIDDEN");
205: executeError("UPDATE TEST SET ID=0");
206: executeError("CALL SELECT MIN(PASSWORD) FROM PASS");
207: executeSuccess("SELECT * FROM SUB_TABLE");
208: executeSuccess("INSERT INTO SUB_TABLE VALUES(1)");
209: executeError("DELETE FROM SUB_TABLE");
210: executeError("UPDATE FROM SUB_TABLE");
211:
212: executeError("CREATE USER TEST3 PASSWORD 'def'");
213: executeError("ALTER USER TEST2 ADMIN FALSE");
214: executeError("ALTER USER TEST2 SET PASSWORD 'ghi'");
215: executeError("ALTER USER TEST2 RENAME TO TEST_X");
216: executeError("ALTER USER TEST RENAME TO TEST_X");
217: executeSuccess("ALTER USER TEST SET PASSWORD 'ghi'");
218: executeError("DROP USER TEST2");
219:
220: conn.close();
221: conn = getConnection("rights");
222: stat = conn.createStatement();
223: executeSuccess("DROP ROLE SUB1");
224: executeSuccess("DROP TABLE ROLE_TABLE");
225: executeSuccess("DROP USER TEST");
226:
227: conn.close();
228: conn = getConnection("rights");
229: stat = conn.createStatement();
230:
231: executeSuccess("DROP TABLE IF EXISTS TEST");
232: executeSuccess("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
233: executeSuccess("CREATE USER GUEST PASSWORD 'abc'");
234: executeSuccess("GRANT SELECT ON TEST TO GUEST");
235: executeSuccess("ALTER USER GUEST RENAME TO GAST");
236: conn.close();
237: conn = getConnection("rights");
238: conn.close();
239: }
240:
241: private void testTableType(Connection conn, String type)
242: throws Exception {
243: executeSuccess("SET DEFAULT_TABLE_TYPE " + type);
244: executeSuccess("CREATE TABLE TEST(ID INT)");
245: ResultSet rs = conn
246: .createStatement()
247: .executeQuery(
248: "SELECT STORAGE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='TEST'");
249: rs.next();
250: check(rs.getString(1), type);
251: executeSuccess("DROP TABLE TEST");
252: }
253:
254: public void executeError(String sql) throws Exception {
255: try {
256: stat.execute(sql);
257: error("not admin");
258: } catch (SQLException e) {
259: checkNotGeneralException(e);
260: }
261: }
262:
263: public void executeSuccess(String sql) throws Exception {
264: if (stat.execute(sql)) {
265: ResultSet rs = stat.getResultSet();
266:
267: // this will check if the result set is updatable
268: rs.getConcurrency();
269:
270: ResultSetMetaData meta = rs.getMetaData();
271: int columnCount = meta.getColumnCount();
272: for (int i = 0; i < columnCount; i++) {
273: meta.getCatalogName(i + 1);
274: meta.getColumnClassName(i + 1);
275: meta.getColumnDisplaySize(i + 1);
276: meta.getColumnLabel(i + 1);
277: meta.getColumnName(i + 1);
278: meta.getColumnType(i + 1);
279: meta.getColumnTypeName(i + 1);
280: meta.getPrecision(i + 1);
281: meta.getScale(i + 1);
282: meta.getSchemaName(i + 1);
283: meta.getTableName(i + 1);
284: }
285: while (rs.next()) {
286: for (int i = 0; i < columnCount; i++) {
287: rs.getObject(i + 1);
288: }
289: }
290: }
291: }
292:
293: }
|