oracle与操作系统集成,在xp中建立ora_dba组,并将安装用户加入该组,使之成为DBA,因此在sqlplus中,普通用户tom
,可以用:
connect tom/tomcat as sysdba;
登录,也可以如下登录:
connect / as sysdba;
创建用户:
oracle不支持先输数字,再跟字母的口令。
概要文件:配置文件。
表空间:一定要指定。
授予权限:
grant SELECT ON dept TO tester;
revoke SELECT ON dept FROM tester;
grant SQL命令 ON 表 TO 用户;
revoke SQL命令 ON 表 FROM 用户;
create user tom identified by tomcat;
grant connect to tom;
grant resource to tom;
授权成功。
tom 用户表:
grant all on t_staff to scott;
grant all on t_corp to scott;
grant all on t_abc to scott;
show user;
grant connect/resouce/dba to user;
revoke emp on select from user;
alter session set nls_date_format='yyyy_mm_dd';
create user tester profile default identified by test account unlock;
--赋给角色
grant connect to tester;
--赋给权限
grant select on scott.dept to tester;
--将users作为test的默认表空间。
alter user tester default tablespace users;
--修改密码
alter user tester identified by tester;
--锁定用户
alter user tester account lock;
--解除锁定
alter user tester account unlock;
--再建用户,默认使用users表空间
create user jones identified by jones default tablespace jones;
--删除用户
drop user jones cascade;
--授权tester对表scott.dept有select权,且tester可对其它用户授予该权限
grant select on scott.dept to tester with grant option;
--对存储过程授权
grant execute on scott.myproc to tester;
--对创建用户权限授权
grant create user to tester with grant option;
grant drop user to tester;
--如下级联授权操作
conn / as sysdba;
--管理员将权限授予tester,可查询scott.dept,并且tester可再授权
grant select on scott.dept to tester with grant option;
conn tester/test;
select * from scott.dept;
--tester将查询scott.dept权限授予tom
grant select on scott.dept to tom with grant option;
--or
grant select on scott.dept to tom;
conn tom/tomcat;
select * from scott.dept;
--反向操作
conn / as sysdba;
--管理员收回tester的权限
revoke select on scott.dept from tester;
conn tester/test;
select * from scott.dept;
conn tom/tomcat;
select * from scott.dept;
--事实表明,如果管理员收回tester的权限,则tester授予tom的相同权限也被回收。
系统授权,可以加with admin option
对象授权,可以加with grant option
grant create user to tester with admin option;
角色授权:
create role myrole;
grant all on scott.dept to myrole;
grant all on scott.emp to myrole;
grant all on scott.staff to myrole;
grant all on scott.corp to myrole;
grant myrole to tester;
conn tester/test;
select * from scott.dept;
删除角色权限,则对应用户就没有了该权限。
revoke all on scott.dept from myrole;
概要文件:创建、使用(网页方式)。
Oracle 角色授权与安全
最新推荐文章于 2022-04-14 15:21:07 发布