Controlling User Access 控制用户访问

you should be able to do the following:

1、Differentiate system privileges from object privileges2、Grant privileges on tables3、Grant roles4、Distinguish between privileges and roles

Database security:

1、System security2、Data securitySystem privileges: Performing a particular action within the databaseObject privileges: Manipulating the content of the database objectsSchemas: 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 TABLECREATE SEQUENCECREATE VIEWCREATE PROCEDURE

GRANT create session, create table,

create sequence, create viewTO 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 密码test1grant 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 objectTO {user|role|PUBLIC}[WITH GRANT OPTION];

GRANT select

ON employeesTO demo;

GRANT update (department_name, location_id)

ON departmentsTO demo, manager;

GRANT select, insert

ON departmentsTO demoWITH GRANT OPTION;

GRANT select

ON departmentsTO PUBLIC; 对所有用户

USER_SYS_PRIVS 用户权限视图表

USER_ROLE_PRIVS ROLE_SYS_PRIVSROLE_TAB_PRIVSDBA_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, insertON departmentsFROM 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; 删除用户。