Oracle培训笔记 8.6 用户权限

 

数据库安全

1）规章制度
2）用户-权限
3）审计

系统默认表空间
select * from database_properties
select * from dba_tablespaces

alter database default tablespace tbs1;
alter database default temporary tablespace temp01;

1、用户
查询系统用户
select * from dba_users;
default_tablespace: 缺省表空间
temporary_tablespace: 临时表空间
profile：概要文件（资源限制用）
inital_rsrc_consumer_group: 资源组

查询用户系统权限
select * from dba_sys_privs where grantee ='SYSTEM';
grantee: 被授权者，用户，角色
privilege: 系统权限
admin_option: 管理选项

查询系统所有权限
select * from system_privilege_map;

查询用户对象权限
select * from dba_tab_privs  where grantee ='SYSTEM';

查询用户具备角色
select * from dba_role_privs  where grantee ='SYSTEM';

查询超级用户
select * from v$pwfile_users;
sysdba:可创建数据库，
sysoper:

orapwd file=E:\oracle\product\10.2.0\db_1\database\pwdmysid.ora password=123
 entries=5 force=y
create user u1 identified by u1;
create user u2 identified by u1;
create user u3 identified by u1;
create user u4 identified by u1;
create user u5 identified by u1;
create user u6 identified by u1;
create user u7 identified by u1;
create user u8 identified by u1;
create user u9 identified by u1;

grant sysdba to u1,u2,u3,u4,u5,u6,u7,u8,u9;
revoke sysdba from  u1,u2,u3,u4,u5,u6,u7,u8;

drop user u1;
drop user u2;
drop user u3;
drop user u4;
drop user u5;
drop user u6;
drop user u7;
drop user u8;
drop user u9;
 
语法
create user 用户名 identified by 密码
default tablespace 默认表空间
temporary tablespace 临时表空间
quota nM|unlimited on 表空间1
quota nM|unlimited on 表空间2
password expire
account lock|unlock
profile 概要文件

练习1：创建用户u1
create user u1 identified by abc
default tablespace tbs1
temporary tablespace temp01;

grant create session, create table to u1;
alter user u1 quota unlimited on tbs1;
alter user u1 quota unlimited on tbs2;

create table t1(id number);
create table t2(id number) tablespace tbs2;
select * from dba_tables where table_name ='T1'
 
练习2：创建用户u2，指定password expire
create user u2 identified by abc
default tablespace tbs1
temporary tablespace temp01
quota unlimited on tbs1
password expire;

grant create session,create table to u2;
 
练习3： 创建用户u3，指定account lock
create user u3 identified by abc
default tablespace tbs1
temporary tablespace temp01
quota unlimited on tbs1
account lock;
select * from dba_users;
grant create session,create table to u3;

解锁
alter user u3 account unlock;

profile：概要文件
select * from dba_profiles;

设置系统参数
SQL> show parameter resource
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
resource_limit                       boolean     FALSE

alter system set resource_limit=true;

创建profile
create profile myprofile limit
SESSIONS_PER_USER 3;

与用户关联
alter user u1 profile myprofile;
select * from dba_users
select sysdate from dual
修改profile
alter profile myprofile limit
CPU_PER_CALL 1000;

alter profile myprofile limit
FAILED_LOGIN_ATTEMPTS 3;

alter profile myprofile limit
PASSWORD_LIFE_TIME 30;
alter user u1 account unlock;

校验函数
E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlpwdmg.sql

CREATE OR REPLACE FUNCTION myverify
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
begin
if password=old_password or password=username then
return false;
else
return true;
end if;
end; 

alter profile myprofile limit
PASSWORD_VERIFY_FUNCTION myverify;

2、权限
系统权限：指执行某些语句完成特定任务的权限。
create table
create session
create view
create procedure
....

create any table

对象权限：指访问对象的权限。
表： select update delete insert ....
存储过程： execute

授权回收：
grant ：授权
revoke：回收

系统权限授予回收
grant create table to u1 with admin option;
select * from dba_sys_privs where grantee='U1'

以u1登录，将权限授予u2
grant create table to u2 with admin option;
select * from dba_sys_privs where grantee like 'U_'

以sys用户登录，回收u1用户的create table权限
revoke create table from u1;

对象权限的授予回收
select * from t1;
grant select on t1 to u1 with grant option;
select * from dba_tab_privs where grantee like 'U_';

以u1登录，将权限授予u2
grant select on sys.t1 to u2 with grant option;

以sys用户登录，回收u1用户的select on sys.t1权限
revoke select on t1 from u1;

3、角色
select * from dba_roles;

select * from role_sys_privs where role='CONNECT'; dba_sys_privs
select * from role_tab_privs where role='CONNECT'; dba_tab_privs
select * from role_role_privs where role='CONNECT'; dba_role_privs

select * from dba_tab_privs where grantee like 'U_';
revoke select on sys.t1 from u1;
select * from dba_sys_privs where grantee like 'U_'
revoke create session from u1,u2;
revoke create table from u2;

create role myrole;
grant create session to myrole;
grant myrole to u1,u2;

grant create table to myrole;

create role myrole1;
grant create view to myrole1;
grant myrole1 to u1,u2;

grant create view to u1;
revoke myrole1 from u1;

练习：获取用户所有权限

create table sysprivs as select * from dba_sys_privs where 1=2;
create table tabprivs as select * from dba_tab_privs where 1=2;

create or replace procedure getprivs (uname varchar2)
is
cursor c(uname varchar2) is
select granted_role from dba_role_privs where grantee=upper(uname);
begin
for v in c(uname) loop
getprivs(v.granted_role);
dbms_output.put_line(v.granted_role);
end loop;
insert into sysprivs select * from dba_sys_privs where grantee=upper(uname);
insert into tabprivs select * from dba_tab_privs where grantee=upper(uname);
commit;
end;

begin
getprivs('system');
end;

select * from sysprivs;
select * from tabprivs;

授权
1）系统权限和对象权限不能同时授予
grant create table,select on t1 to u1;

2）不同对象的权限不能同时授予
grant select on t1,t2 to u1;

public
select * from dba_sys_privs where grantee='PUBLIC';
select * from dba_tab_privs where grantee='PUBLIC';
select * from dba_role_privs where grantee='PUBLIC';