on 07-14-2009 12:12 PM
Hi., Friends,
I want to create an user in Oracle 10.2.0.4 with read only rights of my hole database. I am not having Enterprise Manager Console so i want create from command prompt.Can u please explain me the step for create and assign read only role to user.
Regards
Mahendran
Hello Mahendran,
your description sounds like a license violation with SAP and Oracle.
Please check sapnote #581312
Regards
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Mahendra,
I am happy with Surendrajain's reply, but with this sql you will not able to view the data present in SAP Schema,
The entire sql query with the comments in bracket is given below
1) create user PPMTEST identified by program1;
2) Create role PPMROLE; { PPMROLE is the role name which will be later assigned to the user PPMTEST}
3) Grant CONNECT to PPMROLE; { CONNECT role allows the user to connect to oracle database}
4) Grant SELECT_CATALOG_ROLE to PPMROLE; { SELECT_CATALOG_ROLE role allows the user to view the oracle data dictionary}
5) GRANT SELECT ANY TABLE to PPMROLE; { "SELECT ANY TABLE" privilege allows the user to view the table which is present in the SAP schema}
6) Grant PPMROLE to PPMTEST; { Assigning the role PPMROLE to the user PPMTEST}
7) COMMIT;
Thanks and Regards
Debdeep
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
> 4) Grant SELECT_CATALOG_ROLE to PPMROLE; { SELECT_CATALOG_ROLE role allows the user to view the oracle data dictionary}
> 5) GRANT SELECT ANY TABLE to PPMROLE; { "SELECT ANY TABLE" privilege allows the user to view the table which is present in the SAP schema}
Look there ... data security, access control, SOX complience ... right out of the window...
regards,
Lars
Hi. Debdeep,
Thanks for your reply. And some more thing i have to know in below things,
1) how to alter the password for created user and
2) delete an user.
3) Suppose if we assigned more than one role means how to remove one particular role for an user..
4) view the privileges of newly created role.
Regards
Mahendran
Hi Mahendran,
1) how to alter the password for created user
alter user user_name identified by new_password;
2) delete an user.
drop user user_name cascade;
3) Suppose if we assigned more than one role means how to remove one particular role for an user.
revoke role_name from User_name;
.
4) view the privileges of newly created role.
select privilege from dba_sys_privs where grantee = 'role_name';
Please get back in case you face any issues with the above sql queries.
Thanks and Regards
Debdeep
Edited by: Debdeep Ray on Jul 15, 2009 4:27 PM
Edited by: Debdeep Ray on Jul 15, 2009 4:29 PM
Hi,
Lar's is true but ur answer
1.alter user <username> identified by <Newpassword>;
2.drop user <username>;
3. revoke <roleName> from <username>;
4. see these views
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
drop user user_name cascade; not required because this is read only.
SurendraJain
Edited by: Surendrajain2003 on Jul 15, 2009 4:31 PM
Hi Mahendran,
SELECT TABLE_NAME FROM USER_TABLES will provide you with listing of tables in a particular schema.
SELECT TABLE_NAME, OWNER FROM ALL_TABLES will provide you with listing of all tables for all schemas in the DB
If you need table definitions you may need to tap into USER_TAB_COLUMNS and or ALL_TAB_COLUMNS data dictionary views.
Thanks
Debdeep
Hi Mahendran,
Execute the following sqls
SELECT TABLE_NAME from ALL_TABLES where OWNER = 'SAPDEV';
The above sql will show all the table names under schema SAPDEV
SELECT TABLE_NAME,OWNER from ALL_TABLES;
The above sql will show all the table in the database and in the output under column OWNER you will get to know the schema in which that particular table exists
Thanks
Debdeep
> I want to create an user in Oracle 10.2.0.4 with read only rights of my hole database.
Somehow people seem to be attracted by this 'simple' approach to data access very much.
Please - don't start doing that.
Stop it right away in your own best interest.
There had been many discussions about database level access of SAP databases in the DB-forums here at SDN, just like this one .
To cut the long story short: the SAP data is accessible through the application layer only.
This API performs all kinds of checks, permission management, consistency checking etc.
Don't bypass it. Don't mess up your system with myriads of little data interfaces.
regards,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Lars,
> To cut the long story short: the SAP data is accessible through the application layer only.
Sapnote #581312 disagree with your statement ... just a little quote of the sapnote
As of point 3, it follows that direct access to the Oracle database is only allowed for tools from the areas of system administration and monitoring
But all that political stuff doesn't bother me )
Regards
Stefan
Hi,
what is the purpose of new user in oracle while sap created during installation.
You can create by command
SQL> create user <Username> identified by <Passowrd>;
Now u can give only connect
SQL> grant connect to <Username>;
For Dictionary view
SQL> grant SELECT_CATALOG_ROLE to username;
surendrajain
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.