diff options
Diffstat (limited to 'doc/src/sgml/html/sql-grant.html')
| -rw-r--r-- | doc/src/sgml/html/sql-grant.html | 318 |
1 files changed, 318 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-grant.html b/doc/src/sgml/html/sql-grant.html new file mode 100644 index 0000000..9d4a136 --- /dev/null +++ b/doc/src/sgml/html/sql-grant.html @@ -0,0 +1,318 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>GRANT</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-fetch.html" title="FETCH" /><link rel="next" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">GRANT</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-fetch.html" title="FETCH">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-GRANT"><div class="titlepage"></div><a id="id-1.9.3.150.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">GRANT</span></h2><p>GRANT — define access privileges</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } + [, ...] | ALL [ PRIVILEGES ] } + ON { [ TABLE ] <em class="replaceable"><code>table_name</code></em> [, ...] + | ALL TABLES IN SCHEMA <em class="replaceable"><code>schema_name</code></em> [, ...] } + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <em class="replaceable"><code>column_name</code></em> [, ...] ) + [, ...] | ALL [ PRIVILEGES ] ( <em class="replaceable"><code>column_name</code></em> [, ...] ) } + ON [ TABLE ] <em class="replaceable"><code>table_name</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { { USAGE | SELECT | UPDATE } + [, ...] | ALL [ PRIVILEGES ] } + ON { SEQUENCE <em class="replaceable"><code>sequence_name</code></em> [, ...] + | ALL SEQUENCES IN SCHEMA <em class="replaceable"><code>schema_name</code></em> [, ...] } + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } + ON DATABASE <em class="replaceable"><code>database_name</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { USAGE | ALL [ PRIVILEGES ] } + ON DOMAIN <em class="replaceable"><code>domain_name</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { USAGE | ALL [ PRIVILEGES ] } + ON FOREIGN DATA WRAPPER <em class="replaceable"><code>fdw_name</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { USAGE | ALL [ PRIVILEGES ] } + ON FOREIGN SERVER <em class="replaceable"><code>server_name</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { EXECUTE | ALL [ PRIVILEGES ] } + ON { { FUNCTION | PROCEDURE | ROUTINE } <em class="replaceable"><code>routine_name</code></em> [ ( [ [ <em class="replaceable"><code>argmode</code></em> ] [ <em class="replaceable"><code>arg_name</code></em> ] <em class="replaceable"><code>arg_type</code></em> [, ...] ] ) ] [, ...] + | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA <em class="replaceable"><code>schema_name</code></em> [, ...] } + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { USAGE | ALL [ PRIVILEGES ] } + ON LANGUAGE <em class="replaceable"><code>lang_name</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } + ON LARGE OBJECT <em class="replaceable"><code>loid</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { { SET | ALTER SYSTEM } [, ... ] | ALL [ PRIVILEGES ] } + ON PARAMETER <em class="replaceable"><code>configuration_parameter</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] } + ON SCHEMA <em class="replaceable"><code>schema_name</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { CREATE | ALL [ PRIVILEGES ] } + ON TABLESPACE <em class="replaceable"><code>tablespace_name</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT { USAGE | ALL [ PRIVILEGES ] } + ON TYPE <em class="replaceable"><code>type_name</code></em> [, ...] + TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +GRANT <em class="replaceable"><code>role_name</code></em> [, ...] TO <em class="replaceable"><code>role_specification</code></em> [, ...] + [ WITH ADMIN OPTION ] + [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ] + +<span class="phrase">where <em class="replaceable"><code>role_specification</code></em> can be:</span> + + [ GROUP ] <em class="replaceable"><code>role_name</code></em> + | PUBLIC + | CURRENT_ROLE + | CURRENT_USER + | SESSION_USER +</pre></div><div class="refsect1" id="SQL-GRANT-DESCRIPTION"><h2>Description</h2><p> + The <code class="command">GRANT</code> command has two basic variants: one + that grants privileges on a database object (table, column, view, + foreign table, sequence, database, foreign-data wrapper, foreign server, + function, procedure, procedural language, large object, configuration + parameter, schema, tablespace, or type), and one that grants + membership in a role. These variants are similar in many ways, but + they are different enough to be described separately. + </p><div class="refsect2" id="SQL-GRANT-DESCRIPTION-OBJECTS"><h3>GRANT on Database Objects</h3><p> + This variant of the <code class="command">GRANT</code> command gives specific + privileges on a database object to + one or more roles. These privileges are added + to those already granted, if any. + </p><p> + The key word <code class="literal">PUBLIC</code> indicates that the + privileges are to be granted to all roles, including those that might + be created later. <code class="literal">PUBLIC</code> can be thought of as an + implicitly defined group that always includes all roles. + Any particular role will have the sum + of privileges granted directly to it, privileges granted to any role it + is presently a member of, and privileges granted to + <code class="literal">PUBLIC</code>. + </p><p> + If <code class="literal">WITH GRANT OPTION</code> is specified, the recipient + of the privilege can in turn grant it to others. Without a grant + option, the recipient cannot do that. Grant options cannot be granted + to <code class="literal">PUBLIC</code>. + </p><p> + If <code class="literal">GRANTED BY</code> is specified, the specified grantor must + be the current user. This clause is currently present in this form only + for SQL compatibility. + </p><p> + There is no need to grant privileges to the owner of an object + (usually the user that created it), + as the owner has all privileges by default. (The owner could, + however, choose to revoke some of their own privileges for safety.) + </p><p> + The right to drop an object, or to alter its definition in any way, is + not treated as a grantable privilege; it is inherent in the owner, + and cannot be granted or revoked. (However, a similar effect can be + obtained by granting or revoking membership in the role that owns + the object; see below.) The owner implicitly has all grant + options for the object, too. + </p><p> + The possible privileges are: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">SELECT</code><br /></span><span class="term"><code class="literal">INSERT</code><br /></span><span class="term"><code class="literal">UPDATE</code><br /></span><span class="term"><code class="literal">DELETE</code><br /></span><span class="term"><code class="literal">TRUNCATE</code><br /></span><span class="term"><code class="literal">REFERENCES</code><br /></span><span class="term"><code class="literal">TRIGGER</code><br /></span><span class="term"><code class="literal">CREATE</code><br /></span><span class="term"><code class="literal">CONNECT</code><br /></span><span class="term"><code class="literal">TEMPORARY</code><br /></span><span class="term"><code class="literal">EXECUTE</code><br /></span><span class="term"><code class="literal">USAGE</code><br /></span><span class="term"><code class="literal">SET</code><br /></span><span class="term"><code class="literal">ALTER SYSTEM</code></span></dt><dd><p> + Specific types of privileges, as defined in <a class="xref" href="ddl-priv.html" title="5.7. Privileges">Section 5.7</a>. + </p></dd><dt><span class="term"><code class="literal">TEMP</code></span></dt><dd><p> + Alternative spelling for <code class="literal">TEMPORARY</code>. + </p></dd><dt><span class="term"><code class="literal">ALL PRIVILEGES</code></span></dt><dd><p> + Grant all of the privileges available for the object's type. + The <code class="literal">PRIVILEGES</code> key word is optional in + <span class="productname">PostgreSQL</span>, though it is required by + strict SQL. + </p></dd></dl></div><p> + </p><p> + The <code class="literal">FUNCTION</code> syntax works for plain functions, + aggregate functions, and window functions, but not for procedures; + use <code class="literal">PROCEDURE</code> for those. + Alternatively, use <code class="literal">ROUTINE</code> to refer to a function, + aggregate function, window function, or procedure regardless of its + precise type. + </p><p> + There is also an option to grant privileges on all objects of the same + type within one or more schemas. This functionality is currently supported + only for tables, sequences, functions, and procedures. <code class="literal">ALL + TABLES</code> also affects views and foreign tables, just like the + specific-object <code class="command">GRANT</code> command. <code class="literal">ALL + FUNCTIONS</code> also affects aggregate and window functions, but not + procedures, again just like the specific-object <code class="command">GRANT</code> + command. Use <code class="literal">ALL ROUTINES</code> to include procedures. + </p></div><div class="refsect2" id="SQL-GRANT-DESCRIPTION-ROLES"><h3>GRANT on Roles</h3><p> + This variant of the <code class="command">GRANT</code> command grants membership + in a role to one or more other roles. Membership in a role is significant + because it conveys the privileges granted to a role to each of its + members. + </p><p> + If <code class="literal">WITH ADMIN OPTION</code> is specified, the member can + in turn grant membership in the role to others, and revoke membership + in the role as well. Without the admin option, ordinary users cannot + do that. A role is not considered to hold <code class="literal">WITH ADMIN + OPTION</code> on itself. Database superusers can grant or revoke + membership in any role to anyone. Roles having + <code class="literal">CREATEROLE</code> privilege can grant or revoke membership + in any role that is not a superuser. + </p><p> + If <code class="literal">GRANTED BY</code> is specified, the grant is recorded as + having been done by the specified role. Only database superusers may + use this option, except when it names the same role executing the command. + </p><p> + Unlike the case with privileges, membership in a role cannot be granted + to <code class="literal">PUBLIC</code>. Note also that this form of the command + does not allow the noise word <code class="literal">GROUP</code> + in <em class="replaceable"><code>role_specification</code></em>. + </p></div></div><div class="refsect1" id="SQL-GRANT-NOTES"><h2>Notes</h2><p> + The <a class="link" href="sql-revoke.html" title="REVOKE"><code class="command">REVOKE</code></a> command is used + to revoke access privileges. + </p><p> + Since <span class="productname">PostgreSQL</span> 8.1, the concepts of users and + groups have been unified into a single kind of entity called a role. + It is therefore no longer necessary to use the keyword <code class="literal">GROUP</code> + to identify whether a grantee is a user or a group. <code class="literal">GROUP</code> + is still allowed in the command, but it is a noise word. + </p><p> + A user may perform <code class="command">SELECT</code>, <code class="command">INSERT</code>, etc. on a + column if they hold that privilege for either the specific column or + its whole table. Granting the privilege at the table level and then + revoking it for one column will not do what one might wish: the + table-level grant is unaffected by a column-level operation. + </p><p> + When a non-owner of an object attempts to <code class="command">GRANT</code> privileges + on the object, the command will fail outright if the user has no + privileges whatsoever on the object. As long as some privilege is + available, the command will proceed, but it will grant only those + privileges for which the user has grant options. The <code class="command">GRANT ALL + PRIVILEGES</code> forms will issue a warning message if no grant options are + held, while the other forms will issue a warning if grant options for + any of the privileges specifically named in the command are not held. + (In principle these statements apply to the object owner as well, but + since the owner is always treated as holding all grant options, the + cases can never occur.) + </p><p> + It should be noted that database superusers can access + all objects regardless of object privilege settings. This + is comparable to the rights of <code class="literal">root</code> in a Unix system. + As with <code class="literal">root</code>, it's unwise to operate as a superuser + except when absolutely necessary. + </p><p> + If a superuser chooses to issue a <code class="command">GRANT</code> or <code class="command">REVOKE</code> + command, the command is performed as though it were issued by the + owner of the affected object. In particular, privileges granted via + such a command will appear to have been granted by the object owner. + (For role membership, the membership appears to have been granted + by the containing role itself.) + </p><p> + <code class="command">GRANT</code> and <code class="command">REVOKE</code> can also be done by a role + that is not the owner of the affected object, but is a member of the role + that owns the object, or is a member of a role that holds privileges + <code class="literal">WITH GRANT OPTION</code> on the object. In this case the + privileges will be recorded as having been granted by the role that + actually owns the object or holds the privileges + <code class="literal">WITH GRANT OPTION</code>. For example, if table + <code class="literal">t1</code> is owned by role <code class="literal">g1</code>, of which role + <code class="literal">u1</code> is a member, then <code class="literal">u1</code> can grant privileges + on <code class="literal">t1</code> to <code class="literal">u2</code>, but those privileges will appear + to have been granted directly by <code class="literal">g1</code>. Any other member + of role <code class="literal">g1</code> could revoke them later. + </p><p> + If the role executing <code class="command">GRANT</code> holds the required privileges + indirectly via more than one role membership path, it is unspecified + which containing role will be recorded as having done the grant. In such + cases it is best practice to use <code class="command">SET ROLE</code> to become the + specific role you want to do the <code class="command">GRANT</code> as. + </p><p> + Granting permission on a table does not automatically extend + permissions to any sequences used by the table, including + sequences tied to <code class="type">SERIAL</code> columns. Permissions on + sequences must be set separately. + </p><p> + See <a class="xref" href="ddl-priv.html" title="5.7. Privileges">Section 5.7</a> for more information about specific + privilege types, as well as how to inspect objects' privileges. + </p></div><div class="refsect1" id="SQL-GRANT-EXAMPLES"><h2>Examples</h2><p> + Grant insert privilege to all users on table <code class="literal">films</code>: + +</p><pre class="programlisting"> +GRANT INSERT ON films TO PUBLIC; +</pre><p> + </p><p> + Grant all available privileges to user <code class="literal">manuel</code> on view + <code class="literal">kinds</code>: + +</p><pre class="programlisting"> +GRANT ALL PRIVILEGES ON kinds TO manuel; +</pre><p> + + Note that while the above will indeed grant all privileges if executed by a + superuser or the owner of <code class="literal">kinds</code>, when executed by someone + else it will only grant those permissions for which the someone else has + grant options. + </p><p> + Grant membership in role <code class="literal">admins</code> to user <code class="literal">joe</code>: + +</p><pre class="programlisting"> +GRANT admins TO joe; +</pre></div><div class="refsect1" id="SQL-GRANT-COMPATIBILITY"><h2>Compatibility</h2><p> + According to the SQL standard, the <code class="literal">PRIVILEGES</code> + key word in <code class="literal">ALL PRIVILEGES</code> is required. The + SQL standard does not support setting the privileges on more than + one object per command. + </p><p> + <span class="productname">PostgreSQL</span> allows an object owner to revoke their + own ordinary privileges: for example, a table owner can make the table + read-only to themselves by revoking their own <code class="literal">INSERT</code>, + <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>, and <code class="literal">TRUNCATE</code> + privileges. This is not possible according to the SQL standard. The + reason is that <span class="productname">PostgreSQL</span> treats the owner's + privileges as having been granted by the owner to themselves; therefore they + can revoke them too. In the SQL standard, the owner's privileges are + granted by an assumed entity <span class="quote">“<span class="quote">_SYSTEM</span>”</span>. Not being + <span class="quote">“<span class="quote">_SYSTEM</span>”</span>, the owner cannot revoke these rights. + </p><p> + According to the SQL standard, grant options can be granted to + <code class="literal">PUBLIC</code>; PostgreSQL only supports granting grant options + to roles. + </p><p> + The SQL standard allows the <code class="literal">GRANTED BY</code> option to + specify only <code class="literal">CURRENT_USER</code> or + <code class="literal">CURRENT_ROLE</code>. The other variants are PostgreSQL + extensions. + </p><p> + The SQL standard provides for a <code class="literal">USAGE</code> privilege + on other kinds of objects: character sets, collations, + translations. + </p><p> + In the SQL standard, sequences only have a <code class="literal">USAGE</code> + privilege, which controls the use of the <code class="literal">NEXT VALUE FOR</code> + expression, which is equivalent to the + function <code class="function">nextval</code> in PostgreSQL. The sequence + privileges <code class="literal">SELECT</code> and <code class="literal">UPDATE</code> are + PostgreSQL extensions. The application of the + sequence <code class="literal">USAGE</code> privilege to + the <code class="literal">currval</code> function is also a PostgreSQL extension (as + is the function itself). + </p><p> + Privileges on databases, tablespaces, schemas, languages, and + configuration parameters are + <span class="productname">PostgreSQL</span> extensions. + </p></div><div class="refsect1" id="id-1.9.3.150.9"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-revoke.html" title="REVOKE"><span class="refentrytitle">REVOKE</span></a>, <a class="xref" href="sql-alterdefaultprivileges.html" title="ALTER DEFAULT PRIVILEGES"><span class="refentrytitle">ALTER DEFAULT PRIVILEGES</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-fetch.html" title="FETCH">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA">Next</a></td></tr><tr><td width="40%" align="left" valign="top">FETCH </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> IMPORT FOREIGN SCHEMA</td></tr></table></div></body></html>
\ No newline at end of file |