Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
關注 1
收藏 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
分支: master
分支列表 標籤
${ item.name }
建立分支 ${ searchTerm }
從 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/sql-alterdefaultprivileges....

159 line
11KB
原始文件 永久連結 Blame 歷史記錄 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!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>ALTER DEFAULT PRIVILEGES</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 V1.79.1" /><link rel="prev" href="sql-alterdatabase.html" title="ALTER DATABASE" /><link rel="next" href="sql-alterdomain.html" title="ALTER DOMAIN" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">ALTER DEFAULT PRIVILEGES</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alterdatabase.html" title="ALTER DATABASE">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 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-alterdomain.html" title="ALTER DOMAIN">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-ALTERDEFAULTPRIVILEGES"><div class="titlepage"></div><a id="id-1.9.3.8.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER DEFAULT PRIVILEGES</span></h2><p>ALTER DEFAULT PRIVILEGES — define default access privileges</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">

  3. ALTER DEFAULT PRIVILEGES

  4.     [ FOR { ROLE | USER } <em class="replaceable"><code>target_role</code></em> [, ...] ]

  5.     [ IN SCHEMA <em class="replaceable"><code>schema_name</code></em> [, ...] ]

  6.     <em class="replaceable"><code>abbreviated_grant_or_revoke</code></em>

  7. 

  8. <span class="phrase">where <em class="replaceable"><code>abbreviated_grant_or_revoke</code></em> is one of:</span>

  9. 

  10. GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }

  11.     [, ...] | ALL [ PRIVILEGES ] }

  12.     ON TABLES

  13.     TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

  14. 

  15. GRANT { { USAGE | SELECT | UPDATE }

  16.     [, ...] | ALL [ PRIVILEGES ] }

  17.     ON SEQUENCES

  18.     TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

  19. 

  20. GRANT { EXECUTE | ALL [ PRIVILEGES ] }

  21.     ON { FUNCTIONS | ROUTINES }

  22.     TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

  23. 

  24. GRANT { USAGE | ALL [ PRIVILEGES ] }

  25.     ON TYPES

  26.     TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

  27. 

  28. GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }

  29.     ON SCHEMAS

  30.     TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

  31. 

  32. REVOKE [ GRANT OPTION FOR ]

  33.     { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }

  34.     [, ...] | ALL [ PRIVILEGES ] }

  35.     ON TABLES

  36.     FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]

  37.     [ CASCADE | RESTRICT ]

  38. 

  39. REVOKE [ GRANT OPTION FOR ]

  40.     { { USAGE | SELECT | UPDATE }

  41.     [, ...] | ALL [ PRIVILEGES ] }

  42.     ON SEQUENCES

  43.     FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]

  44.     [ CASCADE | RESTRICT ]

  45. 

  46. REVOKE [ GRANT OPTION FOR ]

  47.     { EXECUTE | ALL [ PRIVILEGES ] }

  48.     ON { FUNCTIONS | ROUTINES }

  49.     FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]

  50.     [ CASCADE | RESTRICT ]

  51. 

  52. REVOKE [ GRANT OPTION FOR ]

  53.     { USAGE | ALL [ PRIVILEGES ] }

  54.     ON TYPES

  55.     FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]

  56.     [ CASCADE | RESTRICT ]

  57. 

  58. REVOKE [ GRANT OPTION FOR ]

  59.     { USAGE | CREATE | ALL [ PRIVILEGES ] }

  60.     ON SCHEMAS

  61.     FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]

  62.     [ CASCADE | RESTRICT ]

  63. </pre></div><div class="refsect1" id="SQL-ALTERDEFAULTPRIVILEGES-DESCRIPTION"><h2>Description</h2><p>

  64.    <code class="command">ALTER DEFAULT PRIVILEGES</code> allows you to set the privileges

  65.    that will be applied to objects created in the future.  (It does not

  66.    affect privileges assigned to already-existing objects.)  Currently,

  67.    only the privileges for schemas, tables (including views and foreign

  68.    tables), sequences, functions, and types (including domains) can be

  69.    altered.  For this command, functions include aggregates and procedures.

  70.    The words <code class="literal">FUNCTIONS</code> and <code class="literal">ROUTINES</code> are

  71.    equivalent in this command.  (<code class="literal">ROUTINES</code> is preferred

  72.    going forward as the standard term for functions and procedures taken

  73.    together.  In earlier PostgreSQL releases, only the

  74.    word <code class="literal">FUNCTIONS</code> was allowed.  It is not possible to set

  75.    default privileges for functions and procedures separately.)

  76.   </p><p>

  77.    You can change default privileges only for objects that will be created by

  78.    yourself or by roles that you are a member of.  The privileges can be set

  79.    globally (i.e., for all objects created in the current database),

  80.    or just for objects created in specified schemas.

  81.   </p><p>

  82.    As explained in <a class="xref" href="ddl-priv.html" title="5.7. Privileges">Section 5.7</a>,

  83.    the default privileges for any object type normally grant all grantable

  84.    permissions to the object owner, and may grant some privileges to

  85.    <code class="literal">PUBLIC</code> as well.  However, this behavior can be changed by

  86.    altering the global default privileges with

  87.    <code class="command">ALTER DEFAULT PRIVILEGES</code>.

  88.   </p><p>

  89.    Default privileges that are specified per-schema are added to whatever

  90.    the global default privileges are for the particular object type.

  91.    This means you cannot revoke privileges per-schema if they are granted

  92.    globally (either by default, or according to a previous <code class="command">ALTER

  93.    DEFAULT PRIVILEGES</code> command that did not specify a schema).

  94.    Per-schema <code class="literal">REVOKE</code> is only useful to reverse the

  95.    effects of a previous per-schema <code class="literal">GRANT</code>.

  96.   </p><div class="refsect2" id="id-1.9.3.8.5.6"><h3>Parameters</h3><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>target_role</code></em></span></dt><dd><p>

  97.       The name of an existing role of which the current role is a member.

  98.       If <code class="literal">FOR ROLE</code> is omitted, the current role is assumed.

  99.      </p></dd><dt><span class="term"><em class="replaceable"><code>schema_name</code></em></span></dt><dd><p>

  100.       The name of an existing schema.  If specified, the default privileges

  101.       are altered for objects later created in that schema.

  102.       If <code class="literal">IN SCHEMA</code> is omitted, the global default privileges

  103.       are altered.

  104.       <code class="literal">IN SCHEMA</code> is not allowed when setting privileges

  105.       for schemas, since schemas can't be nested.

  106.      </p></dd><dt><span class="term"><em class="replaceable"><code>role_name</code></em></span></dt><dd><p>

  107.       The name of an existing role to grant or revoke privileges for.

  108.       This parameter, and all the other parameters in

  109.       <em class="replaceable"><code>abbreviated_grant_or_revoke</code></em>,

  110.       act as described under

  111.       <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a> or

  112.       <a class="xref" href="sql-revoke.html" title="REVOKE"><span class="refentrytitle">REVOKE</span></a>,

  113.       except that one is setting permissions for a whole class of objects

  114.       rather than specific named objects.

  115.      </p></dd></dl></div></div></div><div class="refsect1" id="SQL-ALTERDEFAULTPRIVILEGES-NOTES"><h2>Notes</h2><p>

  116.    Use <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a>'s <code class="command">\ddp</code> command

  117.    to obtain information about existing assignments of default privileges.

  118.    The meaning of the privilege display is the same as explained for

  119.    <code class="command">\dp</code> in <a class="xref" href="ddl-priv.html" title="5.7. Privileges">Section 5.7</a>.

  120.   </p><p>

  121.    If you wish to drop a role for which the default privileges have been

  122.    altered, it is necessary to reverse the changes in its default privileges

  123.    or use <code class="command">DROP OWNED BY</code> to get rid of the default privileges entry

  124.    for the role.

  125.   </p></div><div class="refsect1" id="SQL-ALTERDEFAULTPRIVILEGES-EXAMPLES"><h2>Examples</h2><p>

  126.    Grant SELECT privilege to everyone for all tables (and views) you

  127.    subsequently create in schema <code class="literal">myschema</code>, and allow

  128.    role <code class="literal">webuser</code> to INSERT into them too:

  129. 

  130. </p><pre class="programlisting">

  131. ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;

  132. ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;

  133. </pre><p>

  134.   </p><p>

  135.    Undo the above, so that subsequently-created tables won't have any

  136.    more permissions than normal:

  137. 

  138. </p><pre class="programlisting">

  139. ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;

  140. ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;

  141. </pre><p>

  142.   </p><p>

  143.    Remove the public EXECUTE permission that is normally granted on functions,

  144.    for all functions subsequently created by role <code class="literal">admin</code>:

  145. </p><pre class="programlisting">

  146. ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

  147. </pre><p>

  148.    Note however that you <span class="emphasis"><em>cannot</em></span> accomplish that effect

  149.    with a command limited to a single schema.  This command has no effect,

  150.    unless it is undoing a matching <code class="literal">GRANT</code>:

  151. </p><pre class="programlisting">

  152. ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

  153. </pre><p>

  154.    That's because per-schema default privileges can only add privileges to

  155.    the global setting, not remove privileges granted by it.

  156.   </p></div><div class="refsect1" id="id-1.9.3.8.8"><h2>Compatibility</h2><p>

  157.    There is no <code class="command">ALTER DEFAULT PRIVILEGES</code> statement in the SQL

  158.    standard.

  159.   </p></div><div class="refsect1" id="id-1.9.3.8.9"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a>, <a class="xref" href="sql-revoke.html" title="REVOKE"><span class="refentrytitle">REVOKE</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-alterdatabase.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-alterdomain.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER DATABASE </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> ALTER DOMAIN</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1