Changeset: 9366974b94c0 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9366974b94c0 Branch: nospare Log Message:
merged diffs (truncated from 301 to 300 lines): diff --git a/documentation/source/developers_handbook.rst b/documentation/source/developers_handbook.rst --- a/documentation/source/developers_handbook.rst +++ b/documentation/source/developers_handbook.rst @@ -70,7 +70,7 @@ directory where they reside:: Adding sqllogic test ------------------ +-------------------- See `<https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki/>`_ for detail information on how to structure sqllogic test if you desire to make one by hand. We have extended the diff --git a/documentation/source/windowsbuild.rst b/documentation/source/windowsbuild.rst deleted file mode 100644 --- a/documentation/source/windowsbuild.rst +++ /dev/null @@ -1,121 +0,0 @@ -.. This Source Code Form is subject to the terms of the Mozilla Public -.. License, v. 2.0. If a copy of the MPL was not distributed with this -.. file, You can obtain one at http://mozilla.org/MPL/2.0/. -.. -.. Copyright 1997 - July 2008 CWI, August 2008 - 2020 MonetDB B.V. - -.. This document is written in reStructuredText (see - http://docutils.sourceforge.net/ for more information). - Use ``rst2html.py`` to convert this file to HTML. - -Building MonetDB On Windows -+++++++++++++++++++++++++++ - -In this document we describe how to build the MonetDB suite of -programs on Windows using the sources from `our source repository`__. -This document is mainly targeted at building on Windows on a 32-bit -architecture, but there are notes throughout about building on Windows -on a 64-bit architecture which is indicated with Windows64. We have -successfully built on Windows XP, Windows Server, and Windows 7. - -.. _MonetDB: https://dev.monetdb.org/hg/MonetDB/ - -__ MonetDB_ - -Introduction -============ - -The MonetDB suite of programs consists of a number of components which -we will describe briefly here. The section names are the names of the -top-level folders in the Mercurial clone. - -Note that in branches up to and including Oct2010 the build process -was different. This document describes the build process for the -branch this document is part of. Use the command ``hg branch`` to -find out the name of the branch. - -buildtools ----------- - -The buildtools component contains tools that are used to build the -other components. This component is required, but not all parts of -this component are required for all configurations. - -common ------- - -Also known as the MonetDB Common component contains some generally -useful libraries. This component is required. - -gdk ---- - -Also known as the Goblin Database Kernel contains the database kernel, -i.e. the heart of MonetDB. This component is required. - -clients -------- - -Also known as the MonetDB Client component contains a library which -forms the basis for communicating with the MonetDB server components, -and some interface programs that use this library to communicate with -the server. This component is required. - -monetdb5 --------- - -The MonetDB5 Server component is the database server. It uses MAL -(the MonetDB Algebra Language) as programming interface. This -component is required. - -sql ---- - -Also known as MonetDB SQL, this component provides an SQL frontend to -MonetDB5. This component is required if you need SQL support. - -tools ------ - -The tools component contains two parts. The mserver part is the -actual database server binary and is required. The merovingian part -is not used on Windows. - -geom ----- - -The geom component provides a module for the MonetDB SQL frontend. -This component is optional. - -testing -------- - -The testing component contains some files and programs we use for -testing the MonetDB suite. This component is optional. - -Prerequisites -============= - -In order to compile the MonetDB suite of programs, several other -programs and libraries need to be installed. Some further programs -and libraries can be optionally installed to enable optional features. -The required programs and libraries are listed in this section, the -following section lists the optional programs and libraries. - -Chocolatey ----------- - -Although Chocolatey_ is not a prerequisite per se, it makes -installing and maintaining some of the other prerequisites a lot -easier. Therefore we recommend installing chocolatey. Instructions -are on their website__. - -We have installed the following programs using Chocolatey_:: - - choco install ActivePerl ant cmake ruby - choco install python3 python3-x86_32 - -.. _Chocolatey: https://chocolatey.org/ - -__ Chocolatey_ - diff --git a/sql/test/Users/Tests/All b/sql/test/Users/Tests/All --- a/sql/test/Users/Tests/All +++ b/sql/test/Users/Tests/All @@ -12,6 +12,7 @@ schemaRights table_privs table view_privs +view_privs_chain unknown_user userCallFunction withGrantOption diff --git a/sql/test/Users/Tests/view_privs_chain.SQL.py b/sql/test/Users/Tests/view_privs_chain.SQL.py new file mode 100644 --- /dev/null +++ b/sql/test/Users/Tests/view_privs_chain.SQL.py @@ -0,0 +1,147 @@ +### +# Check indirect VIEW privilege: +# check that GRANT SELECT on <view> works correctly in assigning various +# chains of views and tables to different users +### +from MonetDBtesting.sqltest import SQLTestCase +from decimal import Decimal + +with SQLTestCase() as mdb: + mdb.connect(username="monetdb", password="monetdb") + + # create two users in my_schema + mdb.execute("CREATE ROLE my_role;").assertSucceeded() + mdb.execute("CREATE SCHEMA my_schema AUTHORIZATION my_role;").assertSucceeded() + mdb.execute("CREATE USER usr1 WITH PASSWORD 'p1' NAME 'usr1' SCHEMA my_schema;").assertSucceeded() + mdb.execute("CREATE USER usr2 WITH PASSWORD 'p2' NAME 'usr2' SCHEMA my_schema;").assertSucceeded() + mdb.execute("GRANT my_role to usr1;").assertSucceeded() + mdb.execute("GRANT my_role to usr2;").assertSucceeded() + + # create tables and views in another schema + mdb.execute("CREATE SCHEMA your_schema;").assertSucceeded() + mdb.execute("SET SCHEMA your_schema;").assertSucceeded() + mdb.execute("CREATE TABLE person (name VARCHAR(10), birthday DATE, ssn CHAR(9));").assertSucceeded() + mdb.execute("INSERT INTO person VALUES ('alice', '1980-01-01', 'AAAAAAAAA'), ('bob', '1970-01-01', '000000000');").assertRowCount(2) + mdb.execute("CREATE TABLE employee (name VARCHAR(10), salary DECIMAL(10,2));").assertSucceeded() + mdb.execute("INSERT INTO employee VALUES ('alice', 888.42), ('bob', 444.42);").assertRowCount(2) + + # v1 = join(table, table) + mdb.execute(""" + CREATE VIEW v1 AS SELECT + p.name, + EXTRACT(YEAR FROM birthday) as yr, + '********'||substring(ssn,9,9) as ssn, + ifthenelse((salary > 500), 'high', 'low') as salary + FROM person p, employee e + WHERE p.name = e.name; + """).assertSucceeded() + mdb.execute("SELECT * FROM v1;").assertSucceeded()\ + .assertDataResultMatch([('alice', 1980, '********A', 'high'), ('bob', 1970, '********0', 'low')]) + + # v2 = join(table, view) + mdb.execute(""" + CREATE VIEW v2 AS SELECT v1.name, v1.ssn, e.salary + FROM v1, employee e + WHERE v1.name = e.name; + """).assertSucceeded() + mdb.execute("SELECT * FROM v2;").assertSucceeded()\ + .assertDataResultMatch([('alice', '********A', Decimal('888.42')), ('bob', '********0', Decimal('444.42'))]) + + # v3 = join(view, view) + mdb.execute(""" + CREATE VIEW v3 AS SELECT v1.name, v1.yr, v2.salary + FROM v1, v2 + WHERE v1.name = v2.name; + """).assertSucceeded() + mdb.execute("SELECT * FROM v3;").assertSucceeded()\ + .assertDataResultMatch([('alice', 1980, Decimal('888.42')), ('bob', 1970, Decimal('444.42'))]) + + # v4 = project(view) + mdb.execute(""" + CREATE VIEW v4 AS SELECT yr, + ifthenelse((salary > 500), 'high', 'low') as salary + FROM v3 + """).assertSucceeded() + mdb.execute("SELECT * FROM v4;").assertSucceeded()\ + .assertDataResultMatch([(1980, 'high'), (1970, 'low')]) + mdb.execute("SET SCHEMA sys;").assertSucceeded() + + # usr1 has no access to the tables and gets access to views top-down + with SQLTestCase() as tc: + tc.connect(username="usr1", password="p1") + + mdb.execute("GRANT SELECT on your_schema.v4 to usr1;").assertSucceeded() + tc.execute("SELECT * FROM your_schema.v4;").assertSucceeded()\ + .assertDataResultMatch([(1980, 'high'), (1970, 'low')]) + tc.execute("SELECT * FROM your_schema.v3;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr1 to view 'your_schema.v3'") + tc.execute("SELECT * FROM your_schema.v2;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr1 to view 'your_schema.v2'") + tc.execute("SELECT * FROM your_schema.v1;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr1 to view 'your_schema.v1'") + + mdb.execute("GRANT SELECT on your_schema.v3 to usr1;").assertSucceeded() + tc.execute("SELECT * FROM your_schema.v3;").assertSucceeded()\ + .assertDataResultMatch([('alice', 1980, Decimal('888.42')), ('bob', 1970, Decimal('444.42'))]) + tc.execute("SELECT * FROM your_schema.v2;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr1 to view 'your_schema.v2'") + tc.execute("SELECT * FROM your_schema.v1;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr1 to view 'your_schema.v1'") + + mdb.execute("GRANT SELECT on your_schema.v2 to usr1;").assertSucceeded() + tc.execute("SELECT * FROM your_schema.v2;").assertSucceeded()\ + .assertDataResultMatch([('alice', '********A', Decimal('888.42')), ('bob', '********0', Decimal('444.42'))]) + tc.execute("SELECT * FROM your_schema.v1;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr1 to view 'your_schema.v1'") + + mdb.execute("GRANT SELECT on your_schema.v1 to usr1;").assertSucceeded() + tc.execute("SELECT * FROM your_schema.v1;").assertSucceeded()\ + .assertDataResultMatch([('alice', 1980, '********A', 'high'), ('bob', 1970, '********0', 'low')]) + tc.execute("SELECT * FROM your_schema.person;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr1 to table 'your_schema.person'") + tc.execute("SELECT * FROM your_schema.employee;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr1 to table 'your_schema.employee'") + + # usr2 has access to one table and gets access to views top-down + with SQLTestCase() as tc: + tc.connect(username="usr2", password="p2") + mdb.execute("GRANT SELECT on your_schema.employee to usr2;").assertSucceeded() + + mdb.execute("GRANT SELECT on your_schema.v4 to usr2;").assertSucceeded() + tc.execute("SELECT * FROM your_schema.v4;").assertSucceeded()\ + .assertDataResultMatch([(1980, 'high'), (1970, 'low')]) + tc.execute("SELECT * FROM your_schema.v3;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr2 to view 'your_schema.v3'") + tc.execute("SELECT * FROM your_schema.v2;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr2 to view 'your_schema.v2'") + tc.execute("SELECT * FROM your_schema.v1;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr2 to view 'your_schema.v1'") + + mdb.execute("GRANT SELECT on your_schema.v3 to usr2;").assertSucceeded() + tc.execute("SELECT * FROM your_schema.v3;").assertSucceeded()\ + .assertDataResultMatch([('alice', 1980, Decimal('888.42')), ('bob', 1970, Decimal('444.42'))]) + tc.execute("SELECT * FROM your_schema.v2;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr2 to view 'your_schema.v2'") + tc.execute("SELECT * FROM your_schema.v1;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr2 to view 'your_schema.v1'") + + mdb.execute("GRANT SELECT on your_schema.v2 to usr2;").assertSucceeded() + tc.execute("SELECT * FROM your_schema.v2;").assertSucceeded()\ + .assertDataResultMatch([('alice', '********A', Decimal('888.42')), ('bob', '********0', Decimal('444.42'))]) + tc.execute("SELECT * FROM your_schema.v1;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr2 to view 'your_schema.v1'") + + mdb.execute("GRANT SELECT on your_schema.v1 to usr2;").assertSucceeded() + tc.execute("SELECT * FROM your_schema.v1;").assertSucceeded()\ + .assertDataResultMatch([('alice', 1980, '********A', 'high'), ('bob', 1970, '********0', 'low')]) + tc.execute("SELECT * FROM your_schema.person;")\ + .assertFailed(err_code="42000", err_message="SELECT: access denied for usr2 to table 'your_schema.person'") + tc.execute("SELECT * FROM your_schema.employee;").assertRowCount(2) + + # clean up + mdb.execute("DROP USER usr1;").assertSucceeded() + mdb.execute("DROP USER usr2;").assertSucceeded() + mdb.execute("DROP ROLE my_role;").assertSucceeded() + mdb.execute("DROP SCHEMA my_schema CASCADE;").assertSucceeded() + mdb.execute("DROP SCHEMA your_schema CASCADE;").assertSucceeded() _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
