blocks|key|640361|text|以系统身份连接。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|640362|CREATE+USER+username+IDENTIFIED+BY+apassword;

GRANT+CONNECT+TO+username;

GRANT+EXECUTE+on+schema.procedure+TO+username;|code-block|syntax|javascript|640363|您可能还需要：|640364|GRANT+SELECT+[,+INSERT]+[,+UPDATE]+[,+DELETE]+on+schema.table+TO+username;|640365|到该过程使用的任何表。|640366|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Connect as SYSTEM.

<pre><code>CREATE USER username IDENTIFIED BY apassword;

GRANT CONNECT TO username;

GRANT EXECUTE on schema.procedure TO username;
</code></pre>

You may also need to:

<pre><code>GRANT SELECT [, INSERT] [, UPDATE] [, DELETE] on schema.table TO username;
</code></pre>

to whichever tables the procedure uses.

blocks|key|425981|text|CREATE+USER+USER_NAME+IDENTIFIED+BY+PASSWORD;
GRANT+CONNECT,+RESOURCE+TO+USER_NAME;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|425982|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>CREATE USER USER_NAME IDENTIFIED BY PASSWORD;
GRANT CONNECT, RESOURCE TO USER_NAME;
</code></pre>

blocks|key|640425|text|按照以下步骤在Oracle中创建用户。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|640426|--以系统用户身份连接|640427|CONNECT+<USER-NAME>/<PASSWORD>@<DATABASE+NAME>;|code-block|syntax|javascript|640428|--创建用户查询|640429|CREATE+USER+<USER+NAME>+IDENTIFIED+BY+<PASSWORD>;|640430|--提供角色|640431|GRANT+CONNECT,RESOURCE,DBA+TO+<USER+NAME>;|640432|--提供权限|640433|GRANT+CREATE+SESSION,+GRANT+ANY+PRIVILEGE+TO+<USER+NAME>;
GRANT+UNLIMITED+TABLESPACE+TO+<USER+NAME>;|640434|--提供对表的访问。|640435|GRANT+SELECT,UPDATE,INSERT+ON+<TABLE+NAME>+TO+<USER+NAME>;|640436|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|17|8|@]|9|@]|A|$]]|$1|S|3|T|5|F|7|18|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|19|8|@]|9|@]|A|$]]|$1|W|3|X|5|F|7|1A|8|@]|9|@]|A|$G|H]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

Follow the below steps for creating a user in Oracle. 
--Connect as System user
<pre><code>CONNECT &lt;USER-NAME&gt;/&lt;PASSWORD&gt;@&lt;DATABASE NAME&gt;;
</code></pre>
--Create user query
<pre><code>CREATE USER &lt;USER NAME&gt; IDENTIFIED BY &lt;PASSWORD&gt;;
</code></pre>
--Provide roles
<pre><code>GRANT CONNECT,RESOURCE,DBA TO &lt;USER NAME&gt;;
</code></pre>
--Provide privileges
<pre><code>GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO &lt;USER NAME&gt;;
GRANT UNLIMITED TABLESPACE TO &lt;USER NAME&gt;;
</code></pre>
--Provide access to tables.
<pre><code>GRANT SELECT,UPDATE,INSERT ON &lt;TABLE NAME&gt; TO &lt;USER NAME&gt;;
</code></pre>

blocks|key|640505|text|正如前面在评论中多次提到的那样，甲骨文不鼓励使用CONNECT、RESOURCE和DBA角色。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|640506|您必须以SYS身份连接才能创建您的角色和被赋予此角色的用户。您可以根据自己的喜好使用SQL+Developer或SQL*Plus。不要忘记在登录字符串中提到SYSDBA角色。connect_identifier使用不同的语法。|640507|sqlplus+sys/<<password>>@<<connect_identifier>>+as+sysdba|code-block|syntax|javascript|640508|假设您有一个12cR1，类似于使用"Oracle+Technology+Network+Developer+Day“作为VM提供的12cr1。连接字符串可能是(连接到提供的PDB)：|640509|sqlplus+sys/oracle@127.0.0.1/orcl+as+sysdba
sqlplus+sys@"127.0.0.1/orcl"+as+sysdba+--+to+avoid+putting+the+pw+in+clear|640510|请注意，在Unix下，必须对引号进行转义，否则它们将被shell使用。这样，"就变成了\"。|640511|然后创建角色MYROLE，并为其授予其他角色或权限。我添加了几乎最低限度的代码来做一些有趣的事情：|640512|create+role+myrole+not+identified;
grant+create+session+to+myrole;
grant+alter+session+to+myrole;
grant+create+table+to+myrole;|640513|接下来，创建用户MYUSER。identified+by后面的字符串是密码，区分大小写。剩下的就不是了。您也可以使用SQL分隔的标识符(用引号"括起来)，而不是常规标识符，后者转换为大写形式，并受一些限制。配额可以是unlimited而不是20m。|640514|create+user+myuser+identified+by+myuser+default+tablespace+users+profile+default+account+unlock;
alter+user+myuser+quota+20m+on+users;
grant+myrole+to+myuser;|640515|最终，您将以新用户的身份进行连接。|640516|请注意，您还可以更改默认配置文件或提供另一个配置文件，以自定义某些设置，如密码的有效期、允许的失败登录尝试次数等。|640517|entityMap|0|LINK|mutability|MUTABLE|url|http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html^0|O|7|W|8|15|3|0|2F|I|0|0|I|13|0|0|0|12|1|17|2|0|6|6|0|0|8|6|F|D|1Z|1|30|9|3C|3|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1J|8|@$9|1K|A|1L|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|1M|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1N|8|@]|D|@$9|1O|A|1P|1|1Q]]|E|$]]|$1|O|3|P|5|J|7|1R|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1S|8|@$9|1T|A|1U|B|C]|$9|1V|A|1W|B|C]]|D|@]|E|$]]|$1|S|3|T|5|6|7|1X|8|@$9|1Y|A|1Z|B|C]]|D|@]|E|$]]|$1|U|3|V|5|J|7|20|8|@]|D|@]|E|$K|L]]|$1|W|3|X|5|6|7|21|8|@$9|22|A|23|B|C]|$9|24|A|25|B|C]|$9|26|A|27|B|C]|$9|28|A|29|B|C]|$9|2A|A|2B|B|C]]|D|@]|E|$]]|$1|Y|3|Z|5|J|7|2C|8|@]|D|@]|E|$K|L]]|$1|10|3|11|5|6|7|2D|8|@]|D|@]|E|$]]|$1|12|3|13|5|6|7|2E|8|@]|D|@]|E|$]]|$1|14|3|-4|5|6|7|2F|8|@]|D|@]|E|$]]]|15|$16|$5|17|18|19|E|$1A|1B]]]]

As previously mentioned multiple times in the comments, the use of the <code>CONNECT</code>, <code>RESOURCE</code> and <code>DBA</code> roles is discouraged by Oracle.

You have to connect as SYS to create your role and the user(s) which are given this role. You can use SQL Developer or SQL*Plus as you prefer. Do not forget to mention the SYSDBA role in the logon string. The <code>connect_identifier</code> uses different syntaxes.

<pre><code>sqlplus sys/&lt;&lt;password&gt;&gt;@&lt;&lt;connect_identifier&gt;&gt; as sysdba
</code></pre>

Let's say you have a 12cR1 like the one provided as a VM with the "<a href="http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html" rel="nofollow">Oracle Technology Network Developer Day</a>". The connect strings might be (to connect to the provided PDB) :

<pre><code>sqlplus sys/oracle@127.0.0.1/orcl as sysdba
sqlplus sys@"127.0.0.1/orcl" as sysdba -- to avoid putting the pw in clear
</code></pre>

Note that under Unix, the quotes have to be escaped otherwise they will be consumed by the shell. Thus <code>"</code> becomes <code>\"</code>.

Then you create the role <code>MYROLE</code>and grant it other roles or privileges. I added nearly the bare minimum to do something interesting :

<pre><code>create role myrole not identified;
grant create session to myrole;
grant alter session to myrole;
grant create table to myrole;
</code></pre>

Next your create the user <code>MYUSER</code>. The string following <code>identified by</code> which is the password is case-sensitive. The rest is not. You could also use SQL delimited identifiers (surrounded by quotes <code>"</code>) instead of regular identifiers which are converted tu uppercase and subject to a few limitations. The quota could be <code>unlimited</code> instead of <code>20m</code>.

<pre><code>create user myuser identified by myuser default tablespace users profile default account unlock;
alter user myuser quota 20m on users;
grant myrole to myuser;
</code></pre>

Eventually, you connect as your new user.

Please note that you could also alter the default profile or provide another one to customize some settings as the expiration period of passwords, the number of permitted failed login attempts, etc.

blocks|key|640532|text|CREATE+USER+books_admin+IDENTIFIED+BY+MyPassword;
GRANT+CONNECT+TO+books_admin;
GRANT+CONNECT,+RESOURCE,+DBA+TO+books_admin;
GRANT+CREATE+SESSION+GRANT+ANY+PRIVILEGE+TO+books_admin;
GRANT+UNLIMITED+TABLESPACE+TO+books_admin;
GRANT+SELECT,+INSERT,+UPDATE,+DELETE+ON+schema.books+TO+books_admin;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|640533|https://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1006107+https://chartio.com/resources/tutorials/how-to-create-a-user-and-grant-permissions-in-oracle/|unstyled|offset|length|640534|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1006107|1|https://chartio.com/resources/tutorials/how-to-create-a-user-and-grant-permissions-in-oracle/^0|0|0|25|0|26|2L|1|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|T|8|@]|9|@$G|U|H|V|1|W]|$G|X|H|Y|1|Z]]|A|$]]|$1|I|3|-4|5|F|7|10|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]|Q|$5|L|M|N|A|$O|R]]]]

<pre><code>CREATE USER books_admin IDENTIFIED BY MyPassword;
GRANT CONNECT TO books_admin;
GRANT CONNECT, RESOURCE, DBA TO books_admin;
GRANT CREATE SESSION GRANT ANY PRIVILEGE TO books_admin;
GRANT UNLIMITED TABLESPACE TO books_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON schema.books TO books_admin;
</code></pre>

<a href="https://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1006107" rel="nofollow noreferrer">https://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1006107</a>
<a href="https://chartio.com/resources/tutorials/how-to-create-a-user-and-grant-permissions-in-oracle/" rel="nofollow noreferrer">https://chartio.com/resources/tutorials/how-to-create-a-user-and-grant-permissions-in-oracle/</a>

Can someone advise me on how to create a user in Oracle 11g and only grant that user the ability only to execute one particular stored procedure and the tables in that procedure.

I am not really sure how to do this!

How to create a user in Oracle 11g and grant permissions

是否有人可以建议我如何在Oracle 11g中创建用户，并仅授予该用户执行一个特定存储过程和该过程中的表的能力。我真的不知道该怎么做！

问如何在Oracle 11g中创建用户并授予权限
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle 11g中创建用户并授予权限EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle 11g中创建用户并授予权限
EN