权限

 

SYS、SYSTEM
数据库中所有字典表和视图都存储在SYS模式中，
SYS主要用来维护系统信息和管理实例。
SYSTEM是默认的ORACLE系统管理员，
通常通过SYSTEM用户管理数据库用户、权限和存储等。

oracle:orapwd file=... password=... entries=5(表示该密码文件最多可容纳具有sysdba权限的5个用户) 

    entries是可以保存的记录个数，每个具有sysdba或sysoper权限的用户算一个记录，如果一个用户同时具有sysdba和sysoper的权限，则只占一个记录。
数据库启动后可以通过v$pwfile_users来查看密码文件中记录的情况。
      但是entries并不是一个准确的值，也就是说，如果我们设置entries为10,可能会有超过10个用户可以具有sysdba或者sysoper的权限
当不指定entries值或者指定的entries的值为0-4的时候，可以有五个用户具有sysdba或者sysoper的权限。

sysoper和sysdba权限 在DB的字典视图中无
sysoper: startup,shutdown,alter database open|mount,
         alter database backup controlfile,
     alter tablespace begin/end backup,
     recover database
      alter database archivelog,restricted session
sysdba: sysoper privileges with admin option,
        create database,
        recover database until
       

sys:
SQL> select * from v$pwfile_users;******查看密码文件中记录的情况
  
USERNAME                       SYSDBA SYSOPER
------------------------------ ------ -------
SYS                            TRUE   TRUE

sys:
grant sysdba to lh; ***********给用户授与sysdba权限的过程就是把密码从数据字典复制到了密码文件中的过程。

select * from v$pwfile_users
  sys true true
  lh  true false
sqlplus lh/lh as sysdba
  SQL> show user
  USER is "SYS"
grant sysoper to lh;
sys:
select * from v$pwfile_users
  sys true true
  lh  true true
SQL> connect u1/u1 as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.

 
       
remote_login_passwordfile ***远程登录的时候用不用密码文件
  none  设置成none的时候，需要密码文件验证
  shared/exclusive  这俩个没什么区别，前者出现在文档中，后者出现在程序中，意思一样
alter system set remote_login_passwordfile=none scope=spfile;  *******设置成none的意思是需要用OS验证，必须使用ORACLE用户登录，是本地登录
create pfile from spfile;                                              设置成exclusive或者是share的时候，需要密码文件验证，需要远程登录
shutdown immediate
startup
D:\>sqlplus as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

sqlnet.ora
  sqlnet.authentication_services=none/all ***是none的时候，严格的用DB验证；是all的时候用OS和DB验证，先是用OS验证，再用DB验证，是在本地登录

alter system set remote_login_passwordfile=exclusive   ***用于限制远程登录
  scope=spfile;
create pfile from spfile; 
shutdown immediate
startup
 

权限
     系统权限：访问或者使用数据库的权力，使用户在数据库中能够执行一些特定的操作
     对象权限：维护数据库中的对象的权力，使用户能够访问和维护某一特定的对象
     角色：一组命名的相关的权限，这组权限可以通过这个名字授予用户或其他的角色
    
     managing privileges

     sys:
 如下操作是在sys用户下：
     drop user u1 cascade;  ***彻底删除用户     
     create user u1 identified by u1  **创建一个用户，它的表空间名字是users，是一个临时表空间
       default tablespace users
       temporary tablespace temp;
     connect u1/u1
     connect sys/oracle as sysdba ***当创建了一个用户的时候，它是没有权限的，所以它是不能连接数据库的，这时需要授予权限
                         
（1）授予权限：
     grant create session to u1;  **授予一个create session的权限
     connect u1/u1
     create table test(id number,name varchar2(20));
     connect sys/oracle as sysdba
     grant create table to u1;   **授予一个创建表的权限
     connect u1/u1
     create table test(id number,name varchar2(20));
     connect sys/oracle as sysdba
     alter user u1 quota 10M on users;   **修改一下表空间的磁盘限额，否则不能创建表
     connect u1/u1
     create table test(id number,name varchar2(20));
     create table test2(id number,name varchar2(20))
       tablespace sale_ts;     
     connect sys/oracle as sysdba
     alter user u1 quota 10M on sale_ts;
     connect u1/u1
     create table test2(id number,name varchar2(20))
       tablespace sale_ts;  
      
     select * from user_sys_privs  **查询系统权限
     select * from user_tab_privs  **查询对象权限
   select * from system_privilege_map  **这个视图存放所有的系统权限

     sys:
     create role rt;  **创建一个角色
     grant create table,create session to rt;
     select * from role_sys_privs where role='RT'
     grant rt to qw;
     grant select on lh.test to qw; **授予qw查询的权限
     grant update (age) on lh.test to qw;  **精确到列的权限（只有update能）
     revoke update on lh.test from qw;  **收回权限

 通过视图来查询权限：
     select * from dba_role_privs where grantee='QW'
     select * from dba_sys_privs where grantee='QW'
     select * from dba_tab_privs where grantee='QW'
     select * from DBA_COL_PRIVS where grantee='QW'
     select * from dba_ts_quotas
     select * from role_sys_privs where role='RT'
     select * from role_tab_privs where role='RT'
 select * from role_role_privs where role='RT'  **查询角色权限
     
     grant update (sal) on scott.emp to u1;
     
     1.system privileges:
     view => system_privilege_map ,
             dba_sys_privs,session_privs
     
     2.grant system privilege **授予系统权限
     sql> grant create session,create table to managers;
     sql> grant create session to scott with admin option; ******with admin option的意思是授予系统权限
     with admin option can grant or revoke privilege from any user or role;（给一个用户授予“admin option“权限，可以给其他的用户或者角色授予或者收回权限）                                                                                                                                     
     sql> grant select on scott.emp to u1 with grant option;**********with grant option的意思是授予对象权限
     
     3.sysdba and sysoper privileges: 在DB的字典视图中无
     sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,
          alter tablespace begin/end backup,recover database
           alter database archivelog,restricted session
     sysdba: sysoper privileges with admin option,create database,recover database until
     sys:sysdba,sysoper v$pwfile_users
     Sysdba权限认证过程主要涉及ORACLE中的相关参数和配置文件
      
     sys:
     select * from v$pwfile_users
       sys true true
     sys:
     grant sysdba to lh;
     select * from v$pwfile_users
       sys true true
       lh  true false
     sqlplus lh/lh as sysdba
       SQL> show user
       USER is "SYS"
     grant sysoper to lh;
     sys:
     select * from v$pwfile_users
       sys true true
       lh  true true
     SQL> connect u1/u1 as sysdba
     ERROR:
     ORA-01031: insufficient privileges
     Warning: You are no longer connected to ORACLE.

     4.password file members: view:=> v$pwfile_users
     
     5.O7_dictionary_accessibility =true
       restriction access to view or tables in other schema

       alter system set O7_dictionary_accessibility=true scope=spfile;
       
      grant select any dictionary to U1;**授予用户这个权限的时候，用户具有访问系统下的任何数据字典视图
       
  （2）收回权限：
     6.revoke system privilege **收回系统权限
     sql> revoke create table from karen;
     sql> revoke create session from scott;
     
     7.grant object privilege
     sql> grant execute on dbms_pipe to public;
     sql> grant update(first_name,salary) on employee to karen with grant option;
     
     8.display object privilege : view => dba_tab_privs, dba_col_privs
     
     9.revoke object privilege
     sql> revoke execute on dbms_pipe from scott [cascade constraints];
     
     
      
     manager role
     
     select * from dba_roles where role='ROLE1'
     select * from ROLE_SYS_PRIVS where role='ROLE1'
     SELECT * from role_tab_privs where role='ROLE1'
     select * from role_role_privs where role='ROLE1'
                
（3）创建角色：
     1.create roles
     sql> create role sales_clerk;
     sql> create role hr_clerk identified by bonus;
     sql> create role hr_manager identified externally;
          --EXTERNALLY Specify EXTERNALLY
          to create an external role.
          An external user must be
          authorized by an external service,
          such as an operating system or
          third-party service, before
          enabling the role.

     2.modify role
     sql> alter role sales_clerk identified by commission;
     sql> alter role hr_clerk identified externally(外部标识);
     sql> alter role hr_manager not identified;
     
     3.assigning roles
     sql> grant sales_clerk to scott;
     sql> grant hr_clerk to hr_manager;
     sql> grant hr_manager to scott with admin option;
                                               
                                               **只有对象权限用的是"with grant option"，系统权限和角色权限都用"with admin option"
     
     4.establish default role
     sql> alter user scott default role hr_clerk,sales_clerk;
     sql> alter user scott default role all;
     sql> alter user scott default role all except hr_clerk;
     sql> alter user scott default role none;
     
     5.enable and disable roles
       only current session
     sql> set role hr_clerk;
     sql> set role sales_clerk identified by commission;
     sql> set role all except sales_clerk;
     sql> set role none;
     sql> set role all;
     select * from SESSION_ROLES;
sys:
SQL> grant resource to b ; 
quit 
sqlplus b/b   
SQL> create table t (id number);
Table created.
SQL> set role none;  **关闭一个角色，开启的命令是“set role all”
Role set.
SQL> create table t2(id number);
create table t2(id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> set role resource;
Role set.
SQL> create table t2 (id number);
Table created.
SQL> set role none;
Role set.
SQL>
SQL> show user
USER is "B"
SQL>

D:\>sqlplus
SQL> create table t3(id number);
表已创建。
SQL> show user
USER 为 "B"
SQL>

     
     6.revoke role from user
     sql> revoke sales_clerk from scott;
     sql> revoke hr_manager from public;
     
     7.remove role
     sql> drop role hr_manager;
     
     8.display role information
     view: =>dba_roles,dba_role_privs,
     role_role_privs,dba_sys_privs,
     role_sys_privs,
     role_tab_privs,session_roles

select * from dba_roles where role='ROLE1'
select * from ROLE_SYS_PRIVS where role='DBA'
SELECT * from role_tab_privs where role='ROLE1'
select * from role_role_privs where role='ROLE1'
select * from ROLE_SYS_PRIVS where role='CONNECT'
select * from dict where table_name like '%ROLE_P%'

select * from dba_role_privs where grantee='U1'
select * from role_role_privs where role='ROLE1'

select * from dba_sys_privs where grantee='U3'
select * from dba_tab_privs where grantee='U1'
select * from DBA_COL_PRIVS where grantee='U1'
select * from dba_ts_quotas
 

grant create session to u1 with admin option;
grant update on scott.emp to u1 with grant option;
grant role1 to u1 with admin option;