第37次活动：2017-11-29（星期三晚上7:00-9:30）
select * from system_privilege_maporder by name;
select * from table_privilege_map;
select * from dba_roles;
select* from user$ where name='SH' ;
grant sysdba to hr;
grant sysdba to oe;
alter user oe identified by oracle_4U account unlock;
select* from dba_sys_privs sp where sp.privilege='CREATE TABLE';
revoke sysdba from oe;
select* from dba_sys_privs sp where sp.grantee='HR';
select* from dba_tab_privs tp where tp.grantee='HR';
select* from dba_role_privs rp where rp.grantee='HR';
grant select_catalog_role to hr;
select* from dba_users u
where u.username='SYS';
select* from dba_users u
where u.username='USER3';SQLPLUS：
# su - oracle
$ cd /u01/app/oracle/product/
$ ls
11.2.011.2.0.3
$ cd 11.2.0
$ ls
dbhome_1grid
$ cd dbhome_1/
$ ls
apex configdc_ocm has javavmlog odbc oui rdbms sqlplus usm
assistants crs deinstall hs jdbc md olap owb relnotes srvm utl
bin csmig demo ide jdev mesg OPatch owm root.sh station90.example.com_orclwwg
ccr css diagnosticsinstall jdk mgw opmn perl scheduler sysman xdk
cdata ctx dv instantclientjlib networkoracore plsql slax timingframework
cfgtoollogscv emcli inventory ldap nls oraInst.locprecompsqldeveloperucp
clone dbs EMStage j2ee lib oc4j ord racg sqlj uix
$ cd dbs
$ ls
hc_DBUA0.dathc_orcl.dathc_rcat.datinit.orainitorcl.oralkORCLorapworclorapwrcatsnapcf_orcl.fspfilercat.ora
$ vim orapworcl
$ vim orapworcl
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 29 20:00:11 2017
Copyright (c) 1982, 2011, Oracle.All rights reserved.
SQL> conn hr/oracle_4U@orcl as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL> conn hr/oracle_4U as sysdba
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user sys account lock;
User altered.
SQL> conn / as sysdba
Connected.
SQL> conn sys/oracle_4U@orcl as sysdba
Connected.
SQL> conn / as sysdba
Connected.
SQL> drop user sys;
drop user sys
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> drop user sys;
drop user sys
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> drop user system ;
drop user system
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'SYSTEM'
SQL> drop user system cascade ;
drop user system cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> conn system/oracle_$U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn system/oracle_4U
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user sys password expire;
User altered.
SQL> conn / as sysdba
Connected.
SQL> conn sys/oracle_4U@orcl as sysdba
Connected.
SQL> show parameter authen
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE
SQL> create user ops$oracle identified externally;
User created.
SQL> conn /
ERROR:
ORA-01045: user OPS$ORACLE lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to ops$oracle;
Grant succeeded.
SQL> conn /
Error accessing PRODUCT_USER_PROFILE
Warning:Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> show user
USER is "OPS$ORACLE"
SQL>
SQL>
SQL>
SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 7 days
Error accessing PRODUCT_USER_PROFILE
Warning:Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
$ cd /u01/app/oracle/product/11.2.0
$ ls
dbhome_1grid
$ cd dbhome_1/
$ ls
apex configdc_ocm has javavmlog odbc oui rdbms sqlplus usm
assistants crs deinstall hs jdbc md olap owb relnotes srvm utl
bin csmig demo ide jdev mesg OPatch owm root.sh station90.example.com_orclwwg
ccr css diagnosticsinstall jdk mgw opmn perl scheduler sysman xdk
cdata ctx dv instantclientjlib networkoracore plsql slax timingframework
cfgtoollogscv emcli inventory ldap nls oraInst.locprecompsqldeveloperucp
clone dbs EMStage j2ee lib oc4j ord racg sqlj uix
$ cd sql
sqldeveloper/ sqlj/ sqlplus/
$ cd sqlplus/
$ ls
adminbindoclibmesg
$ cd admin/
$ ls
glogin.sqlhelplibsqlplus.defplustrce.sqlpupbld.sql
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 29 20:21:49 2017
Copyright (c) 1982, 2011, Oracle.All rights reserved.
SQL> conn system/oracle_4U
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> @pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
DATE_VALUE FROM PRODUCT_USER_PROFILE
*
ERROR at line 3:
ORA-00980: synonym translation is no longer valid
DROP TABLE PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
DROP TABLE PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist
View created.
Grant succeeded.
Synonym dropped.
Synonym created.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
Synonym created.
Synonym dropped.
Synonym created.
SQL> conn /
Connected.
SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> !pwd
/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin
SQL> conn /
Connected.
SQL> show user
USER is "OPS$ORACLE"
SQL> conn / as sysdba
Connected.
SQL> conn /
Connected.
SQL> conn hr/oracle_4U@orcl
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> conn /@orcl
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn /
Connected.
SQL> conn / as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
$ echo $TNS_ADMIN
/u01/app/oracle/product/11.2.0/grid/network/admin
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 29 20:27:08 2017
Copyright (c) 1982, 2011, Oracle.All rights reserved.
SQL> conn /
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL> conn /
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> exit
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 29 20:27:35 2017
Copyright (c) 1982, 2011, Oracle.All rights reserved.
SQL> conn /
Connected.
SQL> conn / as sysdba
Connected.
SQL>
口令过期：
select * from dba_users u
where u.username='USER3';
alter user user3 account lock;
alter user user3 account unlock;
alter user user3 password expire;
select * from dba_users u
where u.username='USER3';
select* from dba_sys_privs sp where sp.grantee='USER3';
select* from dba_tab_privs tp where tp.grantee='USER3';
select* from dba_role_privs rp where rp.grantee='USER3';
grant create table to user3with adminoption ;
revoke create table from user3;
grant selecton employees to user3 with grant option;
revoke selecton employees from user3;
grant update (email) on employees to user3;
select* from dba_col_privsc
where c.grantee='USER3';
select* from dba_sys_privs sp where sp.grantee='USER3';
select* from dba_tab_privs tp where tp.grantee='USER3';
select* from dba_role_privs rp where rp.grantee='USER3';
select * from dba_roles order by 1;
create role role1;
select* from role_sys_privs rsp where rsp.role='ROLE1';
select * from role_tab_privs rtp where rtp.role='ROLE1';
select * from role_role_privs rrp where rrp.role='ROLE1';
grant select on hr.employees to role1;
grant update ( email )on hr.employees to role1;
grant role1 to user3 with admin option;
revoke role1 from user3;
revoke updateon hr.employees from role1;
revoke selecton hr.employees from role1;
grant select on hr.employees to role1;
create role role2 ;
grant role1 to role2;
grant role2 to user1;
select * from role_role_privs rrp where rrp.role='ROLE2';
select* from dba_role_privs rp
where rp.grantee='USER1';
alter user user1 default role connect;
select* from dba_roles r where r.role='ROLE2';
alter role role2 identified by oracle_4U;
grant role2 to user2;
select* from dba_role_privs rp
where rp.grantee='USER2';
alter user user1 default role none;
select* from role_sys_privs rsp where rsp.role='RESOURCE';
select * from role_tab_privs rtp where rtp.role='RESOURCE';
select * from role_role_privs rrp where rrp.role='RESOURCE';
grant resource to user2;
unlimited tablespace 彩蛋：
页:
[1]