※ SYS,SYSTEM,scott사용자
- 오라클 설치시 자동적으로 생성됨
- SYS : DBMS의 데이터딕셔너리소유자, 오라클데이터베이스관리자(super user)
디폴트패스워드 - change_on_install
DB생성 가능
- SYSTEM : SQL*Forms등의 툴을 위한 데이터딕셔너리소유자
모든 권한이 SYS와 같으나 DB생성 권한은 없음
디폴트패스워드 - manager
- scott : sample 사용자계정
디폴트패스워드 - tiger
오라클 sys, system암호를 분실한 경우
C:>sqlplus "/as sysdba"
SQL> show user
USER is "SYS"
암호를 원하는 대로 설정합니다.
SQL> alter user sys identified by 암호;
SQL> alter user system identified by 암호;
접속
SQL> connect sys/암호 as sysdba
SQL> connect systemp/암호
Create a new user.
Give the new user permission to connect and to create their own tables etc.
| Specific to Oracle |
|
You should specify the "tablespace" - if you don't it defaults to SYS which causes a whole world of pain. The CONNECT and RESOURCE grants permit users to connect to the system and to create stuff. |
CREATE USER scott IDENTIFIED BY tiger
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE users;
GRANT CONNECT TO scott;
GRANT RESOURCE TO scott
GRANT DBA TO scott;
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE users;
GRANT CONNECT TO scott;
GRANT RESOURCE TO scott
GRANT DBA TO scott;
Read tables from another schema/database
A particular server may support a number of different sets of tables. In Oracle these are schemas in MySQL they are databases. In both cases each user normally has their own set of tables, other users tables may be accessed using a dot notation.
| Specific to Oracle |
| haggis is another user with a table called tat. |
SELECT COUNT(*) FROM haggis.tat
Change the default schema/database.
In many engines several independant databases may exist. Often each user has his or her own database. This allows different users to use the same names for tables.
| Specific to Oracle |
| In Oracle this is called a "schema". The new schema is called haggis - it belongs to a user called haggis. |
Find another process and kill it.
Sometimes users set off queries that may take a very long time to complete.
We may want to find such long running processes and stop them. Some kind of administrative account is usually required.
SELECT sid, serial#, username,
TO_CHAR(logon_time,'Month dd hh24:mi:ss')
FROM sys.v_$session;
ALTER SYSTEM KILL SESSION '12,33'
-- 12,33 is the sid and serial#
Set a timeout.
Users may accidentally (or deliberately) start queries which would take a very long time to complete. We can set a 'timeout'; this means that the system gives up after a certain amount of time.
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
CREATE PROFILE gisqprofile
LIMIT CONNECT_TIME 1
;
ALTER USER scott PROFILE gisqprofile;
Change my own password
Users should be able to change their own passwords. The administrator should be able to change other people's passwords.
WARNING
Many of these commands will work and change the password for the user scott. This will prevent you and other users from using SQLZoo - if you do change it please set the password back by visiting the appropriate fix page.
| Specific to Oracle |
From with sqlplus the command is password |
| Creating Oracle (Password) Authenticated User | ||||||||||
Dependencies |
proxy_users - users who can assume the identity of other users | |||||||||
| System Privileges |
become user - allows grantee to act as any other user | |||||||||
| Default users and passwords | col user_name format a30 col pwd_verifier format a20 SELECT * FROM default_pwd$ ORDER BY 1; | |||||||||
Changing DBSNMP Password |
1.0 Stop the standalone dbconsole on UNIX/Linux $ emctl stop dbconsole on Windows stop the Oracle<oracle_home_name>DBConsole<SID> service or open a DOS Command Window and set the ORACLE_HOME and ORACLE_SID environment variables. Then: C:\> emctl stop dbconsole 2.0 Verify the standalone dbconsole and the emagent are stopped on Unix $ emctl status dbconsole $ emctl status agent on Windows C:\> emctl status dbconsole C:\> emctl status agent 3.0 Connect to the database as a user with DBA privilege with SQL*Plus and execute SQL> alter user dbsnmp identified by <new_password>; 4.0 Verify the new password is valid SQL> connect dbsnmp/<new_password>[@database_alias] 5.0 Go to $ORACLE_HOME/host_sid/sysman/emd 5.1 Save the file targets.xml to targets.xml.orig 5.2 Open the file targets.xml and search for the line: <Property NAME="password" VALUE="<encrypted_string>" ENCRYPTED="TRUE"/> Replace the encrypted value by the new password value Replace TRUE by FALSE 6.0 Restart the standalone dbconsole on Unix $ emctl start dbconsole on Windows Start the Windows Service Oracle<oracle_home_name>DBConsole<SID> or open a DOS Command Window and type: C:\> set ORACLE_SID=<The SID of the database monitored by the dbconsole> C:\> set ORACLE_HOME=<ORACLE_HOME of the database> C:\> cd %ORACLE_HOME%/bin C:\> emctl start dbconsole 7.0 Check that the password has been encrypted Open the file targets.xml and search for the line: <Property NAME="password" VALUE="<encrypted_string>" ENCRYPTED="TRUE"/> Check that the password VALUE is encrypted Check that the value of ENCRYPTED is TRUE | |||||||||
Changing SYSMAN Password |
1.0 Stop the standalone dbconsole on Unix $ emctl stop dbconsole on Windows Stop the Windows Service Oracle<oracle_home_name>DBConsole or open a DOS Command Window and type: C:\> emctl stop dbconsole 2.0 Check that the standalone dbconsole is stopped on Unix $ emctl status dbconsole on Windows check the status of the Windows Service Oracle<oracle_home_name>DBConsole or open a DOS Command Window and type: C:\> emctl status dbconsole 3.0 Connect to the database as a user with DBA privilege with SQL*Plus and execute SQL> alter user sysman identified by <new_password>; 4.0 Check the new password SQL> connect sysman/<new_password>[@database_alias] 5.0 Go to $ORACLE_HOME/host_sid/sysman/config 5.1 Save the file emoms.properties to emoms.properties.orig 5.2 Edit the file emoms.properties a. Search for the line beginning with: oracle.sysman.eml.mntr.emdRepPwd= Replace the encrypted value by the new password value b. Search for the line: oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE Replace TRUE by FALSE 6.0 Restart the standalone dbconsole on Unix $ emctl start dbconsole on Windows Start the Windows Service Oracle<oracle_home_name>DBConsole or open a DOS Command Window and type: C:\> emctl start dbconsole 7.0 Check that the password has been encrypted Edit the file emoms.properties 7.1 Search for the line beginning with: oracle.sysman.eml.mntr.emdRepPwd= Check that the password is encrypted 7.2 Search for the line beginning with: oracle.sysman.eml.mntr.emdRepPwdEncrypted= Check that the value is TRUE | |||||||||
| Create User Authenticated by Password | ||||||||||
| Simple Password | CREATE USER <user_name> IDENTIFIED BY <password> | |||||||||
| CREATE USER oracle1 IDENTIFIED BY oracle1; SELECT username, password, created, password_versions FROM dba_users ORDER BY 1; | ||||||||||
| Create User with Complex Password | CREATE USER <user_name> IDENTIFIED BY "<password>" | |||||||||
| CREATE USER oracle2 IDENTIFIED BY "N0t!4N0W" | ||||||||||
Include Access To A Default Tablespace Thanks Teresa Robinson for the correction |
CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name>; | |||||||||
| SELECT tablespace_name FROM dba_tablespaces WHERE contents NOT IN ('TEMPORARY', 'UNDO') AND tablespace_name NOT IN ( SELECT tablespace_name FROM dba_rollback_segs) AND tablespace_name NOT LIKE 'SYS%'; CREATE USER oracle3 IDENTIFIED BY oracle3 DEFAULT TABLESPACE uwdata; SELECT username, default_tablespace FROM dba_users ORDER BY 1; | ||||||||||
Include Access To A Temporary Tablespace |
CREATE USER <user_name> IDENTIFIED BY <password> TEMPORARY TABLESPACE <temporary_tablespace_name>; | |||||||||
| SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY'; CREATE USER oracle4 IDENTIFIED BY oracle4 DEFAULT TABLE uwdata TEMPORARY TABLESPACE temp; SELECT username, default_tablespace, temporary_tablespace FROM dba_users ORDER BY 1; | ||||||||||
Include Quota On Tablespaces |
CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> QUOTA <quota_amount> ON <tablespace_name> QUOTA <quota_amount> ON <tablespace_name>; | |||||||||
| CREATE USER oracle5 IDENTIFIED BY oracle5 DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE temp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON uwdata QUOTA 10M ON data_med; SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas ORDER BY 1; | ||||||||||
Include Profile Follow PROFILE link at page bottom for more information |
CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name>; | |||||||||
| SELECT DISTINCT profile FROM dba_profiles; CREATE USER oracle6 IDENTIFIED BY "N0Way!" DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE temp QUOTA 0 ON SYSTEM QUOTA 0 ON SYSAUX QUOTA UNLIMITED ON uwdata PROFILE monitoring_profile; SELECT username, profile FROM dba_users ORDER BY 1; | ||||||||||
Expire the password on creation |
CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> PASSWORD EXPIRE; | |||||||||
| CREATE USER oracle7 IDENTIFIED BY oracle7 DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON uwdata PASSWORD EXPIRE; SELECT username, expiry_date, account_status FROM dba_users; | ||||||||||
Lock or unlock the account on creation |
CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> ACCOUNT <LOCK | UNLOCK>; | |||||||||
| CREATE USER oracle8 IDENTIFIED BY oracle8 DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON uwdata ACCOUNT LOCK; SELECT username, lock_date, account_status FROM dba_users; | ||||||||||
Other user creation defaults |
set linesize 121 SELECT username, initial_rsrc_consumer_group, editions_enabled FROM dba_users ORDER BY 1; conn uwclass/uwclass col service_name format a20 SELECT schemaname, service_name FROM gv$session ORDER BY 1; | |||||||||
| Creating Operating System Authenticated User | ||||||||||
Changes to make for external authentication |
1. Set the initSID.ora parameters: remote_os_authent=TRUE os_authent_prefix = "OPS$" 2. Generate a new spfile CREATE spfile FROM pfile='initorabase.ora'; 3. Add the following to the sqlnet.ora sqlnet.authentication_services = (NTS) | |||||||||
The syntax for CREATE USER where authentication is performed by the operating system on the server |
CREATE USER <user_name> IDENTIFIED EXTERNALLY; | |||||||||
| Step 1: Connect as system/manager in SQL*Plus and create the Oracle user: CREATE USER ops$oracle IDENTIFIED EXTERNALLY; SELECT username, password, external_name FROM dba_users ORDER BY 1; GRANT create session TO ops$oracle; Step 2: Create a user in the operating system named oracle if one does not already exist. Step 3: Go to command line (terminal window in UNIX, cmd in Windows. Type 'sqlplus' (without the single quotes). You should be connected to the database without having to enter username/password. | ||||||||||
The syntax for CREATE USER where authentication is performed by the operating system on the client |
CREATE USER <machine_name\user_name> IDENTIFIED EXTERNALLY; | |||||||||
| Step 1: Connect as system/manager in SQL*Plus and create the Oracle user: CREATE USER "PC100\USER" IDENTIFIED EXTERNALLY; where PC100 is the name of the client computer. Then GRANT CREATE SESSION TO "PC100\USER"; 2 - Create a user in Windows named USER. 3 - Log on Windows as USER and go to the C:\> command line. Type 'sqlplus' (without the single quotes). You should be connected to your database without having to enter any username/password. | ||||||||||
| Note: | Automatic logins by PC, Apple MacIntosh, and OS/2 users are not secure. Anyone can edit the Oracle configuration file and change their user ID. For security reasons, if users of these systems are logging in over the network, Oracle Corporation strongly recommends you disable the ops$ logins in the listener.ora. | |||||||||
| Group membership in UNIX | Operating system accounts that are members of the operating system's DBA group are not required to provide a userid and password when logging in. | |||||||||
| DBA | ||||||||||
| Group membership in Windows | Operating system accounts that are members of the operating system's ORA_DBA group are not required to provide a userid and password when logging in. | |||||||||
| ORA_DBA | ||||||||||
| Alter User | ||||||||||
| Change The Password | ALTER USER <user_name> IDENTIFIED BY <new_password>; | |||||||||
| ALTER USER SYS IDENTIFIED BY "N0t!A!Chance"; | ||||||||||
| View Password Hashes | Current Password: SELECT name, password FROM user$; | |||||||||
| Previous Passwords (requires Profile verify function is active): SELECT u.name, h.password, h.password_date FROM user$ u, user_history$ h WHERE u.user# = h.user#; | ||||||||||
| Grant Access To A Tablespace | ALTER USER <user_name> QUOTA <quota_amount> ON <tablespace_name>; | |||||||||
| ALTER USER uwclass QUOTA 100K ON XDB; | ||||||||||
| Revoke Access From A Tablespace | ALTER USER <user_name> QUOTA 0 ON <tablespace_name>; | |||||||||
| ALTER USER uwclass QUOTA 0 ON XDB; | ||||||||||
| Lock An Account | ALTER USER <user_name> ACCOUNT LOCK; | |||||||||
| ALTER USER uwclass ACCOUNT LOCK; | ||||||||||
| Unlock An Account | ALTER USER <user_name> ACCOUNT UNLOCK; | |||||||||
| ALTER USER uwclass ACCOUNT UNLOCK; | ||||||||||
Change Password Based on Hash |
ALTER USER <user_name> ACCOUNT IDENTIFIED BY VALUES '<password_hash'>; | |||||||||
| SELECT password FROM user$ WHERE name = 'SCOTT'; ALTER USER scott IDENTIFIED BY XYZ; SELECT password FROM user$ WHERE name = 'SCOTT'; ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67'; conn scott/tiger | ||||||||||
| Proxy Clause | ||||||||||
| Grant Proxy with Password | ALTER USER <user_name> GRANT CONNECT THROUGH <app_server_userid> AUTHENTICATED USING PASSWORD; | |||||||||
| ALTER USER app_user GRANT CONNECT THROUGH uwweb AUTHENTICATED USING PASSWORD; | ||||||||||
| Grant Proxy with Distinguished Name | ALTER USER <user_name> GRANT CONNECT THROUGH <app_server_userid> AUTHENTICATED USING DISTINGUISHED NAME; | |||||||||
| ALTER USER app_user GRANT CONNECT THROUGH uwweb AUTHENTICATED USING DISTINGUISHED NAME; | ||||||||||
| Grant Proxy with Role | ALTER USER <user_name> GRANT CONNECT THROUGH <app_server_userid> WITH ROLE <role_name>; | |||||||||
| ALTER USER app_user GRANT CONNECT THROUGH uwweb WITH ROLE CONNECT; or ALTER USER app_user GRANT CONNECT THROUGH uwweb WITH ROLE ALL EXCEPT payroll; | ||||||||||
| Grant Proxy based on Authenticating Certificate | ALTER USER <user_name> GRANT CONNECT THROUGH <app_server_userid> AUTHENTICATED USING CERTIFICATE TYPE <name> VERSION <version_no>; | |||||||||
| ALTER USER appuser GRANT CONNECT THROUGH uwweb AUTHENTICATED USING CERTIFICATE TYPE 'X.509' VERSION '3'; | ||||||||||
| Drop Proxy User | DROP USER <user_name> REVOKE CONNECT THROUGH <app_server_userid>; | |||||||||
| ALTER USER app_user REVOKE CONNECT THROUGH uwweb; | ||||||||||
| Drop User | ||||||||||
| Drop User Without Objects | DROP USER <user_name>; | |||||||||
| DROP USER uwclass; | ||||||||||
| Drop User With Objects | DROP USER <user_name> CASCADE; | |||||||||
| DROP USER uwclass CASCADE; | ||||||||||
| User Related Queries | ||||||||||
| View Memory Use for Each User Session | SELECT username, value || 'bytes' "Current UGA memory" FROM v_$session sess, v_$sesstat sstat, v_$statname sname WHERE sess.sid = sstat.sid AND sstat.statistic# = sname.statistic# AND sname.name = 'session uga memory'; | |||||||||
| Active Connected Users | SELECT COUNT(*) "ACTIVE USERS" FROM v_$session WHERE username IS NOT NULL; | |||||||||
Currently Connected Users |
SELECT SUBSTR(v_$session.USERNAME,1,15) USERNAME, SUBSTR(s.status,1,8) STATUS, SUBSTR(s.server,1,10) SERVER, SUBSTR(s.type,1,10) TYPE, SUBSTR(s.event,1,20) "WAIT EVENT", DECODE(s.command, 1,'Create Table', 2,'Insert', 3,'Select', 6,'Update', 7,'Delete', 8,'Drop', 9,'Create Index', 10,'Drop Index', 12,'Drop Table', 17,'Grant', 26,'Lock Table', 42,'Alter Session', 43,'Alter User', 44,'Commit', 45,'Rollback', s.command) COMMAND FROM v_$session s, v_$session_wait w WHERE (s.sid = w.sid) AND s.username != 'SYS' ORDER BY s.username; | |||||||||
User Information |
set linesize 121 col username format a10 col profile format a10 col "tmp tbs" format a10 SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS", u.profile, r.granted_role, r.admin_option, r.default_role FROM sys.dba_users u, sys.dba_role_privs r WHERE u.username = r.grantee (+) GROUP BY u.username, u.default_tablespace, u.temporary_tablespace, u.profile, r.granted_role, r.admin_option, r.default_role; | |||||||||
Identify Current Session |
SELECT user, osuser FROM gv_$session WHERE sid = ( SELECT sid FROM gv$mystat WHERE rownum = 1); | |||||||||
Idle Time |
col SID format 999 col IDLE format a20 col PROGRAM format a20 col USERNAME format a20 SELECT sid, osuser, username, status, TO_CHAR(logon_time, 'DAY HH24:MI:SS') LOGON_TIME, FLOOR(last_call_et/3600)||':'|| FLOOR(MOD(last_call_et,3600)/60)||':'|| MOD(MOD(last_call_et,3600),60) IDLE, program FROM v_$session WHERE username IS NOT NULL ORDER BY last_call_et; | |||||||||
User Information |
col program format a17 SELECT sid, serial#, SUBSTR(username,1,10) NAME, SUBSTR(machine,1,10) COMPUTER, command, status, SUBSTR(osuser,1,8) OSUSER, process, program FROM v_$session ORDER BY name; | |||||||||
| Connection Information | set linesize 121 SELECT sid, authentication_type, osuser, network_service_banner FROM gv_$session_connect_info; | |||||||||
Privileged Users |
SELECT * FROM gv$pwfile_users; /* However, for that to be meaningful, you must be using a password file to authenticate privileged users. You could instead choose to use O/S authentication, in which case it's membership of the relevant O/S group that confers 'super user' status on a person (and I've no idea how to code a procedure that would query group memberships for ORA_DBA group (Windows) or dba group (Unix)), if it were actually possible in the first place. What's more, the check of gv$pwdfile_users is only relevant if you're using an exclusive password file (ie, remote_login_passwordfile in the init.ora is set to EXCLUSIVE). If it is instead set to SHARED, then SYS is, and can be, the only privileged user. You then typically let the relevant people know what SYS's password is... and there really isn't a stored procedure which can determine what you happen to have mentioned to assorted members of the DBA team. */ | |||||||||