用户管理和权限控制

oracle203

---

---

用户管理和权限控制

create USER avyrros//用户名
    IDENTIFIED EXTERNALLY//密码
    DEFAULT TABLESPACE data_ts//默认的表空间
    TEMPORARY TABLESPACE temp_ts//临时表空间
    QUOTA 100M ON data_ts
    QUOTA 0 ON test_ts
    PROFILE clerk;//概要文件默认是使用default文件

profile（概要文件）管理用户1

当创建用户时候，如果没有指定profile，那么Oracle把名字叫default的profile赋予给用户。

指定test1只能最多输入3次密码，

创建profile文件：create profile pro1 limitfailed_login_attempts3password_lock_time2;

其中，FAILED_LOGIN_ATTEMPTS:用于指定联系登陆的最大失败次数. PASSWORD_LOCK_TIME:用于指定帐户被锁定的天数.

创建属于pro1的用户：

create user aa identified by aa profile pro1;

修改用户的profile文件：

alter user test1 profile pro1;

解除锁定：alter user test1 account unlock;

终止口令：需要每隔10天修改密码，最多宽限2天：ALTER PROFILE "PRO1" LIMIT PASSWORD_LIFE_TIME 10 PASSWORD_GRACE_TIME 2。

删除profile：drop profile pro1 cascade;使得test1的profile重新变成了default

上面的对profile的文件的创建修改都可以通过EM的控制台来查看信息：

JavaScript is not supported by your browser. Oracle requires JavaScript support in order to completely render this page.

	Password

Expire in (days)	10
Lock (days past expiration)	2

	Failed Login

Number of failed login attempts to lock after	3
Number of days to lock for	2

SQL> create profile test limit failed_login_attempts 3 password_lock_time 2;

Profile created

SQL> alter user test1 profile test;

User altered

SQL> alter profile test limit password_life_time 10 password_grace_time 2;

Profile altered

SQL>

下面的表示测试创建的新用户到该用户创建表成功所需要的权限测试：按照序号读

SQL> select username,user_id,password,account_status from dba_users where username='TEST1';//1 USERNAME USER_ID PASSWORD ------------------------------ ---------- ------------------------------ ACCOUNT_STATUS -------------------------------- TEST1 91 OPEN SQL> SET LINESIZE 10000; SQL> grant create session to test1;//3 Grant succeeded. SQL> grant create table to test1;//6 Grant succeeded. //上面的都是系统权限的授予 SQL> grant insert to test1; grant insert to test1 * ERROR at line 1: ORA-01919: role 'INSERT' does not exist SQL> grant add to test1; grant add to test1 * ERROR at line 1: ORA-01919: role 'ADD' does not exist SQL> grant insert table to test1; grant insert table to test1 * ERROR at line 1: ORA-00990: missing or invalid privilege SQL> grant add table to test1; grant add table to test1 * ERROR at line 1: ORA-00990: missing or invalid privilege SQL> grant select to test1; grant select to test1 * ERROR at line 1: ORA-01919: role 'SELECT' does not exist SQL> grant select table to test1; grant select table to test1 * ERROR at line 1: ORA-00990: missing or invalid privilege //之所以上面的授权都失败了，是因为上面红色标注的都是对象权限，对象权限的授予是需要针对 对象的权限来进行授予某个用户的的。下面可行 SQL> grant selecton test1.test1to test1;//8 Grant succeeded. SQL> grant insert on test1.test1 to test1;//11 Grant succeeded. SQL> grant unlimited tablespace to test1;//12 Grant succeeded. SQL>

[oracle@localhost ~]$ sqlplus test1/test1//2 SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 10 19:46:50 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. ERROR: ORA-01045: user TEST1 lacks CREATE SESSION privilege; logon denied Enter user-name: / [oracle@localhost ~]$ sqlplus test1/test1//4 SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 10 19:47:32 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table test1(id number(5,3),name varchar(20)); create table test1(id number(5,3),name varchar(20))//5 * ERROR at line 1: ORA-01031: insufficient privileges SQL> create table test1(id number(5,3),name varchar(20));//7 Table created. SQL> insert into test1 values(1,'zhangsan'); insert into test1 values(1,'zhangsan') * ERROR at line 1: ORA-01950: no privileges on tablespace 'USERS' SQL> select * from test1;//9 no rows selected SQL> insert into test1 values(1,'zhangsan');//10 insert into test1 values(1,'zhangsan') * ERROR at line 1: ORA-01950: no privileges on tablespace 'USERS' SQL> insert into test1 values(1,'zhangsan');//13 1 row created. SQL>

下面测试的是系统/对象权限是否级联回收：

对象权限：访问某个用户的特定对象(如表、视图等) 的对象权限(select onscott.student) GRANT object_privilege[columns…] ONobjectTOuser [, user|role, PUBLIC…] [WITH GRANT OPTION]; 收回对象权限REVOKE： REVOKE privilegeON object FROM { user|role|PUBLIC}; 例： revoke select onscott.studentfrom test2;

授予用户权限，可以是系统权限或者对象权限。 GRANT privilege [,privilege…] TO user [,user|role, PUBLIC…] [WITH ADMIN OPTION]; 说明：包含了WITH ADMIN OPTION，只能是系统权限。 收回系统权限REVOKE 例：REVOKE create session FROM test1;

SQL> grant create session to test1 with admin option;//1 Grant succeeded. SQL> create user test2 identified by test2 2 default tablespace users 3 profile test; User created. SQL> revoke create session from test1;//4 Revoke succeeded. SQL> grant select on test1.test1 to test1 with grant option;//9 Grant succeeded. SQL> grant create session to test1;//7 Grant succeeded. SQL> revoke select on test1.test1 from test1; Revoke succeeded.//12 SQL>

SQL>SHOW USER TEST1 SQL> grant create session to test2;//2 Grant succeeded. SQL> conn test2/test2;//3 Connected. SQL> conn test2/test2; Connected. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@localhost ~]$ sqlplus test2/test2//5 SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 10 21:14:17 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn test1/test1//6 ERROR: ORA-01045: user TEST1 lacks CREATE SESSION privilege; logon denied //会发现系统权限不会存在级联收回的情况，假如A赋予B的权限后回收A的权限不会把B权限一并回收 Warning: You are no longer connected to ORACLE. SQL> conn test1/test1//8 Connected. SQL> conn test2/test2 Connected. SQL> select * from test1.test1;//9 select * from test1.test1 * ERROR at line 1: ORA-00942: table or view does not exist SQL> conn test1/test1 Connected. SQL> grant select on test1.test1 to test2 ;//10 Grant succeeded. SQL> conn test2/test2; Connected. SQL> select * from test1.test1;//11 ID NAME ---------- -------------------- 1 zhangsan SQL> select * from test1.test1;//12 select * from test1.test1 * ERROR at line 1: ORA-00942: table or view does not exist //通过上面对表的查询你会发现对象权限是存在级联回收的，假如A的查询的对象权限赋予了B 回收A的查询的权限则B权限一并回收。 SQL>

提示：SQL> select * from dba_sys_privs dsp where dsp.grantee='TEST1';

//查询某个用户的系统权限

GRANTEE PRIVILEGE ADMIN_OPTION

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

TEST1 UNLIMITED TABLESPACE NO

TEST1 CREATE TABLE NO

TEST1 CREATE SESSION NO

SQL> select * from dba_tab_privs dtp where dtp.grantee='TEST1';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY

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

TEST1 TEST1 TEST1 TEST1 INSERT NO NO

SQL>

下面的是角色管理：

角色管理:角色是一组相关权限的组合，可以将权限授予角

色，再把角色授予用户，以简化权限管理。

(1)

创建角色CREATE ROLE,应该具有CREATE ROLE系统权限。

CREATE ROLE

role_name;

(2)

授予角色权限，可以是系统权限或者对象权限。

GRANT privilege TO ROLE;

例：

grant create session ,create any table, drop any table to role1;

(3)

将角色授予用户

GRANT role TO user;

例：

grant role1 to test2;

(4)

从用户收回角色

REVOKE role FROM user;

CREATE ROLE role
   [ NOT IDENTIFIED
   | IDENTIFIED { BY password
                | USING [ schema. ] package
                | EXTERNALLY
                | GLOBALLY
                }
   ] ;

SQL> create role test1;

create role test1

ORA-01921: 角色名 'TEST1' 与另一个用户名或角色名发生冲突//角色和用户名不能重读

SQL> create role role1;

Role createdgrant

SQL>

SQL> grant create session to role1;//给某个角色赋予权限

Grant succeeded

SQL> select * from role_role_privs rrp where rrp.role='ORLE1';//角色与角色表，就是角色赋予角色的表

ROLE GRANTED_ROLE ADMIN_OPTION

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

SQL> select * from role_sys_privs rsp where rsp.role='ORLE1';//角色与系统权限表

ROLE PRIVILEGE ADMIN_OPTION

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

SQL> select * from role_sys_privs rsp where rsp.role='ROLE1';//被赋予了一个创建会话的系统权限

ROLE PRIVILEGE ADMIN_OPTION

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

ROLE1 CREATE SESSION NO

SQL> grant select on test1.test1 to role1;//赋予角色对象权限

Grant succeeded

SQL> select * from role_tab_privs rtp where rtp.role='ROLE1';

ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRANTABLE

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

ROLE1 TEST1 TEST1 SELECT NO

//角色-对象权限表上面的属性：ROLE1这个角色拥有对象TEST1的表TEST1的查询的 对象权限。

SQL> grant role1 to test1;

Grant succeeded

SQL> grant dba to role1;

Grant succeeded

SQL> select * from role_role_privs rrp where rrp.role='ROLE1';//角色赋予角色的表；某个角色被赋予某个角色

ROLE GRANTED_ROLE ADMIN_OPTION

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

ROLE1 DBA NO

提示：SQL> select * from dba_roles dr where dr.role='ROLE1';//查询角色的信息

ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE

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

ROLE1 NO NONE

SQL> select * from dba_role_privs drp where drp.grantee='TEST1';//查询角色与用户的信息

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

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

TEST1 ROLE1 NO YES

SQL>

SQL> select * from dba_role_privs drp inner join role_tab_privs rtp on drp.granted_role=rtp.role where drp.grantee='TEST1';

//通过上面的查询就可以得到某个用户所拥有的对象权限信息了

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRANTABLE

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

TEST1 ROLE1 NO YES ROLE1 TEST1 TEST1 SELECT NO

SQL>

注意：授予权限可以精确到列上

SQL> grantupdate(id)on test1.test1 to test2;

Grant succeeded.

SQL> grant select on test1.test1 to test2;

Grant succeeded.

SQL> conn test2/test2;
Connected.
SQL> select * from test1.test1;

ID NAME
---------- --------------------
2 lisi

SQL> show user;
USER is "TEST2"
SQL> update test1.test1 set id=3 ,name = 'wangwu' where id = 2;
update test1.test1 set id=3 ,name = 'wangwu' where id = 2
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>update test1.test1 set id = 3 where id = 2;

1 row updated.

SQL>