用户和权限

创建用户

CREATE USER user_name IDENTIFIED BY password;

DROP USER user_name [cascade];

ALTER USER user_name ACCOUNT [UN]LOCK;

什么是权限

权限就是用来控制特定的用户发送特定的SQL语句是否允许正确的执行

在多用户环境,DBA需要维护数据库访问的安全

以确保特定用户拥有特定的操作权限

数据库的权限分为

系统权限

对象权限

系统权限

大部分是针对对象的创建,删除,修改,审计等等的操作

查看系统都具备哪些权限

select PRIVILEGE from dba_sys_privs;

查看当前会话都具备哪些权限

select * from session_privs;

对象权限

授予特定用户对特定对象的权限

特定对象:表、视图、序列、过程、函数、程序包上。

角色

系统中有166个权限 我们不便于管理

于是oracle对很多需求的权限定义成了角色的模式 来方便我们管理

角色就是一组权限的集合

我们也可以自己建立包含自己拟定权限的角色

查看oracle给你定制了哪些角色

select distinct GRANTEd_role  from dba_role_privs ;

常见的角色中包含的权限

dba connect resource

查看角色中都包含了哪些权限

session A:> select GRANTEE,PRIVILEGE  from dba_sys_privs where GRANTEE='RESOURCE';

GRANTEE          PRIVILEGE

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

RESOURCE        CREATE TRIGGER

RESOURCE        CREATE SEQUENCE

RESOURCE        CREATE TYPE

RESOURCE        CREATE PROCEDURE

RESOURCE        CREATE CLUSTER

RESOURCE        CREATE OPERATOR

RESOURCE        CREATE INDEXTYPE

RESOURCE        CREATE TABLE

8 rows selected.

session A:> select GRANTEE,PRIVILEGE  from dba_sys_privs where GRANTEE='CONNECT';

GRANTEE          PRIVILEGE

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

CONNECT          CREATE SESSION

session A:>  

CREATE any table和create table他们是有区别的

可以在任意用户模式下建表

只可以在自己的模式下建表

用户被创建后,就需要授予他系统权限,刚创建的用户连登陆权限都没有(create session)

对于应用开发用户需要的基本权限:

CREATE SESSION 

CREATE TABLE 

CREATE SEQUENCE 

CREATE VIEW 

CREATE PROCEDURE 

授权通过grant

语法:

GRANT object_priv[(columns)]

[ON object]

TO {user|role|public}

[WITH GRANT OPTION]

回收通过revoke

语法:

REVOKE {privilege[,privilege...]|ALL}

[ON object]

FROM {user[,user...]|role|PUBLIC}

[CASCADE CONSTRAINTS]

SYS@ora10g> create user u1 identified by p1;

User created.

SYS@ora10g> grant connect,resource to u1;

Grant succeeded.

SYS@ora10g> desc dba_role_privs;

 Name      Null?    Type

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

 GRANTEE       VARCHAR2(30)

 GRANTED_ROLE      NOT NULL VARCHAR2(30)

 ADMIN_OPTION       VARCHAR2(3)

 DEFAULT_ROLE       VARCHAR2(3)

SYS@ora10g> select * from dba_role_privs where GRANTEE='U1';

GRANTEE          GRANTED_ROLE       ADM DEF

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

U1        RESOURCE         NO  YES

U1        CONNECT       NO  YES

SYS@ora10g> revoke RESOURCE,CONNECT from u1;

Revoke succeeded.

SYS@ora10g> select * from dba_role_privs where GRANTEE='U1';

no rows selected

session A:> alter user u1 account lock;

User altered.

session A:> alter user u1 account unlock;

User altered.

session A:> 

SYS@ora10g> drop user u1;

User dropped.

SYS@ora10g> 

如果用户模式下有对象是不能删除用户的 可以加cascade连同对象一起删

session A:> create table u1.t as select * from scott.emp;

Table created.

session A:> drop user u1;

drop user u1

*

ERROR at line 1:

ORA-01922: CASCADE must be specified to drop 'U1'

session A:> drop user u1 cascade;

User dropped.

session A:>

角色操作

session B:> drop role r1;

Role dropped.

session B:> create role r1;

Role created.

session B:> grant connect,create table to r1;

Grant succeeded.

session B:> create role r2;

Role created.

session B:> grant create view to r2;

Grant succeeded.

session B:> grant r2 to r1;

Grant succeeded.

session B:> grant r1 to scott;

Grant succeeded.

session B:>

授权的级联

grant选项

with admin option 权限回收无级联 适用系统权限和角色

with grant option 权限回收有级联 适用对象权限

session B:> create user u1 identified by u1;

User created.

session B:> create user u2 identified by u2;

User created.

session B:> grant connect to u1 with grant option;

grant connect to u1 with grant option

                         *

ERROR at line 1:

ORA-01939: only the ADMIN OPTION can be specified

session B:> grant connect to u1 with admin option;

Grant succeeded.

session B:> conn u1/u1

Connected.

session B:> grant connect to u2 ;

Grant succeeded.

session B:> conn / as sysdba

Connected.

session B:> select * from dba_role_privs where GRANTEE in ('U1','U2');

GRANTEE          GRANTED_ROLE       ADM DEF

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

U2        CONNECT       NO  YES

U1        CONNECT       YES YES

session B:> revoke connect from u1;

Revoke succeeded.

session B:> select * from dba_role_privs where GRANTEE in ('U1','U2');

GRANTEE          GRANTED_ROLE       ADM DEF

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

U2        CONNECT       NO  YES

session B:> 

恢复U1的connect权限 再测试对象权限

session B:> select * from dba_role_privs where GRANTEE in ('U1','U2');

GRANTEE          GRANTED_ROLE       ADM DEF

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

U2        CONNECT       NO  YES

U1        CONNECT       NO  YES

session B:> 

session B:> grant select on scott.emp to u1 with admin option;

grant select on scott.emp to u1 with admin option

                                     *

ERROR at line 1:

ORA-00993: missing GRANT keyword

session B:> grant select on scott.emp to u1 with grant option;

Grant succeeded.

session B:> conn u1/u1

Connected.

session B:> grant select on scott.emp to u2;

Grant succeeded.

session B:> conn / as sysdba

Connected.

session B:> COL GRANTEE FOR A20

session B:> COL OWNER FOR A20

session B:> COL TABLE_NAME FOR A20

session B:> COL GRANTOR FOR A20

session B:> COL PRIVILEGE FOR A20

session B:>

session B:> select * from dba_TAB_privs where GRANTEE in ('U1','U2');

GRANTEE          OWNER   TABLE_NAME GRANTOR PRIVILEGE      GRA HIE

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

U2        SCOTT   EMP      U1   SELECT      NO  NO

U1        SCOTT   EMP      SCOTT SELECT      YES NO

session B:> revoke select on scott.emp from u1;

Revoke succeeded.

session B:> select * from dba_TAB_privs where GRANTEE in ('U1','U2');

no rows selected

session B:> 

U2的也被回收了  回收级联

访问不同模式中的表

   模式schema

模式是一个逻辑的概念 是一组对象的集合

一个用户创建了,那么该用户的schema名等于用户名,并作为该用户缺省schema。

这也就是我们看到schema名都为数据库用户名的原因

模式是可以切换的 这样可以实现以A用户的身份去访问B用户的模式

SQL> conn / as sysdba

SQL> drop user u2 cascade;

User dropped.

SQL> drop user u1 cascade;

User dropped.

SQL> create user u1 identified by u1;

User created.

SQL> grant connect,resource,select any table to u1;

Grant succeeded.

SQL> create user u2 identified by u2;

User created.

SQL> grant connect,resource,select any table to u2;

Grant succeeded.

SQL> create table u1.tt(ename varchar2(10));

Table created.

SQL> insert into u1.tt values('U1');

1 row created.

SQL> create table u2.tt(ename varchar2(10));

Table created.

SQL> insert into u2.tt values('U2');

1 row created.

SQL> conn u1/u1

Connected.

SQL> select * from tt;

ENAME

----------

U1

SQL> alter session set current_schema=u2;

Session altered.

SQL> select * from tt;

ENAME

----------

U2

SQL> show user

USER is "U1"

SQL> 

了解这个概念即可 因为我们基本不会改用户的模式,而是通过在对象前使用schema前缀 例如scott.emp来实现模式转换的.