Managing the access control in database

[Carnie]

I am using oracle database on linux operating system and with the SYSTEM privilege.Recently,I created a function and grant this function to the SCOTT user of the database but he reported that he can access the privileges that I gave him.What the problem may with him to cause this type of error.I will be highly obliged to you support for this answer.

Thanks.

[Praetor]

Managing the access control =>

You are running as a SYSTEM user in your database and gave some privileges to the SCOTT user but what the problem he has you didn't discuss here then how can we able to give the answer,At least provide me some reported error to discuss .

But If he is getting problem then there may be different reasons.May be some changes in the database done by SYS user or may be the table space related errors ,etc .

[Katty]

Privileges granting error

The privileges that you have granted to the SCOTT user that may be not done successfully.This may be one of the reason to access problem of database object .

You have to use the below command to grant the privileges on any object to any user of database :

SQL> GRANT <List of privileges>ON<Object_name>TO <User_name>;
That was a simple synopsis to grant just look below with an example:

SQL> GRANT SELECT,UPDATE,DELETE ON CALCULATE_FUN TO SCOTT;

[kelfro]

If the SCOTT has tablespace related problem then that would be an administrative task to available the data.

In this case,Try to access yourself and make sure any tablespace related error is occurring or not.May be some long operation is going on with the tablespace and the database administrator makes the tablespace in read only mode or offline mode .

To solve this issue,you need to contact with database administrator and if you have success privilege on the tablespace then disable the restriction and made available to the user SCOTT to access that object.

Convert the tablespace in WRITE and ONLINE mode using the following command :

SQL> ALTER TABLESPACE <tablespace_name>READ WRITE;
SQL> ALTER TABLESPACE <tablespace_name>ONLINE;

[Zecho]

User access control -

The error may be from the user control where user is not configured properly and need to check the status of his account.

When a user is created a default profile is assigned to the user from the database and when a user is working on organization standard then they use different privilege as per the organization rule.May be the user has done some mistake which goes beyond the rules of profile or role.

You need to check his status and made the required changes.May be his account has been locked so that you need to activate his account again with the ALTER USER command to unlock the status .