创建用户并赋予权限

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE 1. 创建用户

          Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONESQL> create user jay identified by jay

         2  default tablespace users

         3  temporary tablespace tempts1;

User created.

2.  赋予权限

         Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE新建的用户是没有权限的，若用该用户登陆数据库，会出现以下错误，

         SQL> conn jay/ jay

ERROR:

ORA-01045: user TESTUSER lacks CREATE SESSION privilege; logon denied

         赋予其create session权限，即可进入数据库

         SQL> conn / as sysdba

Connected.

SQL> grant create session to testuser;

Grant succeeded.

         SQL> conn jay / jay

         Connected

         SQL> show user

USER is "JAY"

         如果此时创建表，会收到权限不足的提示：

         SQL> create table jay(name varchar2(20));

create table jay(name varchar2(20))

*

ERROR at line 1:

ORA-01031: insufficient privileges

         把create table权限赋予：

         SQL> conn / as sysdba

Connected.

SQL> grant create table to jay;

Grant succeeded.

SQL> conn jay / jay

Connected.

SQL> create table jay(name varchar2(20));

Table created.

         通过DBA_SYS_PRIVS查询用户权限

         SQL> conn / as sysdba

Connected.

         SQL>  select * from dba_sys_privs where grantee='JAY';

GRANTEE                        PRIVILEGE                            ADM

------------------------------ ---------------------------------------- ---

JAY                           CREATE TABLE                           NO

JAY                           CREATE SESSION                        NO

参考资料：《循序渐进Oracle》第四章

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24068527/viewspace-666487/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24068527/viewspace-666487/