SQL ORA-01031 创建视图时权限不足？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16125429/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01031 Insufficient privileges while CREATING a VIEW?
提问by Tan
When I try to create a view that including different tables I'm getting the following error: Error at Line 1: ORA-01031 Insufficient privileges.
当我尝试创建包含不同表的视图时，出现以下错误：第 1 行错误：ORA-01031 权限不足。
Could anyone tell me what could be the problem. I tried following the another stackoverflow post mentioned here but it's pertaining to different schemas.
谁能告诉我可能是什么问题。我尝试遵循此处提到的另一篇 stackoverflow 帖子，但它与不同的模式有关。
ORA-01031: insufficient privileges when selecting view
Please let me know as I'm new here.
请让我知道，因为我是新来的。
My Query is as follows:
我的查询如下：
ORiginal Question:Create a view to select employee ID, employee name, hire date, and department number.
原始问题：创建一个视图来选择员工 ID、员工姓名、雇用日期和部门编号。
MY SOLUTION:
我的解决方案：
CREATE VIEW SIMPVIEW AS
SELECT EMPNO, ENAME, HIREDATE,DEPTNO
FROM EMP;
采纳答案by Arul Christo
Then probably you may not have the privileges to perform the CREATE VIEWcommand in your database schema... Log in into SYSDBA account and issue the command
那么可能你可能没有权限CREATE VIEW在你的数据库模式中执行命令......登录到 SYSDBA 帐户并发出命令
GRANT CREATE VIEW TO <dbusername>;
Here <dbusername>should be replaced with the name of the user you want to give access to the CREATE VIEWcommand.
此处<dbusername>应替换为您要授予该CREATE VIEW命令访问权限的用户名。
回答by listik
You can check if your user has VIEWcreation privileges using select * from session_privs.
您可以VIEW使用select * from session_privs.
Note that to be able to create a view, the user that is creating it needs to have been granted SELECTprivileges on all the objects being used, as well as the mentioned CREATE VIEWprivilege. You can also check that by querying to USER_TAB_PRIVSwith the user getting the error.
请注意，为了能够创建视图，创建它的用户需要被授予SELECT对所有正在使用的对象的权限，以及提到的CREATE VIEW权限。您还可以通过查询USER_TAB_PRIVS获取错误的用户来检查。
回答by Dhruv Das
when I wanted to execute the above query in sql developer I faced issues as I did not have enough privileges to create a view or other oracle object schema such as trigger, packages, procedures etc. I found the error to i.e. “Error at Line 1: ORA-01031 Insufficient privileges”. so, I needed the all privileges to practice all these queries and programs. I took the following steps in order to solve my problem:
当我想在 sql developer 中执行上述查询时，我遇到了问题，因为我没有足够的权限来创建视图或其他 oracle 对象模式，例如触发器、包、过程等。我发现错误为“第 1 行错误” : ORA-01031 权限不足”。所以，我需要所有权限来练习所有这些查询和程序。我采取了以下步骤来解决我的问题：
- As I logged in as a user name ‘scott', so my name is ‘scott' not ‘Dhruv'. My ambition was to grant all the privileges to me i.e. to the user ‘scott'.
- For that, I need to enter in the database as a DBA. Now, question is! How to log in as DBA. For this, I opened command prompt and I logged in the database as sysdba by following the below steps:
- 由于我以用户名“scott”登录，所以我的名字是“scott”而不是“Dhruv”。我的目标是将所有权限授予我，即用户“scott”。
- 为此，我需要以 DBA 身份进入数据库。现在，问题是！如何以 DBA 身份登录。为此，我打开命令提示符并按照以下步骤以 sysdba 身份登录数据库：
a) In window run, I typed cmd to open command prompt. I typed: sqlplus /nolog which means that I logged in without providing required credentials.
b) I authenticated myself for my underlying O/S and entered in database as DBA. For that, I typed in command prompt: connect / as sysdba;
c) I evaluated who is the DBA user in my database if exists. For that I typed: select name from V$database;
d) Here we go after this command. I finally granted myself (scott) to create view in sql developer by typing the command: grant create view to scott;
e) Finally, I granted myself all the privileges by typing: grant all privileges to scott;
a) 在窗口运行中，我输入 cmd 打开命令提示符。我输入： sqlplus /nolog 这意味着我在没有提供所需凭据的情况下登录。
b) 我为我的底层操作系统验证了自己的身份，并以 DBA 的身份进入了数据库。为此，我在命令提示符中输入：connect / as sysdba; c) 如果存在，我评估了谁是我的数据库中的 DBA 用户。为此，我输入： select name from V$database; d) 在这里，我们执行此命令。我终于允许自己（scott）通过键入以下命令在 sql developer 中创建视图：grant create view to scott；e) 最后，我通过键入以下内容授予自己所有权限：grant all privileges to scott；
Snapshot of command prompt: I have attached.
命令提示符的快照：我已附加。
Finally, I executed and created my view: I have attached
最后，我执行并创建了我的视图：我已附加
回答by Kapila
You have to give select any table privilege to the user. Then the view will compile successfully. No need to explicitly grant select to the user to all the objects.
您必须向用户授予 select any table 权限。然后视图将成功编译。无需向用户显式授予对所有对象的选择权。
