в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:43Z

1 версия

Новая страница

== Alter user to change the password==



<source lang="sql">
SQL>
SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> ALTER USER Alice IDENTIFIED BY complicatedpassword;
User altered.
SQL>
SQL>
SQL> drop user alice;
User dropped.
SQL>
SQL></source>

== Creates a user named henry and specifies a default and temporary tablespace==



<source lang="sql">
SQL>
SQL> CREATE USER henry IDENTIFIED BY pass
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE temp;
User created.
SQL>
SQL> DROP USER henry;
User dropped.</source>

== Creates a user named jason with a password of pass==



<source lang="sql">
SQL>
SQL> --CONNECT system/manager
SQL> CREATE USER jason IDENTIFIED BY pass;
User created.
SQL>
SQL> DROP USER jason;
User dropped.</source>

== Create user and assign password==



<source lang="sql">
SQL> create user user1 identified by VALUES "1A2DD3CCEE354DFA";
SQL>
SQL>
SQL> drop user user1;</source>

== Create User Utility==



<source lang="sql">
/*
* CreateUser.sql
* Chapter 3, Oracle10g PL/SQL Programming
* by Ron Hardman, Mike McLaughlin, and Scott Urman
*
* This script creates the plsql user for chapter 3
* examples. You must run this script as SYS or
* SYSTEM as SYSDBA. The script can be rerun.
*
* The plsql user is created using the USERS and TEMP tablespace.
* If these tablespaces do not exist in your environment, change
* the script to use an appropriate tablespace.
*
* Modify the conn_string value below, providing your Net Service
* Name if not using your default.
*/
DEF conn_string = plsql/oracle
DEF username = plsql
DEF default_ts = USERS
DEF temp_ts = TEMP
SET FEEDBACK OFF SERVEROUTPUT ON VERIFY OFF TERMOUT OFF
SPOOL CreateUser.log
DECLARE
v_count INTEGER := 0;
v_statement VARCHAR2 (200);
BEGIN
SELECT COUNT (1)
INTO v_count
FROM dba_users
WHERE username = UPPER ("&username");
IF v_count != 0
THEN
EXECUTE IMMEDIATE ("DROP USER &username CASCADE");
END IF;
v_statement :=
"CREATE USER &username IDENTIFIED BY oracle"
|| " DEFAULT TABLESPACE &default_ts"
|| " TEMPORARY TABLESPACE &temp_ts"
|| " QUOTA UNLIMITED ON &default_ts"
|| " ACCOUNT UNLOCK";
EXECUTE IMMEDIATE (v_statement);
-- Grant permissions
EXECUTE IMMEDIATE ("GRANT create session TO &username");
EXECUTE IMMEDIATE ("GRANT connect TO &username");
EXECUTE IMMEDIATE ("GRANT create procedure TO &username");
EXECUTE IMMEDIATE ("GRANT execute ON utl_ref TO &username");
EXECUTE IMMEDIATE ("GRANT create trigger TO &username");
EXECUTE IMMEDIATE ("GRANT resource TO &username");
DBMS_OUTPUT.put_line (" ");
DBMS_OUTPUT.put_line ("User &username created successfully");
DBMS_OUTPUT.put_line (" ");
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
DBMS_OUTPUT.put_line (" ");
END;
/
SET FEEDBACK ON TERMOUT ON
PROMPT
PROMPT ******** Connecting to user &username!! ********
PROMPT
CONN &conn_string
CREATE OR REPLACE PACKAGE clean_schema IS
PROCEDURE trigs;
PROCEDURE tables;
PROCEDURE procs;
END;
/

CREATE OR REPLACE PACKAGE BODY clean_schema AS
PROCEDURE trigs
IS
v_string VARCHAR2(50);
v_tab_error EXCEPTION;
PRAGMA EXCEPTION_INIT(v_tab_error, -4080);
BEGIN
BEGIN
v_string := "DROP TRIGGER author_trig";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_tab_error
THEN
NULL;
END;
END trigs;

PROCEDURE tables
IS
v_string VARCHAR2(50);
v_tab_error EXCEPTION;
PRAGMA EXCEPTION_INIT(v_tab_error, -942);
BEGIN
BEGIN
v_string := "DROP TABLE precision";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_tab_error
THEN
NULL;
END;
BEGIN
v_string := "DROP TABLE inventory";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_tab_error
THEN
NULL;
END;
BEGIN
v_string := "DROP TABLE books";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_tab_error
THEN
NULL;
END;
BEGIN
v_string := "DROP TABLE authors";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_tab_error
THEN
NULL;
END;
END TABLES;
PROCEDURE procs
IS
v_string VARCHAR2(50);
v_proc_error EXCEPTION;
PRAGMA EXCEPTION_INIT(v_proc_error, -4043);
BEGIN
BEGIN
v_string := "DROP PROCEDURE compile_error";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := "DROP PROCEDURE compile_warning";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := "DROP PROCEDURE named_block";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := "DROP PROCEDURE book_ins";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := "DROP PROCEDURE authors_sel";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := "DROP PROCEDURE bind_test";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
BEGIN
v_string := "DROP PROCEDURE author_book_count";
EXECUTE IMMEDIATE (v_string);
EXCEPTION
WHEN v_proc_error
THEN
NULL;
END;
END procs;
END;
/

spool off</source>

== Create user with storage quota==



<source lang="sql">
create user hr_audit
identified by hr_audit
/
grant create session, resource
to hr_audit
/
alter user hr_audit
identified by oracle;
alter user hr_audit
quota unlimited on users
quota 10M on temp
quota 0M on system;</source>

== create user with temporary tablespace, quota and default tablespace==



<source lang="sql">
SQL> create user dropme
2 identified by pass
3 default tablespace users
4 temporary tablespace temp
5 quota unlimited on users;
User created.
SQL>
SQL> grant create session, create table
2 to dropme;
Grant succeeded.
SQL>
SQL>
SQL>
SQL> --drop user dropme;
SQL> drop user dropme cascade;
User dropped.
SQL>
SQL></source>

== Creating a User==

To create a user in the database, you use the CREATE USER statement.
The simplified syntax for the CREATE USER statement is as follows:



<source lang="sql">
CREATE USER user_name IDENTIFIED BY password
[DEFAULT TABLESPACE default_tablespace]
[TEMPORARY TABLESPACE temp_tablespace];</source>

where
<OL><LI>If you omit a default tablespace, the default SYSTEM tablespace is used.</LI><LI>Tablespaces are used by the database to separate objects.</LI><LI>temp_tablespace specifies the default tablespace where temporary objects are stored.</LI><LI>If you omit a temporary tablespace, the default SYSTEM tablespace is used.</LI></OL>

== Display current user==



<source lang="sql">
SQL>
SQL>
SQL> show user
USER is "sqle"
SQL></source>

== User space==



<source lang="sql">
create user hr_audit
identified by hr_audit
/
grant create session, resource
to hr_audit
/
alter user hr_audit
identified by oracle;
alter user hr_audit
default tablespace users
temporary tablespace temp;

SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/User Privilege/Create User - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия