Changeset: 1a20d058fda3 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=1a20d058fda3
Added Files:
sql/test/Views/Tests/Indirect.py
sql/test/Views/Tests/Indirect.stable.err
sql/test/Views/Tests/Indirect.stable.out
Modified Files:
sql/server/rel_select.c
sql/test/Views/Tests/All
Branch: indirect-privs
Log Message:
Fixed privileged access on views. If the user is allowed to select a subset of
columns from a view, no error should be thrown at reference time.
diffs (156 lines):
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -962,9 +962,9 @@ table_ref(sql_query *query, sql_rel *rel
}
if (!allowed)
rel = rel_reduce_on_column_privileges(sql, rel,
t);
- if (allowed && rel)
- return rel;
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
access denied for %s to table '%s.%s'", get_string_global_var(sql,
"current_user"), t->s->base.name, tname);
+ if (!rel)
+ return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: access denied for %s to view '%s.%s'", get_string_global_var(sql,
"current_user"), t->s->base.name, tname);
+ return rel;
}
if ((isMergeTable(t) || isReplicaTable(t)) &&
list_empty(t->members))
return sql_error(sql, 02, SQLSTATE(42000) "MERGE or
REPLICA TABLE should have at least one table associated");
@@ -972,7 +972,7 @@ table_ref(sql_query *query, sql_rel *rel
if (!allowed) {
res = rel_reduce_on_column_privileges(sql, res, t);
if (!res)
- return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: access denied for %s to table '%s.%s'", get_string_global_var(sql,
"current_user"), t->s->base.name, tname);
+ return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: access denied for %s to %s '%s.%s'", get_string_global_var(sql,
"current_user"), isView(t) ? "view" : "table", t->s->base.name, tname);
}
if (tableref->data.lval->h->next->data.sym &&
tableref->data.lval->h->next->data.sym->data.lval->h->next->data.lval) { /* AS
with column aliases */
res = rel_table_optname(sql, res,
tableref->data.lval->h->next->data.sym, refs);
diff --git a/sql/test/Views/Tests/All b/sql/test/Views/Tests/All
--- a/sql/test/Views/Tests/All
+++ b/sql/test/Views/Tests/All
@@ -1,1 +1,2 @@
Views
+Indirect
diff --git a/sql/test/Views/Tests/Indirect.py b/sql/test/Views/Tests/Indirect.py
new file mode 100644
--- /dev/null
+++ b/sql/test/Views/Tests/Indirect.py
@@ -0,0 +1,86 @@
+import os, sys, pymonetdb
+
+
+port = int(os.environ['MAPIPORT'])
+db = os.environ['TSTDB']
+
+conn1 =
pymonetdb.connect(port=port,database=db,autocommit=True,username='monetdb',password='monetdb')
+cur1 = conn1.cursor()
+cur1.execute("""
+start transaction;
+create schema foo;
+create user u1 with password '1' name 'u1' schema foo;
+create user u2 with password '2' name 'u2' schema foo;
+create table foo.tab1 (col1 int, col2 int);
+create table foo.tab2 (col1 int, col2 int);
+insert into foo.tab1 values (1, 1);
+insert into foo.tab2 values (2, 2);
+create view foo.v1(col1,col2) as (select col1, col2 from foo.tab1);
+create view foo.v2(col1,col2) as (select col1, col2 from foo.tab2);
+commit;
+""")
+cur1.close()
+conn1.close()
+
+conn1 =
pymonetdb.connect(port=port,database=db,autocommit=True,username='u1',password='1')
+cur1 = conn1.cursor()
+try:
+ cur1.execute('SELECT "col1" FROM "foo"."v1";') # error, not allowed
+ sys.stderr.write("Exception expected")
+except pymonetdb.DatabaseError as e:
+ if "SELECT: access denied for u1 to view 'foo.v1'" not in str(e):
+ sys.stderr.write('Wrong error %s, expected SELECT: access denied for
u1 to view \'foo.v1\'' % (str(e)))
+try:
+ cur1.execute('SELECT "col2" FROM "foo"."v1";') # error, not allowed
+ sys.stderr.write("Exception expected")
+except pymonetdb.DatabaseError as e:
+ if "SELECT: access denied for u1 to view 'foo.v1'" not in str(e):
+ sys.stderr.write('Wrong error %s, expected SELECT: access denied for
u1 to view \'foo.v1\'' % (str(e)))
+try:
+ cur1.execute('SELECT "col1" FROM "foo"."tab1";') # error, not allowed
+ sys.stderr.write("Exception expected")
+except pymonetdb.DatabaseError as e:
+ if "SELECT: access denied for u1 to table 'foo.tab1'" not in str(e):
+ sys.stderr.write('Wrong error %s, expected SELECT: access denied for
u1 to table \'foo.tab1\'' % (str(e)))
+cur1.close()
+conn1.close()
+
+conn1 =
pymonetdb.connect(port=port,database=db,autocommit=True,username='monetdb',password='monetdb')
+cur1 = conn1.cursor()
+cur1.execute("""
+grant select ("col1") ON "foo"."v1" TO u1;
+""")
+cur1.close()
+conn1.close()
+
+conn1 =
pymonetdb.connect(port=port,database=db,autocommit=True,username='u1',password='1')
+cur1 = conn1.cursor()
+cur1.execute('SELECT "col1" FROM "foo"."v1";')
+if cur1.fetchall() != [(1, )]:
+ sys.stderr.write("[(1, )] expected")
+try:
+ cur1.execute('SELECT "col2" FROM "foo"."v1";') # error, not allowed
+ sys.stderr.write("Exception expected")
+except pymonetdb.DatabaseError as e:
+ if "SELECT: identifier 'col2' unknown" not in str(e):
+ sys.stderr.write('Wrong error %s, expected "SELECT: identifier
\'col2\' unknown' % (str(e)))
+try:
+ cur1.execute('SELECT "col1" FROM "foo"."tab1";') # error, not allowed
+ sys.stderr.write("Exception expected")
+except pymonetdb.DatabaseError as e:
+ if "SELECT: access denied for u1 to table 'foo.tab1'" not in str(e):
+ sys.stderr.write('Wrong error %s, expected SELECT: access denied for
u1 to table \'foo.tab1\'' % (str(e)))
+cur1.close()
+conn1.close()
+
+conn1 =
pymonetdb.connect(port=port,database=db,autocommit=True,username='monetdb',password='monetdb')
+cur1 = conn1.cursor()
+cur1.execute("""
+start transaction;
+drop user u1;
+drop user u2;
+drop schema foo cascade;
+commit;
+""")
+cur1.close()
+conn1.close()
diff --git a/sql/test/Views/Tests/Indirect.stable.err
b/sql/test/Views/Tests/Indirect.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/Views/Tests/Indirect.stable.err
@@ -0,0 +1,12 @@
+stderr of test 'Indirect` in directory 'sql/test/Views` itself:
+
+
+# 17:45:28 >
+# 17:45:28 > "/usr/bin/python3.9" "Indirect.py" "Indirect"
+# 17:45:28 >
+
+
+# 17:45:28 >
+# 17:45:28 > "Done."
+# 17:45:28 >
+
diff --git a/sql/test/Views/Tests/Indirect.stable.out
b/sql/test/Views/Tests/Indirect.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/Views/Tests/Indirect.stable.out
@@ -0,0 +1,12 @@
+stdout of test 'Indirect` in directory 'sql/test/Views` itself:
+
+
+# 17:45:28 >
+# 17:45:28 > "/usr/bin/python3.9" "Indirect.py" "Indirect"
+# 17:45:28 >
+
+
+# 17:45:28 >
+# 17:45:28 > "Done."
+# 17:45:28 >
+
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
