Oracle笔记之用户与角色

Oracle笔记之用户与角色

• (一)授权对象权限
• (二)授予系统权限
• (三)查看用户拥有的系统权限
• (四)回收系统权限
• (五)查看对象权限
• (六)回收对象权限
• (七)查询一个用户开放哪些权限给别的用户
• (八)同义词
• (九)角色
• (十)查询角色的系统权限和对象权限
• (十一)删除角色
• (十二)从角色删除权限
• (十三)关于审计

–创建一个用户

create user user_test identified by 123;

grant connect,resource to user_test;

conn user_test/123;

SQL> select * from scott.emp;
 
select * from scott.emp
 
ORA-00942: 表或视图不存在

(一)授权对象权限

--授予select on scott.emp权限给user_test
SQL>  grant select on scott.emp to user_test;

create or replace  procedure  p_test as 
begin
  dbms_output.put_line('Hello World!');
end;
--使用set serveroutput on 命令设置环境变量serveroutput为打开状态，从而使得pl/sql程序能够在SQL*plus中输出结果
set serveroutput on;
--执行存储过程p_test
execute p_test;--执行失败，因为还没有授权
--授权execute on sys.p_test给user_test
SQL> grant execute on sys.p_test to user_test;

(二)授予系统权限

grant execute any procedure to user_test;

(三)查看用户拥有的系统权限

select * from user_sys_privs;

--包含role
select * from session_privs;

(四)回收系统权限

revoke connect from user_test;

(五)查看对象权限

select * from user_tab_privs;

grant update(sal) on scott.emp to user_test;

select * from user_col_privs;

(六)回收对象权限

revoke select ,update on scott.emp from user_test;

revoke execute on sys.p_test from user_test;

(七)查询一个用户开放哪些权限给别的用户

 conn scott/tiger;
 grant select on scott.emp to user_test;
  --查询表对象
 select * from user_tab_privs_made;

 --查询列对象
 select * from user_col_privs_made;

给oracle添加一个用户时一般都分配个connect、resource 角色就行了如：
　　grant connect,resource to xxx;
　　但这里connect 角色有一个 UNLIMITED TABLESPACE 权限，也就是xxx这个用户可以在其他表空间
　　里随意建表。
　
　　SQL> select * from dba_sys_privs where grantee=‘xxx’;
　　一般ＤＢＡ要把这个 UNLIMITED TABLESPACE权限关掉
　　SQL> revoke unlimited tablespace from xxx; #回收unlimited tablespace权限
　　Revoke succeeded
　　SQL> alter user xxx quota unlimited on tbs; #其中后面tbs是表空间名
使xxx在表空间tbs中午限制，一般还是不希望，用户有随意建表的权限

--一般创建用户的方法
SQL> drop tablespace hxts including contents and datafiles;


1.create tablespace ts_test datafile 'D:\ts_test.data' size 10M autoextend on extent management local segment space management auto;


2.create user uset_test identified by 123 default tablespace ts_test;

3.grant resource ,connect to uset_test ;
   conn uset_test /123
   select * from user_sys_privs;
   create table t(id int) tablespace users; --创建在其它的表空间了
   
4.revoke unlimited tablespace from uset_test ;

   conn uset_test /123;
	SQL> create table t(id int);

	create table t(id int)

	ORA-01950: no privileges on tablespace 'ts_test' --不能创建表了

5. alter user uset_test quota 5M on ts_test ;

   alter user uset_test quota unlimited on ts_test ; 
   
6. create table t(id int) --OK了   

create user myuser identified by 123 default tablespace ts1 temporary tablespace temp;--也可以后期修改

注意：权限的授予一定要按照最小原则

grant create any procedure to ocpexam;

grant execute any procedure to ocpexam;

注意授权时可以带选项:
with admin option;–系统权限
with grant option;–对象权限

回收时对用户的权限影响
with admin option;–不级联
with grant option;–级联

(八)同义词

 create synonym emp for scott.emp;
ORA-01031: 权限不足

grant create synonym to user_test;

 create synonym emp for scott.emp;
drop synonym emp ;

--创建公有同义词(所有用户可见)

 grant create public synonym to user_test;

 create public synonym myemp for scott.emp;

--另一用户dd
select * from myemp;--但是必须已经对dd授予scott.emp的select权限

(九)角色

GRANT CREATE ROLE TO store;
GRANT CREATE USER TO store WITH ADMIN OPTION;

--sys
 create role r1;
 grant select on scott.emp to r1;

 create role r2;
 grant select on scott.dept to r2;

  grant r1 to user_test;

  grant r2 to user_test;


--user_test
   set role r1;
   select * from scott.emp;--正常

   select * from scott.dept --无权

   set role r2 
   select * from scott.dept;--正常

   但
   select * from scott.emp;--不正常
  
   --查看设置 
   select * from user_roles_privs 
  
   conn user_test/123后两个表都可以访问


 set role all ;--起用所有角色
    set role none;
    SET ROLE overall_manager IDENTIFIED BY manager_password
    SET ROLE ALL EXCEPT overall_manager

   ALTER USER user_test DEFAULT ROLE ALL EXCEPT r1;

(十)查询角色的系统权限和对象权限

--系统权限
SELECT * FROM role_sys_privs;
--对象权限
select * from role_tab_privs;

(十一)删除角色

DROP ROLE overall_manager;

(十二)从角色删除权限

REVOKE ALL ON products FROM product_manager 

(十三)关于审计

SQL> alter system set audit_trail=DB scope=spfile;

--重启数据库

SQL> audit select on user_test.t1 by access;

--查询做了哪些审计
SQL> select * from dba_obj_audit_opts where object_name like '%T1%';

SQL> select * from dba_priv_audit_opts;


SQL> delete from sys.aud$;

SQL> select * from Dba_Audit_Trail where obj_name like '%T1%';