GRANT
Grants the specified privileges to an existing user. The user will be created if it does not exist.
All of the privileges and their scope (global, database, table, etc.) are explained in the Permissions Matrix.
Syntax
GRANT [TRANSFERABLE] priv_type [, priv_type [ ... ]] ON priv_level TO user_or_role [, user_or_role [ ... ]] [WITH GRANT OPTION] [REQUIRE {SSL | NONE}] priv_type: ALL PRIVILEGES | SELECT | INSERT | UPDATE | DELETE | CREATE | DROP | RELOAD | PROCESS | FILE READ | FILE WRITE | INDEX | ALTER | SHOW METADATA | GRANT OPTION | SUPER | SYSTEM_VARIABLE_ADMIN | CREATE TEMPORARY TABLES | LOCK TABLES | CREATE VIEW | SHOW VIEW | CREATE USER | ALTER USER | CLUSTER | ALTER VIEW | DROP VIEW | BACKUP | CREATE DATABASE | DROP DATABASE | CREATE PIPELINE | DROP PIPELINE | ALTER PIPELINE | START PIPELINE | SHOW PIPELINE | EXECUTE | CREATE ROUTINE | ALTER ROUTINE | SHOW ROUTINE | CREATE POOL | DROP POOL | CREATE LINK | DROP LINK | SHOW LINK priv_level: * | *.* | database.* | database.table user_or_role: user [, user] | role user: 'user_name'[@'host_name'] [IDENTIFIED BY 'password'] role: ROLE 'role_name'
Arguments
priv_type: The privileges to grant the specified user.
priv_level: The resource on which to grant privileges.
*
-USAGE
privilege only. See Permissions Matrix for more detailed information on which commands can be executed with minimalUSAGE
privilege.*.*
- Global privileges are administrative or apply to all databases on a given server.database.*
- Database privileges apply to all objects in a given database. Wildcards (%) are accepted in database names. The underscore character (_) can also be used as a wildcard to match any single character. A backspace (\) can precede an underscore to indicate the underscore is a literal part of a database name, e.g.db_1
. A sharp character (#) must precede the database name if wildcards are used in aGRANT
command. This will allow users to be granted permissions to any database that fits the pattern. See the following examples.Grant access to user1 for all databases that starts with "db" and is followed by a single character, e.g.
db1
,db2
, ordba
:GRANT ALL ON `#db_`.* TO user1;
Grant access to user2 for all databases that start with "db" and followed by zero or more characters, e.g.
db
,db1
,db123
, ordb_456
:GRANT ALL ON `#db%`.* TO user2;
Grant access to user3 for all databases that start with "db_" and followed by zero or more characters, e.g.
db_
,db_1
,db_abc
, ordb_123
:GRANT ALL ON `#db\_%`.* TO user3;
database.table
- Table privileges apply to all columns in a given table in a given database.
user_or_role: One or more user attributes or a role attribute. The @'host_name'
part is optional. If a hostname is not specified, @'%'
will be appended to the user string. For example, 'demo-user'
is equivalent to 'demo-user'@'%'
.The '%'
wildcard matches any hostname. user
and host_name
need not be quoted if they are legal as unquoted identifiers. Quotes must be used if a host_name
string contains wildcard characters (such as %
), or a user string contains special characters (such as space or -
) or is a restricted keyword.
Remarks
The
GRANT
command creates a new user if the specified username does not exist.A user with the
SUPER
permission can perform the following actions:Set global variables, run administrative commands, and change passwords for other users.
Access all available resource pools. Permissions for additional resource pools can be given using the
GRANT
command.
See the Permission Matrix for the required permission.
USAGE
privilege allows users to connect to the cluster, view some SHOW Commands, and view objects on theinformation_schema
database.PROCESS
,FILE READ
,FILE WRITE
,SHOW METADATA
,SUPER
,CREATE USER
,SYSTEM_VARIABLES_ADMIN
,CLUSTER
,CREATE POOL
, andDROP POOL
are global privileges. They can be granted only on*.*
and not on specific databases or tables.The
SYSTEM_VARIABLES_ADMIN
permission allows users to set certain engine variables. These engine variables can be found by running theSHOW VARIABLES EXTENDED
command and confirming the value is "Yes" in theIn_Allow_List
column.The
WITH GRANT OPTION
clause grants theGRANT OPTION
privilege, which allows a user to grant all privileges that the user already has to other users within a scoped database. Theprivilege_transfer_mode
engine variable must be left on the default valuegrant_option
for theWITH GRANT OPTION
clause to work as expected. TheGRANT ... WITH GRANT OPTION
andGRANT GRANT OPTION ...
statements are synonymous. See Default Mode Permissions Changes for more information.Note: Different users with
GRANT OPTION
privilege may be able to combine their privileges.A user with both the
GRANT OPTION
andSUPER
privileges can grant any privilege to the users (including themselves) within a scoped database.The
TRANSFERABLE
clause allows a user to grant individual privileges to another user. Theprivilege_transfer_mode
engine variable must be set toper_privilege
for theTRANSFERABLE
clause to work as expected. Ifprivilege_transfer_mode
is left on the default valuegrant_option
and theTRANSFERABLE
clause is attempted, the user will receive an error. If a non-transferable privilege is granted to a user with the same privilege level as the grantee, then transferability is revoked.Note
The user must have
SYSTEM_VARIABLES_ADMIN
orSUPER
privilege to change the value ofprivilege_transfer_mode
.For example, after this statement, both
SELECT
andINSERT
ondb.*
will be available to 'user'@'%
' and 'user'@'%
' will be able to grant them to others:GRANT TRANSFERABLE SELECT, INSERT ON db.* TO 'user'@'%'
The
EXECUTE
permission allows a user to execute an extensibility object. For example, having this permission for UDFs, UDAFs, and TVFs allows the user to run them inSELECT
queries. Similarly, a user withEXECUTE
privileges for a stored procedure can run the SP using theCALL
orECHO
statements.The
CREATE ROUTINE
privilege allows a user to create an extensibility object (for example, a UDF, UDAF, TVF, or SP).The
ALTER ROUTINE
privilege allows a user to replace or delete an extensibility object (for example, a UDF, UDAF, TVF, or SP).GRANT
syntax for the following authentication users as follows:The
ALTER USER
permission can be used to executeALTER USER
andSET PASSWORD
commands.This command causes implicit commits. See COMMIT for more information.
This command can be run on any SingleStoreDB node. For more information, see the Node Requirements for SingleStoreDB Commands.
The
GRANT
command holds a global lock on the cluster until the processing of the command is completed. See Cluster Level Locks for more info.
Examples
The following examples demonstrates how to grant privileges to the existing singlestore_admin
user:
GRANT ALL PRIVILEGES ON *.* TO 'singlestore_admin' IDENTIFIED BY 'password' WITH GRANT OPTION;
Or
GRANT * ON *.* TO 'singlestore_admin' IDENTIFIED BY 'password' WITH GRANT OPTION;
Note: The privilege_transfer_mode
engine variable must be left on the default value grant_option
for the WITH GRANT OPTION
clause to work as expected. The user must have SYSTEM_VARIABLES_ADMIN
privilege to be able to change the value of privilege_transfer_mode
.
GRANT TRANSFERABLE SELECT, INSERT ON db.* TO 'singlestore_admin';
Note: The privilege_transfer_mode
engine variable must be set to value per_privilege
to grant transferable privileges. The user must have SYSTEM_VARIABLES_ADMIN
privilege to be able to change the value of privilege_transfer_mode
.
The following example demonstrates how to grant the specified privileges to all databases to the full_backup_role
role.
GRANT BACKUP, RELOAD ON *.* TO ROLE 'full_backup_role';
Note
If you removed ROLE
from the example above, the GRANT
command would apply privileges to the user 'full_backup_role'
.
The following example demonstrates how to grant EXECUTE
and ALTER ROUTINE
privileges for the stored procedure recStock
to user 'adam'
.
GRANT EXECUTE, ALTER ROUTINE ON db_tmp1.recStock TO 'adam';
The following example demonstrates how to grant permissions for a resource pool p1
to a user u1
:
GRANT USAGE ON RESOURCE POOL p1 to 'u1';
The following example demonstrates how to grant the execute permission for a stored procedure or function:
GRANT EXCUTE ON dbname.procname TO username;
Related Topics