User Creation
to let the user login SQL> GRANT CREATE SESSION TO salapati; Grant succeeded. SQL> assign quota to user on users tablespace for the usre to be able to create tables SQL> ALTER USER salapati 2 QUOTA 100M ON users; User altered. SQL> To give user unlimited space usage on all tablespaces: SQL> GRANT UNLIMITED TABLESPACE TO salapati; Grant succeeded. SQL> example showing how to create a user by explicitly specifying the default tablespaces (temporary and permanent). The GRANT QUOTA clause gives the user a 500MB space allocation in the USERS tablespace so the user can create objects there: SQL> CREATE USER salapati IDENTIFIED BY sammyy1 TEMPORARY TABLESPACE TEMPTBS01 DEFAULT TABLESPACE USERS GRANT QUOTA 500M ON USERS; User created. SQL> change user's password SQL> ALTER USER salapati IDENTIFIED BY sammyy1; User altered. SQL> To drop a user, you use the DROP USER statement, as shown here: SQL> DROP USER salapati; User Dropped. SQL> The DROP USER command will remove not only the user, but all objects owned by the user as well. If other objects in the database depend on this user, you won’t be able to use the simple DROP USER command—you must use the DROP USER . . . CASCADE statement, which drops the user, the user’s schema objects, and any dependent objects as well. Here’s an example: SQL> DROP USER salapati CASCADE; User Dropped. SQL> or simply leave the user and the user’s schema intact, but deny the user access to the database by using the following command: SQL> REVOKE CREATE SESSION FROM salapati; Revoke succeeded. SQL>
SQL> SELECT 'alter user tester identified by values '||password||';' 2 FROM dba_users 3* WHERE username='TESTER'; 'ALTERUSERTESTERIDENTIFIED --------------------------------------------------------- alter user tester identified by values 1825ACAA229030F1; SQL> Now change the password of user tester so you can log in as that user: SQL> ALTER USER tester IDENTIFIED BY newpassword; When you’re done using the tester user account, use the ALTER USER statement again to change user tester’s password back to its original value. Make sure you enclose the encrypted password in single quotes. SQL> ALTER USER tester IDENTIFIED BY VALUES '1825ACAA229030F1'; User altered. SQL> |
OracleDB >