关于oracle的dba身份登陆说明

oracle创建用户、创建表空间、授权、建表的完整过程

oracle创建⽤户、创建表空间、授权、建表的完整过程1.⾸先以sysdba的⾝份登录oracle conn /as sysdba 查询⽤户： select username from dba_users; 查询表空间 select username, default_tablespace from dba_users where username='⽤户名‘; 查询数据⽂件 select file_name, tablespace_name from dba_data_files;2.然后就可以来创建⽤户. create user ⽤户名 identified by 密码;3.修改⽤户的密码 alter user ⽤户名 identified by 新密码;4.创建⼀个表空间 create tablespace 表空间名 datafile '空间物理存储路径（f:\ts_zzg\zzg_data.dbf后缀随意）' size ⼤⼩（200M）;5.将表空间分配给⽤户 alter user ⽤户名 default tablespace 表空间名;6.为⽤户分配权限 grant create session,create table,create view,create sequence,unlimited tablespace to ⽤户名; grant all privileges to ⽤户名;（全部权限）7.查看所有⽤户所在的表空间 select username,default_tablespace from dba_users;8.查询⽤户所具有的权限 select *from session_privs;9.删除⽤户及其相关对象 drop user ⽤户名;。

Oracle 学习笔记 - SYSDBA登陆权限问题

4.Enter user-name:
5.
6.C:\>sqlplus wrong_user/wrong_password sysdba
7.ERROR:
8.ORA-01017: invalid username/password; logon denied
9.Enter user-name:
1.C:\>sqlplus / as sysdba
2.ERROR:
3.ORA-01031: insufficient privileges
4.Enter user-name:
5.
6.C:\>sqlplus sys/change_on_install as sysdba
With the OLAP and Data Mining options
SQL>
C:\>sqlplus wrong_user/wrong_password as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
14.With the OLAP and Data Mining options
15.SQL>
C:\>sqlplus / as sysdba
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
C:\>sqlplus wrong_user/wrong_password sysdba
测试一：ora_dba用户本地登陆

Oracle的操作系统身份认证

oracle安装用户本地sqlplus “/ as sysdba”无法登录
非oracle安装用户本机sqlplus “sys/change_on_install as sysdba”能登录
非oracle安装用户远程sqlplus “/ as sysdba_on_install@sid as sysdba”能登录
os认证：如果启用了os认证，以sysdba登录，那么我们只要用oracle软件的安装用户就能登录：sqlplus “/ as sysdba”。如果我们要禁用os认证，只利用密码文件登录，我们首先要有一个密码文件：
orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDoralocal.ora password=maximo entries=10;
设置初始化参数 REMOTE_LOGIN_PASSWORDFILE ：
在 Oracle 数据库实例的初始化参数文件中，此参数控制着密码文件的使用及其状态。它可以有以下几个选项：
1、NONE ：指示 Oracle 系统不使用密码文件，特权用户的登录通过操作系统进行身份验证；
2、EXCLUSIVE ：指示只有一个数据库实例可以使用此密码文件。只有在此设置下的密码文件可以包含有除 SYS 以外的用户信息，即允许将系统权限 SYSOPER /SYSDBA 授予除 SYS 以外的其他用户。此设置oracle 9i中为缺省值。有朋友说是shared,其实不然.
（3）REMOTE_LOGIN_NTICATION_SERVICES= (NTS)：
oracle安装用户本地sqlplus “/ as sysdba”能登录
非oracle安装用户本机sqlplus “sys/change_on_install as sysdba”无法登录

获取oracle数据库用户登陆和登出信息

--查询当前数据库数据文件存放位置select * from dba_data_files;--为记录会话登陆和登出信息的表单独创建一个表空间，避免此表的数据增长给其它业务系统的表空间带来影响。

--drop tablespace logon_info including contents and datafiles;create tablespace logon_info datafile '+OAUTFVG/noarcha/logon_inf01.dbf' size 16g autoextend off;--创建一个间隔分区表，每隔一天创建一个新的分区，便于清理存放于此表的会话登陆和登出信息。

create table erlogon_info(username varchar2(30),session_id number(10),host_name varchar2(30),last_module varchar2(30),logon_day date,logon_time varchar2(15),logoff_day date,logoff_time varchar2(10),elapsed_minutes number(10),program varchar2(50),osuser varchar2(50))partition by range(logon_day) interval(numtodsinterval(1,'day'))store in (logon_info)(partition p0 values less than (to_date('2018-09-13','YYYY-MM-DD')) tablespace logon_info);alter table erlogon_info add ip_address varchar2(30) default null;--创建会话登陆触发器，用户登陆立即在此表中新增一条相关记录。

oracle登录语句

oracle登录语句1. 使用Oracle登录语句Oracle是一种常用的关系型数据库管理系统，登录到Oracle数据库需要使用合适的登录语句。

以下是一些常见的Oracle登录语句：1) 使用用户名和密码登录：```sqlplus 用户名/密码@数据库实例名```其中，用户名是登录Oracle数据库的用户名，密码是对应的密码，数据库实例名是Oracle数据库的实例名。

2) 使用用户名和密码登录，指定连接端口：```sqlplus 用户名/密码@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=T CP)(HOST=主机名或IP)(PORT=端口号)))(CONNECT_DATA=(SID=数据库实例名)))```其中，主机名或IP是Oracle数据库所在的主机名或IP地址，端口号是Oracle数据库监听程序的端口号。

3) 使用用户名和密码登录，指定连接服务名：```sqlplus 用户名/密码@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=T CP)(HOST=主机名或IP)(PORT=端口号)))(CONNECT_DATA=(SERVICE_NAME=服务名)))```其中，主机名或IP是Oracle数据库所在的主机名或IP地址，端口号是Oracle数据库监听程序的端口号，服务名是Oracle数据库的服务名。

4) 使用用户名和密码登录，指定连接SID：```sqlplus 用户名/密码@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=T CP)(HOST=主机名或IP)(PORT=端口号)))(CONNECT_DATA=(SID=数据库实例名)))```其中，主机名或IP是Oracle数据库所在的主机名或IP地址，端口号是Oracle数据库监听程序的端口号，数据库实例名是Oracle数据库的实例名。

Oracle用户角色与权限控制

alter user system account unlock;
alter user system identified by abc123;
12.2 权限
权限（Privilege）的最终作用对象是用户。即所有用户在数据库内的操作对象和可执行的动作都是受到限制的。 Oracle中共有两种权限：系统权限和对象权限。
select * from system.t_employees order by employee_id;
12.3.2 继承角色
角色继承是指一个角色可以继承其他角色的权限信息，从而减少自身使用grant的机会。
create role role_hr;
grant role_employee to role_hr;
grant update, insert on t_employees to role_hr;
select table_name, grantee, grantor, privilege from dba_tab_privs where table_name = 'T_EMPLOYEES';
create user cat identified by abc;
grant create session to cat;
grant role_hr to cat;
update system.t_employees set status = 'CXL' where employee_id = 13;
12.3.3 禁用和启用角色
每个用户登录数据库时，都可以获得其默认角色。可以通过查询视图session_roles来获得当前会话下该用户的默认角色。管理员可以禁用用户的默认角色，一旦禁用，则用户从该角色获得的权限将不再有效。

关于oracle 用户去DBA权限的管理

为了规范数据库用户的操作，特制订如下规范：一、每个系统建立自己的数据库文件和索引文件，每个文件不允许超过2G，不支持自动扩展，系统根据自己的情况申请相应大小的数据文件；创建文件系统的命令为：（1）创建表空间Create tablespace coredb_data logging datafile'/home/oracle/oracle/product/10.2.0/oradata/orcl/coredb_data01.dbf'size 2G autoextend off（2）创建索引空间Create tablespace coredb_idx logging datafile'/home/oracle/oracle/product/10.2.0/oradata/orcl/coredb_idx01.dbf'size 1G autoextend off（3）增扩表空间alter tablespace coredb_idx add datafile'/home/oracle/oracle/product/10.2.0/oradata/orcl/coredb_idx02.dbf' size 2Gautoextend off（4）查看表空间select dbf.tablespace_name,dbf.totalspace "总量(M)",dbf.totalblocks as 总块数,dfs.freespace "剩余总量(M)",dfs.freeblocks "剩余块数",(dfs.freespace / dbf.totalspace) * 100 "空闲比例"from (select t.tablespace_name,sum(t.bytes) / 1024 / 1024 totalspace,sum(t.blocks) totalblocksfrom dba_data_files tgroup by t.tablespace_name) dbf,(select tt.tablespace_name,sum(tt.bytes) / 1024 / 1024 freespace,sum(tt.blocks) freeblocksfrom dba_free_space ttgroup by tt.tablespace_name) dfswhere trim(dbf.tablespace_name) = trim(dfs.tablespace_name)二、创建用户的命令使用ORACLE用户执行如下命令（无DBA权限）：sqlplus '/as sysdba' <<!drop user <user> cascade;create user <user> identified by <user> default tablespace coredb_data temporary tablespace temp;grant connect to <user>;grant resource to <user>;grant create session to <user>;grant create view to <user>;alter user <user> quota unlimited on coredb_idx;alter user <user> quota unlimited on coredb_data;!三、检查用户是否有DBA权限select * from sys.dba_role_privs where granted_role='DBA';GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---------------------- SYSTEM DBA YES YESGLSDB DBA YES YESSHENG DBA YES YESSYS DBA YES YES COREDB DBA YES YES四、去除DBA权限1）Sqlplus “/as sysdba”2）revoke dba from <user>;3）exit;五、用户的权限（1）查看用户权限；（2）增加用户权限；当系统报错：ORA-01031: 权限不足grant create any synonym to dhrep;//增加同义词权限（3）解除用户权限；五、异常情况的处理（1）ora-01045:user system lacks create session privilege; logon denied 解决办法：1）使用oracle 用户执行sqlplus “/as sysdba”2）grant create session,resource to <user>;3）exit;（2）ORA-1536：space quota exceeded for tablespace解决办法：1）使用oracle 用户执行sqlplus “/as sysdba”2）Grant unlimited tablespace to <user>;3）Exit;（3）ORA-01950: 对表空间'USERS' 无权限解决办法：1）使用oracle 用户执行sqlplus “/as sysdba”2）Conn <user>/<passwd>;3）select * from user_sys_privs;USERNAME PRIVILEGE ADM-----------------------------------------------------coredb CREATE SESSION NO4）grant connect,resource,create session,create view to glsdb_hn;5）select * from user_sys_privs;USERNAME PRIVILEGEADM------------------------ ---------------------------------------- ---coredb_HN CREATE VIEW NOcoredb_HN CREATE SESSION NOcoredb_HN UNLIMITED TABLESPACE NO 6）Exit;（4）没有DBA权限后，如果涉及跨db_user访问其他用户的，需要以下方法：1）创建存储过程将两个用户名作为参数传给存储过程：/*把用户（From_user）所有表的读写权限赋给用户（To_user）的存储过程为*/create or replace procedure grantUser(From_user in varchar2, To_user in varchar2) isretval number;scursor int;v_tablename VARCHAR2(60);sqlstr varchar2(200);cursor c_tablename is select table_name from dba_tables where owner=From_user; BEGINfor v_tablename in c_tablenameloopsqlstr := 'grant select,delete,update,insert on '|| From_user||'.'||v_tablename.table_name || ' to '||To_user;scursor := dbms_sql.open_cursor;dbms_sql.parse(scursor,sqlstr, dbms_sql.native);retval := dbms_sql.execute(scursor);dbms_sql.close_cursor(scursor);end loop;exceptionWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('发生其它错误!');END grantUser;//*把用户（From_user）所有表的读写权限从用户（To_user）收回的存储过程为*/ create or replace procedure revokeUser(From_user in varchar2, To_user in varchar2) isretval number;scursor int;v_tablename VARCHAR2(60);sqlstr varchar2(200);cursor c_tablename is select table_name from dba_tables where owner=From_user; BEGINfor v_tablename in c_tablenameloopsqlstr := 'revoke select,delete,update,insert on '||From_user||'.'||v_tablename.table_name || ' from '||To_user;scursor := dbms_sql.open_cursor;dbms_sql.parse(scursor,sqlstr, dbms_sql.native);retval := dbms_sql.execute(scursor);dbms_sql.close_cursor(scursor);end loop;exceptionWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('发生其它错误!');END revokeUser;2）执行存储过程（注意，存储过程的名称不能一样，否则会被替换）Execute grantUser / revokeUser;3）如果增加新的表，还需要充分执行同样的操作；。

Oracle Database Administration 2019 认证说明书

Introduction ................................................................................................................................................2Certification Details ....................................................................................................................................2Certification Benefits . (3)What IT Certification Offers What Oracle Certification OffersOracle Certification Innovation with Digital BadgingExam Preparation .......................................................................................................................................5Exam Topics (6)Oracle Database Administration I | 1Z0-082Oracle Database Administration II | 1Z0-083Sample Questions (13)Oracle Database Administration I | 1Z0-082Oracle Database Administration II | 1Z0-083Exam Registration Process .........................................................................................................................18Exam Score ................................................................................................................................................18Oracle Certification Program Candidate Agreement ...................................................................................19Oracle Certification Program Guidelines .. (19)Oracle Database Administration I & Oracle Database Administration IICertification Overview and Sample QuestionsOracle DatabaseIntroductionPreparing to earn the Oracle Database Administration 2019 Certified Professional certification helps candidates gain the skills and knowledge to install, patch and upgrade Oracle Database and Oracle Grid Infrastructure for a standalone server, create and manage a backup and recovery strategy using Recovery Manager (RMAN), use RMAN for Database duplication and transportation, diagnose failures using RMAN, and manage all aspects of Multitenant container databases, pluggable databases and application containers including creation, cloning, security, transportation and backup and recovery. The Administration I exam and recommended training focus on fundamental Database Administration topics such as understanding the database architecture, managing database instances, managing users, roles and privileges, and managing storage that lay the foundation for a Database Administrator entry-level job role. Additionally, the Admin I exam assumes knowledge of SQL.The Administration II exam and associated recommended training presents advanced topics such as multi-tenancy, backup and recovery, deploying, patching, and upgrading.Certification BenefitsWhat Oracle Certification OffersBy becoming a certified Oracle Database Administrator Professional , you demonstrate the full skill set needed to perform day to day administration of the Oracle Database.Preparing to take the Oracle Database certification exam broadens your knowledge and skills by exposing you to a wide array of important database features, functions and tasks. Oracle Database certification preparation teaches you how to perform complex, hands-on activities through labs, study and practice.Additionally, Oracle certification exams validate your capabilities using real-world, scenario-based questions that assess and challenge your ability to think and perform.What IT Certification OffersRecognitionof having required skillsExperienced a Greater Demandfor Their SkillsReceived Positive Impact onProfessional Imagethrough new skillsOpportunitiesSaid Certification was a Key Factor in Recent Raiseby peers and managementConfidence and RespectJanuary 2018 issue of Certification Magazine’s annual salary survey The kind of longevity suggests that earning and maintaining a certification can keep you moving for-ward in your career, perhaps indefinitely.73%65%71%January 2019 issue of Certification Magazine’s annual salary survey January 2019 issue of Certification Magazine’s annual salary surveyCertification that Signifies Y our Readiness to Perform Earned badges represent recognized skills and capabilitiesDisplay Y our Oracle Certification BadgeY ou‘ve earned it. Get the recognition you deserve.Modern Representation of Skills Tied to Real Time Job Markets View from your profile and apply to jobs that are matched to your skills; based on location, title, employers or salary rangeDisplay Y our AchievementA secure way to display and share your certification achievement • Industry Recognized • Credible • Role Based• Product Focused Across Database, Applications, Cloud, Systems, Middleware and Java• Globally one of the top 10 certifica-tion programs availableOracle Certification Innovation with Digital Badging Use Your Badge to Apply for JobsBenefitsOracle Certification Signifies a Candidate’s Readiness to Perform2019 Oracle Certified Professional Oracle Database AdministratorBoost Y our Professional ImageLearn MoreExam PreparationBy passing these exams, a certified individual proves fluency in and solid understanding of the skills required to be an Oracle Database Administrator.Recommendations to successfully prepare for Oracle Database Administration I | 1Z0-082 and Oracle Database Administration II | 1Z0-083 exams are:Attend Recommended Oracle T rainingThe courses below are currently available and are terrific tools to help you prepare not only for your exams, but also for your job as an Oracle Database Administrator.The new Oracle Database Administration Learning Subscription also helps you prepare for these exams with 24/7 access to continually updated training and hands-on labs and integrated certification.Recommended for 1Z0-082• O racle Database: Administration Workshop • Oracle Database: Introduction to SQL Recommended for 1Z0-083• O racle Database: Deploy, Patch and Upgrade Workshop• O racle Database: Backup and Recovery Workshop • O racle Database: Managing Multitenant Architecture• Oracle Database Administration: Workshop • O racle Database 19c: New Features for Administrators• O racle Database 18c: New Features forAdministrators (for 10g and 11g OCAs and OCPs)• O racle Database 12c R2: New Features for 12c R1 Administrators (12c R1 OCAs and OCPs)• O racle Database 11g: New Features for Administrators (for 10g OCAs and OCPs)The following topics are covered in the Oracle Database: Administration Workshop course.The following topics are covered in theOracle Database: Introduction to SQL course.The following topics are covered in the Oracle Database: Managing Multitenant Architecture Ed 1 course.The following topics are covered in the Oracle Database: Backup and Recovery Workshop course.The following topics are covered in the Oracle Database: Deploy, Patch and Upgrade Workshop course.The following topics are covered in the Oracle Database 19c: New Features for Administrators course.The following topics are covered in the Oracle Database: Administration Workshop course1. Which two statements are true about the Oracle Database server architecture?A. An Oracle Database server process represents the state of a user’s login to an instance.B. An Oracle Database server process is always associated with a session.C. Each server process has its own User Global Area (UGA).D. A connection represents the state of a user’s login to an instance.E. The entire data dictionary is always cached in the large pool.2. W hich two statements are true about the Oracle Database server during and immediatelyafter SHUTDOWN IMMEDIATE?A. New connection requests made to the database instance are refused.B. Uncommitted transactions are rolled back automatically.C. All existing connections to the database instance remain connected until all transactions eitherroll back or commit.D. Uncommitted transactions are allowed to continue to the next COMMIT.E. All existing transactions are aborted immediately.3. Which three statements are true about Oracle database block space management?A. A row can be migrated to a block in a different extent than the extent containing the originalblock.B. An insert statement can result in a migrated row.C. An update statement cannot cause chained rows to occur.D. A row can be migrated to a block in the same extent as the extent containing the originalblock.E. An insert statement can result in a chained row.1 2. 3.4. A n Oracle Database server session has an uncommitted transaction in progress whichupdated 5000 rows in one table.In which two situations does the transaction complete, thereby committing the updates?A. When a DDL statement is executed successfully by same user in a different session.B. When a DDL statement is executed successfully by the user in the same session.C. When a DML statement is executed successfully by same user in a different session.D. When a DML statement is executed successfully by the user in the same session.E. When a DBA issues a successful SHUTDOWN NORMAL statement and the sessionterminates normally.5.Which two statements are true about indexes and their administration in an Oracle database?A. An index can be scanned to satisfy a query without the indexed table being accessed.B. A non-unique index can be converted to a unique index using a Data Definition Language(DDL) command.C. A descending index is a type of bitmapped index.D. An invisible index is maintained when a Data Manipulation Language (DML) command is per-formed on its underlying table.E. An index is always created by scanning the key columns from the underlying table.6. Which two statements are true about sequences in a single instance Oracle database?A. Sequences that start with 1 and increment by 1 can never have gaps.B. A sequence can issue the same number more than once.C. Sequence numbers that are allocated require a COMMIT statement to make the allocationpermanent.D. A sequence can provide numeric values for more than one column or table.E. The data dictionary is always updated each time a sequence number is allocated.4. 5. 6.7. E xamine the description of the SALES table:Name Null? Type---------------------------- -------- --------------PRODUCT_ID NOT NULL NUMBER(10)CUSTOMER_ID NOT NULL NUMBER(10)TIME_ID NOT NULL DATECHANNEL_ID NOT NULL NUMBER(5)PROMO_ID NOT NULL NUMBER(5)QUANTITY_SOLD NOT NULL NUMBER(10,2)PRICE NUMBER(10,2)AMOUNT_SOLD NOT NULL NUMBER(10,2)The SALES table has 55,000 rows.Examine this statement:CREATE TABLE mysales (prod_id, cust_id, quantity_sold, price)ASSELECT product_id, customer_id, quantity_sold, priceFROM salesWHERE 1 = 2;Which two statements are true?A. MYSALES is created with no rows.B. MYSALES will have no constraints defined regardless of which constraints might be de-fined on SALES.C. MYSALES has NOT NULL constraints on any selected columns which had that constraintin the SALES table.D. MYSALES is created with 2 rows.E. MYSALES is created with 1 row.71. Which three are true about an application container?A. It always contains multiple applications.B. Two or more application PDBs in the same application container can share access to tables.C. It can have new application PDBs created by copying PDB$SEED.D. T wo or more application PDBs in the same application container can be given exclusive accessto some tables.E. It always has a new application PDBs created by copying PDB$SEED.F. It always contains a single application.2. RMAN has just been connected to a target database and the recovery catalog database.In which two cases would an automatic partial resynchronization occur between this target database’s control file and the RMAN recovery catalog?A. When any control file metadata for data file backups or image copies is now older thanCONTROL_FILE_RECORD_KEEP_TIME.B. When a new data file is added to a tablespace in a registered target database.C. When a backup of the current SPFILE is created.D. When the target is first registered.E. When any control file metadata for archive log backups or image copies is now older thanCONTROL_FILE_RECORD_KEEP_TIME.3. Which two are true about Oracle Grid Infrastructure for a Standalone Server?A. Oracle Restart can be used without using ASM for databases.B. Oracle Restart can attempt to restart a failed ASM instance automatically.C. It must be installed before Oracle Database software is installed.D. It must be installed after Oracle Database software is installed.E. It allows ASM binaries to be installed without installing Oracle Restart.F. It allows Oracle Restart binaries to be installed without installing ASM.1 2. 3.4. W hich two are true about creating container databases (CDBs) and pluggable databases (PDBs) inOracle 19c and later releases?A. A CDB can be duplicated using the Database Configuration Assistant (DBCA) in silent mode.B. A CDB can be duplicated using Recovery Manager (RMAN) with no configuration requiredbefore starting the duplication.C. A PDB snapshot must be a full copy of a source PDB.D. A PDB snapshot can be a sparse copy of a source PDB.E. A CDB can be duplicated only by using the Database Configuration Assistant (DBCA).5. Which two are true about the Oracle Optimizer?A. It requires system statistics when generating SQL execution plans.B. It always generates an index access operation when a statement filters on an indexed columnwith an equality operator.C. It ignores stale object statistics in the Data Dictionary.D. It can automatically re-optimize execution plans that were detected to be sub-optimal whenexecuting.E. It can re-write a statement internally in order to generate a more optimal plan.4. 5.Exam Registration ProcessOracle exams are delivered through the independent company Pearson VUE. Create a Pearson VUE loginOracle Certification Program Candidate AgreementIn order to take your Oracle certification, you will need to agree to the Oracle Certification Program Candidate Agreement. Please review this document by going here.Oracle Certification Program GuidelinesLearn more about Oracle Certification policies here.This certification overview and sample questions were created in June 2019. The content is subject to change,please always check the web site for the most recent information regarding certifications and related exams: /certification。

oracle_创建create_user_及授权grant_查看登陆的用户及更改用户默认表空间

oracle 创建create user 及授权grant 查看登陆的用户：以下都可以：show user;select sys_context('userenv','session_user') from dual;select user from dual;查看所有登录的用户必须为DBA 用户：select username from v$session;sys、system等DBA 用户查看其他用户(test)中的对象(表)：SQL> select * from test.student;创建一个普通用户都把该用户用起来的流程：1、创建用户SQL>create user test indentified by test;这样就创建了一个用户名密码都为test的用户但这个时候test还是不能登陆成功的，我们需要赋予相应的权限2、赋予create session的权限SQL>grant create session to test;这样test用户就能成功登陆进去但是此时用户还是不能创建表我们需要赋予用户创建表的权限：SQL>grant create table to test;但是用户此时还不能创建表因为需要有使用表空间的权限（相当于用户有了进房间的钥匙但是没有进大门的钥匙。

）所以也应该赋予相应的权限SQL>grant unlimited tablespace to test;这个时候用户就拥有了创建表的权限由于表是用户test的相应的他就拥有了对创建的表的增删查改的权限了3、查看用户拥有什么权限可以通过查询一个系统的视图(数字字典)SQL>select * from user_sys_privs;这样就可以知道当前用户的权限4、撤销权限SQL> revoke create table from test;-----------------------------一些常用视图的区分dba_tables dba_all_tables user_tables user_all_tables all_tables all_all_tables当前用户所属的所有表(注意大写)SQL> select tablespace_name,table_name from user_all_tables where table_name='STUDENT'; SQL> select table_name,tablespace_name from user_tables where table_name='STUDENT'; TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------STUDENT USERSsys 要查看dba_all_tables,ALL_ALL_TABLES才能查看到test 用户的表。

Oracle数据库等级保护实施指导书(二级)

2.1版第0次修订Oracle数据库等级保护实施指导书（二级）序号控制点测评项操作步骤预期结果数据库依托的操作系统数据库具体版本检查应用使用哪个账户11身份鉴别a)应对登录操作系统和数据库系统的用户进行身份标识和鉴别；应检查Oracle数据库系统，查看是否存在空口令或默认口令的用户（Oracle默认满足，但应防止口令出现空口令或默认口令情况）。

默认口令，如：SYS/CHANGE_ON_INSTALLSYSTEM/MANAGER常用口令：sys:oracle/admin;system:oralce/admin管理口令由谁掌握Select*from dba_usersSelect*from all_users1、对登陆操作系统和数据库系统的用户进行身份鉴别2、不得使用默认用户和默认口令2.1版第0次修订Select*from user_usersb)操作系统和数据库系统管理用户身份鉴别信息应具有不易被冒用的特点，口令应有复杂度要求并定期更换；1、确定用户使用的profile：select username,profile from dba_userswhere account_status=’OPEN’2、查看是否启用口令复杂度函数，执行命令：select*from dba_profileswhere profile='DEFAULT'andresource_name='PASSWORD_VERIFY_FUNCTION'2、检查utlpwdmg.sql中“--Check for the minimum length of thepassword”部分中“length(password)<“后的值3、select*from dba_profiles where profile='DEFAULT'andresource_name='PASSWORD_LIFE_TIME'参考路径$ORACLE_HOME/rdbms/admin/utlpwdmg.sql1、口令由数字、大小写字母、符号混排、无规律方式2、用户口令的长度至少为8位3、口令每季度更换一次，更新的口令至少5次内不能重复如：1、返回结果应该为VERIFY_FUNCTION2、length(password)<后的值至少为8c)应启用登录失败处理功能，可采取结束会话、限制非法登录次数和自动退出等措施；1、执行命令：select limit from dba_profiles where profile='DEFAULT'and resource_name='FAILED_LOGIN_ATTEMPTS'。

Oracle用户及权限管理

25
2014年5月28日星期三
Oracle数据库管理
Oracle 数据库管理
课堂训练-3
1、设置用户密码失效 2、并测试是否修改成功
26
2014年5月28日星期三
Oracle数据库管理
Oracle 数据库管理
锁定和解除用户锁定
如果要禁止某个用户访问Oracle 系统，那么最好的方式是锁定该用户，而不是删除该用户。锁定用户并不影响该用户所拥有的对象和权限，这些对象和权限依然存在，只是暂时不能以该用户的身份访问系统。当锁定解除后，该用户可以正常地访问系统、按照自己原有的权限访问各种对象。
Oracle 数据库管理
本讲目标
能初步掌握用户的管理能初步掌握用户权限的管理能初步掌握角色的管理
3
2014年5月28日星期三
Oracle数据库管理
Oracle 数据库管理
1、用户与模式的关系
用户：Oracle用户是用于连接数据库和访问数据库对象的。模式（方案）：是用户所拥有的数据库对象的集合，模式对象是数据库数据的逻辑结构
其中
在创建用户时，创建者必须具有create user系统权限。
user_name指定将要创建的新数据库用户名称。 Password指定该新数据库用户的密码。 def_tablespace指定存储该用户所创建对象的默认表空间。 temp_tablespace指定存储临时对象的默认表空间。
18
15
2014年5月28日星期三
Oracle数据库管理
Oracle 数据库管理
oracle的normal 、 sysdba 、 sysoper 有什么区别？
注意：system 如果正常登录，它其实就是一个普通的 dba 用户，但是如果以 as sysdba 登录，其结果实际上它是作为 sys 用户登录的。其他用户也是一样，如果 as sysdba 登录，也是作为 sys 用户登录的。

Oracle新建用户、角色,授权,建表空间

Oracle新建用户、角色，授权，建表空间oracle数据库的权限系统分为系统权限与对象权限。

系统权限( database systemprivilege )可以让用户执行特定的命令集。

例如，create table权限允许用户创建表，grant any privilege 权限允许用户授予任何系统权限。

对象权限( database object privilege )可以让用户能够对各个对象进行某些操作。

例如delete权限允许用户删除表或视图的行，select权限允许用户通过select从表、视图、序列(sequences)或快照(snapshots)中查询信息。

每个oracle用户都有一个名字和口令,并拥有一些由其创建的表、视图和其他资源。

oracle角色(role)就是一组权限(privilege)(或者是每个用户根据其状态和条件所需的访问类型)。

用户可以给角色授予或赋予指定的权限，然后将角色赋给相应的用户。

一个用户也可以直接给其他用户授权。

一、创建用户oracle内部有两个建好的用户：system和sys。

用户可直接登录到system用户以创建其他用户，因为system具有创建别的用户的权限。

在安装oracle时，用户或系统管理员首先可以为自己建立一个用户。

例如：Sql代码create user user01 identified by u01;该命令还可以用来设置其他权限，详细情况参见自学资料。

要改变一个口令，可以使用alter user命令： alter user user01 identified by usr01; 现在user01的口令已由“u01”改为“usr01”。

Oracle Access Manager 配置指南说明书

Configuring Oracle HTTP Server WebGate....................................................................................... 5 Registering Agents for Oracle Access Manager Server .................................................................... 6 Enabling the Oracle Identify Federation Services............................................................................. 7
EnterpriseTrack OAM Configuration Guide 16 R1
June t Configuring Oracle Access Manager....................................................................................... 5 Prerequisites..................................................................................................................................... 5
Where: <Webgate_Home> is the Webgate Home directory. For example, /u01/app/Oracle/Middleware/Oracle_OAMWebGate1. <MW_HOME> is oracle middleware home directory, For example, /u01/app/Oracle/Middleware 2) Go to <Webgate_Home>/webgate/ohs/tools/deployWebGate. 3) Run the following: deployWebgateInstance.sh -w <Webgate_Instance_Directory> -oh <Webgate_Oracle_Home> where:<Webgate_Instance_Directory> is the location of Webgate Instance Home <Webgate_Oracle_Home> is the directory where Oracle HTTP Server Webgate is installed and created as the Oracle Home for Webgate. For example, run the following: deployWebgateInstance.sh -w <MW_HOME>/Oracle_WT1/instances/instance1/config/OHS/ohs1 -oh <MW_HOME>/Oracle_OAMWebGate1

[指南]Oracle内置角色connect与resource的权限

Oracle内置角色connect与resource的权限.txt懂得放手的人找到轻松，懂得遗忘的人找到自由，懂得关怀的人找到幸福！女人的聪明在于能欣赏男人的聪明。

生活是灯，工作是油，若要灯亮，就要加油！相爱时，飞到天边都觉得踏实，因为有你的牵挂；分手后，坐在家里都觉得失重，因为没有了方向。

Oracle内置角色connect与resource的权限首先用一个命令赋予user用户connect角色和resource角色：grant connect,resource to user;运行成功后用户包括的权限:CONNECT角色： --是授予最终用户的典型权利，最基本的ALTER SESSION --修改会话CREATE CLUSTER --建立聚簇CREATE DATABASE LINK --建立数据库链接CREATE SEQUENCE --建立序列CREATE SESSION --建立会话CREATE SYNONYM --建立同义词CREATE VIEW --建立视图RESOURCE 角色： --是授予开发人员的CREATE CLUSTER --建立聚簇CREATE PROCEDURE --建立过程CREATE SEQUENCE --建立序列CREATE TABLE --建表CREATE TRIGGER --建立触发器CREATE TYPE --建立类型从dba_sys_privs里可以查到（注意这里必须以DBA角色登录）:select grantee,privilege from dba_sys_privswhere grantee='RESOURCE' order by privilege;GRANTEE PRIVILEGE------------ ----------------------RESOURCE CREATE CLUSTERRESOURCE CREATE INDEXTYPERESOURCE CREATE OPERATORRESOURCE CREATE PROCEDURERESOURCE CREATE SEQUENCERESOURCE CREATE TABLERESOURCE CREATE TRIGGERRESOURCE CREATE TYPE=================================================一、何为角色？我在前面的篇幅中说明权限和用户。

Oracle数据库创建用户的命令和方法

Oracle 权限设置一、权限分类：系统权限：系统规定用户使用数据库的权限。

（系统权限是对用户而言)。

实体权限：某种权限用户对其它用户的表或视图的存取权限。

（是针对表或视图而言的）。

二、系统权限管理：1、系统权限分类：DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户：授予connect, resource权限。

对于DBA管理用户：授予connect，resource, dba权限。

2、系统权限授权命令：[系统权限只能由DBA用户授出：sys, system(最开始只能是这两个用户)]授权命令：SQL> grant connect, resource, dba to 用户名1 [,用户名2]...;[普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system 用户的权限也可以被回收。

]例：SQL> connect system/managerSQL> Create user user50 identified by user50;SQL> grant connect, resource to user50;查询用户拥有哪里权限：SQL> select * from dba_role_privs;SQL> select * from dba_sys_privs;SQL> select * from role_sys_privs;删除用户：SQL> drop user 用户名 cascade; //加上cascade则将用户连同其创建的东西全部删除3、系统权限传递：增加WITH ADMIN OPTION选项，则得到的权限可以传递。

系统中用户权限的赋予

Oracle系统中用户权限的赋予,查看和管理.在Oracle数据库中,用户的权限分为两种(在这里我们不讨论dba或oper的权限,只考虑普通用户的权限),分别是System Privilege系统权限和User Table Privilege用户数据表权限.1.首先,创建用户,以下几条命令可以创建一个用户,前提是必须以DBA的身份登录(如果你不是DBA,不要看下去了):create user DB_USER identified by DB_USER_PW '创建用户DB_USER,密码为DB_USER_PW grant create session to DB_USER '给用户创建会话的权限grant resource to DB_USER2.当用户建立后,会自动在Oracle数据库系统中生成属于该用户的Scheme (可以理解为所有属于该用户的表,视图....等对象的集合).该用户可以将对这些对象的访问权限赋予其它的系统用户.3.该用户用sqlplus登录后,以下命令可以看到该用户的权限(该部分取自于CNOUG网站):本用户读取其他用户对象的权限:select * from user_tab_privs;本用户所拥有的系统权限:select * from user_sys_privs;ORACLE数据库用户与权限管理ORACLE是多用户系统，它允许许多用户共享系统资源。

为了保证数据库系统的安全，数据库管理系统配置了良好的安全机制。

2. 1 ORACLE数据库安全策略建立系统级的安全保证系统级特权是通过授予用户系统级的权利来实现，系统级的权利（系统特权）包括：建立表空间、建立用户、修改用户的权利、删除用户等。

系统特权可授予用户，也可以随时回收。

ORACLE系统特权有80多种。

建立对象级的安全保证对象级特权通过授予用户对数据库中特定的表、视图、序列等进行操作（查询、增、删改）的权利来实现。

Oracle数据库的用户和权限管理

5.2.2 创建角色
使用CREATE ROLE语句可以创建一个新的角色，执行该语句的用户必须具有CREATE ROLE系统权限。
在角色刚刚创建时，它并不具有任何权限，这时的角色是没有用处的。因此，在创建角色之后，通常会立即为它授予权限。例如：利用下面的语句创建了一个名为OPT_ROLE的角色，并且为它授予了一些对象权限和系统权限：
ORACEL利用角色更容易地进行权限管理。有下列优点：（1）减少权限管理，不要显式地将同一权限组授权给几个用户，只需将这权限组授给角色，然后将角色授权给每一用户。（2）动态权限管理，如果一组权限需要改变，只需修改角色的权限，所有授给该角色的全部用户的安全域将自动地反映对角色所作的修改。（3）权限的选择可用性，授权给用户的角色可选择地使其可用或不可用。（4）应用可知性，当用户经用户名执行应用时，该数据库应用可查询字典，将自动地选择使角色可用或不可用。（5）应用安全性，角色使用可由口令保护，应用可提供正确的口令使用角色，如不知其口令，不能使用角色。
系统权限可授权给用户或角色，一般，系统权限只授予管理人员和应用开发人员，终端用户不需要这些相关功能。 2）对象权限：在指定的表、视图、序列、过程、函数或包上执行特殊动作的权利。
2.角色为相关权限的命名组，可授权给用户和角色。数据库角色包含下列功能：（1）一个角色可授予系统权限或对象权限。（2）一个角色可授权给其它角色，但不能循环授权。（3）任何角色可授权给任何数据库用户。（4）授权给用户的每一角色可以是可用的或者不可用的。一个用户的安全域仅包含当前对该用户可用的全部角色的权限。（5）一个间接授权角色对用户可显式地使其可用或不可用。在一个数据库中，每一个角色名必须唯一。角色名与用户不同，角色不包含在任何模式中，所以建立角色的用户被删除时不影响该角色。