权限，密码文件

 

创建密码文件：

 
orapwd file=C:\oracle\product\10.2.0\admin\orcl\pwdmysid.ora password=123;

 create user test1 identified by test1;

 grant sysdba to test1;

 

 select * from v$pwfile_users;

revoke sysdba from test1

 

权限相关：

练习1

创建一个用户user1

授予权限 create session,create table

查询用户权限

回收权限

 

create user user1 identified by abc;

select * from dba_users where username='USER1';

select * from database_properties;

 

授予、查询系统权限

grant create session,create table to user1;

select * from dba_sys_privs where grantee='USER1'

 

授予、查询对象权限

create table tbl1 (id number);

grant select on tbl1 to user1;

select * from dba_tab_privs where grantee='USER1'

 

回收权限

revoke create session,create table from user1;

revoke select on tbl1 from user1;

 

练习2

系统权限 with admin option

对象权限 with grant option

 

create user user2 identified by abc;

grant create session,create table to user2;

grant create session,create table to user1;

 

grant create view to user1 with admin option;

 

select * from dba_sys_privs where grantee like 'USER_'

 

以user1登录，将create view 授予user2

 

回收user1 的create view权限

revoke create view from user1

 

收回所有权限

revoke create table from user1,user2

revoke create view from user2

 

授予user1访问tbl1权限

grant select on tbl1 to user1 with grant option;

select * from dba_tab_privs where grantee like 'USER_'

 

revoke select on tbl1 from user1;

 

练习3

select * from dba_sys_privs where grantee like 'USER_'

select * from dba_tab_privs where grantee like 'USER_'

select * from dba_role_privs where grantee like 'USER_'

revoke create session from user1,user2;

 

创建角色1

create role myrole1;

select * from dba_roles;

select * from role_sys_privs; dba_sys_privs

select * from role_tab_privs;

select * from role_role_privs;

 

grant create session to myrole1;

grant myrole1 to user1;

select * from dba_role_privs;

 

alter user user1 quota unlimited on users;

以user1登录

 

grant create table to myrole1;

 

创建角色2,对于已登录的用户必须重新登录，新授予的角色才生效

create role myrole2;

grant create view to myrole2;

grant myrole2 to user1;