Oracle 11g语句创建新用户并授予特权? [英] Oracle 11g statements to create new user and grant privileges?

问题描述

我想在oracle 11g中创建一个用户/架构，并将所有特权授予该用户.我如何用一个简单的脚本来做到这一点.我查看了以下链接，但不确定使用哪个链接或这些语句是否是最佳方法.

I want to create a user/schema in oracle 11g and grant all privileges to the user. How can I do this with a simple script. I looked at the following links but I am not sure which one to use or if these statements are the best way.

http://ss64.com/ora/grant.html

您能建议我如何以最简单的方式安全地执行此操作吗?

Can you suggest how I may do this in the simplest possible way and securely ?

推荐答案

要创建新用户，请使用"创建用户"命令.因此，典型的create user命令为:

To create a new user you use the "create user" command. So a typical create user command would be :

create user test identified by test default tablespace mytbsp.

当然，您需要用不同的值替换用户，密码和表空间的值.但是，我建议您查看Oracle文档 http: //docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm .

Of course you need to replace the values for the user, password and tablespace with different values. However I'd recommend that you have a look at Oracle's documentation http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm.

下一步是授予用户相应的权限.向用户授予所有权限是一种非常糟糕的方法，因为您还将为他赋予dba特权.相反，您要给他连接权限和默认表空间的权限.另外，最好使用角色而不是直接授予权限.因此，如果您必须再次授予权限，则只需授予角色.第一步是创建角色:

The next step is to grant the user the corresponding rights. To give a user all the rights is a very bad approach as you would also give him dba privileges. What you instead is to give him connect privileges and the permissions to his default tablespace. Also it is better to use roles instead of granting the rights directly. So if you have to grant the rights again you only need to grant the role. First step is to create the role:

GRANT CREATE session, CREATE table, CREATE view, 
      CREATE procedure,CREATE synonym,
      ALTER table, ALTER view, ALTER procedure,ALTER synonym,
      DROP table, DROP view, DROP procedure,DROP synonym
      TO MyRole;

此语句不完整，您可能需要其他权限(例如，索引维护)，但请查看联机oracle文档.

This statement is not complete you might require additional rights (index maintenance for instance), but have a look at the online oracle documentation.

之后，您将角色授予新创建的用户.

After that you grant the role to the newly created user.

GRANT myrole to test;

这篇关于Oracle 11g语句创建新用户并授予特权?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！