一、Oracle PL/SQL使用1、用户管理数据库系统权限(DataBaseSystem Privilege) 数据库对象权限(DataBaseSystem Privilege) Orac
一、Oracle PL/SQL使用
1、用户管理
数据库系统权限(DataBaseSystem Privilege)
数据库对象权限(DataBaseSystem Privilege)
Oracle内部用户Sys和System
创建用户:create user user_test identified by user_test123;
修改用户:alter user user_test identified by user_test123;
删除用户:drop user user_test;
删除用户与其对象:drop user user_testCASCADE;
用户授权连接:grant connect,resource to user_test;
在sqlplus 中使用以下命令可以查出用户表的清单:
sqlplus scott/tiger -- 使用sqlplus 登录 oracle 数据库
col table_name format a30; -- 指定table_name 的列宽
col table_type format a10; -- 指定table_type 的列宽
select * from cat; -- 列出用户数据表
2、角色管理
1)Connect Role(连接角色):临时用户,不需要建表的用户。
2)Resource Role(资源角色):更可靠和正式的数据库用户。
3)Dba Role(数据库管理员角色):拥有所有的系统权限。
操作:
grant(授权)命令:grant connect,resource to user_test;
revoke(撤销)命令:revoke connect,resource to user_test;
创建角色:除了系统自带的3种标准角色外用户可以创建自己的role.
create role user_test_role;
角色授权:grant select on t_service to user_test_role;
注:拥有student角色的用户都具有对t_service表的select权限。
删除角色:droprole user_test_role;
查看用户有哪些角色:
select grant_role fromdba_role_privs where grantee='scott';
查看用户有哪些权限:
select privilege from dba_sys_privs wheregrantee='scott';
select privilege fromdba_sys_privs where grantee='CONNECT';
默认用户:Sys:oralce中的超级用户,主要用来维护系统信息和管理实例。
System:oracle中默认的系统管理员,拥有dba权限。通常管理oracle数据库的用户、权限和存储等。
Scott:oracle数据库的一个示范账户,在数据库安装时创建。
用户授权: grant 权限 [ on 对象名 ] to 用户名 [ with grant option];
grant select on scott.emp to user_test with grant option;
grant create session to user_test;
3、权限管理
授予user_test用户查看emp表数据的权限
1.验证user_test对scott用户的emp表进行查询的权限。
SQL>select *from scott.emp;
2.为用户user_test授予scott用户的emp表的查询权限。
SQL>conns cott/scott@test;
SQL>grant select on scott.emp to user_test;
3.使用user_test账户登录并查询scott用户的表emp信息。
SQL>conn user_test/u01@test;
SQL>select *from scott.emp;
收回权限: revoke 权限 [ on 对象名 ] from 用户名
SQL>revoke select on scott.emp from user_test;
修改用户密码:alter user 用户名 identified by 新密码;
与权限相关的表:
1.dba_sys_privs(所有系统权限)
2.user_sys_privs(用户拥有的系统权限)
3.user_col_privs(用户拥有的对象权限)
常用的系统权限赋值语句:
SQL>grant create session to user_test;
SQL>grant create table to user_test;
SQL>grant unlimited tablespace to user_test;
SQL>grant create session to public;
对象权限的赋予与撤销语句:
SQL>grant select on mytable to user_test;
SQL>grant all on mytable to user_test;
SQL>revoke select on mytable from user_test;
SQL>revoke all on mytable from user_test;
Oracle中除了能在表对象上赋予相应的权限,,还能将权限控制到表的列上:
SQL>grant update(name)on mytable to user_test;
SQL>grant insert(id) on mytable to user_test;
4、口令管理
使用profile管理用户口令
profile是口令限制,资源管理的命令集合,当建立数据库时,Oracle会自动建立名词为default的profile。当建立用户没有指定profile选项,那Oroacle就会将default分配给用户。
关键字列表:
SQL>create profile 配置文件名称 limit failed_login_attempts 尝试次数password_lock_time 锁定天数;
SQL>alter user 用户名 profile 配置文件名称;
例:指定tea用户最多只能尝试3次登陆,锁定时间为2天,下面可以实现。
SQL>create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
SQL>alter user tea profile lock_account;
给账号解锁
SQL>alter user user_test account unlock;
例:为了让用户定期修改密码可使用终止口令完成。
SQL>create profile myprofile limit password_life_time 10 password_grace_time 2;
给用户解锁
SQL>alter user user_testprofile myprofile;
删除profile
SQL>drop profile myprofile[cascade];
5、有关表sequence
创建主键自增表
1)创建表 t_test
create table t_test(
userid number(10) NOT NULL primary key, /*主键,自动增加*/
username varchar2(20)
);
2)创建自动增长序列
CREATESEQUENCE t_test_increase_sequence
INCREMENTBY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值 ,设置最大值:maxvalue 9999
NOCYCLE -- 一直累加,不循环
CACHE 10;
示例:CREATE SEQUENCE t_service_sequence INCREMENT BY1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 10;
3)创建触发器(客户端工具添加)
CREATE TRIGGER t_service_triggerBEFORE
insert ON t_service FOR EACH ROW /*对每一行都检测是否触发*/
begin
select t_service_sequence.nextvalinto:New.userid from dual;
end;
示例:
create orreplacetrigger t_service_trigger
before insert on t_service
for eachrow
declare
-- local variables here
begin
select t_service_sequence.nextvalinto:New.idfrom dual;
end t_service_trigger;
4)提交
commit;
5)测试
insert intoTest_Increase(Username) values('test');
6、远程连接
在连接之前设置好监听程序然后使用如下命令:
sqlplus usr/pwd@//host:port/sid
二、SpringJDBC配置
#flow config jdbcfor oracle
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
jdbc.username=user_test
jdbc.password=user_test123
xml里配置方言:
相关阅读:
rlwrap - 解决Linux下SQLPLUS退格、上翻键乱码问题
SQLPLUS spool 到动态日志文件名
Oracle SQLPLUS提示符设置
通过设置SQLPLUS ARRAYSIZE(行预取)加快SQL返回速度

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

SQL commands in MySQL can be divided into categories such as DDL, DML, DQL, DCL, etc., and are used to create, modify, delete databases and tables, insert, update, delete data, and perform complex query operations. 1. Basic usage includes CREATETABLE creation table, INSERTINTO insert data, and SELECT query data. 2. Advanced usage involves JOIN for table joins, subqueries and GROUPBY for data aggregation. 3. Common errors such as syntax errors, data type mismatch and permission problems can be debugged through syntax checking, data type conversion and permission management. 4. Performance optimization suggestions include using indexes, avoiding full table scanning, optimizing JOIN operations and using transactions to ensure data consistency.

InnoDB achieves atomicity through undolog, consistency and isolation through locking mechanism and MVCC, and persistence through redolog. 1) Atomicity: Use undolog to record the original data to ensure that the transaction can be rolled back. 2) Consistency: Ensure the data consistency through row-level locking and MVCC. 3) Isolation: Supports multiple isolation levels, and REPEATABLEREAD is used by default. 4) Persistence: Use redolog to record modifications to ensure that data is saved for a long time.

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

MySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.

InnoDB effectively prevents phantom reading through Next-KeyLocking mechanism. 1) Next-KeyLocking combines row lock and gap lock to lock records and their gaps to prevent new records from being inserted. 2) In practical applications, by optimizing query and adjusting isolation levels, lock competition can be reduced and concurrency performance can be improved.


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

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

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

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment