By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
CREATE USER new_user IDENTIFIED BY new_pass DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
-- 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.