CREATE USER new_user IDENTIFIED BY new_pass DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;Now that the user has been created, you'll want to grant the appropriate roles, system privileges, and object privileges to the user. Run the following commands to generate the GRANT commands you'll need:
-- Grant the roles to the new user. SELECT 'GRANT '||granted_role||' TO new_user;' FROM dba_role_privs WHERE grantee='OLD_USER'; --Grant the system privileges to the new user. SELECT 'GRANT '||privilege||' TO new_user;' FROM dba_sys_privs WHERE grantee='OLD_USER'; --Grant the object privilegs to the new user. SELECT 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO new_user;' FROM dba_tab_privs WHERE grantee='OLD_USER';
The SQL statements above will generate a series of GRANT commands. You can use these generated commands to grant the privileges to the new user.
Dig Deeper on Oracle database design and architecture
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.