 Create a user similar to apps with read-only privileges only [message #175169] |
Thu, 01 June 2006 02:57  |
pmereddy
Messages: 1
Registered: June 2006
|
Junior Member |
|
|
Hi,
Can somebody help me in finding a script to create a user similar to apps with only read privileges. I got a script in orafaq which creates a user which is a carbon copy of another user. But here what I need is to give only read privileges to the new user. HELP!!!
|
|
|
|
| Re: Create a user similar to apps with read-only privileges only [message #175180 is a reply to message #175169] |
Thu, 01 June 2006 03:40  |
manwadkar
Messages: 104
Registered: September 2005
Location: Washington DC
|
Senior Member |
|
|
1. Login to Oracle database (Example: - User Name: - system, Password: - manager)
2. Check whether "USER1" user is already in Oracle database or not. (SELECT * FROM DBA_USERS WHERE USERNAME='USER1');
If user is present and used by any other application, then you need to choose different user name and change this script with that user name. Creating user and granting him “connect access” is one time task (step 1 to 4). You do not need to repeat this when you add/modify new table access to this user.
3. If user "USER1" is not present then at SQL prompt run command "CREATE USER USER1 IDENTIFIED BY USER1;"
4. At SQL prompt run command "GRANT CONNECT TO USER1;"
5. At SQL prompt run command "DROP ROLE ROLE_USER1;". This command removes ROLE_USER1 role from Oracle. If this role is not already in Oracle database this command fails, which is okay.
6. At SQL prompt run command "CREATE ROLE ROLE_USER1;". This command creates ROLE_USER1 role in Oracle. If this role is already in Oracle database this command fails, which is okay.
7. At SQL prompt run following scripts
SCRIPT 1
This script creates a text file with commands to generate PUBLIC SYNONYMS. See step 8 for more details.
SET SQLPROMPT ''
SET ECHO OFF
SET FEEDBACK OFF
SPOOL C:\CREATE_SYNONYM_TABLE.SQL
SET HEADING OFF
SET LINESIZE 200
SELECT 'CREATE OR REPLACE PUBLIC SYNONYM ' ||TABLE_NAME|| ' FOR '||OWNER||'.'||TABLE_NAME||';' FROM SYS.DBA_TABLES WHERE TABLE_NAME IN ('TABLE1','TABLE2');
SPOOL OFF
SET SQLPROMPT SQL>
SCRIPT 2
This script creates a text file with commands to grant SELECT access to ROLE. See step 9 for more details.
SET SQLPROMPT ''
SET ECHO OFF
SET FEEDBACK OFF
SPOOL C:\GRANT_SELECT_TABLE.SQL
SET HEADING OFF
SET LINESIZE 200
SELECT 'GRANT SELECT ON ' ||TABLE_NAME|| ' TO ROLE_USER1;' FROM SYS.DBA_TABLES WHERE TABLE_NAME IN ('TABLE1','TABLE2');
SPOOL OFF
SET SQLPROMPT SQL>
8. At SQL prompt run command "@C:\CREATE_SYNONYM_TABLE.SQL". This sql file has set of commands, which create PUBLIC SYNONYMS.
9. At SQL prompt run command "@C:\GRANT_SELECT_TABLE.SQL". This sql file has set of commands, which grant SELECT access to ROLE.
10. At SQL prompt run command “GRANT ROLE_USER1 TO USER1;”.
If you need to grant SELECT access to additional objects; you may do that running following commands at SQL prompt. You should login as user SYSTEM.
CREATE OR REPLACE PUBLIC SYNONYM <<TABLE_NAME>> FOR <<OWNER.TABLE_NAME>>;
GRANT SELECT ON <<TABLE_NAME|>> TO ROLE_USER1;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
