oracle权限培训,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';