Oracle_071_lesson_p18
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 USERdemo
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
GRANTcreate session, create table,
create sequence, create view
TO demo;
grantcreate table, createviewto user1,user2;
grantread,write on dictionary dmp to user1;
select*fromv$SESSION;
grantselectanydictionary touser1;
grantselectanytabletouser1;
descDBA_SYS_PRIVS系统权限视图表
selectdictinctprivilegefromdba_sys_privs;
CREATE ROLE manager;创建角色
GRANT create table, create view 对角色赋权
TO manager;
GRANT manager TO alice; 对用户赋予某角色的权限
示例：创建角色并赋权给用户
createroledev;
grantcreatesession, createtableto dev;
createusertest1 > grantdevtotest1;
对用户改密码：
alterusertest1 >
grant > ALTER USER demo
IDENTIFIED BY employ;
GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
;
GRANTselect
ON employees
TO demo;
GRANTupdate (department_name, location_id)
ON departments
TO demo, manager;
GRANTselect, insert
ON departments
TO demo
WITH GRANT OPTION;
GRANTselect
ON departments
TO PUBLIC; 对所有用户
USER_SYS_PRIVS用户权限视图表
USER_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
DBA_ROLE_PRIVS
一般对开发者开以下权限：
grantconnect, resourceto dev1;
grantunlimitedtablespacetodev1; 对表空间不限制
alterusertestquota10Mon users; test用户只能对users 表空间有10M空间
指定用户默认表空间：
alterusertest1defaulttablespacesystem;
select*fromdba_userswhere username='test1';
withgrantoption 转授权限，可转授权限给其他用户
user_tab_privs
回收权限
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
;
REVOKEselect, insert
ON departments
FROM demo;
selectfromusr_tab_privs_recd;用户的权限可通过这个查询；
select fromusr_tab_privs_madewheretablename='emp'; 谁有权限对表可访问
grantallonemptouser1; 赋予全部权限
revokeallonempfromuser1; 回收全部权限
select *fromuser_tab_privs; 查用户的权限的表
dropusertestcascate;删除用户。
页:
[1]