oracle用户权限

系统用户个数

 

select username from DBA_USERS;

select * from all_users;

3个默认的用户

 

                   sys         change_on_install     [as sysdba]                                                                                           

                   system  manager                                                                                                                                                                      

                   scott       tiger

                   【当sys用户丢失密码可通过“删除口令文件---再次 生成口令文件”的方法来重置sys用户密码 】

                                  口令文件：D:\oracle\product\10.2.0\db_1\database\pwdora9i.ora;

                                  生成方法：orapwdfile=D:\oracle\product\10.2.0\db_1\database\pwdora9i.ora                                                                                           

    password=test1234 entries=10;

                                   select * from v$pwfile_users;

                    【当一般用户丢失密码可通过  alter user scott identified by 123;重置】                                                                    

                  

创建用户

                  create user longzai identified by shaolin;

                  drop user longzaicascade;          【用cascade 用户立即删除】

 

系统权限

                 

                 grant create session to longzai;

                 grant unlimited tablespace to longzai;

                 grant create table to longzai;

 

                 revoke create session from longzai;

                 revoke unlimited tablespace from longzai;

                 revoke create table from longzai;

                   

                 grant create session to public;

                 select * from user_sys_privs;

对象权限

                grant select on mytab tolongzai;                                                             

                grant all on mytab to longzai;

 

                rovoke select on mytab from longzai;

                rovoke select on mytab from longzai;

 

                select * from user user_tab_privs;

 

对象权限可以控制到列

               grant update(name) on mytab tolongzai;                    

               grant insert(id) on mytab to longzai;

 

               select * from user_col_privs;

               注意：查询和删除不能控制到列。

                             //ddl dml dcl

权限的传递

 

               grant alter any table to longzai with admin option;

               grant select on A to longzai with grant option;

 

角色

               create role myrole;

               grant create session to myrole;

               drop role myrole;

注意：有些系统权限无法直接赋予角色

 

               create table          create anytable

              【altertable】     alter any table

              【droptable】     drop any table

               表是属于某一个用户的；角色不属于任何一个。

 

数据库的三种验证机制

 

               操作系统验证

               密码文件验证

               数据库验证

linux下 oracle的启动过程

        lsnrctl start

         sqlplus/nolong                          //sqlplus sys/test1234 as sysdba 【oracle10】

        conn sys/test1234 as sysdba

        startup

windows 下 oracle的启动过程

           lsnrctl start

           oradim -starup -sid orcl