| Author |
Topic |
|
chevy
Starting Member
11 Posts |
 Posted - 2007-12-18 : 15:40:42
|
| We just brought in TM1 for our budgeting process. One of the things that is needed is an extract from our payroll system. I have written a query that will extract payroll data based on their user id in a table and what departments they have. This works great. However I would like to be able to close the loose end of the user having to have access to the payroll data itself unless they go through a specific process. So I guess my question is this.how can give the user the data they want on demand without giving the user actuall access to the data. I was thinking that a proxy account would do this, but I am not sure how to use one via a stored procedure.Thanks in advance to all |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-18 : 21:24:07
|
| You can create view for the user, let user access view but not base table. |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-12-18 : 21:45:54
|
| Do not allow Select,Update,Delete access to that table for the user group that you want to Limit.Create a procedure with your query, and give the restricted group access to execute the procedure. |
|
|
|
chevy
Starting Member
11 Posts |
Posted - 2007-12-18 : 22:14:24
|
quote:
Originally posted by rmiao
You can create view for the user, let user access view but not base table.
How can I give the user access to the view but not to the underlying table? Don't they need that access? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-18 : 22:40:12
|
| Just grant select permission on the view, it woreks as long as view and table have same owner. |
|
|
|
chevy
Starting Member
11 Posts |
Posted - 2007-12-19 : 08:31:54
|
quote:
Originally posted by rmiao
Just grant select permission on the view, it woreks as long as view and table have same owner.
The problem with allowing them to view the data in the table, is that they would have access to all payroll data. I want to limit them to what they can see. Ie CIO can only see people that work for him, manger of engineering can only see people that work for him. |
|
|
|
chevy
Starting Member
11 Posts |
Posted - 2007-12-19 : 09:22:42
|
quote:
Originally posted by Vinnie881
Do not allow Select,Update,Delete access to that table for the user group that you want to Limit.Create a procedure with your query, and give the restricted group access to execute the procedure.
When I pulled the access to the database for those users and ran my procedure, they stopped working. It gives them an error that they do not have permissions to select data from the database. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-19 : 14:53:10
|
| Does your sp have same owner as base table does? Did you grant exec permission on the sp to users? |
|
|
|
chevy
Starting Member
11 Posts |
Posted - 2007-12-19 : 14:58:55
|
quote:
Originally posted by rmiao
Does your sp have same owner as base table does? Did you grant exec permission on the sp to users?
it does not because there could be many people that run this. I suppose i am not sure a stored procedure is the best approach. I am looking for any solution that provides me a means to get the user the data that belongs to them without the user having access to the whole entire table. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-19 : 15:34:30
|
| Object owner is nothing to do on who will use it. You can use either view or sp as mentioned above for this. |
|
|
|
chevy
Starting Member
11 Posts |
Posted - 2007-12-20 : 10:08:53
|
quote:
Originally posted by rmiao
Object owner is nothing to do on who will use it. You can use either view or sp as mentioned above for this.
i figured out my problem. the view i was creating was actually in a different database than the table that holds the data. once i put an extract of the data into the same database it worked fine. now my problem is how do i restrict access to the table by use of excel? |
|
|
|
chevy
Starting Member
11 Posts |
Posted - 2007-12-20 : 11:09:21
|
quote:
Originally posted by chevy
quote:
Originally posted by rmiao
Object owner is nothing to do on who will use it. You can use either view or sp as mentioned above for this.
i figured out my problem. the view i was creating was actually in a different database than the table that holds the data. once i put an extract of the data into the same database it worked fine. now my problem is how do i restrict access to the table by use of excel?
Actually i got this to work as well. it seems that since i was using active directory security, the change i made did not take effect until they logged out and back in. thank you for all your help. |
|
|
|
|
Can a proxy be used like this?