ITPub博客

首页 > 数据库 > Oracle > 用户和权限

用户和权限

原创 Oracle 作者:y81277241 时间:2014-03-07 12:01:52 0 删除 编辑

创建用户
 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来实现模式转换的.
 

 

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

上一篇: index损坏恢复
下一篇: Opatch的使用
请登录后发表评论 登录
全部评论

注册时间:2014-03-06

  • 博文量
    22
  • 访问量
    57394