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

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

提问by Andy5

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.

有人可以告诉我如何在 Oracle 11g 中创建用户并只授予该用户仅执行一个特定存储过程和该过程中的表的能力。

I am not really sure how to do this!

我真的不知道该怎么做！

回答by cagcowboy

Connect as SYSTEM.

以系统身份连接。

CREATE USER username IDENTIFIED BY apassword;

GRANT CONNECT TO username;

GRANT EXECUTE on schema.procedure TO username;

You may also need to:

您可能还需要：

GRANT SELECT [, INSERT] [, UPDATE] [, DELETE] on schema.table TO username;

to whichever tables the procedure uses.

到过程使用的任何表。

回答by Sanoop

Follow the below steps for creating a user in Oracle.
--Connect as System user

按照以下步骤在 Oracle 中创建用户。
--以系统用户身份连接

CONNECT <USER-NAME>/<PASSWORD>@<DATABASE NAME>;

--Create user query

--创建用户查询

CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;

--Provide roles

--提供角色

GRANT CONNECT,RESOURCE,DBA TO <USER NAME>;

--Provide privileges

--提供权限

GRANT CREATE SESSION GRANT ANY PRIVILEGE TO <USER NAME>;
GRANT UNLIMITED TABLESPACE TO <USER NAME>;

--Provide access to tables.

--提供对表的访问。

GRANT SELECT,UPDATE,INSERT ON <TABLE NAME> TO <USER NAME>;

回答by APC

The Oracle documentation is comprehensive, online and free. You should learn to use it. You can find the syntax for CREATE USER hereand for GRANT here,

Oracle 文档全面、在线且免费。你应该学会使用它。你可以在这里找到CREATE USER和GRANT的语法，

In order to connect to the database we need to grant a user the CREATE SESSION privilege.

为了连接到数据库，我们需要授予用户CREATE SESSION 权限。

To allow the new user rights on a stored procedure we need to grant the EXECUTE privilege. The grantor must be one of these:

要允许对存储过程的新用户权限，我们需要授予 EXECUTE 权限。设保人必须是其中之一：

• the procedure owner
• a user granted execute on that procedure with the WITH ADMIN option
• a user with the GRANT ANY OBJECT privilege
• a DBA or other super user account.

• 程序所有者
• 使用 WITH ADMIN 选项授予用户执行该过程的权限
• 具有 GRANT ANY OBJECT 权限的用户
• DBA 或其他超级用户帐户。

Note that we would not normally need to grant rights on objects used by a stored procedure in order to use the procedure. The default permission is that we execute the procedure with the same rights as the procedure owner and, as it were, inherit their rights when executing the procedure. This is covered by the AUTHID clause. The default is definer (i.e. procedure owner). Only if the AUTHID is set to CURRENT_USER (the invoker, that is our new user) do we need to grant rights on objects used by the procedure. Find out more.

请注意，我们通常不需要为存储过程使用的对象授予权限才能使用该过程。默认权限是我们以与过程所有者相同的权限执行过程，并且在执行过程时可以继承他们的权限。这包含在 AUTHID 子句中。默认为定义者（即过程所有者）。只有当 AUTHID 设置为 CURRENT_USER（调用者，即我们的新用户）时，我们才需要授予过程使用的对象的权限。 了解更多。

回答by erhun

Don't use these approach in critical environment like TEST and PROD. Below steps are just suggested for local environment. For my localhost i create the user via these steps:

不要在 TEST 和 PROD 等关键环境中使用这些方法。以下步骤仅针对本地环境建议。对于我的本地主机，我通过以下步骤创建用户：

IMPORTANT NOTE :Create your user with SYSTEM user credentials.Otherwise you may face problem when you run multiple application on same database.

重要说明：使用 SYSTEM 用户凭据创建您的用户。否则，当您在同一数据库上运行多个应用程序时，您可能会遇到问题。

 CONNECT SYSTEM/<<System_User_Password>>@<<DatabaseName>>; -- connect db with username and password, ignore if you already connected to database.

Then Run below script

然后运行下面的脚本

CREATE USER <<username>> IDENTIFIED BY <<password>>; -- create user with password
GRANT CONNECT,RESOURCE,DBA TO <<username>>; -- grant DBA,Connect and Resource permission to this user(not sure this is necessary if you give admin option)
GRANT CREATE SESSION TO <<username>> WITH ADMIN OPTION; --Give admin option to user
GRANT UNLIMITED TABLESPACE TO <<username>>; -- give unlimited tablespace grant

EDIT:If you face a problem about oracle ora-28001 the password has expired also this can be useful run

编辑：如果您遇到有关 oracle ora-28001 的问题，密码也已过期，这也很有用运行

select * from dba_profiles;-- check PASSWORD_LIFE_TIME 
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; -- SET IT TO UNLIMITED

回答by Ludovic Kuty

As previously mentioned multiple times in the comments, the use of the CONNECT, RESOURCEand DBAroles is discouraged by Oracle.

正如前面在评论中多次提到的CONNECT，Oracle 不鼓励使用,RESOURCE和DBA角色。

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 connect_identifieruses different syntaxes.

您必须以 SYS 身份连接以创建您的角色和被赋予此角色的用户。您可以根据需要使用 SQL Developer 或 SQL*Plus。不要忘记在登录字符串中提及 SYSDBA 角色。在connect_identifier使用不同的语法。

sqlplus sys/<<password>>@<<connect_identifier>> as sysdba

Let's say you have a 12cR1 like the one provided as a VM with the "Oracle Technology Network Developer Day". The connect strings might be (to connect to the provided PDB) :

假设您有一个 12cR1，就像在“ Oracle 技术网开发人员日”中作为 VM 提供的那样。连接字符串可能是（连接到提供的 PDB）：

sqlplus sys/[email protected]/orcl as sysdba
sqlplus sys@"127.0.0.1/orcl" as sysdba -- to avoid putting the pw in clear

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

请注意，在 Unix 下，引号必须被转义，否则它们将被 shell 消耗掉。从而"变成\"。

Then you create the role MYROLEand grant it other roles or privileges. I added nearly the bare minimum to do something interesting :

然后创建角色MYROLE并授予它其他角色或权限。我添加了几乎最低限度的东西来做一些有趣的事情：

create role myrole not identified;
grant create session to myrole;
grant alter session to myrole;
grant create table to myrole;

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

接下来创建用户MYUSER。identified by密码后面的字符串区分大小写。其余的不是。您还可以使用 SQL 分隔标识符（由引号包围"）而不是常规标识符，这些标识符被转换为大写并受到一些限制。配额可以unlimited代替20m.

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

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.

请注意，您还可以更改默认配置文件或提供另一个配置文件来自定义一些设置，例如密码的有效期、允许的失败登录尝试次数等。

回答by SREE

CREATE USER USER_NAME IDENTIFIED BY PASSWORD;
GRANT CONNECT, RESOURCE TO USER_NAME;

回答by Md Nazrul Islam

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;

https://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1006107https://chartio.com/resources/tutorials/how-to-create-a-user-and-grant-permissions-in-oracle/

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 -oracle中的权限/

回答by Manideep

First step:

第一步：

Connect to a database using System/Password;

Connect to a database using System/Password;

second Step:

第二步：

create user username identified by password; (syntax)

创建由密码标识的用户用户名；（句法）

Ex: create user manidb idntified by mypass;

Ex: create user manidb idntified by mypass;

third Step:

第三步：

grant connect,resource to username; (Syntax)

授予连接，资源给用户名；（句法）

Ex: grant connect,resource to manidb;

Ex: grant connect,resource to manidb;

回答by raju deshmukh

• step 1 .

  create user raju identified by deshmukh;

• step 2.

  grant connect , resource to raju;

• step 3.

  grant unlimitted tablespace to raju;

• step4.

  grant select , update , insert , alter to raju;

• 第1步 。

  create user raju identified by deshmukh;

• 第2步。

  grant connect , resource to raju;

• 第 3 步。

  grant unlimitted tablespace to raju;

• 第四步。

  grant select , update , insert , alter to raju;