Security
Statement Execution
Opteryx allows you to prevent users from executing classes of queries, for example, you may limit a population of users to just being able to run SELECT
statements and prevent them from running other types of queries.
This can be used to limit user's ability to perform certain actions on the engine; for example, limiting users to only perform EXECUTE
queries.
Below is the complete list of permissions and the SQL query keyword which indicates that query type:
Permission | Query Keyword |
---|---|
Analyze | ANALYZE |
Execute | EXECUTE |
Explain | EXPLAIN |
Query | SELECT |
SetVariable | SET |
ShowColumns | SHOW COLUMNS |
ShowCreate | SHOW CREATE |
ShowFunctions | SHOW FUNCTIONS |
ShowVariables | SHOW VARIABLES |
ShowVariable | SHOW |
Note
Analyze
andExecute
are not fully supported statements.ShowVariable
only applies to queries that are not one of the more specificSHOW
query types.- Permissions exist for query types supported by the parser library but not supported by Opteryx.
Permissions are applied to connections using the permissions
parameter. The default permissions allow all queries to be executed.
import opteryx
conn = opteryx.connect(permissions={"Query"})
curr = conn.cursor()
# The user does not have permissions to execute a SHOW COLUMNS statement
# and this will raise a PermissionsError
try:
curr.execute("SHOW COLUMNS FROM $planets")
print(curr.head())
except opteryx.exceptions.PermissionsError:
print("User does not have permission to execute this query")
Opteryx does not have any defined roles; however, we can implement a Role-Based access model using code similar to the below.
import opteryx
# Define which roles exist and the permissions each role has,
# `opteryx.constants.PERMISSIONS` is all available permissions.
role_permissions = {
"admin": opteryx.constants.PERMISSIONS,
"user": {"Query"}
}
def get_user_permissions(user_roles:list):
# return the accumulated permissions for a user by appending
# the permissions for each of the roles for that user
permissions = set()
for role in user_roles:
if role in role_permissions:
permissions |= role_permissions[role]
return permissions
user_permissions = get_user_permissions(["user"])
# this can now be passed when creating a connection
conn = opteryx.connect(permissions=user_permissions)
In this code, we have a variable user_roles
that contains the roles a user has and a dictionary role_permissions
that contains the permissions each role has. When the code executes, it sets the permissions
variable with all the permissions the user has.