Controlling User Access 控制用户访问

you should be able to do the following: 1、Differentiate system privileges from object privileges 2、Grant privileges on tables 3、Grant roles 4、Distinguish between privileges and roles

Database security: 1、System security 2、Data security System privileges: Performing a particular action within the database Object privileges: Manipulating the content of the database objects Schemas: Collection of objects such as tables, views, and sequences

CREATE USER user
IDENTIFIED BY password;

CREATE USER demo IDENTIFIED BY demo;

GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...];

An application developer, for example, may have the following system privileges: CREATE SESSION 限制登录数据库权限 CREATE TABLE CREATE SEQUENCE CREATE VIEW CREATE PROCEDURE

GRANT create session, create table, create sequence, create view TO demo;

grant create table, create view to user1,user2;

grant read,write on dictionary dmp to user1;

select * from v$SESSION;

grant select any dictionary to user1;

grant select any table to user1;

desc DBA_SYS_PRIVS 系统权限视图表

select dictinct privilege from dba_sys_privs;

CREATE ROLE manager; 创建角色

**GRANT create table, create view 对角色赋权 TO manager; **

**GRANT manager TO alice; ** 对用户赋予某角色的权限

示例：创建角色并赋权给用户 create role dev; grant create session, create table to dev; create user test1 identified by test1 ; 用户test1 密码test1 grant dev to test1;

对用户改密码： alter user test1 identified by test2; grant alter user to test1; 给普通用户改其他用户的密码的权限；

ALTER USER demo
IDENTIFIED BY employ;

GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION];

GRANT select ON employees TO demo;

GRANT update (department_name, location_id) ON departments TO demo, manager;

GRANT select, insert ON departments TO demo WITH GRANT OPTION;

GRANT select ON departments TO PUBLIC; 对所有用户

USER_SYS_PRIVS 用户权限视图表 USER_ROLE_PRIVS ROLE_SYS_PRIVS ROLE_TAB_PRIVS DBA_ROLE_PRIVS

一般对开发者开以下权限： grant connect, resource to dev1; grant unlimited tablespace to dev1; 对表空间不限制 alter user test quota 10M on users; test 用户只能对users 表空间有10M空间

指定用户默认表空间： alter user test1 default tablespace system; select * from dba_users where username='test1';

with grant option 转授权限，可转授权限给其他用户 user_tab_privs

回收权限 **REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS]; ** REVOKE select, insert ON departments FROM demo;

select * from usr_tab_privs_recd; 用户的权限可通过这个查询； select * from usr_tab_privs_made where tablename='emp'; 谁有权限对表可访问

grant all on emp to user1; 赋予全部权限 revoke all on emp from user1; 回收全部权限

select * from user_tab_privs; 查用户的权限的表

drop user test cascate; 删除用户。