ironpen45
asked on
sql server trigger to modify permissions to add records to a table?
is it possible to modify the permissions for a table (allow them to add records when they can't cuz they belong to a group that prohibits) to 2 individuals via a trigger? it only affects one table and should happen when they add records. tia!
ASKER
how can an sp execute the insertion? can an sp override restrictions on a table? doesn't the same apply to a view as well?
If I understand you correcetly, what angel is saying is you assign can permissions to the stored procedure for doing inserts. If a person doesn't have permission to run the procedure for the insert.
OR You can use a view to grant permissions to some people but not others for the insert.
OR You can use a view to grant permissions to some people but not others for the insert.
> how can an sp execute the insertion?
>can an sp override restrictions on a table?
Sample code:
CREATE TABLE dbo.test_table ( data varchar(20))
GO
CREATE PROCEDURE dbo.test_proc ( @data varchar(20))
AS INSERT INTO dbo.test_table (data) select @data
GO
now, grant execute permissions to the user for that procedure, not NO permissions on the table.
confirm that the user cannot insert into the table directly.
let the user run the procedure with a parameter, confirm that after the procedure run, the record got inserted to the table
The explanation:
* the procedure owner (dbo) needs to have the permissions to insert to the table,
* the executor or the procedure does NOT need the permissions to the table, only to execute the procedure.
>can an sp override restrictions on a table?
Sample code:
CREATE TABLE dbo.test_table ( data varchar(20))
GO
CREATE PROCEDURE dbo.test_proc ( @data varchar(20))
AS INSERT INTO dbo.test_table (data) select @data
GO
now, grant execute permissions to the user for that procedure, not NO permissions on the table.
confirm that the user cannot insert into the table directly.
let the user run the procedure with a parameter, confirm that after the procedure run, the record got inserted to the table
The explanation:
* the procedure owner (dbo) needs to have the permissions to insert to the table,
* the executor or the procedure does NOT need the permissions to the table, only to execute the procedure.
ASKER
i c. so, i have an msa mdb front end. the form's record source is the table in question, and i'm 1 of 2 end users who need to enter records to the table via the form.
how can i make this happen? eg, does the record source have to change? on do i need a command button to execute the sp?
how can i make this happen? eg, does the record source have to change? on do i need a command button to execute the sp?
I know that one can connect with MS Access directly to a sql server database, and have done so for some small configuration tables already, but I don't really like this for multi-user / prod applications, at least not with simple-bound forms.
ie, you would have to have the form unbound, and control all the data display and data save operations manually (by code), using the stored procedures.
ie, you would have to have the form unbound, and control all the data display and data save operations manually (by code), using the stored procedures.
ASKER
r u saying that from the unbound form, with the new data in the corresponding controls, execute the sp that will enter the new values into the restricted table? i gather that from ur example, unless i'm misunderstanding u. thanks.
yes, you got that correct!
ASKER
i just thought of an alternative, a bit simpler than an sp:
i can modify the table for full access to all users, but then a trigger will test whether someone is authorized to enter a record into the table. if the end user is not authorized, the entry is rejected.
can anyone help me with a sample trigger?
i can modify the table for full access to all users, but then a trigger will test whether someone is authorized to enter a record into the table. if the end user is not authorized, the entry is rejected.
can anyone help me with a sample trigger?
ASKER
how about it, angelIII? for 300 pts?
create trigger trg_check_permissions
on yourtable
for update
as
IF ( <check permissions of user are not good> )
BEGIN
ROLLBACK
RAISERROR (16,1,'You do not have good permissions' )
END
GO
hope this helps
on yourtable
for update
as
IF ( <check permissions of user are not good> )
BEGIN
ROLLBACK
RAISERROR (16,1,'You do not have good permissions' )
END
GO
hope this helps
ASKER
so my trigger would look like this?:
****************
create trigger trg_check_permissions
on PtblVendor
for insert
as
IF ( user='hhenke' or user='kgrecco' or user='psangabriel' or user='mjperry' )
BEGIN
ROLLBACK
RAISERROR (16,1,'You do not have good permissions' )
END
GO
****************
thanks!
****************
create trigger trg_check_permissions
on PtblVendor
for insert
as
IF ( user='hhenke' or user='kgrecco' or user='psangabriel' or user='mjperry' )
BEGIN
ROLLBACK
RAISERROR (16,1,'You do not have good permissions' )
END
GO
****************
thanks!
ASKER
actually it's allow the 4 users!:
****************
create trigger trg_check_permissions
on PtblVendor
for insert
as
IF ( user<>'hhenke' or user<>'kgrecco' or user<>'psangabriel' or user<>'mjperry' )
BEGIN
ROLLBACK
RAISERROR (16,1,'You do not have good permissions' )
END
GO
****************
thanks!
****************
create trigger trg_check_permissions
on PtblVendor
for insert
as
IF ( user<>'hhenke' or user<>'kgrecco' or user<>'psangabriel' or user<>'mjperry' )
BEGIN
ROLLBACK
RAISERROR (16,1,'You do not have good permissions' )
END
GO
****************
thanks!
ASKER
i tested it an it's giving me an error 'You do not have good permissions'
ASKER
oops, it's:
Error 170: Line 8: Incorrect syntax near 'You do not have good permissions'
Error 170: Line 8: Incorrect syntax near 'You do not have good permissions'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
so, to allow insert on a table, you have to go another way:
* use a stored procedure that does the insert
* create a view to which you can grant the insert