Oracle PL/SQL Tutorial/User Privilege/Create User
Содержание
- 1 Alter user to change the password
- 2 Creates a user named henry and specifies a default and temporary tablespace
- 3 Creates a user named jason with a password of pass
- 4 Create user and assign password
- 5 Create User Utility
- 6 Create user with storage quota
- 7 create user with temporary tablespace, quota and default tablespace
- 8 Creating a User
- 9 Display current user
- 10 User space
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
- If you omit a default tablespace, the default SYSTEM tablespace is used.
- Tablespaces are used by the database to separate objects.
- temp_tablespace specifies the default tablespace where temporary objects are stored.
- If you omit a temporary tablespace, the default SYSTEM tablespace is used.
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>
