Changeset: 79294808aa31 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/79294808aa31
Added Files:
sql/test/mapi/Tests/clientinfo-nonadmin.SQL.py
Modified Files:
monetdb5/modules/mal/clients.c
sql/backends/monet5/sql_upgrades.c
sql/scripts/22_clients.sql
sql/test/mapi/Tests/All
Branch: Aug2024
Log Message:
Allow every user to see their own rows in sys.sessions
This fixes #7549
diffs (246 lines):
diff --git a/monetdb5/modules/mal/clients.c b/monetdb5/modules/mal/clients.c
--- a/monetdb5/modules/mal/clients.c
+++ b/monetdb5/modules/mal/clients.c
@@ -797,6 +797,7 @@ CLTsessions(Client cntxt, MalBlkPtr mb,
bat *clientpidId = getArgReference_bat(stk, pci, 14);
bat *remarkId = getArgReference_bat(stk, pci, 15);
Client c;
+ bool admin;
timestamp ts;
lng pid;
const char *s;
@@ -848,73 +849,80 @@ CLTsessions(Client cntxt, MalBlkPtr mb,
throw(SQL, "sql.sessions", SQLSTATE(HY013) MAL_MALLOC_FAIL);
}
+ admin = strcmp(cntxt->username, "monetdb") == 0;
+
MT_lock_set(&mal_contextLock);
-
for (c = mal_clients; c < mal_clients + MAL_MAXCLIENTS; c++) {
- if (c->mode == RUNCLIENT) {
- const char *username = c->username;
- if (!username)
- username = str_nil;
- if (BUNappend(user, username, false) != GDK_SUCCEED)
+ if (c->mode != RUNCLIENT)
+ continue;
+
+ bool allowed_to_see = admin || c == cntxt ||
strcmp(c->username, cntxt->username) == 0;
+ if (!allowed_to_see)
+ continue;
+
+ const char *username = c->username;
+ if (!username)
+ username = str_nil;
+ if (BUNappend(user, username, false) != GDK_SUCCEED)
+ goto bailout;
+ ts = timestamp_fromtime(c->login);
+ if (is_timestamp_nil(ts)) {
+ msg = createException(SQL, "sql.sessions",
+
SQLSTATE(22003)
+ "Failed
to convert user logged time");
+ goto bailout;
+ }
+ if (BUNappend(id, &c->idx, false) != GDK_SUCCEED)
goto bailout;
- ts = timestamp_fromtime(c->login);
+ if (BUNappend(login, &ts, false) != GDK_SUCCEED)
+ goto bailout;
+ timeout = (int) (c->logical_sessiontimeout);
+ if (BUNappend(sessiontimeout, &timeout, false) != GDK_SUCCEED)
+ goto bailout;
+ timeout = (int) (c->querytimeout / 1000000);
+ if (BUNappend(querytimeout, &timeout, false) != GDK_SUCCEED)
+ goto bailout;
+ if (c->idle) {
+ ts = timestamp_fromtime(c->idle);
if (is_timestamp_nil(ts)) {
msg = createException(SQL, "sql.sessions",
-
SQLSTATE(22003)
-
"Failed to convert user logged time");
+
SQLSTATE(22003)
+
"Failed to convert user logged time");
goto bailout;
}
- if (BUNappend(id, &c->idx, false) != GDK_SUCCEED)
- goto bailout;
- if (BUNappend(login, &ts, false) != GDK_SUCCEED)
- goto bailout;
- timeout = (int) (c->logical_sessiontimeout);
- if (BUNappend(sessiontimeout, &timeout, false) !=
GDK_SUCCEED)
- goto bailout;
- timeout = (int) (c->querytimeout / 1000000);
- if (BUNappend(querytimeout, &timeout, false) !=
GDK_SUCCEED)
- goto bailout;
- if (c->idle) {
- ts = timestamp_fromtime(c->idle);
- if (is_timestamp_nil(ts)) {
- msg = createException(SQL,
"sql.sessions",
-
SQLSTATE(22003)
-
"Failed to convert user logged time");
- goto bailout;
- }
- } else
- ts = timestamp_nil;
- if (BUNappend(idle, &ts, false) != GDK_SUCCEED)
+ } else
+ ts = timestamp_nil;
+ if (BUNappend(idle, &ts, false) != GDK_SUCCEED)
+ goto bailout;
+ if (BUNappend(opt, &c->optimizer, false) != GDK_SUCCEED)
goto bailout;
- if (BUNappend(opt, &c->optimizer, false) != GDK_SUCCEED)
- goto bailout;
- if (BUNappend(wlimit, &c->workerlimit, false) !=
GDK_SUCCEED)
- goto bailout;
- if (BUNappend(mlimit, &c->memorylimit, false) !=
GDK_SUCCEED)
- goto bailout;
- if (BUNappend(language, getScenarioLanguage(c), false)
!= GDK_SUCCEED)
- goto bailout;
- s = c->peer ? c->peer : str_nil;
- if (BUNappend(peer, s, false) != GDK_SUCCEED)
- goto bailout;
- s = c->client_hostname ? c->client_hostname : str_nil;
- if (BUNappend(hostname, s, false) != GDK_SUCCEED)
- goto bailout;
- s = c->client_application ? c->client_application :
str_nil;
- if (BUNappend(application, s, false) != GDK_SUCCEED)
- goto bailout;
- s = c->client_library ? c->client_library : str_nil;
- if (BUNappend(client, s, false) != GDK_SUCCEED)
- goto bailout;
- pid = c->client_pid;
- if (BUNappend(clientpid, pid ? &pid : &lng_nil, false)
!= GDK_SUCCEED)
- goto bailout;
- s = c->client_remark ? c->client_remark : str_nil;
- if (BUNappend(remark, s, false) != GDK_SUCCEED)
- goto bailout;
- }
+ if (BUNappend(wlimit, &c->workerlimit, false) != GDK_SUCCEED)
+ goto bailout;
+ if (BUNappend(mlimit, &c->memorylimit, false) != GDK_SUCCEED)
+ goto bailout;
+ if (BUNappend(language, getScenarioLanguage(c), false) !=
GDK_SUCCEED)
+ goto bailout;
+ s = c->peer ? c->peer : str_nil;
+ if (BUNappend(peer, s, false) != GDK_SUCCEED)
+ goto bailout;
+ s = c->client_hostname ? c->client_hostname : str_nil;
+ if (BUNappend(hostname, s, false) != GDK_SUCCEED)
+ goto bailout;
+ s = c->client_application ? c->client_application : str_nil;
+ if (BUNappend(application, s, false) != GDK_SUCCEED)
+ goto bailout;
+ s = c->client_library ? c->client_library : str_nil;
+ if (BUNappend(client, s, false) != GDK_SUCCEED)
+ goto bailout;
+ pid = c->client_pid;
+ if (BUNappend(clientpid, pid ? &pid : &lng_nil, false) !=
GDK_SUCCEED)
+ goto bailout;
+ s = c->client_remark ? c->client_remark : str_nil;
+ if (BUNappend(remark, s, false) != GDK_SUCCEED)
+ goto bailout;
}
MT_lock_unset(&mal_contextLock);
+
*idId = id->batCacheid;
BBPkeepref(id);
*userId = user->batCacheid;
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -7098,6 +7098,7 @@ sql_update_aug2024(Client c, mvc *sql, s
" )\n"
" external name sql.sessions;\n"
"create view sys.sessions as
select * from sys.sessions();\n"
+ "grant select on sys.sessions
to public;\n"
"create procedure
sys.setclientinfo(property string, value string)\n"
" external name
clients.setinfo;\n"
"grant execute on procedure
sys.setclientinfo(string, string) to public;\n"
diff --git a/sql/scripts/22_clients.sql b/sql/scripts/22_clients.sql
--- a/sql/scripts/22_clients.sql
+++ b/sql/scripts/22_clients.sql
@@ -39,7 +39,7 @@ returns table(
)
external name sql.sessions;
create view sys.sessions as select * from sys.sessions();
--- we won't grant sys.sessions to the public
+grant select on sys.sessions to public;
create procedure sys.setclientinfo(property string, value string)
external name clients.setinfo;
diff --git a/sql/test/mapi/Tests/All b/sql/test/mapi/Tests/All
--- a/sql/test/mapi/Tests/All
+++ b/sql/test/mapi/Tests/All
@@ -10,3 +10,4 @@ HAVE_HGE?python3_int128
HAVE_HGE?sql_dec38
HAVE_HGE?python3_dec38
clientinfo-mclient
+clientinfo-nonadmin
diff --git a/sql/test/mapi/Tests/clientinfo-nonadmin.SQL.py
b/sql/test/mapi/Tests/clientinfo-nonadmin.SQL.py
new file mode 100644
--- /dev/null
+++ b/sql/test/mapi/Tests/clientinfo-nonadmin.SQL.py
@@ -0,0 +1,61 @@
+
+import os
+import sys
+from typing import List, Tuple
+import pymonetdb
+
+
+def connect(remark: str, **args) -> pymonetdb.Connection:
+ dbname = os.environ['TSTDB']
+ mapiport = os.environ['MAPIPORT']
+ conn = pymonetdb.connect(dbname, port=mapiport, autocommit=True, **args)
+ with conn.cursor() as c:
+ c.execute("CALL sys.setclientinfo('ClientRemark', %s)", [remark])
+ return conn
+
+def get_remarks(conn: pymonetdb.Connection) -> List[Tuple[int,str]]:
+ with conn.cursor() as c:
+ c.execute("SELECT sessionid, remark FROM sys.sessions ORDER BY
sessionid")
+ return c.fetchall()
+
+def assert_equal(left, right):
+ if left != right:
+ print(f'LEFT: {left!r}\nRIGHT: {right!r}\n', file=sys.stderr)
+ assert left == right
+
+
+#######################################################################
+# Connect as admin
+
+conn0 = connect('admin 0')
+assert_equal(get_remarks(conn0), [(0, 'admin 0')])
+
+
+#######################################################################
+# Create a user
+
+c0 = conn0.cursor()
+# try:
+# c0.execute('DROP USER nonadmin') # convenientduring interactive
testing
+# except pymonetdb.Error:
+# pass
+c0.execute("CREATE USER nonadmin WITH PASSWORD 'na' NAME 'Not Admin' SCHEMA
sys")
+
+
+#######################################################################
+# Connect as that user, twice
+
+conn1 = connect('user 1', user='nonadmin', password='na')
+conn2 = connect('user 2', user='nonadmin', password='na')
+
+
+#######################################################################
+# Check who can see what
+
+# admin can see both
+assert_equal(get_remarks(conn0), [(0, 'admin 0'), (1, 'user 1'), (2, 'user
2')])
+
+# users can only see themselves
+assert_equal(get_remarks(conn1), [(1, 'user 1'), (2, 'user 2')])
+assert_equal(get_remarks(conn2), [(1, 'user 1'), (2, 'user 2')])
+
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
