Create Read Only User in Oracle 10.2.0.4

[Former Member]

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

[stefan_koehler · Accepted solution]

Hello Mahendran,

your description sounds like a license violation with SAP and Oracle.

Please check sapnote #581312

Regards

Stefan

[former_member475159]

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

[lbreddemann]

> 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

[former_member475159]

Hi Lars

Yes you are absolutely write.

But with this query you can view the data, i have just posted in response to the question from Mahendran.

Hi Mahendran,

As suggested by Lars, though my sql will enable you to view the data, but please be aware of the thing as pointed by Lars.

Thanks

Debdeep

[Former Member]

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

[former_member475159]

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

[Former Member]

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

[Former Member]

Hi..,

I tried you suggestions but it list out only little much of tablenames not full list. How to show the full tablenames list.

[Former Member]

Hi..,

I tried your suggestions but it list out only little much of tablenames not full list. How to show the full tablenames list.

[Former Member]

Hi..,Debdeep

I tried your suggestions but it list out only little much of tablenames not full list. How to show the full tablenames list.

Mahendran

[former_member475159]

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

[Former Member]

Hi..Debdeep,

I am not understand your point. What i need it means

I created one user and profile as your suggestions from this user i show only table names until SAPDEV.FAIBSV18 only not showing remaining tables.

Mahendran

[former_member475159]

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

[Former Member]

Hi Mahendra,

run below statment on SQL prompt

select object_name,object_type from all_objects where owner='SAPDEV';

It will give all objects like table,index,view .

SurendraJain

[Former Member]

Hi..Debdeep,

I have one clarification from your sql query .. if i create one user as of your steps means.. is this user only can view the data or may be do any modification or deletion of data is possible from this role's user?

Please confirm because i need read only.

Mahendran

[former_member475159]

Hi Mahendran,

Users will be having only read access if you create the user according to my SQL

THanks

Debdeep

[lbreddemann]

> 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

[stefan_koehler]

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

[lbreddemann]

Hi Stefan,

the key point here is whether SAP application data is accessed or not.

To monitor the Oracle database you don't need to access the application data.

And no one needs 'read all' on it either...

best regards,

Lars

[Former Member]

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