17.08.09

用户管理

AAA:

Authentication: 身份验证

Authorization: 权限管理

Audition: 审计

authentication

user 用户   schema 模式

 

预定义的系统用户:

SQL> select USERNAME, ACCOUNT_STATUS from dba_users;

open状态的用户:

SQL> select USERNAME, ACCOUNT_STATUS from dba_users ACCOUNT_STATUS='OPEN';

系统管理账号:

SYS           SYSTEM            DBSNMP          SYSMAN

3种身份验证方式:

password验证:

浏览器中创建用户user01

或者用命令创建:

SQL> create user user01 identified by password;

SQL> grant create session to user01;

 

测试:

$ sqlplus user01/password

 

external(os)验证:

操作系统中创建用户:

$ su -

Password:

[root@node1 ~]# useradd osuser

[root@node1 ~]# passwd osuser

$ sqlplus / as sysdba

外部用户使用固定的前缀:

SQL> show parameter os_auth

SQL> create user ops$osuser identified externally;

SQL> grant create session to ops$osuser;

不要su - osuser,环境变量保留:

$ su osuser

Password:

[osuser@node1 admin]$ sqlplus /

SQL> show user

USER is "OPS$OSUSER"

 

管理员的身份验证:

本地连接:

本地连接,预先设置ORACLE_SID,操作系统用户是dba群组的成员

 

$ unset ORACLE_SID

$ echo $ORACLE_SID

 

$ export ORACLE_SID=orcl

$ echo $ORACLE_SID

orcl

 

$ id

uid=1001(oracle) gid=1000(oinstall) groups=1000(oinstall),1031(dba),1032(oper)

$ sqlplus / as sysdba

SQL> show user

USER is "SYS"

$ su -

# usermod -G oper oracle 或

# gpasswd -d oracle dba

# exit

$ sqlplus / as sysdba

报错,权限不够

 

只要是dba群组中的成员,就可以不需要知道sys的口令,直接以sqlplus / as sysdba登录

并且身份为sys。

 

恢复:

# gpasswd -a oracle dba

 

远程客户端连接:

$ sqlplus sys/password@orcl as sysdba

$ ls $ORACLE_HOME/dbs/orapworcl

$ orapwd

authorization

系统权限:

sys执行授权:

预先创建测试表

SQL> create table t1(x int);

SQL> create user user01 identified by password;

SQL> grant create session to user01;

SQL> grant select any table to user01;

user01测试:

$ sqlplus user01/password

SQL> select count(*) from hr.employees(hr.departments scott.emp);

SQL> delete from scott.emp;          失败!

SQL> select * from sys.t1;                失败!

select any table    n-1模式  普通用户授权以后仍没有访问sys表的权限

sys再次授权:

SQL> grant select any dictionary to user01;

user01测试:

SQL> select * from sys.t1;    成功

select any table(n-1)+select any dictionary(1)

sys授权:

SQL> grant create table to user01;

user01测试:

SQL> create table t1(x int);

sys授权:

SQL> grant unlimited tablespace to user01;

user01测试:

SQL> insert into t1 values (1);

 

对象权限:

表的参照权限:

dept

deptno(pk)       dname

10                      sales

20                      market

 

my_emp

empno              deptno(fk)

100                    10

sys授权:

SQL> grant select on hr.employees to user01;

user01测试:

SQL> select count(*) from hr.employees;

SQL> delete from hr.employees;    失败

SQL> select count(*) from hr.departments;            失败

sys授权:

SQL> grant index on hr.employees to user01;

SQL> grant unlimited tablespace to user01;

user01测试:

SQL> create index emp_sal_idx on hr.employees(salary);

SQL> select index_name from user_indexes where table_name='EMPLOYEES';

 

create any table               create table

alter any table                  alter table

drop any table                  drop table

 

权限的级联删除:

系统权限:

sys准备工作:

SQL> drop user user01 cascade;

SQL> drop user user02 cascade;

SQL> create user user01 identified by password;

SQL> create user user02 identified by password;

SQL> grant create session to user01;

SQL> grant create session to user02;

sys授权:

SQL> grant select any table to user01 with admin option;

user01测试成功并授权给user02:

SQL> select count(*) from hr.employees;

SQL> grant select any table to user02 with admin option;

user02测试成功:

SQL> select count(*) from hr.employees;

sys收回权限:

SQL> revoke select any table from user01;

user01操作失败:

SQL> select count(*) from hr.employees;

user02测试成功:

SQL> select count(*) from hr.employees;

对象权限:

SQL> grant select on hr.employees to user01 with grant option;

 

 

dba+sysdba=sys

role

角色就是数据库中的群组!

角色的作用:简化权限的管理,动态更新用户的权限。

 

 

预定义的角色:

SQL> select role from dba_roles;

创建角色:

SQL> create role hr_mgr;

SQL> create role hr_clerk;

SQL> grant select any table to hr_mgr;

SQL> grant select on hr.employees to hr_clerk;

SQL> grant hr_mgr to user01;

SQL> grant hr_clerk to user02;

user01/user02测试:

角色生效必须重新登录

profile

profile主要控制两个方面:

1 用户的资源消耗

2 用户的口令安全

SQL> select * from dba_profiles where profile='DEFAULT';

SQL> select username, profile from dba_users;

SQL> show parameter resource_limit    资源管理的开关参数

查看复杂性函数的脚本:

$ cd $ORACLE_HOME/rdbms/admin

$ vi utlpwdmg.sql

$ cp utlpwdmg.sql /home/oracle/utlpwdmg.sql

$ vi /home/oracle/utlpwdmg.sql      只保留校验函数部分

$ sqlplus / as sysdba

SQL> @/home/oracle/utlpwdmg.sql

sys创建概要文件:

SQL> CREATE PROFILE HR_PROFILE LIMIT

PASSWORD_LIFE_TIME 30

PASSWORD_GRACE_TIME 7

PASSWORD_REUSE_MAX 3

PASSWORD_REUSE_TIME unlimited

PASSWORD_LOCK_TIME 5/1440

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_VERIFY_FUNCTION verify_function_11G;

和用户关联:

SQL> ALTER USER HR PROFILE HR_PROFILE;

测试:

$ sqlplus hr/hr

SQL> alter user hr identified by password123 replace hr;

posted on 2017-08-19 09:04  zhang_x  阅读(97)  评论(0编辑  收藏  举报

导航