Oracle密码机制以及常用操作
Oracle 默认有3个用户: sys 密码:自定;system 密码:自定 ;scott 密码:Tiger(默认锁定);注:用Sys用户登录须根 [as sysdba
Oracle 默认有3个用户: sys 密码:自定;system 密码:自定 ;scott 密码:Tiger(默认锁定);
注:用Sys用户登录须根 [as sysdba]
desc 表名:查看表结构;
Dos启动监听:lsnrctl start
Dos启动Oracle实例服务: oradim -starup -sid orcl
查询用户的系统权限:select * from user_sys_privs;
查询用户的表权限:select * from user_tab_privs;
赋予系统权限的时候在用户后跟:with admin option;--意味着赋予权限的对象有权限将此权限进行传递;
赋予对象权限的时候在用户后跟:with grant option;--可以进行权限传递
对象权限:谁创建,谁授予;
系统权限:sys>>A,A>>B,if(sys对象权限:A>>B; B>>C ;if(A角色可以理解为权限的集合;
创建角色语句: create role 角色名;
删除角色:drop role 角色名;
有些系统权限不能放入角色,比如:unlimited tablespace 权限(工作时不能使用).
不能放入角色的权限只能有Sys直接赋予;
将权限限制到列:grant select(id) on mytable to lisi;
oracle修改会话的时间格式:alter session set nls_date_format='yyyy-mm-dd';
权限对比:
create table ;create any table;
alter table(注意:oracle没有这种权限); alter any table;
drop table(注意:oracle没有这种权限); drop any table;
数据的三种验证机制
操作系统验证
密码文件验证
数据库验证
linux下Oracle的启动过程:
lsnrctl start 启动监听
sqlplus / as sysdba进行密码文件和操作系统验证
(在Oracle9i一下版本登录):
{
sqlplus /nolog
conn sys/oracle as sysdba;}
startup 启动实例
SYS密码丢失解决:
删掉Oracle密码文件,用orapwd file=密码文件路径(文件名要PWDorcl.ora格式) password=Sys密码 entries=特权用户的数量限制 force=是否强制覆盖
select * from v$pwfile_users;//查询有多少特权用户
--select * from dba_users; 查询数据库中的所有用户
--alter user TEST_SELECT account lock; 锁住用户
--alter user TEST_SELECT account unlock; 给用户解锁
--create user xujin identified by xujin; 建立用户
--grant create tablespace to xujin; 授权
--grant select on tabel1 to xujin; 授权查询
--grant update on table1 to xujin;
--grant execute on procedure1 to xujin 授权存储过程
--grant update on table1 to xujin with grant option; 授权更新权限转移给xujin用户,,许进用户可以继续授权;
--收回权限
--revoke select on table1 from xujin1; 收回查询select表的权限;
--revoke all on table1 from xujin;
/*grant connect to xujin;
revoke connect from xujin
grant select on xezf.cfg_alarm to xujin;
revoke select on xezf.cfg_alarm from xujin;*/
--select table_name,privilege from dba_tab_privs where grantee='xujin' 查询一个用户拥有的对象权限
--select * from dba_sys_privs where grantee='xujin' 查询一个用户拥有的系统权限
--select * from session_privs --当钱会话有效的系统权限
--角色
--create role xujin1;--建立xujin1角色
--grant insert on xezf.cfg_alarm to xujin1; 将插入表的信息
--revoke insert on xezf.cfg_alarm from xujin1; 收回xujin1角色的权限
--grant xujin1 to xujin ; 将角色的权限授权给xujin;
-- create role xujin2;
--grant xujin1 to xujin2; 将角色xujin1授权给xujin2;
--alter user xujin default xujin1,xujin2; 修改用户默认角色
-- DROP ROLE xujin1;删除角色1;
--select * from role_sys_privs where role=xujin1;
--查看许进1角色下有什么系统权限;
--select granted_role,admin_option from role_role_privs where role='xujin2';
--查看xujin1角色下面有什么角色权限
--select * from role_sys_privs where role='xujin2';
--select table_name,privilege from role_tab_privs where role='xujin1';
--select * from dba_role_privs where grantee='xujin' --查看用户下面有多少个角色;
更多Oracle相关信息见Oracle 专题页面 ?tid=12

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.

Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.

The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values and pointers to data rows, and is suitable for non-primary key column queries.

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

The article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.
